How to copy multiple records from a table to a new database

Question

0.00/5 (No votes)

See more:

Hi,

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)

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

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

Posted 28-Sep-21 6:42am

Member 14800672

Updated 28-Sep-21 6:58am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2021-09-28T06:58:00

Solution 1

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.

SQL

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;

Posted 28-Sep-21 6:58am

Richard Deeming

Comments

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.