Function call on Insert Query

Question

Post reply

Function call on Insert Query

a.black

SSC Enthusiast

Points: 195
More actions
April 30, 2010 at 9:19 am

#220391

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.

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

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

Lowell SSC Guru Points: 323524 More actions · Answer 1

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!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

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

Paul White SSC Guru Points: 150468 More actions · Answer 3

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.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Function call on Insert Query

Cookies on SQLServerCentral