Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Retrieving Identities on set-based inserts Expand / Collapse
Author
Message
Posted Tuesday, January 19, 2010 8:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 29, 2010 10:43 AM
Points: 9, Visits: 82
Comments posted to this topic are about the item Retrieving Identities on set-based inserts
Post #850196
Posted Tuesday, January 19, 2010 9:37 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 25, 2013 6:11 PM
Points: 118, Visits: 43
I can see how this will return the set of inserted values. However, I am not sure that I understand just how we can match this set of inserted identity values to the rows we have just inserted. To elaborate - lets assume we are inserting two PERSON records. We have separate addresses for each PERSON. Both of the individuals are called "John Smith", with the same birth date. So how can we be sure that we have successfully matched address to the right JOHN SMITH? Or must we be able to uniquely identify the inserted rows to be able to successfully match them to the identities associated with the inserted row?
Post #850208
Posted Wednesday, January 20, 2010 12:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 2, 2012 7:53 AM
Points: 10, Visits: 53
Good to see examples of the output clause.

The @@identity problem should be a no-problem, if people just remembered to use scope_identity() instead. It will return the correct identity value to you. No identities from triggers.

Just remember that using any kind of @@identity or scope_identity() will still require some RBAR on multi line inserts.
Post #850267
Posted Wednesday, January 20, 2010 3:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 4:35 AM
Points: 31, Visits: 79
Paul Marriott-463518 (1/19/2010)
I can see how this will return the set of inserted values. However, I am not sure that I understand just how we can match this set of inserted identity values to the rows we have just inserted. To elaborate - lets assume we are inserting two PERSON records. We have separate addresses for each PERSON. Both of the individuals are called "John Smith", with the same birth date. So how can we be sure that we have successfully matched address to the right JOHN SMITH? Or must we be able to uniquely identify the inserted rows to be able to successfully match them to the identities associated with the inserted row?


There are a few options:
1) put enough columns in the output clause to uniquely identify the inserted rows. Can get a bit messy though, and if there's the possibliity two rows can be genuinely identical then you could end up with some very strange results

2) You can use order by on the select statement used to insert your top level data, which will guarantee your IDs match the row ordering you've chosen. Whether this is useful depends on the rest of your data

3) Add a rowguid column to the tables where you ened to do this, and add that to your output clause. That'll always do the trick

Kev



Post #850348
Posted Wednesday, January 20, 2010 3:47 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 5, 2013 4:35 AM
Points: 31, Visits: 79
kll (1/20/2010)
Good to see examples of the output clause.

The @@identity problem should be a no-problem, if people just remembered to use scope_identity() instead. It will return the correct identity value to you. No identities from triggers.
.

If you're using the output clause then you don't need to use either of those. I'd still use scope_identity for single row inserts though

Just remember that using any kind of @@identity or scope_identity() will still require some RBAR on multi line inserts.


Surely the whole point of this article is that you don't need to do any RBAR. I use this technique a lot. Best result was on a stored proc that inserted typically about 500 top level records, each consisting of several sub-hierarchies ranging from one to 4 levels deep. Execution time went down from about 20 minutes to a few seconds.



Post #850350
Posted Wednesday, January 20, 2010 4:45 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 2012 12:23 PM
Points: 2,087, Visits: 3,932
Hi %

kll (1/20/2010)
Just remember that using any kind of @@identity or scope_identity() will still require some RBAR on multi line inserts.


It's possible to use bulk insert operations if table containing the new rows to be inserted contains any kind of a surrogate id (or another uniqueness).

---========================================================
-- table definitions

-----------------------------------------------------------
-- destination table for new data to be inserted
DECLARE @Destination TABLE (
Id INT NOT NULL IDENTITY(1,1)
PRIMARY KEY CLUSTERED
,SomeInt INT
);

-----------------------------------------------------------
-- table containing all rows to be newly inserted into
-- destination table
-- IMPORTANT
-- the table containing the new data to be inserted requires
-- any kind of uniqueness (e.g. a surrogate id)
DECLARE @NewData TABLE (
SurrogateId INT NOT NULL
PRIMARY KEY CLUSTERED
,SomeInt INT
);

---========================================================
-- create some sample data

-----------------------------------------------------------
-- insert existing data into destination table
INSERT INTO @Destination
SELECT -777
UNION ALL SELECT -888;

-----------------------------------------------------------
-- new rows to be inserted into destination table
INSERT INTO @NewData
SELECT -1, 11
UNION ALL SELECT -2, 22;


---========================================================
-- bulk insert rows into destination table and catch new identities

-----------------------------------------------------------
-- variables to catch count of inserted rows and scope-identity
DECLARE
@LastIdentity INT
,@RowCount INT;


-----------------------------------------------------------
-- bulk insert new rows into destination table
-- IMPORTANT
-- to get the following operations work ensure to use an
-- ORDER BY in SELECT. To avoid problems due to paralellism
-- use MAXDOP 1 option what should be okay for an INSERT
INSERT INTO @Destination
SELECT SomeInt
FROM @NewData
ORDER BY SurrogateId
OPTION (MAXDOP 1);


-----------------------------------------------------------
-- catch the last created scope-identity and the count of
-- rows inserted
SELECT
@LastIdentity = SCOPE_IDENTITY()
,@RowCount = COUNT(*)
FROM @NewData;


-----------------------------------------------------------
-- use a CTE that creates a row number ordered by the
-- surrogate id of newly inserted rows
WITH cte AS (
SELECT
SurrogateId
,ROW_NUMBER() OVER (ORDER BY SurrogateId) RowNum
FROM @NewData
)
SELECT
*
-- calculate destination table IDENTITY values
,@LastIdentity - @RowCount + RowNum
FROM cte

Greets
Flo



The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems
Post #850368
Posted Wednesday, January 20, 2010 7:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 1, 2014 4:59 AM
Points: 41, Visits: 373
I like the idea of using the OUTPUT clause but I have this bad feeling about the example. Perhaps there are requirements that justified doing things this way (insert all parent rows then insert all children), but I think this will cause problems with concurrency.

It seems we are always "fighting" against such requirements as "add all records to the database; rollback all changes for the batch if any errors". When the batch gets large, the locking may escalate to table locks. How large is a dynamic that is based on resources at the time.

I guess my point is to be aware that, if you can, try to make the unit of work one master and all its related child records.
Post #850495
Posted Wednesday, January 20, 2010 8:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 1:09 PM
Points: 3, Visits: 10
This is a sidebar but....

I am struggling with how the CTE is referencing itself from within itself
This works but I need clarification on it. The CTE is referencing itself from within itself.

I did not realize this was possible.
Any clarification would be greatly appreciated.

;WITH mycte AS
(SELECT 100 AS seedValue
UNION ALL
SELECT seedValue + 1 FROM mycte WHERE seedValue + 1 < 200
)

select * from mycte
Post #850548
Posted Wednesday, January 20, 2010 9:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 12:40 PM
Points: 54, Visits: 145
bobmcc-957585 (1/20/2010)
This is a sidebar but....

I am struggling with how the CTE is referencing itself from within itself
This works but I need clarification on it. The CTE is referencing itself from within itself.

I did not realize this was possible.
Any clarification would be greatly appreciated.

;WITH mycte AS
(SELECT 100 AS seedValue
UNION ALL
SELECT seedValue + 1 FROM mycte WHERE seedValue + 1 < 200
)

select * from mycte


Hi Bob

This is a recursive CTE, which is a supported structure in TSQL

One nice use for it is to navigate a parent-child relationship of uncertain depth. MSDN has an article on it at http://msdn.microsoft.com/en-us/library/ms186243.aspx

Post #850570
Posted Wednesday, January 20, 2010 9:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 1:09 PM
Points: 3, Visits: 10
Thanks J!
I'm on my way to check out the article you recommended. I appreciate the reply.
..bob

Hi Bob

This is a recursive CTE, which is a supported structure in TSQL

One nice use for it is to navigate a parent-child relationship of uncertain depth. MSDN has an article on it at http://msdn.microsoft.com/en-us/library/ms186243.aspx
Post #850580
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse