Function call on Insert Query

  • Hi,

    I have an insert query that calls a scalar function to generate a unique identifier for the row. e.g

    INSERT INTO destination_table

    (

    cid,

    field1,

    field2

    ...

    )

    SELECT

    dbo.GetNextID(),

    ...

    ...

    FROM

    source_table

    The function GetNextID() creates a unique id based on the last id stored in the 'destination_table' table. However when calling the insert for multiple rows the query fails due to a unique index on the 'cid' field.

    Is there any way of getting this to work without using cursors to loop through each row in turn or generating the unique id before the insert is called?

    The unique identifier is not set as an IDENTIFIER field unfortunately and the field cannot be changed to be one.

    Thanks in advance.

  • show us the function dbo.GetNextID()

    we canhelp you turn it into an inline tablevalue function which can be used for multiple rows;

    your syntax for usage would change only slightly:

    INSERT INTO destination_table

    (

    cid,

    field1,

    field2

    ...

    )

    SELECT

    myAlias.NewID,

    ...

    ...

    FROM

    source_table

    CROSS APPLY dbo.GetNextID() myAlias

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I assume your function is returning an integer, so I will ask why you can't define the incrementing column in the destination table as an IDENTITY column. I would expect it to perform better, and it's easier.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • a.black (4/30/2010)


    The function GetNextID() creates a unique id based on the last id stored in the 'destination_table' table. However when calling the insert for multiple rows the query fails due to a unique index on the 'cid' field.

    I would need to see the definition of the GetNextID() function to be sure, but if it contains a simple MAX(id) + 1 against the destination table, the problem is that the MAX is evaluated for every row before the inserts start to run. If you look at the query plan, you will see a spool or sort operator between the read and the write sides of the plan.

    Using an in-line function instead of a scalar would not solve this 'problem' - it is a fundamentally unsound design.

    You would need to use an IDENTITY property or a properly coded sequence table to make this work reliably. Please post the function code - if you are interested in seeing a robust implementation of a sequence table I am happy to do so.

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

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