Copy rows from one table to another with identity columns

Question

Post reply

Copy rows from one table to another with identity columns

dhanalakshmi 99938

Mr or Mrs. 500

Points: 594
More actions
October 4, 2011 at 3:26 am

#149695

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

handkot SSCertifiable Points: 5249 More actions · Answer 1

SET IDENTITY_INSERT

Allows explicit values to be inserted into the identity column of a table.

I Have Nine Lives You Have One Only
THINK!

dhanalakshmi 99938 Mr or Mrs. 500 Points: 594 More actions · Answer 2

Thanks

tableA and tableB are in separate databases. Both will have some rows in them already.

But if we set SET IDENTITY_INSERT, When inserting in tableB, if the newly picked ItemID from tableA already is in tableB for some other row, then will it not throw an error?

handkot SSCertifiable Points: 5249 More actions · Answer 3

if

Both will have some rows in them already

it will throw an error

I Have Nine Lives You Have One Only
THINK!

handkot SSCertifiable Points: 5249 More actions · Answer 4

I see only one solution is to add another column to TableA of destination server

Copy from DB1 to DB2

Insert Into TableA(... , DB1_ItemID )

Select ..., ItemID From DB1..dbo.TableA

Insert Into TableB(...)

Select a.ItemID, ... From DB1..dbo.TableB b

Inner Join dbo.TableA a On a.DB1_ItemID = b.ItemID

I Have Nine Lives You Have One Only
THINK!

dhanalakshmi 99938 Mr or Mrs. 500 Points: 594 More actions · Answer 5

I searched in many forums, but could not find any solution. It could not be done in a single query. The only best method is to use stored procedure or a function.

Anyway, Thanks for the help

babu.ece SSC Veteran Points: 298 More actions · Answer 6

Using Nested SQL queries you can do this I think but I don't have correct solution for your problem.

Mobile Application Development|mhealth