March 4, 2012 at 8:53 pm
Hi,
I am copying data of multiple tables from one database to another using stored procedure. Table names were table-driven so I can just put it on a cursor and insert. Tables were like 110 tables and errors encountered are around 20 errors. So some tables were processed successfully but others were not due to these errors. Following are the errors I encountered:
An explicit value for the identity column in table 'TA_GolfStix.dbo.TA_META' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
Column name or number of supplied values does not match table definition.
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TACOMA_ClubTypeOption_TACOMA_OptionType". The conflict occurred in database "TA_GolfStix", table "dbo.TACOMA_OptionType", column 'OptionType'.
Cannot truncate table 'TA_GolfStix.dbo.TACOMA_OptionType' because it is being referenced by a FOREIGN KEY constraint.
Here's the whole script I used.
=======================================
CREATE PROCEDURE [dbo].[CopyDBData]
AS
BEGIN
DECLARE
@TName VARCHAR(100),
@DBOrigin VARCHAR(100),
@DBDestination
VARCHAR(100),
@SqlStmt VARCHAR(150)
SET NOCOUNT ON
DECLARE CheckRec CURSOR FOR
SELECT TableName
FROM TAUTIL_DBDataMnt
OPEN CheckRec
FETCH NEXT FROM CheckRec INTO @TName
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
SET @SqlStmt = 'TRUNCATE TABLE ' + @DBDestination + '.dbo.' + @TName
--PRINT @SqlStmt
EXEC (@SqlStmt)
SET @DBOrigin = (SELECT DBOrigin FROM TAUTIL_DBDataMnt WHERE TableName = @TName)
SET @DBDestination = (SELECT DBDestination FROM TAUTIL_DBDataMnt WHERE TableName = @TName)
SET @SqlStmt = 'INSERT INTO ' + @DBDestination + '.dbo.' + @TName +
' SELECT * FROM ' + @DBOrigin + '.dbo.' + @TName
--PRINT @SqlStmt
EXEC (@SqlStmt)
COMMIT TRAN
FETCH NEXT FROM CheckRec INTO @TName
END
CLOSE CheckRec
DEALLOCATE CheckRec
END
=======================================
Please someone help me... Thanks!
March 4, 2012 at 9:29 pm
You can not truncate the table if any column is referred by any other table like primary and foreign key relation.
you have to first delete the data from foreign key table inorder to truncate the primary table.
March 4, 2012 at 9:33 pm
Ok. This answers one of the errors. I'll note that. Thanks, Grasshopper!
manukumar (3/4/2012)
You can not truncate the table if any column is referred by any other table like primary and foreign key relation.you have to first delete the data from foreign key table inorder to truncate the primary table.
March 4, 2012 at 10:05 pm
Below error message itself tells that what correction needs to be done.
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
March 5, 2012 at 1:02 am
ladyblue1075 (3/4/2012)
Ok. This answers one of the errors. I'll note that. Thanks, Grasshopper!manukumar (3/4/2012)
You can not truncate the table if any column is referred by any other table like primary and foreign key relation.you have to first delete the data from foreign key table inorder to truncate the primary table.
Wrong. Even if the referencing table is empty, you can still not truncate the referenced table. Instead of using "truncate table", you should use "delete" to remove the rows from this table. Either that or you have to remove the foreign keys before you can do a truncate table (and afterwards recreate the FK's).
March 5, 2012 at 3:37 am
Thanks for those who replied to this post.
I have solved the problems using these dynamic scripts:
SET @SQLString =
N'SELECT @ColumnName1 = STUFF((SELECT '', '' + C.name
FROM ' + @SourceDB + '.sys.columns C
WHERE OBJECT_NAME(C.object_id) = ''' + @TName + '''
AND C.is_identity = 0
AND C.system_type_id <> 189
FOR XML PATH('''')),1,1,'''') '
SET @ParamDefination = N'@ColumnName1 VARCHAR(MAX) OUTPUT'
EXEC sp_executesql @SQLString, @ParamDefination , @ColumnName1 = @ColumnName OUTPUT
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply