Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
1  
I'd look into the output clause if I were you. And never ever use @@identity, if you must do it one record at a time, then use scope_identity() – HLGEM Feb 14 at 15:13
4  
Shame you are on 2005. On 2008 the OUTPUT clause and MERGE would be useful – Martin Smith Feb 14 at 15:15

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.