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 |
|
dhw
Constraint Violating Yak Guru
332 Posts |
 Posted - 2009-06-16 : 19:07:08
|
| Hi I have a table that I need to insert several thousand rows. The problem is that the table has an Int column that is a pseudo identity column. I write "psuedo" because the values are derived from a table that contains the ID values for all the tables in this database. So, whenever an INSERT occurs, the procedure has to get the "Next" Id value for a table. Not my idea of fun.Anyway, I was able to get this to work by inserting the data into a table where I set the IDENTITY seed as the "next" value from the ID table and then inserted this data back into the original table. Not elegant, but it worked.Just wondering if there might have been a better solution (other than fixing this design, which i had nothing to do with! :) )thanks - will |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-16 : 19:38:50
|
if the ID is a continuous number, you can use row_number() to provide the running no required and then just update that source of the ID table accordingly
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-06-17 : 10:30:52
|
quote:
Originally posted by khtan
if the ID is a continuous number, you can use row_number() to provide the running no required and then just update that source of the ID table accordingly
Thanks. And I did think of using the Row_Number()...however, I needed to start my id at a particular value (287436 and not 1), which was the value for the "id" from a table that is used to control the incrementing id values for all the tables in the database. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-17 : 10:36:25
|
quote:
Originally posted by dhw
quote:
Originally posted by khtan
if the ID is a continuous number, you can use row_number() to provide the running no required and then just update that source of the ID table accordingly
Thanks. And I did think of using the Row_Number()...however, I needed to start my id at a particular value (287436 and not 1), which was the value for the "id" from a table that is used to control the incrementing id values for all the tables in the database.
you can make it start from your required value. just use 287436 + (rowno generated value -1) |
|
|
|
dhw
Constraint Violating Yak Guru
332 Posts |
Posted - 2009-06-18 : 14:28:41
|
| Doh! yeah, I forgot about that. Thanks for the help.! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-18 : 14:31:27
|
| welcome |
|
|
|
|
|
|
|
|
Update an incremental column from ID table