Solved

Script to turn off then on Identity column

Posted on 2009-05-14
19
859 Views
Last Modified: 2012-05-07
I need some script (T-SQL) to turn off then on the identity column of my table's ID field so I can do an insert.  How do I do that?
0
Comment
Question by:schmir1
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24389053
SET IDENTITY_INSERT OFF

-- T-SQL

SET IDENTITY_INSERT ON
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24389060
Sorry, just a bit dyslexic.

-- turn on identity inserting
SET IDENTITY_INSERT ON

-- do some t-sql inserting

-- turn off identity inserting
SET IDENTITY_INSERT OFF
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24389065
http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx

Having all sorts of problems today getting this out correctly.  See the above.

SET IDENTITY_INSERT {table name} ON/OFF
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24389074
SET IDENTITY_INSERT TableName ON
SET IDENTITY_INSERT TableName OFF
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24389078
You have to specify the table name in the set identity table.  And you have to then specify all of the columns in your insert statement.

SET IDENTITY_INSERT <TABLENAME> ON
INSERT INTO <TABLENAME> (Field1,field2,field3,etc...)
values.......
SET IDENTITY_INSERT <TABLENAME> OFF
0
 

Author Comment

by:schmir1
ID: 24389197
Maybe I didn't say it correctly.  I need to modify my id key field from being a Identity Specification of Yes to No and back again after the Insert.  With the INSERT that I'm using SET IDENTITY_INSERT TableName ON and SET IDENTITY_INSERT TableName OFF don't help.  I would think I would use an ALTER statement?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24389286
You cannot make an identity column non-identity, or a non-identity column identity, without recreating the table.

If you do it through the GUI, it does that as I describe here:

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24409994.html?cid=236#a24389109
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24389300
"With the INSERT that I'm using SET IDENTITY_INSERT TableName ON and SET IDENTITY_INSERT TableName OFF don't help"

What do you mean it didn't help?  Did you specify the field list for insertion as I described you must above in post http:#a24389078 ?
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24389776
It would only not help if you are trying to make the column no longer an identity to insert data that violates the unique constraints of an identity.  In which case, you will not be able to set that column back as an identity/unique column anyway despite Brandon's point.

Look at what you are trying to do carefully and you will either see that we have provided you a solution with turning on identity inserting so you can insert records with pre-defined identity values OR that you cannot do what you are trying to do.

You may even find, you don't need to do that at all if you specify column list to insert and leave out the identity column and let SQL take care of automatically generating that value.
0
 

Author Comment

by:schmir1
ID: 24390120
I'm thinking that the best way to go is to manual change the ID column.  I don't know how to specify a column list automatically and I've got thousands of column spread over hundreds of tables.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24391586
I'm not sure you know what you want to do.  Generally you don't want to force a specific identity value in.  If you do, then you have to specify the field list for insert after enabling the table for identity insert with the statements provided above.

But REGARDLESS, you should ALWAYS specify the field list when doing an insert statement.  This will ensure that DDL changes later don't break existing DML statements.
0
 

Author Comment

by:schmir1
ID: 24396316
Thanks for your advice.  I DEFINITELY DO NOT know what I'm doing.  This is a one time deal to get my Access DB to SQL Server.

I really don't understand some of what you are talking about.  I will do some reading this week-end.  

It is just unbelievable to me that I CAN change the ID column from "IS Identity=YES" to "IS Identity=NO" in the Server Management Studio Column Properties for the table but CANNOT do it with script?

Note:  I've done the following and the table seems just fine after:
1) Set column property for ID column - "IS Identity=NO"
2) INSERT data without naming any columns
3) Set columm property for ID column - "IS Identity=YES"

I may be missing something but my test tables work OK after this.
I'm going to start doing it for real now.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24396409
The script we provided you above does the same thing!  Not sure what the issue with it was.  Did you try it?

-- turn on identity inserting
SET IDENTITY_INSERT your_table_name ON

-- do some t-sql inserting
INSERT INTO your_table_name
VALUES(12345, 'x','y','z')

-- turn off identity inserting
SET IDENTITY_INSERT your_table_name OFF
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 100 total points
ID: 24396472
If you want to script changing a column from being an Identity (Primary Key) column, you can look at ALTER TABLE: http://msdn.microsoft.com/en-us/library/ms174123.aspx.  The point is that doesn't need to be done to do what you are asking.  Simply turn on identity inserting, insert your data, then turn it back off.  Leaving the column as an identity will ensure that the uniqueness the column needs to be a primary key is maintained during the insert of your data from MS Access.  
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 400 total points
ID: 24397500
"It is just unbelievable to me that I CAN change the ID column from "IS Identity=YES" to "IS Identity=NO" in the Server Management Studio Column Properties for the table but CANNOT do it with script?"

You can.  But as I said, when SSMS does it in does the steps in the post referenced in http:#a24389286.  It WILL drop the table and re-create it.  If you don't believe me, run SQL Profiler while you uncheck the box and click save table.
0
 

Author Comment

by:schmir1
ID: 24484372
I'm going to use the Migration Manager.  Thanks for all your help.
0
 

Author Closing Comment

by:schmir1
ID: 31581657
BrandonGalderis:  You are absolutely right.  Dropping the table is what Migrations assistant does also.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24484504
I think that mwvisa1 and myself have provided you with all the necessary detail to assist you and that a split between the two of us would be the most fair allocation of points.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24484507
:)  Spoke to soon.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Parsing the XML data to SQL Server 4 67
Help with SQL joins 9 54
INSERT DATE FROM STRING COLUMN 18 59
Help Required 2 39
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question