February 3, 2009 at 6:21 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
February 3, 2009 at 6:48 pm
Not sure if this will help, but you may be incurring a lock on sys.objects
Can you try a NOLOCK on sys.objects when creating the cursor, using a READ_ONLY cursor or insert the records into a temporary table and loop from there.
Please also have a look at: http://www.sqlservercentral.com/scripts/T-SQL+Aids/30097/
Also, check if you have any foreign key constraints that need disabling.
Max
February 3, 2009 at 7:25 pm
Duplicate post. Please post answers in this thread: http://www.sqlservercentral.com/Forums/Topic649461-338-1.aspx
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply