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 |
|
tanveer
Starting Member
1 Post |
 Posted - 2004-06-06 : 08:48:56
|
| Hi All,Iam in a situtation where i have a query Which Inserts into a table from Select statement. But there is another table which is dependent on the Primay key of the inserted table.Since the insert is multiple iam not able to use the @@Identity.Can some one suggest me How can i over come this situtation.Also Triggers cant be used as the the records are of huge numbers.Eg:- INSERT INTO Users (FirstName, SecondName) SELECT FirstName, SecondName From Old_UsersINSERT INTO UserDependent(UserID,OtherFields)VALUES(@@Identity,'SomeOtherValue')ThanksTanveer |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-06-06 : 10:16:35
|
| We do this by not using Identity, but instead using a SProc that provides the next available number. The SProc looks up, and increments, the number from a "lookup table". The SProc can also provide a "range" of numbers, so the INSERT can use that, known, range - e.g. store into a temporary table first, with the allocated "range" of numbers, and from there into the actual table; and then the temporary table can be used for the insert into the dependant tableIf the data going into the MAIN table is UNIQUE on some other criteria (e.g. First + Second name), that that could be used to derive the Identity for insert into the dependant table.Kristen |
 |
|
|
whlatimer
Starting Member
1 Post |
Posted - 2004-06-06 : 12:11:18
|
| check out alternatives to @@identity: scope_identity() and ident_current(tablename) |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-06-06 : 15:05:33
|
| Is there a natural key on the table you can identify the records with also? You could use this to identify the records in the other table. Another thing you might consider is using a temp table as an intermediary table.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-06-06 : 20:48:59
|
| Take any field (the least common) in the set being inserted and the current max identity on the table.insert in blocks wher ethat feild is unique - I often put on a sequence for the field and use that.You can then use the id and field to insert into child tables or get the id onto a temp talbe from which you are inserting.Then loop for the rest of the unique blocks.Not very nice but a lot faster than row by row.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
How to get the @@Identity for multiple Inserts?