Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 889
  • Last Modified:

Script to turn off then on Identity column

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
schmir1
Asked:
schmir1
  • 7
  • 6
  • 5
  • +1
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
SET IDENTITY_INSERT OFF

-- T-SQL

SET IDENTITY_INSERT ON
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Aneesh RetnakaranDatabase AdministratorCommented:
SET IDENTITY_INSERT TableName ON
SET IDENTITY_INSERT TableName OFF
0
 
BrandonGalderisiCommented:
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
 
schmir1Author Commented:
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
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
"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
 
Kevin CrossChief Technology OfficerCommented:
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
 
schmir1Author Commented:
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
 
BrandonGalderisiCommented:
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
 
schmir1Author Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
BrandonGalderisiCommented:
"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
 
schmir1Author Commented:
I'm going to use the Migration Manager.  Thanks for all your help.
0
 
schmir1Author Commented:
BrandonGalderis:  You are absolutely right.  Dropping the table is what Migrations assistant does also.
0
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
:)  Spoke to soon.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 7
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now