• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 285
  • Last Modified:

MSSQl / TSQl Syntax

I need to perform a Bulk Insert from 3 tables into 3 other tables. However my problem is that Once Insert rows into the first table I need to get the ID as its  the Foriegn key for the other 2 tables

DB1 - TableA, TableB, TableC
DB2    TableA,TableB,TableC

EG:

If it was just one table I would simply do

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

This works for one table.... but with the other 2 tables the foriegn key changes when rows are inserted into TableA.

How can I get the New ID value as we insert a single row and then insert data into the other 2 tabels.
0
isaackhazi
Asked:
isaackhazi
  • 3
  • 2
  • 2
  • +3
1 Solution
 
lwadwellCommented:
The system function @@IDENTITY holds the last created identity by insert; refer to
http://msdn.microsoft.com/en-us/library/ms187342.aspx
0
 
Pratima PharandeCommented:
Here you need to explain us the table structure to code correct query
I will give you one example

Table A having olumns
ID_TB1
NAME_TB1

Table B
ID_TB2
ID_TB1
Name_TB2

Table C
ID_TB3
ID_TB1
Name_TB3

Then Queries are

For Table A a

Insert INTO DB1.dbo.TableA(Name_TB1)
SELECT Name_TB1 FROM DB2.TableA


For Table B

Insert INTO DB1.dbo.TableB(ID_TB1, Name_TB2)
SELECT A_new.ID_TB1,B.Name_TB2 FROM DB1.TableA A_new
Inner join DB2.TableA A_old on A_New.Name_TB1 = A_old.Name_TB1
Inner join DB2.TableB B on A_old.ID_TB1 = B.ID_TB1


For Table C

Insert INTO DB1.dbo.TableC(ID_TB1, Name_TB3)
SELECT A_new.ID_TB1,C.Name_TB3 FROM DB1.TableA A_new
Inner join DB2.TableA A_old on A_New.Name_TB1 = A_old.Name_TB1
Inner join DB2.TableC C on A_old.ID_TB1 = C.ID_TB1
0
 
isaackhaziAuthor Commented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Pratima PharandeCommented:
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
0
 
sameer_goyalCommented:
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.
0
 
isaackhaziAuthor Commented:
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...
0
 
Pratima PharandeCommented:
have you tried my solution ?
It is the same as per your rquirment
let us know if any problem in same
0
 
sameer_goyalCommented:
ok. in that case, what pratima_mcs suggested is the thing to do.

i am afraid, there is no other straight forward way then
0
 
Frank_BaninCommented:
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
0
 
Alpesh PatelAssistant ConsultantCommented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now