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 |
|
LianaS
Starting Member
6 Posts |
 Posted - 2003-06-11 : 11:54:27
|
| Hy everybody!I have a table as follows:CREATE TABLE Test_Constr1 ( ValId int IDENTITY (1, 1) PRIMARY KEY , ValName varchar(100) NOT NULL, V2 int NOT NULL UNIQUE )I want to remove the identity property from the primary key column (ValId) and set it to the V2 column .I know I can do this from the Enterprise Manger but I need to create a script to alter the table and I don't know what statements to use. I looked at the ALTER TABLE statement but it's no good: I may specify IDENTITY only when adding a column.Thank you. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-11 : 12:13:56
|
| Here's an easy way to get the T-SQL for this. Go into Enterprise Manager. Go to your table and into Design mode. Set identity value to No. Now click save change script. It's a button at the top towards the left. Then save your script. Now close the table window without saving the changes. So what you did was save the T-SQL that EM was going to use, but you didn't actually make the change to the table. Have a look at the T-SQL, you'll see why this method was much easier than writing all of that yourself.Tara |
 |
|
|
AndyB13
Aged Yak Warrior
583 Posts |
Posted - 2003-06-11 : 12:15:22
|
| Enterprise Manager (Table design view) can generate scripts for you.After you have done your changes PRIOR to saving your table click on the 3rd icon from the left "Save change script" copy and paste this script into query analyser or save it for later use. Then just quit the design view without saving changes.Read the script to see what it is actually doing that way you will learn from itAndy |
|
|
|
LianaS
Starting Member
6 Posts |
Posted - 2003-06-12 : 07:03:41
|
| Thank you very much. |
|
|
|
|
|
|
Remove the identity form a column