IDENTITY_INSERT 1

[mimi2]

Hello,
I would like to insert all the rows in table1 to table2.
insert into table2 (empty for now)
select * from table1 where id=5

howerver, table1 and 2 have an identity field.
How can i work around that ?

Thanks for any hint.

 

[SQLSister]

If you don;t care if the identities match up, then you simply identify the columns you want to copy and omit copying the identity column.

insert into table2 (col1, col2, col3, col4)
Select col1, col2, col3, col4 from table 1 where id = 5

As a general rule you should always identify the columns not use the *. In the first place it keeps you from sending more columns than needed reducing network traffic, and inthe case of an insert or Union query, it makes sure that you match the columns of one table directly to the corrct column in the other table and insures your code won't break if someone moves columns around or adds them.

Fastest way to add all columns in Query analyzer is to use the object rowser and drag the columns from the table. If you open the columns to see the columns and drag the word columns over you get all of them, otherwise you can drag which individual columns you want. Usually I drag all and tehn just delete the indentity column from the query code after rather than dragging each column individually.

 

[mimi2]

Thanks a lot.
I liked the trick about dragging columns. It saves time.

Is there something like alter table table_name nocheck all (constraints) for identity ?

 

[mihaildamjan]

Below is a procedure I created to go through the whole database, check if a table has a identity column, and disables the identiyu insert on it.
I did this for loading data in an empty database.

You can do it for an individual table like this:

set identity_insert table_name on


************************
declare @Str varchar(200), @Str1 varchar(200)
--take first table
select @Str1 = min(name) from sysobjects where xtype = 'U'
while @Str1 is not null
begin
if ident_incr(@Str1) is not null --take only tables that have identity columns
begin
select @Str = 'set identity_insert ' + @Str1 + ' on '
exec(@Str) -- Execute string
--print @Str -- Print for testing only
end
select @Str1 = min(name) from sysobjects where xtype = 'U' and name > @Str1
end
************************