Insert with an incrementing 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 Development (2000)

Insert with an incrementing column

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-26 : 11:04:46

I can do this with a cursor, but I'm hating cursors more and more

I'll over simplify for this

create table Loadingtable(
InsertID int
)

This table is populated with
1
2
3
3
3
2
2
3
1
2
1

something along those lines.

Create table Destination (
InsertID int
NewID int
)

This table is to look like
insertID,NewID
1,1
1,2
1,3
1,4
2,1
2,2
3,1
3,2
3,3
3,4
3,5

The cursor would be something along the lines of
declare cursor
load first value from LoadingTable into cursor
select @newid = (select count(47) from destination where insertID = @insertID_from_cursor) + 1
insert into destination(insertID,NewID)
values (@insertID_from_cursor,(@newID)
loop cursor

kinda sad that I used count(47) hey?

Any ideas to make this a set based query?

-----------------------
Take my advice, I dare ya

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-06-26 : 11:10:50

Out of curiosity, what do you need it for? Line numbers on an invoice, or something else?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-26 : 11:23:46


create table Loadingtable( 
InsertID int 
)
insert loadingtable
select 1 
union all select 2 
union all select 3 
union all select 3 
union all select 3 
union all select 2 
union all select 2 
union all select 3 
union all select 1 
union all select 2 
union all select 1 

select
    insertid,
    convert(int,'') as new
into
    #temp
from loadingtable
order by insertid

declare @new int, @last int
select @new = 1, @last = 0

update #temp
set
    @new = new = case when @last = insertid then @new + 1 else 1 end,
    @last = insertid

select * from #temp

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-26 : 11:36:10

Ahhh, of course... Temp tables... Now why didn't I think of that. Thanks page, a slight alteration of what you did there will work perfectly

Rob
Its an issue with keys as much as anything.
they have a table full of documents (doc1, doc2, doc3)

Coming from a IDMS (from a text file) is a bunch of comments about these documents... Oil well stuff mostly, a new text file full of em comes nightly (something like 3000 per night... It actually has somewhere near 5000 docs and 787000 comments initially). What they want is a table that records what the commentID and a second number to keep track of which document it is (just an arbitrary number assigned by me). So in the future they can reference it by the document ID and this ID I'm assigning it)

Its kinda along the lines of
Doc1, comment1
doc1, comment2
doc2, comment1
doc2, comment2
doc2, comment3...

They want a comment number to be assigned to each comment by me as they come into my DB from the flat file.

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-26 : 11:39:24

M.E., the important thing there is the order by clause on populating the temp table. Without that, you won't get the expected result. set @var=col=expression is pretty nifty . ..

<O>

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-26 : 11:45:56

quote:
set @var=col=expression

Never really seen it used before actually. Whats that called (besides nifty) and is there an article on sqlteam about it?

-----------------------
Take my advice, I dare ya

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-26 : 11:51:14

If you look up UPDATE in bol there is some stuff about it.

quote:
SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.

I picked it up from rrb . . . http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14095

I think here's an article . . . http://www.sqlteam.com/item.asp?ItemID=765
<O>

Edited by - Page47 on 06/26/2002 11:52:41

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-26 : 12:09:03

Tis a work of art page.. much thanks

-----------------------
Take my advice, I dare ya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources