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 |
|
Jake_BR
Starting Member
4 Posts |
 Posted - 2007-09-04 : 08:10:16
|
| Hey. How that i make to set a column as identity from a table that is already created and with pk?i know how to change the seed value, but i need to know, how to make that column as identity and set the identity on! thanks! |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-04 : 08:15:32
|
| You can do this in Enterprise Manager. Right Click the table, choose DESIGN, change the attributes on the column to be Identity (and set the Seed etc.), and Save.This will make a new table, copy all the data across, set the identity value, then drop the old column and rename the new table to the original name.Take a backup of the database first!Kristen |
 |
|
|
Jake_BR
Starting Member
4 Posts |
Posted - 2007-09-04 : 08:28:41
|
Thanks for the reply, By EM i know how to make it! The problem is that i have more then 30 DB and more then 30 tables in each. So i need a sql script to just copy and paste in another database. my company is changing from interbase to sql server. thats why if we make in EM its gonna take an huuuuuuuuuuuuge time!  |
|
|
|
jaypee_s
Starting Member
22 Posts |
|
|
Jake_BR
Starting Member
4 Posts |
Posted - 2007-09-04 : 09:02:09
|
| Dude, your msg make-me happy and sad at same time couse of what that guy said:"There is no convenient ALTER TABLE command for this. (Except in SQL Server CE!)."So, thank you very much! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-04 : 09:15:15
|
| Yup, 'fraid so. You can get Enterprise Manager Design Table Thingie to generate a script for you (of a sample table change). That would give you the gist of the change that is needed, but I'm afraid its gonna be horrid:Drop FKs, if any (1)Maybe also need to drop some Constaint stuff (2)Create new temp table (same as original, but with IDENTITY stuff)Copy data to temp tableDrop original tableRename Temp Table to Original TableRe-create Constraints as per (2)Re-create FKs as per (1)not easy to generate an automatic script across 30 databases ...One option might be to script the whole database, use some sort of programmers editor (i.e. with powerful Find&Replace) to change the ID columns to be Identity.Create a brand new temporary database, copy data from old database, delete original and rename temporary to original name.Kristen |
|
|
|
Jake_BR
Starting Member
4 Posts |
Posted - 2007-09-04 : 09:30:10
|
| Yea thats it! i gonna make throught the hard way! But thanks guys! Leme go and cry now! :PC yA |
|
|
|
|
|
|
|
|
Identity question!