I want to import data into db.
In order to do so I need to set indentity insert on.
Setting this with edit mapping for 500+ tables is not an option.
So I found this SO Question
But some of that tables already have it set to ON so Puting this in one query will produce error on 1 wrong statement and stopped. How to force it to ignore errors end go to next statement until it will try to set it ON for all tables?
How are you importing your data? You might want to take a look at Keep Identity Values When Bulk Importing Data.
You can only turn IDENTITY_INSERT on for one table at a time (per session). If you need to you can use this query to generate a script to turn IDENTITY_INSERT off for every applicable table.
select
N'SET IDENTITY_INSERT ' + quotename(s.name) + N'.' + quotename(o.name) + N' OFF'
from
sys.objects o
inner join sys.schemas s on s.schema_id = o.schema_id
inner join sys.columns c on c.object_id = o.object_id
where
o.type = 'U' and
c.is_identity = 1
-- for sql server 2005 and on begin try
set identity_insert (table 1 name) on
end try
begin catch
print ' table 1 already on'
end catch
begin try
set identity_insert (table 2 name) on
end try
begin catch
print ' table 2 already on'
end catch
