Sql Server Identity seed on/off by code

Posted on 2010-11-17
Last Modified: 2012-08-14
Hi Experts,

Just like to make you a bit busy.

I am just converting my software from MS-Access to Sql Server 2005 Express Edition. Have to convert all the my clients databases. I set all the create table script and then I import the data from MS-Access to Sql server using Import Export Wizard of Sql Server. Now I like to put identity specification to almost all the tables. Can you let me have the command to turn it on.

Right now I have to modify each table from designer and enable identity. For one time I like to develop a script and will apply the same to other client's database.

I cannot specify identity on at create table as I have to import the data.

Please let me have the possible solution.

Thanks & Best Regards

Jay Khatri
Question by:jaykhatri
  • 4
  • 3
LVL 57

Accepted Solution

Raja Jegan R earned 500 total points
ID: 34161467
Hope you have created the Primary key records with IDENTITY property and while inserting records for your table including IDENTITY column, follow the below


INSERT into ur_table_name (IDENTITY_key_column, other_columns_list)
SELECT columns_list
FROM access_other_table


Do note that you can set IDENTITY INSERT property of only one table in a particular database to ON and hence set it to OFF before turning it ON for another table.

Author Comment

ID: 34161730
CREATE TABLE [dbo].[ac_nature2](
      [ac_natureid] [int] IDENTITY(1,1) NOT NULL,
      [ac_nature] [nvarchar](255) COLLATE SQL_Latin1_General_CP850_CS_AS NULL,
      [ac_natureid] ASC

insert into scdb.dbo.ac_nature2 select * from pragati...ac_nature
SET IDENTITY_INSERT scdb.dbo.ac_nature2 OFF

I am doing this

An explicit value for the identity column in table 'scdb.dbo.ac_nature2' can only be specified when a column list is used and IDENTITY_INSERT is ON.

and I am getting this.
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34161766
>> An explicit value for the identity column in table 'scdb.dbo.ac_nature2' can only be specified when a column list is used and IDENTITY_INSERT is ON.

In order to avoid this error, you need to specify column names explicitly as mentioned in my comment

INSERT into ur_table_name (IDENTITY_key_column, col1, col2, other_columns_list)
SELECT IDENTITY_key_column, col1, col2, other_columns_list
FROM access_other_table

Expert Comment

ID: 34162611
To know how the Identity property can be enabled in a table through SSMS designer or by T-SQL script please go through this detailed article:
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.


Author Comment

ID: 34163354
Hi subhashpunia,

This is called something like explicit arrangement not the concrete solution provided by the language to achieve this.

If still I have to mention columns in your that example, its better to do mention columns explicitly as suggested by rrjegan17.

Am I right.

I haven't yet worked out over the suggested code. But once I will finish it with success. I will confirm it.

Thanks & Best Regards

Jay Khatri
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34169840
>> If still I have to mention columns in your that example, its better to do mention columns explicitly

Yes, that is the recommended approach whenever you use SET IDENTITY INSERT statements.

Author Comment

ID: 34170241
Hi rrjegan17,

Absolutely perfect solution, I never learned sql with book or any institute. What I learned is msdn and google and rest with my job. So know some stuff what an expert know and even don't know some stuff which even a Beginner may know.

Thanks alot, I am closing the ticket now.

Best Regards

Jay Khatri
LVL 57

Expert Comment

by:Raja Jegan R
ID: 34170351
Welcome Jay and wish you good luck in your learning..

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2000: == Linked Server, With higher version == 16 114
Need help with a query 3 46
Getting the 3 middle digits 4 36
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

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

16 Experts available now in Live!

Get 1:1 Help Now