Solved

Script to turn off then on Identity column

Posted on 2009-05-14
19
854 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PK numbers not follow 8 61
index  - last use and update 8 56
if and else in stored procedure 19 42
How to disable/enable multiple sql jobs in efficient way 11 100
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

920 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

11 Experts available now in Live!

Get 1:1 Help Now