Skip to main content

I am trying to convert TX to single database and stuck here

-- Set the source database
USE DVH_PROD_ODX; -- Source database

DECLARE @SourceDatabase NVARCHAR(MAX) = 'DVH_PROD_ODX'; -- Source database
DECLARE @SourceSchema NVARCHAR(MAX) = 'ODX_TZ'; -- Source schema in the source database
DECLARE @TargetDatabase NVARCHAR(MAX) = 'DVH_PROD_MDW'; -- Target database
DECLARE @TargetSchema NVARCHAR(MAX) = 'ODX_TZ'; -- Target schema in the target database

-- Declare variables for table name and dynamic SQL
DECLARE @TableName NVARCHAR(MAX);
DECLARE @SQL NVARCHAR(MAX);

-- Create a cursor to loop through tables in the source schema
SET @SQL = N'DECLARE table_cursor CURSOR FOR '
+ N'SELECT t.TABLE_NAME '
+ N'FROM INFORMATION_SCHEMA.TABLES t '
+ N'WHERE t.TABLE_SCHEMA = ''' + @SourceSchema + ''''
+ N' AND EXISTS ( '
+ N'SELECT 1 '
+ N'FROM '+ @TargetDatabase + N'.INFORMATION_SCHEMA.TABLES t2 '
+ N'WHERE t2.TABLE_NAME = t.TABLE_NAME '
+ N'AND t2.TABLE_SCHEMA = ''' + @TargetSchema + ''''
+ N')';

EXEC sp_executesql @SQL;

-- Open the cursor
OPEN table_cursor;

-- Fetch the first table name
FETCH NEXT FROM table_cursor INTO @TableName;

-- Loop through the tables and copy data
WHILE @@FETCH_STATUS = 0
BEGIN
-- Enable IDENTITY_INSERT for the target table
SET @SQL = N'USE ' + @TargetDatabase + N'; ' +
/*N'EXEC sp_executesql N''SET IDENTITY_INSERT [' + @TargetSchema + N'].[' + @TableName + N'] ON''; ' +*/
N'INSERT INTO [' + @TargetSchema + N'].[' + @TableName + N'] (' +
STUFF((
SELECT N', [' + COLUMN_NAME + N']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @SourceSchema AND COLUMN_NAME <> 'DW_ID'
FOR XML PATH ('')
), 1, 2, N'') + N') ' +
N'SELECT ' +
STUFF((
SELECT N', [' + COLUMN_NAME + N']'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @SourceSchema AND COLUMN_NAME <> 'DW_ID'
FOR XML PATH ('')
), 1, 2, N'') +
N' FROM [' + @SourceDatabase + '].[' + @SourceSchema + N'].[' + @TableName + N']; ' /* +
N'EXEC sp_executesql N''SET IDENTITY_INSERT [' + @TargetSchema + N'].[' + @TableName + N'] OFF'';'*/
;

EXEC sp_executesql @SQL;
-- Select @SQL -- Kun til testing av spørringer

-- Fetch the next table name
FETCH NEXT FROM table_cursor INTO @TableName;
END;

-- Close and deallocate the cursor
CLOSE table_cursor;
DEALLOCATE table_cursor;

The SSMS returns Commands completed successfully, but i cant see that there are any tables in MDW

So the answer to my query was: 
I needed to create tables first in DVH before running the query

 


Reply