SQL Help?

[berderder]

Hi everyone,

 

I wasn't sure where this fit so I point it in the Programming subforum.

 

How do I populate a table with its entire contents?

 

I have an Employee table and I want to fill it with 5 rows of information regarding Employee ID, EmployeeName, etc. and then five iterations of those subjects. 

 

Do I have to use multiple insert statements?

 

I am using Access 2013. 

 

Thank you for any help you can give. 

[Mr_KoKa]

You can specify multiple sets of data after coma like

INSERT INTO employee (col1, col2, col3) VALUES (1, 'a', 1), (2, 'b', 2), (3, 'c', 3), (4, 'd', 4), (5, 'e', 5)

 

[berderder]

3 hours ago, Mr_KoKa said:

You can specify multiple sets of data after coma like

INSERT INTO employee (col1, col2, col3) VALUES (1, 'a', 1), (2, 'b', 2), (3, 'c', 3), (4, 'd', 4), (5, 'e', 5)

 

This is exactly what I needed - thanks! I'll mark as answered when I finish the homework tomorrow 

[berderder]

20 hours ago, Mr_KoKa said:

You can specify multiple sets of data after coma like

INSERT INTO employee (col1, col2, col3) VALUES (1, 'a', 1), (2, 'b', 2), (3, 'c', 3), (4, 'd', 4), (5, 'e', 5)

 

Hmm, it's actually not compiling. It says I am missing a semicolon after the first set of INSERT values. 

 

In your case, that would mean it saying I need a semicolon after (1, 'a', 1)

 

However, when I do that, it no longer recognizes anything else after the semicolon

[Mr_KoKa]

Yeah, I assumed it is very basic feature, but it seems unavailable in Access. Sorry.

I've read that you can only insert multiple row by selecting them from another table.

https://msdn.microsoft.com/en-us/library/ff834799.aspx

 

Just to be clear, that query would work with SQL server, but not Access.

[berderder]

1 minute ago, Mr_KoKa said:

Yeah, I assumed it is very basic feature, but it seems unavailable in Access. Sorry.

I've read that you can only insert multiple row by selecting them from another table.

https://msdn.microsoft.com/en-us/library/ff834799.aspx

Oh, ok, no problem. I think you may be right. I think I read that somewhere too. 

[berderder]

2 minutes ago, Mr_KoKa said:

Yeah, I assumed it is very basic feature, but it seems unavailable in Access. Sorry.

I've read that you can only insert multiple row by selecting them from another table.

https://msdn.microsoft.com/en-us/library/ff834799.aspx

If I filled in a data table, do you know what the necessary SQL statement would look like? I have to prove to the teacher for my assignment that I can use an INSERT SQL statement to populate a table.

[Mr_KoKa]

I don't have access to Access (:D) But i guess that you have to have second table that has at leas same columns as first one.

So let's say you have Employee table with columns (id, name, surname, email)

And second table NewEmployees with columns (id, name, surname, email) so Access can know what put where.

My guess is that NewEmployees table can have more columns, but have to have at least those so it can match with Employee table.

 

And the query for (I would call it import) insert all employees from NewEmployees tablo to Employee would look like:

INSERT INTO Employee SELECT * FROM NewEmployees;

I guess that if you have additional columns in NewEmployees, you need to instead of * list columns separated by comas, that will mach exactly what you can insert into Employee table.

 

I wonder if you can add WHERE at the end, to only add those new employees that meet WHERE criteria.

 

So, yeah, those are all guesses as I cannot check if those works.

[berderder]

2 minutes ago, Mr_KoKa said:

I don't have access to Access (:D) But i guess that you have to have second table that has at leas same columns as first one.

So let's say you have Employee table with columns (id, name, surname, email)

And second table NewEmployees with columns (id, name, surname, email) so Access can know what put where.

My guess is that NewEmployees table can have more columns, but have to have at least those so it can match with Employee table.

 

And the query for (I would call it import) insert all employees from NewEmployees tablo to Employee would look like:

INSERT INTO Employee SELECT * FROM NewEmployees;

I guess that if you have additional columns in NewEmployees, you need to instead of * list columns separated by comas, that will mach exactly what you can insert into Employee table.

 

I wonder if you can add WHERE at the end, to only add those new employees that meet WHERE criteria.

 

So, yeah, those are all guesses as I cannot check if those works.

OK, great, I will give those a shot. Thanks.