Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Script to turn off then on Identity column

Posted on 2009-05-14
19
Medium Priority
?
877 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24389053
SET IDENTITY_INSERT OFF

-- T-SQL

SET IDENTITY_INSERT ON
0
 
LVL 60

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 60

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 60

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 60

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 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 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 1600 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

704 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