I have 2 tables:
declare @Customer_Links table (
Customer_LinkID int identity(1,1),
CustomerID int
)
declare @Customer_Regions table (
CustomerRegionID int identity(1,1),
Customer_LinkID int,
RegionID int
)
which are to be populated with info from this table:
declare @MyTable table (
CustomerID int,
RegionID int
)
Insert into @MyTable(CustomerID,RegionID) values (500,10)
Insert into @MyTable(CustomerID,RegionID) values (510,11)
Insert into @MyTable(CustomerID,RegionID) values (520,12)
The idea is to first enter the
Insert into @Customer_Links(CustomerID)
values(500)
Get the Customer_LinkID which is then generated, and then
Insert into Customer_Regions(Customer_LinkID,RegionID)
values(@NewCustomer_LinkID, RegionID)
e.g. Entry 1 of 500 will generate Customer_LinkID 1, which will then be inserted into
@Customer_Regions table as (CustomerLinkID =1, RegionID = 10)
and do this for all rows in
@MyTable
Question:
Is a cursor my only option here?
Isn't there a way to way to declare @MyTable with an extra column,e.g. CustomerLinkID
and then Insert each record into @Customer_Links, and update @MyTable with the updated Customer_LinkID,e.g.
CustomerID,RegionID,LinkID
500 10 1
510 11 2
520 12 3
And then simply afterwards
Insert into @Customer_Regions(Customer_LinkID,RegionID)
(Select LinkID,RegionID from @MyTable)
OUTPUTclause andMERGEwould be useful – Martin Smith Feb 14 at 15:15