MSSQl / TSQl Syntax

The system function @@IDENTITY holds the last created identity by insert; refer to
http://msdn.microsoft.com/en-us/library/ms187342.aspx

Ok So if my table structures are as follows:

tblPerson
Person_id, name , tel, fax, email

tblJobs
job_id,person_id,job_name,job_title

tblEducation
education_id,person_id,education_degree,education_year

Here person_id is the PK of tblPerson and the Foriegn key for tblJobs and tblEducation

tblJObs and tbleducation follows a Many to 1 relationship with tblPerson. Where tblPerson is the Master table.

tblPerson
Person_id, name , tel, fax, email

tblJobs
job_id,person_id,job_name,job_title

tblEducation
education_id,person_id,education_degree,education_year

For tblPerson -- here Person_Id im PK will genrate automatically as per your info

Insert into DB1.dbo.tblPerson(name , tel, fax, email)
Select name , tel, fax, email from DB2.dbo.tblPerson

For tblJobs
Insert into DB1.dbo.tblJobs(person_id,job_name,job_title)
select P_new.person_id,job.job_name,job.job_title from DB1.dbo.tblPerson P_new
Inner join DB2.dbo.tblPerson P_old on P_new.name =P_old.name and P_new.email=P_old.email
Inner join DB2.dbo.tbljobs job on job.person_id= P_old.person_id

For tblEducation
Insert into DB1.dbo.tblEducation(person_id,education_degree,education_year)
select P_new.person_id,Edu.education_degree,Edu.education_year from DB1.dbo.tblPerson P_new
Inner join DB2.dbo.tblPerson P_old on P_new.name =P_old.name and P_new.email=P_old.email
Inner join DB2.dbo.tblEducation Edu on Edu.person_id= P_old.person_id

Are you using Identity for your keys in the 3 tables? I mean, is person_id in table 1 your primary key and is an auto-incrmenting identity?

If yes, and you want your key from the source to remain the same in the destination table, you can do that too.

So, when you are doing insert from srctable1 to destTable1, do this

USE DB1
GO

SET IDENTITY_INSERT dbo.TableA ON
GO

Insert INTO DB1.dbo.TableA(column1,column2....)
SELECT column1,column2.. FROM DB2.TableA


This will allow inserting the existing key value from your source table to the destination one and then you can simply use the INSERT into command for your other 2 tables

hope it helps.

Yes person_id is PK auto increment....

Dont really want the source and destination PK to be the same as the destination already has rows in it...

have you tried my solution ?
It is the same as per your rquirment
let us know if any problem in same

ok. in that case, what pratima_mcs suggested is the thing to do.

i am afraid, there is no other straight forward way then

This is what you prob want to do. Output only the inserted records from TableA into a table varaible @MyHoldingTableVar after every insert operation and use the result anyhow you want it, ie. if you want to join or union it to tableB.


CREATE TABLE dbo.table1
(
    id int identity(1,1) ,
      Empid INT,
    employee VARCHAR(32)
)
go

DECLARE @MyHoldingTableVar TABLE
(
    id int ,
      Empid INT,
    employee VARCHAR(32)
);


INSERT INTO dbo.table1
   -- OUTPUT INSERTED.id, INSERTED.Empid, INSERTED.employee
      OUTPUT INSERTED.*
        INTO @MyHoldingTableVar

VALUES (5, 'Fred')
     ,(6, 'Tom')
     ,(7, 'Sally')
     ,(8, 'Alice');

select * from @MyHoldingTableVar

You can create SSIS package and do that. using various tasks.


You have to use data flow taks-> Source and DEstination connection + some transformation task to do that.