Post reply

Insert Into from Select - Increment CHR Key by 1 on each insert

  • July 15, 2005 at 3:47 pm

    #76376

    How do you insert multiple rows from a select statement while incrementing a unique primary key in chr?  i.e. 00000001 - 00000100

    What I have tried to no avail, with error:

    A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

    or

    Violation of PRIMARY KEY constraint

    --declare @tempid varchar(8)

    --set @tempid = '00000001'

    INSERT INTO TestDestinationTable(

     Employee_ID,

     Name

    )

    SELECT

     -- @tempid = '00' + Cast((@tempid+1) as varchar(8)),

     --'00' + Cast(((select top 1 employee_id from TestDestinationTable order by employee_id desc)+1) as varchar(8)),

     'TestName'

    FROM

     TestSourceTable

  • July 17, 2005 at 6:51 am

    #575108

    Rob - may I ask why the ID columns have to be varchar data types ?!

    As far as the errors go - in your "select @tempid = ...." - you're selecting a value into your variable whereas the rest of your select is trying to get data from the TestSourceTable....so the first error msg is that you cannot combine these 2 operations.

    The second error msg means that you're violating a unique key constraint - ie. trying to insert a duplicate value when it is not allowed.

    You will have to do a lot of scary casts and converts to get the IDs to be varchar 8 - why don't you post the ddl of your TestSourceTable, some sample data from that table and what you want the data in your TestDestinationTable to look like ?!

    That might help someone suggest a solution for your problem!







    **ASCII stupid question, get a stupid ANSI !!!**

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply