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 |
|
dlcollison
Starting Member
8 Posts |
 Posted - 2007-02-15 : 15:21:33
|
| Hi ...I've taken over a project where the tables were created with identity/primary key columns of type DECIMAL(12,0). The latest addition to the project is to replicate data down to Pocket PC applications. Replication requires that identify/primary key columns be of type INT/BIGINT.I've attempted to ALTER TABLE xxx ALTER COLUMN yyy BIGINT; and it fails. I then did an ALTER TABLE xxx NOCHECK CONSTRAINT ALL; for every table in the system to disable checking of foreign keys and then attempted to alter the column to a bigint and it still failed.How can I change the column from Decimal to BIGINT - or do I have to create new tables, import all the data, get rid of the original tables? Please tell me I don't have to do the latter.Thanks ...David L. Collison Any day above ground is a good day! |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-16 : 08:27:39
|
"do I have to create new tables, import all the data, get rid of the original tables? "yah, that's about it!In SQL 200 I make the change in Enterprise Manager : Design Table.WITHOUT SAVING the change I use the "Script Changes button"I then Abandon the change, review the script, and run the script (which I can easily do on DEV, QA, TEST and Production etc.)The script generated will do an ALTER TABLE where it can, but where it can't it will neatly script all the Drop Constraints, Create temporary table (in new "shape"), Insert data from old table, Drop old table, Rename temporary table, Create PK and Indexes and finally Recreate any FKs.Obviously it can take a while for the script to run if there are lots of rows in the table, but it is certainly "painless" to produce the script - if that is your main concern?  Kristen |
 |
|
|
|
|
|
|
|
Change Identify/Primary Key Column Data Type?