April 30, 2010 at 9:19 am
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.
April 30, 2010 at 11:11 am
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
May 1, 2010 at 12:24 am
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
May 2, 2010 at 10:41 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy