Returning Id's from SET Inserts

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Returning Id's from SET Inserts

Author

Topic

rhein
Starting Member

9 Posts

Posted - 2003-07-30 : 16:53:11

I have a cursor that I loop thru to do inserts, one record at a time. The reason that I am doing this is so that I can get the ID of the record and insert it into another table (the record is in a many to many relationship).

Is there a way to do this as a SET insert? Instead of using a cursor? The cursor works, but I want it to be as FAST as possible, and In undertand that a SET insert is faster than using a cursor.

Thanks.
Rick

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-30 : 16:55:38

Trigger?

rhein
Starting Member

9 Posts

Posted - 2003-07-30 : 16:58:01

I hadn't thought of a trigger.

Would a triggered action be rolled back if the transaction was rolled back? Not too familiar with triggers.

Thanks.
Rick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-30 : 16:58:12

If your column is an identity column, then you can retrieve it from the @@IDENTITY variable.


DECLARE @IdentityValue INT

INSERT INTO SomeTable (Column2, Column3, Column4)
SELECT Column2, Column3, Column4
FROM Table1
WHERE Column5 = 0

SET @IdentityValue = @@IDENTITY

If your column is not an identity column, then you have to perform a select immediately after the insert to get it.

Tara

rhein
Starting Member

9 Posts

Posted - 2003-07-30 : 17:02:06

I am already doing this:

DECLARE @IdentityValue INT

INSERT INTO SomeTable (Column2, Column3, Column4)
SELECT Column2, Column3, Column4
FROM Table1
WHERE Column5 = 0

SET @IdentityValue = @@IDENTITY

But what happens if the select returns more than one row? What is @@IDENTITY? The id of ALL the inserted rows? Or just the last one? My take is that it's the last one.

Thanks.
Rick

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-30 : 17:17:34

If a trigger isn't the ticket, write a query to return the recordset of all the newly inserted rows. A column like CreateDate should do the trick.

SET @LastID = @@IDENTITY -- ID OF LAST ROW

SELECT ID
FROM Mytable
WHERE CreateDate = (select CreateDate from Mytable WHERE ID = @LastID)

rhein
Starting Member

9 Posts

Posted - 2003-07-30 : 17:24:22

That was about where I was going. Triggers won't work in this case. I do have a DateTime stamp that I'm using already, so getting that to work shouldn't be that big of a deal. I was just hoping to not have to store more data in the table.... Oh well.

Thanks,
Rick

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-30 : 17:43:14

This will add a column for you without much trouble.

alter table dbo.MyTable add CreateDate datetime default getdate()

It'll populate automatically with the exact date / time of the INSERT statement.

Sam

rhein
Starting Member

9 Posts

Posted - 2003-07-30 : 17:44:42

Is that datetime going to be the same for all the inserted records?

Thanks.
Rick

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-30 : 17:52:22

It'll probably differ by a couple of milliseconds per row.

Tara

rhein
Starting Member

9 Posts

Posted - 2003-07-30 : 18:04:57

That's what I thought. Darn. Well, feeding the date from the Sproc is not a big deal. I've already just about got the sproc rewritten to pass in the datetime stamp.

Thanks.
Rick

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-30 : 18:40:20

Hold on, time out.

If you are inserting into a table Orders:

Orders
---
OrderID (identity)
...

and then you want to make sure you add all new OrderID's from Orders into OrderDetails, how about:

INSERT INTO OrderDetails (OrderID, ....)
SELECT OrderID, ....
FROM
Orders
WHERE ORderID not IN (Select distinct OrderID from OrderDetails)

Doesn't that work for you? Insert as many rows as you need into Orders, and when you are done, run this INSERT statement to ensure you have row(s) for each OrderID in the child table (OrderDetails).

- Jeff

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-30 : 18:42:44

All the DateTime columns from a single INSERT will have identical values.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources