Solved

Script to turn off then on Identity column

Posted on 2009-05-14
19
851 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now