Transfer data from one database to another without identities but keep the relation b/w PK and Foreign key

You can simply do this

USE DB2
GO
DECLARE @INSERTED_TABLEA
(
Col1 int IDENTITY(1,1) NOT NULL,
Col2 <datatype here>,
Col3 <datatype here>
)
DECLARE @INSERTED_TABLEB
(
Col1 int IDENTITY(1,1) NOT NULL,
Col2 <datatype here>,
Col3 <datatype here>
)

DECLARE @INSERTED_TABLEC
(
Col1 int IDENTITY(1,1) NOT NULL,
Col2 <datatype here>,
Col3 <datatype here>
)
INSERT TableA(Col2,Col3)
OUTPUT INSERTED.Col1,INSERTED.Col2,INSERTED.Col3 INTO @INSERTED_TABLEA
SELECT Col2,Col3
FROM DB1.dbo.TableA


INSERT TableB(Col2,Col3)
OUTPUT INSERTED.Col1,INSERTED.Col2,INSERTED.Col3 INTO @INSERTED_TABLEB
SELECT a1.Col1,Col3
FROM DB1.dbo.TableB  b
INNER JOIN DB1.dbo.TableA a
ON a.Col1 = b.Col2
INNER JOIN @INSERTED_TABLEA a1
ON a1.Col2 = a.Col2
AND a1.Col3 = a.Col3



INSERT TableC(Col2,Col3)
OUTPUT INSERTED.Col1,INSERTED.Col2,INSERTED.Col3 INTO @INSERTED_TABLEC
SELECT a1.Col1,Col3
FROM DB1.dbo.TableC  b
INNER JOIN DB1.dbo.TableB a
ON a.Col1 = b.Col2
INNER JOIN @INSERTED_TABLEB a1
ON a1.Col2 = a.Col2
AND a1.Col3 = a.Col3

INSERT TableD(Col2,Col3)
SELECT a1.Col1,Col3
FROM DB1.dbo.TableD  b
INNER JOIN DB1.dbo.TableC a
ON a.Col1 = b.Col2
INNER JOIN @INSERTED_TABLEC a1
ON a1.Col2 = a.Col2
AND a1.Col3 = a.Col3

DB2 is your destination database and DB1 is your source database

I assume both databases are in same server otherwise you need to use linked server for the above

http://ift.tt/OK5nC4

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://ift.tt/19nLNVq http://ift.tt/1iEAj0c