Post reply

Insert multiple rows to table

  • May 13, 2008 at 11:52 pm

    #190520

    I am using an input variable of type XML and it all works just fine when updating.

    Though, I wish to use the same type of XML for inserting but I am having problems with my primary keys.

    As I am not using IDENTITY(x,x) for my primary key column I have problems inserting multiple rows to my table since I don't have any PK value incoming with my XML.

    Simplified my problem is like this;

    --My table below is filled from my input XML

    DECLARE @propertylist TABLE(id INT, name NVARCHAR(100), value NVARCHAR(100))

    ...

    /*

    Result from @propertylist

    id, name, value

    -1, String1, MyText1

    -1, String2, MyText2

    */

    INSERT INTO PropertyValue(id, name, value)

    SELECT id, name, value

    FROM @propertylist

    Since id from @propertylist is -1 for both my records this won't work. Instead I want to retrieve the next id from PropertyValue and of course incremented, ie my inserted records should look like, for example

    7, String1, MyText1

    8, String2, MyText2

    Anyone having the best solution for this?

    Thanks, Per

  • May 14, 2008 at 1:26 am

    #814965

    Hi,

    Try to update the table with no Duplicate keys and then insert into other table

    I think, this is what you are looking.

    Rajesh

  • May 14, 2008 at 1:33 am

    #814968

    No, this is not it.

    -1 is only indicating that it is a new record. All new records will have -1 and that value is not what I suppose to put into my table. Ie this is not duplicates.

    Two NEW rows that I want to create unique primary keys for (inside my stored procedure).

  • May 14, 2008 at 1:40 am

    #814969

    Can you post the exact scenario by creating some example tables and error state ,so we will try our level best to help you

    Rajesh

Viewing 4 posts - 1 through 3 (of 3 total)

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