July 8, 2005 at 6:17 am
Howzit,
I'm trying to a do an insert into a table that contains an (non-identity) integer primary key using something like:
INSERT INTO x(ID, Name)
SELECT {ID}, Name
FROM y
ID is a mandatory field in x.
Where ID needs be based on the Max(ID) already in x and - being primary key needs to be different for every row in the select. I cannot figure out how to do this. If there is a way to generate an incremental number for every row in the select... or another way...
Help will be much appreciated
Regards,
Gilbert
July 8, 2005 at 6:26 am
p.s.
solution must be supported in SQL2000 and SQL7.
July 8, 2005 at 6:26 am
There's probably a fancy way, but you could always create a new ID field in table Y (identity, seed = 12000 (or whatever you need your numbering to start at), increment 1) and then proceed as planned. Let SQL Server do the numbering work, then write the query to insert this into x, pretty much as you have it written above.
Obviously, this assumes that Max(ID) from x isn't changing much.
Regards
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 8, 2005 at 6:42 am
Problem is, in my situation, the same record in y can get added multiple times to x, each with a unique numbner in x - something like:
INSERT INTO x(ID, Name)
SELECT {ID}, Name
FROM y
CROSS JOIN z
The only idea i have is to create a temp table with an Identity column seeded with Max(x), insert into there from y then copy #temp --> x. Was hoping for a more efficient solution as this will happen often with large amounts of data.
July 8, 2005 at 6:53 am
From what you've said, I think the temp table / table variable (each should be tried to see which is faster) is the way to go too.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply