Writing an autocount Query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Writing an autocount Query

Hello All,<br /><br />I am stumped (which really isn’t that hard to do <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> )<br /><br />I am trying to write an update query to insert into a varchar colum this text D6_ with the underscore followed by and auto incrementing number up to 641 so the final product should appear as so in the colum: D6_1<br /> D6_2<br /> D6_3<br /> etc.<br /><br />so far i have this part of my query written<br /><br />UPDATE (table)<br />SET (colum name) = ‘D6_’ +<br /><br />and that is it (yes i have completed all the easy stuff myself <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> ) i have researched using the CURSOR, CAST and CONVERT arguments (because i have a int colum in the same table that i could relate to for the "counting") but to no avail. does anyone have any insight into what *should* be a suuuuuuuuuuuper easy query to write?<br /><br />Thank you all so much in advance!
look at : sp_executesql It will give you a possibility to render a text (SQL) and execute it. However, it sounds like a FUBAR database if you have that many fields named silly numbers. Consider re-designing your database instead. regs, Eventloop "Follow the join tree" – Dan Tow
declare @v int
select @v = 1 while @v >= 641
UPDATE (table)
SET (colum name) = ‘D6_’ + convert(Varchar(3), @v)
select @v = @v+1
end MohammedU.
Hey thanks for the tip. I probably didn’t explain this well so i will attempt again. There is one table and in that table are 641 records I only want to populate one field/colum of said table with the syntax described above, think of the displayed D6_ as the indentifier as to what "book" is being used and the numbers proceeding the underscore as book page numbers for each index that is being cycled through in the utility program that uses the DB. does that make more sense? Thanks again in advance for the help!

I didn’t get it…
Can you provide the sample input and output format…
Hey MohammedU Sorry that first reply was for Eventloop. I think you are onto something… while i applied the query to my DB and it didn’t work I have fiddled with it a bit and have ended up with this syntax: DECLARE @v int
SELECT @v = 1 WHILE @v <= 641 UPDATE tblDocumentInfo
SET sBookNo = ‘D6_’ + @v + 1
but I am receiving this error: Server: Msg 245, Level 16, State 1, Line 6
Syntax error converting the varchar value ‘D6_’ to a column of data type int.
just to let you sBookNo is set up as a varchar field.. got any ideas where i am going wrong now?