February 3, 2009 at 6:25 pm
I need to copy records from tables in one database to tables of the same name in another database. (using SQL Server 2005)
II used the following dode
declare @sql varchar (8000)
Declare @tablename varchar (255)
DECLARE locTable_cursor CURSOR
FOR SELECT NAME FROM SYSOBJECTS WHERE [NAME] like N'loc%' AND type = 'U'
OPEN locTable_cursor
FETCH NEXT FROM locTable_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
select @sql ='if OBJECTPROPERTY(object_id(''' + @tablename +'''), ''TableHasIdentity'') <> 0 begin SET IDENTITY_INSERT dbo.' + @tablename+ ' ON end'
select @sql = @sql + ' insert into matremdata.dbo.' + @tablename + ' SELECT * FROM MATMPOdata_test.dbo.' + @tablename
print @sql
exec (@sql)
exec ('if OBJECTPROPERTY(object_id('''+@tablename+'''), ''TableHasIdentity'') <> 0 begin SET IDENTITY_INSERT dbo.' + @tablename+ ' OFF end')
FETCH NEXT FROM locTable_cursor INTO @tablename
END
CLOSE locTable_cursor
DEALLOCATE locTable_cursor
but the code fails as soon as it hits a table with an identity column.
can anyone see anything wrong?
john
Edited: Today @ 1:22:11 AM by john
February 3, 2009 at 6:36 pm
[font="Verdana"]You need to specifically name the columns you are inserting into when you have identity insert on.[/font]
February 3, 2009 at 6:56 pm
Thanks for the quick response, added in some code to get the field list and it works great.
John
February 3, 2009 at 7:13 pm
Sorry I thought it was working, changed code to
declare @sql varchar (8000)
Declare @tablename varchar (255)
declare @columns as varchar (8000)
declare @field as varchar (100)
DECLARE locTable_cursor CURSOR
FOR SELECT NAME FROM SYSOBJECTS WHERE [NAME] like N'loc%' AND type = 'U'
OPEN locTable_cursor
FETCH NEXT FROM locTable_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
select @columns=''
declare col_cur cursor
for select column_name
from information_schema.columns where table_name = @tablename
OPEN col_cur
FETCH NEXT FROM col_cur
INTO @field
WHILE @@FETCH_STATUS = 0
BEGIN
if @columns<> ''
begin
select @columns=@columns+ ', '
end
select @columns=@columns+ '[' + @field + ']'
FETCH NEXT FROM col_cur
INTO @field
END
CLOSE col_cur
DEALLOCATE col_cur
select @sql ='if OBJECTPROPERTY(object_id(''' + @tablename +'''), ''TableHasIdentity'') <> 0 begin SET IDENTITY_INSERT dbo.' + @tablename+ ' ON end'
print @sql
exec (@sql)
select @sql = 'insert into matremdata.dbo.' + @tablename + ' (' + @columns + ' ) SELECT ' + @columns + ' FROM MATMPOdata_test.dbo.' + @tablename
print @sql
exec (@sql)
select @sql ='if OBJECTPROPERTY(object_id(''' + @tablename +'''), ''TableHasIdentity'') <> 0 begin SET IDENTITY_INSERT dbo.' + @tablename+ ' OFF end'
print @sql
exec (@sql)
FETCH NEXT FROM locTable_cursor INTO @tablename
END
CLOSE locTable_cursor
DEALLOCATE locTable_cursor
but now get error
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'tablename' when IDENTITY_INSERT is set to OFF.
instead of
Error 8101
John
February 3, 2009 at 7:17 pm
[font="Verdana"]I'll have to have a look at the code... for starters though, I wouldn't mix using INFORMATION_SCHEMA with the sys tables. One or the other. Not both. :D[/font]
February 3, 2009 at 7:22 pm
If this is a one-time thing why not download a product like RedGate SqlDataCompare or ApexSQL SQLDiff to transfer the data? If it is done regularly why not buy one? The task would be done and tool available for use later.
Or you could use SSIS to transfer the data.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 3, 2009 at 7:25 pm
Thanks for the suggestion, but this code has to be relatively generic and run as part of a batch on a customers site.
The tables that require copying will grow as the application develops.
John
February 3, 2009 at 7:37 pm
Have you considered replication?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 3, 2009 at 7:39 pm
[font="Verdana"]I rewrote your code somewhat, and removed the dynamic SQL. I just wanted to test that I was getting a correct list of table names, columns names and whether the table has an identity field.
I also wanted to deal with schemas and quoting names.
Here was my code framework (which works):
declare@sqlvarchar(8000),
@tableSchemasysname,
@tableNamesysname,
@fullNamenvarchar(257),
@columnsvarchar(8000),
@isIdentitybit;
set @tableSchema = '';
set @tableName = '';
while (1=1) begin
--
-- get the next table schema and name
--
select top 1
@tableSchema = TABLE_SCHEMA,
@tableName = TABLE_NAME
fromINFORMATION_SCHEMA.TABLES
where(
TABLE_SCHEMA > @tableSchema or
TABLE_SCHEMA = @tableSchema and TABLE_NAME > @tablename
) and
--TABLE_NAME like N'loc%' and
TABLE_TYPE = 'BASE TABLE'
order by
TABLE_SCHEMA,
TABLE_NAME;
if (@@rowcount = 0) break;
--
-- get the list of columns for the table
--
set @columns = '';
select@columns = case @columns when '' then '' else @columns + ', ' end + quotename(COLUMN_NAME)
fromINFORMATION_SCHEMA.COLUMNS
whereTABLE_SCHEMA = @tableSchema and
TABLE_NAME = @tableName;
--
-- does the table have an identity field?
--
set @fullName = quotename(@tableSchema) + '.' + quotename(@tableName);
set @isIdentity =
case objectproperty(object_id(@fullName), 'TableHasIdentity')
when 0 then 0
else 1
end; -- case
print 'Table:' + @fullName;
print 'Columns:' + @columns;
if (@isIdentity = 1) begin
print 'Identity:Yes.';
--
-- do your set identity insert on here
--
end; -- if
--
-- do insert here
--
--if (@isIdentity = 1) begin
--
-- do your set identity insert off here
--
--end; -- if
end; -- while
[/font]
February 3, 2009 at 7:40 pm
Jack,
yes I have, and I am trying to pursuade the boss to move to it, but this sort of method will have to do for a few months till we can get the 'replication' based solution developed and tested.
John
February 3, 2009 at 7:59 pm
Bruce,
I altered the code to insert the records
----------------------------------------------------------------------
declare @sql varchar(8000),
@tableSchema sysname,
@tableName sysname,
@fullName nvarchar(257),
@columns varchar(8000),
@isIdentity bit;
set @tableSchema = '';
set @tableName = '';
while (1=1) begin
--
-- get the next table schema and name
--
select top 1
@tableSchema = TABLE_SCHEMA,
@tableName = TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where (
TABLE_SCHEMA > @tableSchema or
TABLE_SCHEMA = @tableSchema and TABLE_NAME > @tablename
) and
TABLE_NAME like N'loc%' and
TABLE_TYPE = 'BASE TABLE'
order by
TABLE_SCHEMA,
TABLE_NAME;
if (@@rowcount = 0) break;
--
-- get the list of columns for the table
--
set @columns = '';
select @columns = case @columns when '' then '' else @columns + ', ' end + quotename(COLUMN_NAME)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = @tableSchema and
TABLE_NAME = @tableName;
--
-- does the table have an identity field?
--
set @fullName = quotename(@tableSchema) + '.' + quotename(@tableName);
set @isIdentity =
case objectproperty(object_id(@fullName), 'TableHasIdentity')
when 0 then 0
else 1
end; -- case
print 'Table: ' + @fullName;
print 'Columns: ' + @columns;
if (@isIdentity = 1) begin
print 'Identity: Yes.';
--
-- do your set identity insert on here
--
select @sql ='SET IDENTITY_INSERT ' + @fullname + ' ON'
print @sql
exec (@sql)
end; -- if
--
-- do insert here
--
select @sql = 'insert into matremdata.dbo.' + @tablename + ' (' + @columns + ' ) SELECT * FROM MATMPOdata_test.dbo.' + @tablename
print @sql
exec (@sql)
if (@isIdentity = 1) begin
--
-- do your set identity insert off here
--
exec ('SET IDENTITY_INSERT matremdata.dbo.' + @tablename+ ' OFF')
end; -- if
end; -- while
-------------------------------------------------------------------------
but get the error
Table: [dbo].[locPREGnancyCOMPlication]
Columns: [pregcompID], [pregcompDesc], [pregcompcID], [pregcompIntrapartum], [pregcompAntenatal], [pregcompPostnatal], [pregcompObstHist], [infCODE], [pregcompOrigin], [pregcompSeq], [pregcompShortCutKey], [pregcompOneOnly]
Identity: Yes.
SET IDENTITY_INSERT [dbo].[locPREGnancyCOMPlication] ON
insert into matremdata.dbo.locPREGnancyCOMPlication ([pregcompID], [pregcompDesc], [pregcompcID], [pregcompIntrapartum], [pregcompAntenatal], [pregcompPostnatal], [pregcompObstHist], [infCODE], [pregcompOrigin], [pregcompSeq], [pregcompShortCutKey], [pregcompOneOnly] ) SELECT * FROM MATMPOdata_test.dbo.locPREGnancyCOMPlication
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'locPREGnancyCOMPlication' when IDENTITY_INSERT is set to OFF.
John
February 3, 2009 at 8:01 pm
Bruce,
I amended your code
--------------------------------------------------------------------------------------------
declare @sql varchar(8000),
@tableSchema sysname,
@tableName sysname,
@fullName nvarchar(257),
@columns varchar(8000),
@isIdentity bit;
set @tableSchema = '';
set @tableName = '';
while (1=1) begin
--
-- get the next table schema and name
--
select top 1
@tableSchema = TABLE_SCHEMA,
@tableName = TABLE_NAME
from INFORMATION_SCHEMA.TABLES
where (
TABLE_SCHEMA > @tableSchema or
TABLE_SCHEMA = @tableSchema and TABLE_NAME > @tablename
) and
TABLE_NAME like N'loc%' and
TABLE_TYPE = 'BASE TABLE'
order by
TABLE_SCHEMA,
TABLE_NAME;
if (@@rowcount = 0) break;
--
-- get the list of columns for the table
--
set @columns = '';
select @columns = case @columns when '' then '' else @columns + ', ' end + quotename(COLUMN_NAME)
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = @tableSchema and
TABLE_NAME = @tableName;
--
-- does the table have an identity field?
--
set @fullName = quotename(@tableSchema) + '.' + quotename(@tableName);
set @isIdentity =
case objectproperty(object_id(@fullName), 'TableHasIdentity')
when 0 then 0
else 1
end; -- case
print 'Table: ' + @fullName;
print 'Columns: ' + @columns;
if (@isIdentity = 1) begin
print 'Identity: Yes.';
--
-- do your set identity insert on here
--
select @sql ='SET IDENTITY_INSERT ' + @fullname + ' ON'
print @sql
exec (@sql)
end; -- if
--
-- do insert here
--
select @sql = 'insert into matremdata.dbo.' + @tablename + ' (' + @columns + ' ) SELECT * FROM MATMPOdata_test.dbo.' + @tablename
print @sql
exec (@sql)
if (@isIdentity = 1) begin
--
-- do your set identity insert off here
--
exec ('SET IDENTITY_INSERT matremdata.dbo.' + @tablename+ ' OFF')
end; -- if
end; -- while
--------------------------------------------------------
get the following error
Table: [dbo].[locPREGnancyCOMPlication]
Columns: [pregcompID], [pregcompDesc], [pregcompcID], [pregcompIntrapartum], [pregcompAntenatal], [pregcompPostnatal], [pregcompObstHist], [infCODE], [pregcompOrigin], [pregcompSeq], [pregcompShortCutKey], [pregcompOneOnly]
Identity: Yes.
SET IDENTITY_INSERT [dbo].[locPREGnancyCOMPlication] ON
insert into matremdata.dbo.locPREGnancyCOMPlication ([pregcompID], [pregcompDesc], [pregcompcID], [pregcompIntrapartum], [pregcompAntenatal], [pregcompPostnatal], [pregcompObstHist], [infCODE], [pregcompOrigin], [pregcompSeq], [pregcompShortCutKey], [pregcompOneOnly] ) SELECT * FROM MATMPOdata_test.dbo.locPREGnancyCOMPlication
Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'locPREGnancyCOMPlication' when IDENTITY_INSERT is set to OFF.
John
February 3, 2009 at 8:08 pm
[font="Verdana"]Aha. Sorry. Final bit sprung to mind.
You want to assemble your dynamic SQL, and only do one exec. The "set identity_insert on/off" will only apply to the context of the statement you execute it in. So as soon as your dynamic SQL finishes, the table will revert back to off.
Pseudo-SQL should read:
set @sql = '';
if (@isIdentity = 1) begin
set @sql = 'set identity_insert ' + @fullName + ' on; ';
end -- if
set @sql = @sql + 'insert into otherdb.' + @fullName + '(' + @columns + ') select ' + @columns + ' from ' + @fullName + ';';
if (@isIdentity = 1) begin
set @sql = @sql + 'set identity_insert ' + @fullName + ' off; ';
end -- if
exec (@sql);
[/font]
February 4, 2009 at 12:19 pm
Thanks, that seemed to do it
February 4, 2009 at 1:39 pm
[font="Verdana"]Good.
I had a think over this last night, and realised you could simplify it a little by using case. So that would change the psuedo-SQL to look something like this:
set @sql =
case @isIdentity
when 0 then ''
else 'set identity_insert ' + @fullName + ' on; '
end +
'insert into otherdb.' + @fullName + '(' + @columns + ') select ' + @columns + ' from ' + @fullName + ';' +
case @isIdentity
when 0 then ''
else 'set identity_insert ' + @fullName + ' off; '
end;
[/font]
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply