SQL Server Performance

Writing an autocount Query

Discussion in 'SQL Server 2005 General Developer Questions' started by Lynch, Mar 8, 2007.

  1. Lynch New Member

    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!
  2. eloop New Member

    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
  3. MohammedU New Member

    declare @v int
    select @v = 1

    while @v >= 641
    begin
    UPDATE (table)
    SET (colum name) = 'D6_' + convert(Varchar(3), @v)
    select @v = @v+1
    end

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  4. Lynch New Member

    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!
  5. MohammedU New Member

    I didn't get it...
    Can you provide the sample input and output format...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  6. Lynch New Member

    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?

Share This Page