Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-01-27 : 07:43:11
|
| Kevin writes "Grettings, all.On my current project, we are slowly migrating to a new database. The new database uses surrogate keys (please no debate on this subject--it's been settled).When I copy the data from the old database to the new database, I need to generate "one up" keys. However, we can't use identity because we need to be able to insert these generated values into their rows from the application (which identity doesn't allow).I'd like to use statements like:insert into new_table(id, c1, c2, c3)select @id, v1, v2, v3from old_database_tableHowever, I can't figure out how to increment @id for every value inserted.I've tried functions, stored procedures, incrementing the value in line, and more. Currently, I have to solve this using cursors, which is painfully slow, and a pain to code. I'm looking for a solution that will either allow me to increment a number so I can update my sequence in the databases at the end of my batch, or a solution that updates the sequence as it is used.Can anyone help me figure out a way to do this? I'm currently thinking that it's impossible.As an aside, to perhaps clarify what I'm asking, in Oracle, I would just specify sequence_name.nextval for the id:insert into new_table(id, c1, c2, c3)select sequence_name.nextval, v1, v2, v3from old_database_table.Thanks for any help!--Kevin" |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-01-27 : 09:36:02
|
| Perhaps the following link will help you:http://dbforums.com/t555020.html |
 |
|
|
graz
Chief SQLTeam Crack Dealer
4149 Posts |
Posted - 2004-01-27 : 09:41:53
|
| http://www.sqlteam.com/item.asp?ItemID=765 will create a sequence number on the table after the records have been inserted. If you need a sequence as you go, I'd create a temp table with an identity column and columns for the primary keys. Insert all the primary key values into that temp table. When you select back out you'll have the identity values as a sequence number and you can join back to your original table for the rest of the row.===============================================Creating tomorrow's legacy systems today.One crisis at a time. |
|
|
|
|
|
|
|
|
Incrementing Number in Select