Click here to Skip to main content
15,617,203 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

so i have this table (Table1) in database called DB_FROM

Id field1 
2    hi     
4    bye    
5    hello  

where Id is an identity field

Also Table2 (EL is identity)
EL       E1 
1        1     
2        2    
3        3 

Also Table3 (Table3.Id = Table1.Id and Table3.E1 = Table2.E1)
Id E1 
2   1     
4   1    
5   2 

now I need to copy the data in Table1 and Table3 to new Database called DB_To

I need to copy all field values except for the identity fields it needs to be generated
by itself in the new database

so Table1 in the new database, should have these values (Id is identity in this new database)

Id field1 
1    hi     
2    bye    
3    hello 

and Table3 should look like this
Id E1 
1   1     
2   1    
3   2 

table2 is already inserted in the new database with the same values as the old one

What I have tried:

i tried this:

insert into DB_TO.dbo.Table1 select *from DB_FROM.dbo.Table1 where Id in (select Table3.Id from DB_FROM.dbo.Table3 join DB_from.dbo.Table2 on Table2.E1 = Table3.E1
where Table1.EL = 1

However, it's not inserting multiple records
Updated 28-Sep-21 6:58am

1 solution

To insert data into two tables, you'll need two INSERT statements.

If you don't want to copy the identity column over, then you'll need to list the columns explicitly rather than using SELECT * FROM.

Your WHERE clause appears to have nothing to do with your stated requirement.
INSERT INTO DB_TO.dbo.Table1 (field1) SELECT field1 FROM DB_FROM.dbo.Table1;
INSERT INTO DB_TO.dbo.Table3 (E1) SELECT E1 FROM DB_FROM.dbo.Table3;
Share this answer
Member 14800672 28-Sep-21 16:32pm    
Hi Richard,
The where statement was just to specify what to insert

But does this statement insert multiple records.. As you can see i need to copy 3 recrods from table1 in old db to new db
Richard Deeming 29-Sep-21 3:40am    
Yes, the two statements insert all of the records from the source table into the destination table.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900