Retrieval of IDENTITY after "bulk" insert?

21 Oct 2005 2:44 PM

lmcphee

I will be inserting large numbers of records into a table with an Identity
column. I need the Identity value back to use for subsequent inserts to other
tables which link to 1st table on the ID.

I can insert to "parent" table one-by-one, retrieving the ID each time.

INSERT INTO MyTable (MyCharCol) VALUES ('Data')
SELECT @@IDENTITY AS 'Id'
-- use 'Id' in INSERTs to child tables...

But I am wondering if there is a more efficient way to do this. Sending a
separate command to the server for every single parent and dependent table
INSERT will really be a big performance hit. I also have to consider that a
given INSERT may fail and so the dependent INSERTs would then not be executed.

I am going to try creating a single large string with several INSERT/SELECTs
on the parent table in a single command. I expect I would get back a DataSet
with a DataTable for each SELECT @@IDENTITY command? If so, I could then
iterate over those to execute the dependent INSERTs.

LMcPhee

21 Oct 2005 3:24 PM

David Portas

Here's a generic example that may help. If this doesn't answer your
question then please post DDL and sample data so that we don't have to
guess at your requirements. Remember to include your PRIMARY and UNIQUE
keys with the DDL!

CREATE TABLE Foo (foo_id INTEGER IDENTITY PRIMARY KEY, foo_key
VARCHAR(10) NOT NULL UNIQUE) ; /* Parent */

CREATE TABLE Bar (bar_id INTEGER IDENTITY PRIMARY KEY, bar_key
VARCHAR(10) NOT NULL UNIQUE, foo_id INTEGER NOT NULL REFERENCES Foo
(foo_id)) ; /* Referencing table */

/* Parent key */
INSERT INTO Foo (foo_key)
SELECT DISTINCT foo_key
FROM Foo_source ;

/* Referencing data */
INSERT INTO Bar (bar_key, foo_id)
SELECT DISTINCT B.bar_key, F2.foo_id
FROM Bar_source AS B
JOIN Foo_source AS F
ON B.foo_id = F.foo_id
JOIN Foo AS F2
ON F.foo_key = F2.foo_key ;

--
David Portas
SQL Server MVP
--

21 Oct 2005 4:25 PM

lmcphee

Your Foo and Bar table definitions work for my scenario.

I need the Ids back to store with in-memory records of the INSERTs to the
Foo table. There may be INSERTs into the Bar table at a later time which will
reference the records in Foo.

I tried the following query with ADO.Net ...
sSQL =
INSERT INTO Foo(foo_key) VALUES ('val1')
SELECT @@IDENTITY AS 'foo_id'
INSERT INTO Foo(foo_key) VALUES ('val12)
SELECT @@IDENTITY AS 'foo_id'
....

Dim MyDs as New DataSet
Dim MyDa as New OleDbAdapter(sSQL, MyConnection)
MyOleDbAdapter.Fill(MyDs)

The DataSet then has a DataTable for each foo_id. So I get my "bulk" INSERT
and get back the Id values i need to store with in-memory records!

Now I am working on how to catch error for a specific INSERT so remaining
INSERTs in the batch get executed.

I will be glad to provide more details of my scenario if you think it would
help and are interested.

Thanks for your reply.

LMcPhee

Show quote

"David Portas" wrote:

> Here's a generic example that may help. If this doesn't answer your
> question then please post DDL and sample data so that we don't have to
> guess at your requirements. Remember to include your PRIMARY and UNIQUE
> keys with the DDL!
>
> CREATE TABLE Foo (foo_id INTEGER IDENTITY PRIMARY KEY, foo_key
> VARCHAR(10) NOT NULL UNIQUE) ; /* Parent */
>
> CREATE TABLE Bar (bar_id INTEGER IDENTITY PRIMARY KEY, bar_key
> VARCHAR(10) NOT NULL UNIQUE, foo_id INTEGER NOT NULL REFERENCES Foo
> (foo_id)) ; /* Referencing table */
>
> /* Parent key */
> INSERT INTO Foo (foo_key)
> SELECT DISTINCT foo_key
>   FROM Foo_source ;
>
> /* Referencing data */
> INSERT INTO Bar (bar_key, foo_id)
> SELECT DISTINCT B.bar_key, F2.foo_id
>   FROM Bar_source AS B
>   JOIN Foo_source AS F
>    ON B.foo_id = F.foo_id
>   JOIN Foo AS F2
>    ON F.foo_key = F2.foo_key ;
>
> --
> David Portas
> SQL Server MVP
> --
>
>

21 Oct 2005 4:41 PM

David Portas

lmcphee wrote:
Show quote

> Your Foo and Bar table definitions work for my scenario.
>
> I need the Ids back to store with in-memory records of the INSERTs to the
> Foo table. There may be INSERTs into the Bar table at a later time which will
> reference the records in Foo.
>
> I tried the following query with ADO.Net ...
> sSQL =
> INSERT INTO Foo(foo_key) VALUES ('val1')
> SELECT @@IDENTITY AS 'foo_id'
> INSERT INTO Foo(foo_key) VALUES ('val12)
> SELECT @@IDENTITY AS 'foo_id'
> ...
>

More efficient to do this:

INSERT INTO Foo(foo_key)
SELECT 'val1' UNION ALL
SELECT 'val2' UNION ALL
...
SELECT 'val99' ;

SELECT foo_id
FROM foo
WHERE foo_key IN ('val1','val2' ... 'val99')

--
David Portas
SQL Server MVP
--

21 Oct 2005 6:35 PM

Brian Selzer

If your table is designed correctly, then in addition to the IDENTITY
column, you also have a UNIQUE constraint. IDENTITY should almost never be
the sole candidate key for a table. The only exception to this rule is when
the entire table is abstract--that is, no columns contain data that
originate outside of the database. An example of this would be tables that
model the structure of a directed graph which have optimized table-valued
functions that can be used facilitate querying concrete graphs that contain
references to records in the abstract tables. It should be obvious,
therefore, that because a table has more than one candidate key, then it is
possible to obtain the newly generated IDENTITY values by using the other
candidate key to look it up.

Show quote

"lmcphee" <lmcp***@discussions.microsoft.com> wrote in message
news:4E627FCA-80AA-4F80-B33C-0B6B1566B033@microsoft.com...
>I will be inserting large numbers of records into a table with an Identity
> column. I need the Identity value back to use for subsequent inserts to
> other
> tables which link to 1st table on the ID.
>
> I can insert to "parent" table one-by-one, retrieving the ID each time.
>
> INSERT INTO MyTable (MyCharCol) VALUES ('Data')
> SELECT @@IDENTITY AS 'Id'
> -- use 'Id' in INSERTs to child tables...
>
> But I am wondering if there is a more efficient way to do this. Sending a
> separate command to the server for every single parent and dependent table
> INSERT will really be a big performance hit. I also have to consider that
> a
> given INSERT may fail and so the dependent INSERTs would then not be
> executed.
>
> I am going to try creating a single large string with several
> INSERT/SELECTs
> on the parent table in a single command. I expect I would get back a
> DataSet
> with a DataTable for each SELECT @@IDENTITY command? If so, I could then
> iterate over those to execute the dependent INSERTs.
>
> LMcPhee