Is a Cursor the only way to do this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Is a Cursor the only way to do this?

Author

Topic

vpoko
Starting Member

22 Posts

Posted - 2007-11-29 : 09:52:34

I need to INSERT rows from one table into another. The source table contains 4 fields:
[AutoID] INT IDENTITY (PK), [VendorNumber] INT, [InvoiceNumber] INT, [Value] MONEY.

I need to INSERT the rows into a table in the following format:
[VendorNumber], [InvoiceNumber], [Seq_Number], [Value]

For each VendorNumber + InvoiceNumber combination, the Seq_Number needs to start at 0 and be incemented by 1, then start over at the next Vendor+Invoice#, etc.

The only way I can think of doing this is with cursors, as a set-based INSERT will insert the same number into every row. Am I missing something?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-29 : 15:38:25

No, you dont need a cursor
In SQL 2005 it could be doe using ROW_COUNT OVER...
but in SQL 2000 you can do it too.

select identity(int,1,1) as ID, 0 as TmpCol, [VendorNumber], [InvoiceNumber], [Value]
into #t
from TAB order by [VendorNumber], [InvoiceNumber], [Value]
-- at this moment ID is not what we want, but it is grouped by [VendorNumber], [InvoiceNumber]
-- find min(ID) in each group
update #t set TmpCol=(select min(ID) from #t SECOND
where SECOND.VendorNumber=#t.VendorNumber and SECOND.InvoiceNumber=#t.InvoiceNumber)

and voila!
the number you want is

ID-TmpCol+1

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-11-29 : 16:50:42

one little problem can happen with the 2000 technique. Since the ORDER BY is applied after the identity(int,1,1) value is generated it is possible to end up with the ID column "out of sequence" with the ORDER BY. It is easy to avoid by pre-creating the table with an identity column then doing an INSERT rather than SELECT INTO.

Be One with the Optimizer
TG

kiruthika
Yak Posting Veteran

67 Posts

Posted - 2007-11-30 : 04:48:20

Hi!

Here i'm use a trigger to insert.

This is the sample code:
--------------------------

create table first1 (autoid int not null identity primary key,vendornum int,invnum int,value money)
create table second1 (vendornum int,invnum int,seqnum int identity(0,1),value money)

create trigger invoice_trigg on first1
after insert
as
begin
declare @vno int
declare @iv int
declare @m money
select @vno=vendornum,@iv=invnum,@m=value from inserted
insert into second1(vendornum,invnum,value)values(@vno,@iv,@m)

end

insert into first1 values(100,1000,500)
insert into first1 values(200,2000,500)

select * from first1
select * from second1

kiruthika!
http://www.ictned.eu

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-30 : 05:02:58

Why a trigger? You do not need or want a trigger to do this!

What is the appeal of triggers for everything?!?

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-30 : 06:08:36

I agree with RickD

But I also wanbted to add that your trigger has a serious bug
It wont work if you insert multiple rows at once.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-01 : 21:03:20

quote:
Since the ORDER BY is applied after the identity(int,1,1) value is generated it is possible to end up with the ID column "out of sequence" with the ORDER BY

Show me just one example where that happens.

--Jeff Moden

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-02 : 07:58:29

quote:
Originally posted by Jeff Moden

quote:
Since the ORDER BY is applied after the identity(int,1,1) value is generated it is possible to end up with the ID column "out of sequence" with the ORDER BY

Show me just one example where that happens.

--Jeff Moden

AH! A challenge!

Ok, I will try to come up with one. In the meantime here is an except of a thread from a quick google search:

quote:
No! I pointed this out in my post, but I say it again: SELECT INTO
with the IDENTITY() function gives no guarantee about order, and is
overall more prone to botch the order.

The subject is not exactly our issue but someone else out there believes my original statement

:
http://www.thescripts.com/forum/thread501619.html

EDIT:
Ok here is an example (that worked on my laptop running:Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
notice that if you comment out the getdate() from both ORDER BY statements the sequences match


--pre-cretae table with identity
create table #insert
	(rowid int identity(1,1)
	,xtype tinyint, 
	ID int, 
	colID smallint,
	refdate datetime,
	primary key clustered (xtype,ID,colid))

--Select Into with identity(int,1,1)
select rowid = identity(int, 1,1)
	,sc.xtype
	,sc.id
	,sc.colid
	,getdate() as refDate
into	#selectinto
from	northwind..syscolumns sc
order by sc.xtype
	,sc.id
	,sc.colid
	,getdate()

--same statement as select into but as Insert into pre-created table with identity column
insert #insert (xtype,id,colid,refdate)
select	sc.xtype
	,sc.id
	,sc.colid
	,getdate()
from	northwind..syscolumns sc
order by sc.xtype
	,sc.id
	,sc.colid
	,getdate()

PRINT 'Show Sequence differences'
select i.rowid, si.*
from	#insert i
join	#selectinto si 
		on si.xtype = i.xtype 
		and si.id = i.id
		and si.colid = i.colid
where	i.rowid <> si.rowid


drop table #insert
drop table #selectinto

Be One with the Optimizer
TG

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-02 : 10:35:04

Well, I'll be damned... thanks TG! I like good, hard, demonstrable evidence and that's a keeper!

Guess the reason I've never seen that before is because I've never used GETDATE() as part of the insert order. Not sure when you'd want to do that, but it's good evidence that you need to be a wee bit careful if you use SELECT/INTO to build the IDENTITY column...

Thanks again...

--Jeff Moden

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2007-12-02 : 12:17:31

No prob Jeff. But it's not just getdate() that causes it. If you join to sysobjects and use refDate instead of getdate() you'll see the same thing.

Be One with the Optimizer
TG

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-12-03 : 03:16:48

Thanks for the tip, TG... I'll play with this in a bunch of different ways and see what happens.

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources