I've done some research on setting the default value of a column and found the following code:
ALTER TABLE [table1] ALTER COLUMN [column1] SET DEFAULT 0
I am trying to set column1 within table1 to a default value of 0, column1 is an int data type. But it doesn't work within MS SQL SERVER 2000.
Can anyone tell me what's wrong?what you want is this:
ALTER TABLE table1
ADD CONSTRAINT DF_table1_column1
DEFAULT 0 FOR column1|||Thanks very much! It's appreciated.
Showing posts with label column1. Show all posts
Showing posts with label column1. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
Alter a column to be the table identity column
i have a table
table1
column1 int not null
column2 char not nul
column3 char
i want to script a change for table1 to alter column1 to be the table identity column. not primary.you can not. You must create a new table with an identity column already defined and copy your data from the old to the new, drop your old table and rename your new table|||Just a curious question then why is that i can use the enterprise manager to do it?
the tables in question have data in them which is why i dont want to drop them.|||Just a curious question then why is that i can use the enterprise manager to do it?
the tables in question have data in them which is why i dont want to drop them.
Care to guess what Enterprise Mangler is about to do in the background?|||Care to guess what Enterprise Mangler is about to do in the background?
it is really curious that the enterprise mangler does use to do this operation. I had a whole of tables to convert to identity once and I traced the operation that EM does and there is a lot of weird stuff in their that does not pan out in the QA.
as for dudes question. you need to do a an
INSERT INTO MyNewTable
SELECT ... FROM MyOldTable
before you drop the old table.|||Well since it was the client that messed the tables up by importing them into another db then importing them back without selecting all objects, i showed them how to fix them using the EM and said to get after it. (1242 tables in all which is why i was trying to find a way of scripting it). i did use another clients db to create all the indexes and sent to them to use.
anyways thanks for all the time and help.
table1
column1 int not null
column2 char not nul
column3 char
i want to script a change for table1 to alter column1 to be the table identity column. not primary.you can not. You must create a new table with an identity column already defined and copy your data from the old to the new, drop your old table and rename your new table|||Just a curious question then why is that i can use the enterprise manager to do it?
the tables in question have data in them which is why i dont want to drop them.|||Just a curious question then why is that i can use the enterprise manager to do it?
the tables in question have data in them which is why i dont want to drop them.
Care to guess what Enterprise Mangler is about to do in the background?|||Care to guess what Enterprise Mangler is about to do in the background?
it is really curious that the enterprise mangler does use to do this operation. I had a whole of tables to convert to identity once and I traced the operation that EM does and there is a lot of weird stuff in their that does not pan out in the QA.
as for dudes question. you need to do a an
INSERT INTO MyNewTable
SELECT ... FROM MyOldTable
before you drop the old table.|||Well since it was the client that messed the tables up by importing them into another db then importing them back without selecting all objects, i showed them how to fix them using the EM and said to get after it. (1242 tables in all which is why i was trying to find a way of scripting it). i did use another clients db to create all the indexes and sent to them to use.
anyways thanks for all the time and help.
Subscribe to:
Posts (Atom)