CODEWITHSUNDEEP
sqlsql-server-2008sql-insert
ScottGuymer
ScottGuymer

Reputation: 1905

Performing multiple inserts for a single row in a query

I have a table containing data that i need to migrate into another table with a linking table. This is a one time migration as part of an upgrade.

I have a company table that contains records relating to a company and a contact person. I want to migrate the contact details into another table and link the new person with a linking table

Consider I have this table which is already populated

tblCompany

And i want to migrate the contact person data to

tblPerson

and use the identity column resulting and insert it into the linking table

tblCompanyPerson

I've tried a few different ways to approach this using cursors and output variables into a temp table but none seem right to me (or give me the solution...)

The closest i have got is to have a companyID on tblPerson and insert companyId into it and output the new personId and the companyId into a temp table. Then loop through the temp table to create the tblCompanyContact.

example

declare @companycontact TABLE (companyId int, PersonId int)

insert into tblPerson
    (Forename,
    Surname,
    CompanyID)
output inserted.CompanyID, INSERTED.PersonID into @companycontact
select  
    ContactPersonForeName, 
    ContactPersonSurename,  
    CompanyID
from tblCompany c


insert into tblCompanyPerson
    (CompanyID,
    PersonID)
select c.companyId, PersonId from @companycontact c

Background

Upvotes: 0

Views: 144

Answers (2)

Nenad Zivkovic
Nenad Zivkovic

Reputation: 18559

There is a 'trick' using MERGE statement to achieve mapping between newly inserted and source values:

MERGE tblPerson trgt
USING tblCompany src ON 1=0
WHEN NOT MATCHED 
THEN INSERT 
    (Forename, Surename)
    VALUES (src.ContactPersonForeName, src.ContactPersonSurename)
OUTPUT  src.CompanyID, INSERTED.PersonID
INTO  tblCompanyPerson (CompanyId, PersonID);

That 1=0 condition is to always get everything from source. You might want to replace it or even whole source with some sub-query to actually check whatever you already have same person mapped.

EDIT: Here is some reading about using MERGE and OUTPUT

Upvotes: 1

Sarajog
Sarajog

Reputation: 164

Because I don't know what SQL you are using its difficult to decide if this is correct. i also don't know if you already tried this but it's the best idea i have:

insert into tblPerson
(Forename, Surename)
Select ContactForename, ContactPersonSurename
from tblCompany

insert into tblCompanyPerson
(CompanyID, PersonID)
select CompanyId, PersonID 
from tblPerson, tblCompany
where ContactForename = Forename and ContactPersonSurename = Surename

Sarajog

Upvotes: 0

Related Questions