Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
beanz
Starting Member
35 Posts |
 Posted - 2007-06-06 : 12:23:23
|
| Hi,I'm trying to write a stored procedure that imports data from an inbound XML file. I am doing this using OPENXML.The XML is of the structure:<Application> <Applicants> <Applicant> <Addresses> <Address> <Address> <....> </Addresses> <Accounts> <Account> <Account> <....> </Accounts> <Applicant> <...> </Applicants></Application>When inserting the child records I need to have the value of the parent ID in order to link the records. For example, the addresses table has a foreign key linking back to the applicant table.Ideally I could use "SET @ApplicantID = SELECT_IDENTITY()" but with won't work with INSERT INTO... SELECT FROM statements.Does anyone have any suggestions as to how I could achieve this? A colleague of mine suggested a cursor but I'm not too keen...Any help is much appreciated.Danny |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2007-06-07 : 03:29:55
|
| move all the records into a temp table which has a identity column.next take the max(parentid) from u r actual tablenow idenity col + max(parentid) will give u the ids which are inserted in that batch. |
 |
|
|
beanz
Starting Member
35 Posts |
Posted - 2007-06-12 : 06:26:57
|
| Thanks for the follow-up.Unfortunatly, I can't use a "counting" solution because the data is coming from a multiple instances. What I have done is use a "tracking" ID (as an element attribute) on the source data and joined the parent/child records that way.Cheers |
|
|
|
|
|
|
Return SCOPE_IDENTITY() after batch insert