Post reply

How can I INSERT INTO VALUES for a table with a primary key

  • January 26, 2009 at 11:47 am

    #71232

    How can I INSERT INTO VALUES for a table with a primary key? I have a table called TEST with the following two columns:

    id (PK, int, not null)

    name (varchar(50), null)

    I've tried this:

    insert into test

    values ('','John Doe')

    and this:

    insert into test

    values (,'John Doe')

    but, neither of them work. I'm getting this message.

    Violation of PRIMARY KEY constraint 'PK_TEST'. Cannot insert duplicate key in object 'TEST'.

    The statement has been terminated.

    Ultimately, I need to add the same value to the name column for say the first 100 rows. So, I want the id column to show a value of 1, 2, 3 all the way up to 100 and have the column value of 'John Doe' for all 100 rows.

  • January 26, 2009 at 11:56 am

    #932763

    Since the PK column is not declared as identity, you'll need to specify the values for the ID column. That's what it's complaining about, not the 'John Doe' name.

    As the table's currently designed, you need to do this

    Insert into Test (id, name)

    Values (1, 'John Doe')

    If you change the ID column definition to be an identity (if you don't want to specify the ID values), then you can do something like this

    INSERT INTO Test (name) -- must specify columns if you're not giving values for all of them

    VALUES ('John Doe')

    As for inserting 100 rows, first decide if you want the column identity or not.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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