Insert TableA into TableB 1

[benlinkknilneb]

Hi all,

If I have TableA and TableB, and the only difference between them is an autonumber field (it's in A, not in B), how do I add each record from B into A in a query? In Access, there is an "Append" query that looks like this:

INSERT INTO TableA( a, b, c, d, e )
SELECT A, B, C, D, E FROM TableB;

...but I don't know how to set it up in SQL Server (2k)...

Ben

 

[ecobb]

You should be able to use the same query in SQL Server.

 

[ClaireHCM]

That's correct syntax.
But if you have a autonumbered column in Table A and that is the only different column from Table B.You dont need to specify all the column names in A and B

You can query like this

insert into tableA select * from tableB

 

[benlinkknilneb]

That's what I thought... but I got an error when I tried to run it. Maybe I'm not setting this up totally right... but it gave me the SQL statement to fill in that looked like this:

INSERT INTO [tblName] ()
VALUES ()

and when I tried to replace the "VALUES" part with a SELECT statement, it gave me a syntax error. If I just put a reference to the table into the VALUES part, it says that I can't have field names in that statement. What am I doing wrong?

 

[wildsol]

Make sure that when you are inserting into table A that you are not including the autonumber field for the table in the insert statement.

from what you originally posted:

INSERT INTO TableA( a, b, c, d, e )
SELECT A, B, C, D, E FROM TableB

If field x was the autonumberfield for table A, you do not include that in the field list for the insert statement.

 

[roccorocks]

What is the error? Exact description. Where are you running this? In SQL Query Analyzer? Is one of the fields trying to write to the Autonumber field? Maybe you do need to name all the fields, as not to select the Autonumber field? I will test this out.

Thanks,

rocco

 

[TheFrog]

use before your insert the expression in SQL Server to permit insert in autonumber field

SET IDENTITY_INSERT tableA ON

INSERT INTO TableA( a, b, c, d, e ) SELECT A, B, C, D, E FROM TableB;

SET IDENTITY_INSERT tableA ON

 

[benlinkknilneb]

Thanks Frog, that was just what I needed... have a star!

Ben

 

[SQLSister]

It is not generally a good idea to turn identity insert on unless you are importing large quamties of data from another source to initially populate the table. Otherwise the purpose of the autonumbering is lost.

If you are periodically performing this insert and records will be coming in from other sources, then you will have problems with the import eventually as the record coming from table b will have an identical ID to one that was inserted from another source.

If you are only populating from table B, then you need to just do away with the autonumber.

If you want to copy records from b to a and insert records other ways, the insert statement should just not include the field name for the autonumbered field, so that SQL server will assign the autonumber properly.

If this is a one time insert then the statement you used is fine.