I tried
Alter tabletable1 alter columncolumn1 Identity(1,1);
Alter tabletable1 ADD CONSTRAINT column1_def Identity(1,1) FOR column1
they all can not work,
any idea about this? thanks
Technology Tips and News
I tried
Alter tabletable1 alter columncolumn1 Identity(1,1);
Alter tabletable1 ADD CONSTRAINT column1_def Identity(1,1) FOR column1
they all can not work,
any idea about this? thanks
Code Snippet
--create test table
create
table table1 (col1 int, col2 varchar(30))insert
into table1 values (100, 'olddata')--add identity column
alter
table table1 add col3 int identity(1,1)GO
--rename or remove old column
exec
sp_rename 'table1.col1', 'oldcol1', 'column'OR
alter
table table1 drop column col1--rename new column to old column name
exec
sp_rename 'table1.col3', 'col1', 'column'GO
--add new test record and review table
insert
into table1 values ( 'newdata')select
* from table1
You can't alter the existing columns for identity.
You have 2 options,
1. Create a new table with identity & drop the existing table
2. Create a new column with identity & drop the existing column
But take spl care when these columns have any constraints / relations.
Code Snippet
/* For already craeted table Names Drop table Names Create table Names ( ID int, Name varchar(50) )
Insert Into Names Values(1,'SQL Server') Insert Into Names Values(2,'ASP.NET') Insert Into Names Values(4,'C#') */
Code Snippet
--In this Approach you can retain the existing data values on the newly created identity column CREATE TABLE dbo.Tmp_Names ( Id int NOT NULL IDENTITY (1, 1), Name varchar(50) NULL ) ON [PRIMARY]
go SET IDENTITY_INSERT dbo.Tmp_Names ON
go IF EXISTS(SELECT * FROM dbo.Names) INSERT INTO dbo.Tmp_Names (Id, Name) SELECT Id, Name FROM dbo.Names TABLOCKX
go SET IDENTITY_INSERT dbo.Tmp_Names OFF
go DROP TABLE dbo.Names
go
Exec sp_rename 'Tmp_Names', 'Names'
Code Snippet
--In this approach you cant retain the existing data values on the newly created identity column; --The identity column will hold the sequence of number
Alter Table Names Add Id_new Int Identity(1,1) Go
Alter Table Names Drop Column ID Go
Exec sp_rename 'Names.Id_new', 'ID','Column'
thank you
butI could not change the existing number order and add identity
any idea about this?
If you have ms sql srvr mgmt studio you can do it. You just have to do it manually, not in script or command form.
connect to your db
show the table/column namesin object explorer window
right click oncolumn name
select modify
in column properties tab
alter "identity specification"/"is identity" to YES
set increment/seed properties as desired
Note: if you leave it at default 1,1 it will automatically insert the next row with the appropriate nextvalue (not 1)
Also, I had dup vals in the column but my primary key is a compound key with that and another field and I had no problem accessing the old data - just needed to keep the key with both fields.
I hope thisresponse helps and isnt too late... Jane.
In the management studio, drill down to the table and then to the column you want to change to identity. Right click on the column and select Modify option. At the bottom of the screen, you will find column properties. Scroll down tha list and you will see "Identity Specification". Expand that and change (Is Identity) to Yes and give in the Seed (just make sure it is after the latest value that you already have in the table just to avoid conflicts later on) and incremental values and save. That should do the trick.
I had rows existing in my table and managed to keep them with the existing values. New rows loaded did have the id value automatically generated. Did not generate from the seed that I gave, but I am not sure if it was because of rows that had previously existed in the table that I had deleted earlier.
Hope this helps.
You can do it manually through 'Management Studio' but there appears to be a bug which stops you from doing it through SQL. There is even Microsoft sample code:-
CREATE TABLE MyCustomers (CustID INTEGER IDENTITY (100,1) PRIMARY KEY, CompanyName NvarChar (50)) INSERT INTO MyCustomers (CompanyName) VALUES ('A. Datum Corporation') ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2)
which doesn't work on SQL Server 2005 queries.
What you have to do is create a table with the 'identity' column, but with the 'Identity_Insert' property set so that you can put values into it. Unfortunately you must set the 'Identity_Insert' on the server itself (not a linked server) so that everything gets done in the same session. And you have to unset it before you can set it again, even for another table. Very shabby. And all because you can't do in this SQL implementationwhat you can do manually.
you do not need to drop the table or column. You have been given bogus misinformation, which is annoying appearing in SQL-Server Help
I use DBCC Checkident with reseed option to reset identity columns
this example is in books online
USE AdventureWorks; GO DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30); GO |
you do not need to drop the table or column. You have been given bogus misinformation, which is annoying appearing in SQL-Server Help
I use DBCC Checkident with reseed option to reset identity columns
this example is in books online
with this you can alter the column for identity.
USE AdventureWorks; GO DBCC CHECKIDENT ('HumanResources.Employee', RESEED, 30); GO
The easiest way I have found to add an identity to an existing column (assuming you don't have direct access through SSMS) is to
1. Script out the table definition to a query
2. Rename the table in the script and add identity(1,1) to the column you need the identity on
3. Rename the primary key (I usually use the same name and put a 2 behind it)
4. Run the create table script
(Note that the 4 steps above are prerequisites for this technique. Those are really simple steps, so I figured they didn't need much more explanation; however, the procedure that follows it to perform the switcheroo between the table that doesn't have an
identity and the table that does can be a bit touchier)
here comes the magic:
--1.Disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
--2."Switch TO" everything from the existing table to the new table
ALTER TABLE [tablename] SWITCH TO [tablename2]
--3.Drop the table that had no identity drop table [tablename]
--4. Rename the table with new identity column to old table name sp_rename 'tablename2','tablename'--5. Rename pk to match old pk name sp_rename 'pk_tablename2','pk_tablename'
--6. Re-enable all constraints exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
You'd be surprised how much more difficult it is to explain it than it is to actually do it.
Hi All,
Another post for the same. Please try it.
http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/
Starting with SQL Server 2012 SEQUENCE object can be used instead of IDENTITY:
CREATE SEQUENCE Sales.SalesOrderHeaderIdent START WITH 1 INCREMENT BY 1 ; GO SELECT NEXT VALUE FOR Sales.SalesOrderHeaderIdent; GO 50
Hi Toth,
It is great. Here is the complete info about CREATE SEQUENCE in 2012
Hi Toth,
I got one doubt:
If source is other than SQL Server & need to generate this sequence no then How can we do this in ETL? Please can you suggest if any work around?
>If source is other than SQL Server & need to generate this sequence
You import the source first into a SQL Server staging table:
CREATE SEQUENCE NotSSSource START WITH 1 INCREMENT BY 1 ; GO CREATE TABLE StageNotSSSource ( ID INT DEFAULT (NEXT VALUE FOR NotSSSource), ProductID int, ListPrice smallmoney) GO INSERT StageNotSSSource (ProductID, ListPrice) SELECT ProductID, ListPrice FROM AdventureWorks2012.Production.Product WHERE Color is NOT NULL; GO SELECT * FROM StageNotSSSource ORDER BY ID; GO /* ID ProductID ListPrice .... 28 721 1489.3326 29 722 350.8437 30 723 350.8437 31 724 350.8437 32 725 350.8437 33 726 350.8437 .... */
Basically there are four logical steps.
1. Create a new Identity column. Turn on Insert Identity for this new column.
2. Insert the data from the source column (the column you wished to convert to Identity) to this new column.
3. Turn off the Insert Identity for the new column.
4. Drop your source column & rename the new column to the name of the source column.
There may be some more complexities like working across multiple servers etc.
Please refer the following article for the steps (using ssms & T-sql). These steps are intended for beginners with less grip on T-SQL.
http://social.technet.microsoft.com/wiki/contents/articles/23816.how-to-convert-int-column-to-identity-in-the-ms-sql-server.aspx
An easier way is to Go to Tools>Designers>Table and Designers and uncheck "Prevent Saving Changes That Prevent Table Recreation." You can always turn the function back on after you're happy with your table structures.
It looks like very nice solution for alter column as identity instead of creating a new table a d renaming.
What's your thought?