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 |
|
elektrobank
Starting Member
15 Posts |
 Posted - 2003-02-19 : 02:18:10
|
| Is there any way to manually insert a value into an ID (indentity) field?I'm having the following problem. I have made many schema changes and renamed my database. I need to write an updater for my clients who are currently using the old schema so they can start using the newest version of my software with then new schema. My database updater does the following, it first creates the new database with all the tables, then it loops through all the fields in the old database, pulls the data from there and inserts it into the new database. It loops on the field names, so it only copies the old data which has the same fields in the new database, then it removes the old database.The problem I'm having is that if I insert the records and have it autogenerate the IDs for the identities, I will break the references between the tables since their ID (indentity) values will change since the user may have deleted some records, but the links to those IDs from other tables will stay the same. So what I really need is a way to copy over the old ID values from the old database to the new one, is there any way to do this? I was playing around with the ALTER command in an attempt to alter change the type to a regular int, insert my value then change it back to an indentitiy type, but that doesn't look promissing.Anyone know how I can do this or have a better overall approach for updating my database?Thanks!! |
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-02-19 : 03:32:43
|
| You can use SET IDENTITY_INSERT ON. That let's you insert values into an identity fields. Look it up in BOL for more info |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-02-19 : 10:32:22
|
| Just remeber though....From BOL:PermissionsExecute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner.That means if your release code that an application needs to execute, and you're allowing an s/p to be executed by a user (or if you're using connection pooling), you might not be able to execute this...been down this road before.Good Luck Brett8-) |
|
|
|
|
|
|
|
|
Manually inserting IDs