Copy rows from one table to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Copy rows from one table to another

Author

Topic

dhanlak
Starting Member

5 Posts

Posted - 2011-10-04 : 11:27:04

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)

Please does anyone know how to solve this? Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 11:40:40

didnt get how all PlanID values became 100 in tableA DATABASE2. Can you explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources