batch Insert Problem---with identity column

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 Administration (2000)

batch Insert Problem---with identity column

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2004-09-21 : 14:18:51

Hi,

We have a table having identity column. Currently we insert into this table 1 row at a time and check @@identity and insert into second table new row using this identity value. Now we have to convert this in batch processing and have to insert 1000 rows at a time in first table. What is the best possible to check which are the new rows inserted by particular session and insert into second table?
Note: We can't use triggers

Thanks
--rubs

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-21 : 14:42:05

oopsy i didn't read your post correctly.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-21 : 14:53:14

Declare @startId int, @endId int

Select @startId = max(id)+1 From table1

/*******
Do insert into table1 here
*******/

Select @endId = max(id) From table1

Insert Into table2
Select col01*2
From table1
Where id between @startId and @endId

Corey

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-21 : 17:01:34

I take it you have the data in a staging table and want to put some of the row into a master table and the rest into a child?
If you just need the identity value then Seventhnight's solution will work. Otherwise

Note - all of these only work in a single user environment - lock the table if necessary

Simple solution
select *, identity(int, 1, 1) as id into #a from staging
declare @id int
select @id = max(id) from tbl
set identity_insert tbl on
insert tbl select id + @id, col, ... from #a
insert tbl2 select id + @id, col, ... from #a
set indetity_insert tbl off

more complicated - needed if all rows don't go into master
select *, identity(int, 1, 1) as id, seq = 0 as id into #a from staging
pick any column colx that is as unique as possible - can use multiple fields - that are going into the master table.

update #a set seq = (select count(*) from #a t2 where #a.colx = t2.colx and t2.id <= #a.id)
declare @seq int
select @seq = max(seq) from #a
declare @id int
select @id = max(id) from tbl
while @seq > 0
begin
insert tbl select col, ... from #a where seq = @seq
insert tbl2 select tbl.id, #a.col, ... from tbl, #a where #a.colx = tbl.colx and #a.seq = @seq and tbl.id > @id
select @seq = @seq - 1
end

The loop will execute on distinct values so the more copies of a single field the more times it wil execute.

==========================================
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources