Multiple insert with SCOPE_IDENTITY()

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

SQL Server Development (2000)

Multiple insert with SCOPE_IDENTITY()

Author

Topic

DanG
Starting Member

10 Posts

Posted - 2003-05-19 : 05:23:55

I have a table (TableA) with a primary key defined as an identity column.
A second table (TableB) has this key as its foreign key.

I’ve done it before for a single record using SELECT SCOPE_IDENTITY() … in a stored procedure. I obtain the newly created primary key for TableA and use it for insert into TableB

However, now I need to do a similar thing but for multiple records.

Should I use a cursor to iterate each insertion or is there a better way?

Dan.

rjpaulsen
Starting Member

9 Posts

Posted - 2003-05-20 : 15:50:38

DECLARE @MYID INT

INSERT INTO TABLE1 (FIELD1) VALUES ('a')

SET @MYID = @@IDENTITY

INSERT INTO TABLE2 (TABLE1_ID, FIELD1) VALUES (@MYID,'x')
INSERT INTO TABLE3 (TABLE1_ID, FIELD1) VALUES (@MYID,'y')
INSERT INTO TABLE4 (TABLE1_ID, FIELD1) VALUES (@MYID,'z')

Edited by - rjpaulsen on 05/20/2003 15:51:12

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-20 : 16:24:40

Do you mean the insert to be a SELECT Clause? You can use literal and or variables in a SELECT...

INSERT INTO myTable(col1, col2, col3)
SELECT 'X', @IdentValue, a.col4
FROM myTable2 a
WHERE .....

Like that?

Brett

8-)

rjpaulsen
Starting Member

9 Posts

Posted - 2003-05-20 : 16:57:00

A SELECT statement instead of VALUES works, but I'd feel sorry for the guy* that wrote the VALUES clause if nobody ever used it.

*or gal.

DanG
Starting Member

10 Posts

Posted - 2003-05-22 : 19:29:26

quote:

Do you mean the insert to be a SELECT Clause? You can use literal and or variables in a SELECT...

INSERT INTO myTable(col1, col2, col3)
SELECT 'X', @IdentValue, a.col4
FROM myTable2 a
WHERE .....

Like that?

Brett

8-)

It's the "WHERE ...." which is the problem :-) How can I specify which records where last inserted. I cannot use the same WHERE as in the one used for TableA as it may creates duplicates in TableB. I use an identitl precicly to have a unique primary key!

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-05-22 : 20:01:05

The joys of IDENTITY...NOT

Before the Insert into the identity table capture the last ID, do your insert then capture the last ID again. Use these 2 values to build a BETWEEN clause on your second insert. You have to use a isolation level of SERIALIZABLE.

[code]
CREATE TABLE TestPrimary (ID INT IDENTITY(1,1), Data INT)
CREATE table TestForeign (ID INT)
GO
--Ensure that no other DML can affect the results.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
declare @Before int, @After INT
--Before
Select @Before = COALESCE(MAX(ID),0) from TestPrimary
--Simulating multi-row insert
Insert TestPrimary (DATA)
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
--After
Select @After = MAX(ID) from TestPrimary
Insert TestForeign (ID)
Select ID
from TestPrimary
where ID BETWEEN @Before+1 AND @After

Select * from TestPrimary
Select * from TestForeign

drop table TestPrimary, TestForeign
[/code

HTH

DavidM

"SQL-3 is an abomination.."

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2003-05-23 : 12:32:04

How about A S.P. called on update.

Insert Into Dbo.TableB
(TableID)
Select Dbo.TableA.TableID
FROM
dbo. TableA LEFT OUTER JOIN dbo. TableB ON dbo.TableA.TableID = dbo.TableB.TableID
Where (dbo.TableB.TableID Is Null)

This will create the tableB entrys no matter how many records are added.

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources