jaykhatri
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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
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
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/
http://blog.sqlauthority.com/2009/05/03/sql-server-add-or-remove-identity-property-on-column/
ASKER
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
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
>> 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.
Yes, that is the recommended approach whenever you use SET IDENTITY INSERT statements.
ASKER
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
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
Welcome Jay and wish you good luck in your learning..
ASKER
[ac_natureid] [int] IDENTITY(1,1) NOT NULL,
[ac_nature] [nvarchar](255) COLLATE SQL_Latin1_General_CP850_C
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.