Insert a numeric range into a column | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Insert a numeric range into a column

I have a column in a table that I would like to populate a numeric value, incrementing the value by one throughout the record set. The field in question is not indexed, primary or foreign key. Before
ColA ColB ColC
XX Null 10/5/2004
XX Null 12/5/1999

XX Null 1/5/2002 After
ColA ColB ColC
XX 1 10/5/2004
XX 2 12/5/1999
XX 3 12/5/1951

XX 3000 1/5/2002 I populated the table using a cursor already but I was wondering if there is a quicker way to do it, hopefully in a single statement.
yes, there is. create an identity on that table.
Thanks…. Thats so simple…
Hey Bambola is back! glad to see or at least hear from you again! <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /><br /><br />Twan
Yeah … hope everything is safe & sound in Italy.[<img src=’/community/emoticons/emotion-2.gif’ alt=’:D‘ />]<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
Thanks Twan & Satya. It’s good to be back. <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> and sorry guys for not being around lately.
A while back I ran across something peculiar in BOL for the UPDATE grammar ‘SET … @variable = column = expression’. It’s in there check it out.http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ua-uz_82n9.asp Based on this I was able to create a cursor-less auto increment over a range of data. set nocount on create table #tbl (ColA Varchar (30) null, ColB Int null, ColC Datetime) insert into #tbl (ColA, ColB, ColC) select ‘AA’, Null, ’10/5/2004′
insert into #tbl (ColA, ColB, ColC) select ‘BB’, Null, ’12/5/1999′
insert into #tbl (ColA, ColB, ColC) select ‘CC’, Null, ‘1/5/1999’
insert into #tbl (ColA, ColB, ColC) select ‘DD’, Null, ‘2/5/1999’
insert into #tbl (ColA, ColB, ColC) select ‘EE’, Null, ‘3/5/2001’
insert into #tbl (ColA, ColB, ColC) select ‘FF’, Null, ‘7/5/2000’
insert into #tbl (ColA, ColB, ColC) select ‘GG’, Null, ‘1/5/2002’ select * from #tbl
/* BEFORE
ColA ColB ColC
AA NULL 2004-10-05 00:00:00.000
BB NULL 1999-12-05 00:00:00.000
CC NULL 1999-01-05 00:00:00.000
DD NULL 1999-02-05 00:00:00.000
EE NULL 2001-03-05 00:00:00.000
FF NULL 2000-07-05 00:00:00.000
GG NULL 2002-01-05 00:00:00.000
*/ declare @ct int
set @ct = 0 — initialze seed value; — can start at a seed value & increment by values other than 1. update t — Auto Increment/Concatenate on a table
set @ct = t.colB = 1 + @ct + isnull(t.colB ,0) — auto increment
from #tbl t select * from #tbl order by colb /* After
ColA ColB ColC
AA 1 2004-10-05 00:00:00.000
BB 2 1999-12-05 00:00:00.000
CC 3 1999-01-05 00:00:00.000
DD 4 1999-02-05 00:00:00.000
EE 5 2001-03-05 00:00:00.000
FF 6 2000-07-05 00:00:00.000
GG 7 2002-01-05 00:00:00.000 */ — Another related trick is to turn a set of rows into a string w/out a cursor. Using the same data as above.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_978l.asp declare @s varchar(1000) select @s = isnull( quotename( colA, ”” ) ,” ) + ‘ ‘ + isnull( ‘, ‘ + @s, ” ) — Auto Concatenate w/Select
from #tbl
order by colc select @s
— ‘AA’ , ‘GG’ , ‘EE’ , ‘FF’ , ‘BB’ , ‘DD’ , ‘CC’ drop table #tbl –rl
]]>