November 10, 2005 at 3:13 am
I have two tables I need to transfer the data, from one db to another database containing these tables.
The first table uses a IDENT column as the primary key which the second table links to to provide further information on the record.
So when transfering to the data i need to create a new primary key and update the records accordingly. I was tring to create a temporary table starting the ident at the max value(+1) in the second database using dynamic SQL but the sproc can't find the object when creating in dynamic SQL
Any pointers, or am I going down the wrong track
--Code so far
ALTER PROCEDURE usp_Transfer_Data(@STR_ID TEXT, @DB_NAME_FROM VARCHAR(30), @DB_NAME_TO VARCHAR(30))
AS
--
DECLARE @SQL NVARCHAR(3000)
DECLARE @X INTEGER
DECLARE @Y INTEGER
DECLARE @Tmp VARCHAR(20)
DECLARE @MAX_ID BIGINT
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @MAX_ID = MAX(ID) FROM TRACEBASE_NEW.dbo.TBL_RELATIONSHIPS
SET @MAX_ID = @MAX_ID + 1
SET @SQL = "CREATE TABLE #TMP_TABLE_REL ("
SET @SQL = @SQL + "ID BIGINT IDENTITY (" + CONVERT(VARCHAR(30),@MAX_ID) + ",1) NOT NULL,"
SET @SQL = @SQL + "DOC_ID VARCHAR(50) NOT NULL,"
SET @SQL = @SQL + "PARENT_TYPE INT NOT NULL,"
SET @SQL = @SQL + "PARENT_DOC VARCHAR(50) NOT NULL,"
SET @SQL = @SQL + "PARENT_SEC VARCHAR(50) NOT NULL,"
SET @SQL = @SQL + "CHILD_TYPE INT NOT NULL,"
SET @SQL = @SQL + "CHILD_DOC VARCHAR(50) NOT NULL,"
SET @SQL = @SQL + "CHILD_SEC VARCHAR(50) NOT NULL,"
SET @SQL = @SQL + "TYPE_ID BIGINT NOT NULL,"
SET @SQL = @SQL + "OLD_ID BIGINT NOT NULL)"
PRINT(@SQL)
EXEC(@SQL)
SET @SQL = "CREATE TABLE #TMP_TABLE_STAT ("
SET @SQL = @SQL + "ID BIGINT NOT NULL,"
SET @SQL = @SQL + "REL_ID BIGINT NOT NULL,"
SET @SQL = @SQL + "STATUS_ID BIGINT NOT NULL)"
PRINT(@SQL)
EXEC(@SQL)
SET @X = CHARINDEX(";",@STR_ID)
SET @Y = 1
While @X <> 0
BEGIN
SET @Tmp = SUBSTRING(@STR_ID,@Y,(@X-@Y))
SET @SQL = "INSERT INTO " + @DB_NAME_TO + ".dbo.TBL_INSTANCES(DOC_ID,TYPE_ID,DOC,SEC) "
SET @SQL = @SQL + "SELECT DOC_ID,TYPE_ID,DOC,SEC FROM " + @DB_NAME_FROM + ".dbo.TBL_INSTANCES WHERE TYPE_ID=" + @Tmp
PRINT(@SQL)
EXEC(@SQL)
SET @SQL = "INSERT INTO #TMP_TABLE_REL (DOC_ID,PARENT_TYPE,PARENT_DOC,PARENT_SEC,"
SET @SQL = @SQL + "CHILD_TYPE,CHILD_DOC,CHILD_SEC,TYPE_ID,OLD_ID) SELECT DOC_ID,PARENT_TYPE,PARENT_DOC,PARENT_SEC,"
SET @SQL = @SQL + "CHILD_TYPE,CHILD_DOC,CHILD_SEC,TYPE_ID,ID FROM "
SET @SQL = @SQL + @DB_NAME_FROM + ".dbo.TBL_RELATIONSHIPS WHERE TYPE_ID=" + @Tmp
PRINT(@SQL)
EXEC(@SQL)
SET @Y = (@X + 1)
SET @X = CHARINDEX(";",@STR_ID,@Y)
END
SET @SQL = "SET IDENTITY_INSERT " + @DB_NAME_TO + ".dbo.TBL_RELATIONSHIPS ON"
EXEC(@SQL)
PRINT(@SQL)
SET @SQL = "SET IDENTITY_INSERT " + @DB_NAME_TO + ".dbo.TBL_RELATIONSHIPS_STATUS ON"
EXEC(@SQL)
SET @SQL = "INSERT INTO " + @DB_NAME_TO + ".dbo.TBL_RELATIONSHIPS(ID,DOC_ID,PARENT_TYPE,PARENT_DOC,PARENT_SEC,CHILD_TYPE,"
SET @SQL = @SQL + "CHILD_DOC,CHILD_SEC) SELECT ID,DOC_ID,PARENT_TYPE,PARENT_DOC,PARENT_SEC,CHILD_TYPE,CHILD_DOC,CHILD_SEC "
SET @SQL = @SQL + "FROM #TMP_TABLE_REL"
PRINT(@SQL)
EXEC(@SQL)
--SET @SQL = "INSERT INTO " + @DB_NAME_TO + ".dbo.TBL_RELATIONSHIPS_STATUS(ID,REL_ID_STATUS_ID) "
--SET @SQL = @SQL + "SELECT ID,REL_ID,STATUS_ID FROM " + @DB_NAME_FROM + ".dbo.TBL_RELATIONSHIPS_STATUS "
--SET @SQL = @SQL + "INNER JOIN " + @DB_NAME_FROM + ".dbo.TBL_RELATIONSHIPS_STATUS ON #TMP_TABLE_STAT.OLD_ID"
--SET @SQL = @SQL + "=" + @DB_NAME_FROM + "TBL_RELATIONSHIPS_STATUS.REL_ID"
--PRINT(@SQL)
--EXEC(@SQL)
SET @SQL = "SET IDENTITY_INSERT " + @DB_NAME_TO + ".dbo.TBL_RELATIONSHIPS OFF"
EXEC(@SQL)
SET @SQL = "SET IDENTITY_INSERT " + @DB_NAME_TO + ".dbo.TBL_RELATIONSHIPS_STATUS OFF"
EXEC(@SQL)
--SELECT * FROM #TMP_TABLE_STAT
--SELECT * FROM #TMP_TABLE_REL
DROP TABLE #TMP_TABLE_STAT
DROP TABLE #TMP_TABLE_REL
COMMIT TRANSACTION
November 10, 2005 at 5:49 am
Not sure I completely grasp your actual problem here, but.. why do you think you need dynamic SQL just to move rows from one table to another..?
/Kenneth
November 11, 2005 at 1:57 am
Would a "Copy SQL Server Objects Task" in DTS not be less troublesome?
November 11, 2005 at 3:36 am
thanks but all solved now... dynamic sql is needed because new databases are created as part of an admin function in the application...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply