Not logged in - Login

Extract table data into a merge statement w/o unique column

The following extracts data from a SQL tables and creates a merge statement that can be used to populate a table with the same data (presumably, in another database.)

This template will work with tables that do NOT have a single column to uniquely identify a row. Instead, it copies all the data to a temp table with a unique row identifier.

If the table does have a column that can uniquely identify a row, it would be better to use the other extract data script: Extract table data.

This script still requires that the table has a subset of columns that can be used to uniquely identify a row.

/*
       This script extracts the existing data in a table
       and places it into a merge statement to allow the 
       table to be populated with the same data.

       This template will work with tables that do NOT have a single column to uniquely identify a row.
       Instead, it copies all the data to a temp table with a unique row identifier.

       select * from dbo.MyTable

*/


-- specify the name of the table whose data is to be output
:setvar TableName2 "dbo.MyTable"

-- specify any column name here. Picking the 1st column in the table is fine.
:setvar FirstColumn "ColNum1"

-- set a variable that specifies the name of the row number column in our temp table that we create.
-- if the source table already has a column with this exact same name, change this variable so that it is unique.
:setvar RowNumColumn "RowNumb"


GO
:on error exit
GO

/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/

:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
BEGIN
    PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
       raiserror('SQLCMD mode must be enabled to successfully execute this script.', 20, -1) with log
    SET NOEXEC ON;
END


-- drop the source data temp table, if it exists.
IF (OBJECT_ID('tempdb..#SourceData') IS NOT NULL)
begin
    DROP TABLE #SourceData
end

-- make sure the temp table does not already exist
IF (OBJECT_ID('tempdb..#TempData') IS NOT NULL)
begin
    DROP TABLE #TempData
end
GO

GO

-- create a temp table 
CREATE TABLE #TempData
(
       lColOrder int not null primary key
       , sName varchar(255) not null
       , bIsIdentity bit not null default(0)
       , DataTypeId int not null
       , bUseTicks bit not null default(0)
       , ColData varchar(max) null       --not null default('null')

       , unique (sName, lColOrder)
)
GO



-- populate the #SourceData temp table.
print 'populate the #SourceData temp table'
SELECT 
       $(RowNumColumn) = row_number() over (order by $(FirstColumn))
       , a.*
into #SourceData
from $(TableName2) a



-- turn on nocount.
set nocount on;

-- insert the table columns into our temp table.
INSERT into #TempData
(lColOrder, sName, bIsIdentity, DataTypeId )
select column_id, [name], a.is_identity, system_type_id
from tempdb.sys.columns a
where a.[object_id] = object_Id('tempdb..#SourceData')
       and a.is_computed = 0
       --and (a.is_identity = 0 or $(IncludeIdentity) = 1)
       and a.system_type_id <> 189       -- exclude rowversion columns.
       and a.[name] <> '$(RowNumColumn)'
       --
       --and a.column_id <= 4       -- 9
       
-- get the smallest ColOrder value
declare @lFirstColOrder int
set @lFirstColOrder = (
       select top 1 lColOrder from #TempData order by lColOrder
)

-- update the 'Use Ticks' column for the data types that need to be enclosed in single quote characters.
UPDATE #TempData set
       bUseTicks = 1
where DataTypeId in (167, 35, 175, 40, 41, 61, 36, 231)

-- declare a variable to use for our execute command statements.
declare @cmd varchar(max)

-- declare some variables we'll use a little later.
declare @sCurrentCol varchar(255)
       , @lDataType int
       , @RowID int
       , @lSrcRowCount int = 0

-- update the [ColData] column to be null for all rows.
UPDATE #TempData set ColData=null


-- declare a variable to hold the output line.
declare @sOutputLine varchar(max)
declare @crlf varchar(2) = char(13) + char(10)
       , @tab varchar(1) = char(9)
       
print 'Current database: ' + DB_NAME()
print ''
print ''

print '/*'
print ''
print '       delete from $(TableName2)'
print ''
print '*/' + @crlf + @crlf


-- start the CTE portion of the output
set @sOutputLine = ';with cteSrc as (
       SELECT *
       from ( values
              -- Source DB: [' + @@servername + '].[' + DB_NAME() + '] at ' + convert(varchar(40), getdate(), 109) + @crlf 
print @sOutputLine



-- declare & open the columns cursor.
DECLARE curColumns cursor scroll read_only for
       select a.sName, a.DataTypeID 
       from #TempData a 
       --where ( (a.bIsIdentity = 0) or ($(IncludeIdentity) = 1) )
       order by a.lColOrder
OPEN curColumns


DECLARE @lSourceRowCount int
       , @lCurrentSourceRow int;
SELECT @lSourceRowCount = max($(RowNumColumn))
       , @lCurrentSourceRow = min($(RowNumColumn))
from #SourceData


-- keep going while we're still pointing to a valid row number
WHILE (@lCurrentSourceRow <= @lSourceRowCount)
begin
       -- get the 1st row from the columns cursor.
       FETCH first from curColumns into @sCurrentCol, @lDataType

       -- increment our row count
       set @lSrcRowCount = @lSrcRowCount + 1



       -- keep going while the last fetch was good.
       WHILE (@@fetch_status = 0)
       begin
              -- build the command statement for the current column
              set @cmd = 
                     case @lDataType
                            -- if the output order is to be changed, do it here, at the '$(UniqueCol)' section in the text strings
                            when 61 then
                                   'update #TempData set ColData=(select convert(varchar(255), ' + @sCurrentCol + ', 126) from (select ' + @sCurrentCol + ', rownumber = $(RowNumColumn) from #SourceData) a where a.rownumber = ' + convert(varchar, @lCurrentSourceRow) + ') where sName=''' + @sCurrentCol + ''''
                            else
                                   'update #TempData set ColData=(select ' + @sCurrentCol + ' from (select ' + @sCurrentCol + ', rownumber = $(RowNumColumn) from #SourceData) a where a.rownumber = ' + convert(varchar, @lCurrentSourceRow) + ') where sName=''' + @sCurrentCol + ''''
                     end
       
              --print @cmd
              exec (@cmd)

              -- get the next row from the cursor.
              FETCH next from curColumns into @sCurrentCol, @lDataType
       end

       -- set our null value text
       UPDATE #TempData set
              ColData = --'null'
                     case 
                            when ColData is null then 'null'
                            else '''' + replace(ColData, '''', '''''') + ''''
                     end
       where ColData is null or bUseTicks=1

       -- initialize the output line, depending on if this is the 1st line or not.
       if (@lSrcRowCount = 1)
       begin
              set @sOutputLine = @tab + @tab + '('
       end else begin
              set @sOutputLine = @tab + @tab + ', ('
       end

       -- build our output line of data
       SELECT @sOutputLine = @sOutputLine 
              + case when a.rownumber > 1 then ', ' else '' end
              + a.ColData
       from (
              select *
                     , rownumber = row_number() over (order by b.lColOrder)
              from #TempData b              
              --where (b.bIsIdentity = 0 or $(IncludeIdentity) = 1)
       ) a
       order by a.lColOrder

       -- tack on a close parenthesis at the end.
       set @sOutputLIne = @sOutputLine + ')'

       -- output the line we created.
       print @sOutputLine


       
       -- increment our source row index
       set @lCurrentSourceRow = @lCurrentSourceRow + 1
end


-- close & deallocate the columns cursor
CLOSE curColumns
DEALLOCATE curColumns


set @sOutputLine = @tab + ') as src('
SELECT @sOutputLine = @sOutputLine
       + case when a.rownumber = 1 then '' else ', ' end
       + '[' + a.sName + ']'
from ( 
       select *
              , rownumber = row_number() over (order by lColOrder)
       from #TempData b
       --where ( (b.bIsIdentity = 0) or ($(IncludeIdentity) = 1) )
) a
order by a.lColOrder

-- close the CTE portion of the output
set @sOutputLine = @sOutputLine + ')' + @crlf + ')'
print @sOutputLine
print '-- select * from cteSrc'

-- build the merge statement
print 'merge into $(TableName2) as dest -- target'
print 'using cteSrc as src on '
print @tab + '-- Add the necessary field comparisons here that will let us uniquely identify a row.'
print @tab + 'dest.XXXXXXXXXXXX = src.XXXXXXXXXXXX'
print @tab + 'and dest.YYYYYYYYYYYY = src.YYYYYYYYYYYY'
print 'when matched then -- on match update'
print 'update set '
print @tab + '-- Be sure to comment out the update on the columns that uniquely identify a row.'

-- build the update part of the merge statement
set @sOutputLine = ''
SELECT @sOutputLine = @sOutputLine
       + case when a.RowNumber = 1 then @tab else @crlf + @tab + ', ' end
       + 'dest.[' + a.sName + '] = src.[' + a.sName + ']'
from (
       select *
              , RowNumber = row_number() over (order by lColOrder)
       from #TempData b
) a
order by a.lColOrder
print @sOutputLine

-- build the insert part of the merge statement
print 'when not matched then -- insert missing'

-- create the comma separated list of columns.
set @sOutputLine = ''
SELECT @sOutputLine = @sOutputLine
       + case when a.RowNumber = 1 then '' else ', ' end
       + '[' + a.sName + ']'
from (
       select *
              , RowNumber = row_number() over (order by lColOrder)
       from #TempData b
) a
order by a.lColOrder

-- output the insert column & value list.
print @tab + 'insert (' + @sOutputLine + ')'
print @tab + 'values (' + @sOutputLine + ')'

print '--when not matched by source then delete -- Delete missing from source'
print ';'


--turn off the nocount
set nocount off;


--select * from #TempData

---- make sure the temp table does not already exist
--IF (OBJECT_ID('tempdb..#TempData') IS NOT NULL)
--begin
--    DROP TABLE #TempData
--end