How to copy rows from one table to another with same structure

[dha lak]

hi,

I need to copy tableA and tableB from database1 to database2. Both table has the same structure. Only PlanID 1 and PlanID 2's values should be copied
tableA is copied, but tableB is a bit confusing.


DATABASE1
tableA
ItemID | PlanID | col3 | col4 | col5 ......
------ | -------|------|------|----------
21 | 1
22 | 1
23 | 1
24 | 1
25 | 1
26 | 2
27 | 2
28 | 2
29 | 3
30 | 3


tableB
ID | ItemID | col3 | col4 | col5 ......
-- | -------|------|------|-----------
11 | 21
12 | 21
13 | 22
14 | 22
15 | 23
16 | 23



DATABASE2
This is how it should be copied
tableA
ItemID | PlanID | col3 | col4 | col5 ......
------ | -------|------|------|----------
1 | 100
2 | 100
3 | 100
4 | 100
5 | 100
6 | 200
7 | 200
8 | 200


tableB
ID | ItemID | col3 | col4 | col5 ......
-- | -------|------|------|-----------
1 | 1
2 | 1
3 | 2
4 | 2
5 | 3
6 | 3


ItemID -> identity key for tableA
ID -> identity key for tableB
ItemID -> is foreign key in tableB
PlanID -> identity key for Plan table, which i have not given here.

Also, after copying from db1 to db2, other columns(col3,col4....) for tableB in db2 should be same(only the identity field values may be different) as the columns for tableB in db1

Similarly, other columns(col3,col4....) for tableA in db2 should be same(only the identity field values may be different) as the columns for tableA in db1

Is there any way to achieve this using a single Query?
i tried this, but, how to get the ItemID value from tableA

select @ItemID = ?, col3,col4....
from ..db1.tableB
into ...db2.tableB
left join ???
where PlanID in (1,2)

[invoked]

The best advice I can give is download the SSMS tools found here,
www.ssmstoolspack.com

Once installed, you should be able to right click on a specific table and then see options for SSMS tools. Hover over the SSMS tools and there will be an option to Generate Insert Statements. If both of your tables already have the data you need in DB1, and the table structures have already been created in the new database DB2, then this should work perfectly. If the tables are not already created in new database, SQL has its own 'Script table as' tool that will generate the table creation scripts for you. You would do that first and then do the SSMS tools scripting.

Good luck!

[ck9663]

Yes you can. You have to SELECT INTO TargetTable. The list of columns you will select will not include the identity column from the source table. Instead, create a new column using this function.


Good Luck!!!


~~ CK

[dha lak]

Thank you.
But i want to do the whole transfer in a single query without using stored procedures. This query will be called from csharp console application. Or is it possible to create a temporary stored procedure and delete it once the transfer is complete.

[ck9663]

There are no stored proc in my suggestion. The link I gave you is a built-in function.

Happy Coding!!!

~~ CK

[dha lak]

I searched for a solution in may forums.
But seems like it can't be done with a single query.

The only way is to use a stored procedure or function.

Thanks for the help

[Rabbit]

Just multiply by 100 for table a and subtract 20 for table b.

[ck9663]

Let me see if I got it right.

You have two tables, TableA and TableB.

You want to copy those two tables with all the columns into another server. However the ItemId and ID columns will have a new (restarted) value.

All of that using a single query. Is that right?


~~ CK