[Last Call] Learn how to a build a cloud-first strategyRegister Now

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

Sql Server Identity seed on/off by code

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
0
jaykhatri
Asked:
jaykhatri
  • 4
  • 3
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Hope you have created the Primary key records with IDENTITY property and while inserting records for your table including IDENTITY column, follow the below

SET IDENTITY INSERT ur_tableName ON;

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

SET IDENTITY INSERT ur_tableName OFF;

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.
0
 
jaykhatriAuthor Commented:
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,
 CONSTRAINT [PK_ac_nature2] PRIMARY KEY CLUSTERED
(
      [ac_natureid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

SET IDENTITY_INSERT ac_nature2 ON
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
0
Industry Leaders: 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!

 
subhashpuniaCommented:
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:

http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/
0
 
jaykhatriAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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.
0
 
jaykhatriAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome Jay and wish you good luck in your learning..
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now