IDENTITY_INSERT question

Question

IDENTITY_INSERT question

Benjamin Kovac

Right there with Babe

Points: 718
More actions
August 7, 2007 at 5:06 am

#197142

Hi all,
Question for which hopefully I can get a positive answer, even if I know there is a little chance...
The situation I am in:
I needed to have a column in my table where the value will increment automatically,say:1,2,3,4,...
As I found out(help from you guys) there is the IDENTITY in MSSQL to do that.
But I sometimes need to explicitly define this increment number and as far as I know this is done by SET IDENTITY_INSERT tablename ON form.However, only one table can be set to have explicit identity column at any time and I have more than on table with explicit values.Is this limit true or there is other way to have more tables with explicit identities??
thanks
Ben

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

Only one table can be SET IDENTITY_INSERT "ON" in a session. See whthr you want to set identity insert for multiple tables in a session.

Or you can set SET IDENTITY_INSERT "OFF" for a table and then do the "ON" for the next table and so on.... ON and OFF ... ON and OFF....

"Keep Trying"

rashid nizam SSCommitted Points: 1605 More actions · Answer 2

This is true you can have only one table with identity_insert ON at any single time.

You may insert you data through scripts by explcity giving idnetity_insert On command like.

set identity_insert table_name ON

insert (script)

set identity_insert table_name Off

go

set identity_insert table_name2 ON

insert2(script)

set identity_insert table_name Off.

Moreover you can go in the Entperise Mange and disable the identity property and then after adding you desire data you can enable the identity property.

I am not sure about your actual requirement.....but i hope this would word :S

Benjamin Kovac Right there with Babe Points: 718 More actions · Answer 3

Thanks,

My situation is a bit complicated,as I had a mysql database structure and needed to copy it into MSSQL to get work with a java key manager, but seems that they do not really like each other(

the problem is that I can only edit the database creation script whereas the populating of data is done by a third party open source application tested only with mysql.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Hi

Dont know whthr this will help or not.

U can add identity columns to tables after u create the tables and import data into tehm. Identity values will be created in the identity column.

Say you have a table temp which has 2 columns (EmpID,Empname) and 10 records. A identity column (IdentityCol INT IDENTITY(1,1)) can be added to temp and this will have values 1 to 10 for the 10 records.

"Keep Trying"

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

One thing you can do, but this is a bit more labor intensive, is to create the tables without the identity properties. Then you populate it with data. Since you have a tool that copies the data, the field that would be the identity field will have a consistent set of values. After this you need to rebuild the tables that should have the identity columns. (basically:

- drop the foreign keys to the table to be modified

- drop the default bindings

- rename the table using sp_rename

- create a new table with the appropriate schema (i.e. with the identity column)

- set identity insert on on this new table

- insert into all the data into this new table from the renamed version

- set identity insert off again

- delete the old table

- create the default bindings and foreign keys

This would do what you want, but it is not a very simple procedure. This is however roughly what some third party schema synchronization tools do. You could use one of them and compare the following two databases:

A: a database that has the schema you want, i.e with the identity columns, but contains no data at all

B: a database that has a schema identical to A, but the columns that should have identity set do not have it. This database is populated with your automatic tool with all the data you need.

Then you can use the third party synchronization tool to compare the schemata, and create a migration script from database A to database B. Some third party tools will create the script that I've described above, i.e. will alter your schema and preserve the data at the same time.

For the relevant third party tools google.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Benjamin Kovac Right there with Babe Points: 718 More actions · Answer 6

Helo Andras,

If I am not wrong I should say, koszi a tippet

The whole story is about an open source application which was populating and returning data, and initially this was done with MySQL.There was a demand to get work this on MSSQL too, so started to experiment a bit.The data is travelling through the network in XML format and sometimes the ID_column(this would the identity column-int type) is not defined, which causes the ID_column's value to be automat. incremented by 1-for MySQL,and sometimes the ID_column value is defined,which value is inserted if does not already exist.

So I tried to replicate the MySQL table structure into MSSQL, but using the identity column is not a good way to do as there are more data tables in similar situation.

Probably the best way could be to edit the open source application's code for the MSSQL interaction, or I need to sit down on the open source code and work out what similar data table structure would be sufficient without the change of the source code.

Thanks

Ben