Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Change Identity Property in T-SQL Expand / Collapse
Author
Message
Posted Sunday, June 29, 2014 3:18 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:14 PM
Points: 4,243, Visits: 4,291
Sean Pearce (6/29/2014)
Welsh Corgi (6/28/2014)
Sean Pearce (5/21/2014)
Welsh Corgi (5/20/2014)
You are right you can't set the Identity Property to true.

You can set the identity property to false by simply going into design mode and set the property to false.

I just wanted to know how to do it in T-SQL.

I guess I could find out how to do so by setting up a trace.

Thank you.

SSMS will create a new table, copy the data across, delete your original table and rename the new one.



I did not see the command to change the column to an identity in you example?

There isn't one.

CREATE TABLE NewTable (ID INT IDENTITY(1, 1));
GO
SET IDENTITY_INSERT NewTable ON;
GO
INSERT INTO NewTable (ID)
SELECT * FROM OldTable;
GO
SET IDENTITY_INSERT NewTable OFF;
GO
DROP TABLE OldTable;
GO
EXEC sp_rename 'NewTable', 'OldTable';
GO

And Vice Versa


ok, thank you


For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1587487
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse