I have updated your query to avoid into error you are getting. following is the code
DECLARE @TBLNAME varchar(8000) = 'Employee'
DECLARE@SchemaName varchar(200) = 'dbo'
DECLARE @InsertColumnList nvarchar(max)
DECLARE @SelColumnList nvarchar(max)
DECLARE @INSERTSQLSTRING nvarchar(max)
DECLARE @Schema varchar(200) = @SchemaName+'.'
DECLARE @Tablename varchar(8000) = '##'+@TBLNAME
--SELECT @Tablename
SELECT
@InsertColumnList = COALESCE(@InsertColumnList + ', ','') + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_Schema = @SchemaName AND TABLE_NAME = @TBLNAME
SELECT
@InsertColumnList
SELECT @SelColumnList = COALESCE(@SelColumnList + ',','')
+ 'cast(col'+ CAST(ORDINAL_POSITION AS varchar(12)) + ' ' + 'AS' + ' '
+ CASE
WHEN (DATA_TYPE='numeric' OR DATA_TYPE='decimal') THEN DATA_TYPE +
'('+CAST(NUMERIC_PRECISION AS varchar(36))+','+CAST(NUMERIC_SCALE AS varchar(12))+')'
WHEN (CHARACTER_MAXIMUM_LENGTH = -1) THEN DATA_TYPE + '(MAX)'
WHEN (CHARACTER_MAXIMUM_LENGTH IS NULL) THEN DATA_TYPE
ELSE DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(12)) + ')'
END + ')' --+ ' AS col'+CAST(ORDINAL_POSITION AS varchar(12))
--- Added
+ ' AS '+ COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_Schema = @SchemaName AND TABLE_NAME = @TBLNAME
SELECT @SelColumnList
SET @INSERTSQLSTRING =
'SELECT '+ @InsertColumnList +' INTO '+@Tablename+ ' FROM ('
+ CHAR(10) + ' Select ' + @SelColumnList
+ CHAR(10) + 'from dbo.TransformTable ) AS D'
--+ CHAR(10) + 'WHERE NOT EXISTS (SELECT 1 FROM ' + @Schema + @TBLNAME + ' A WHERE ' + @BuildCondition +')' +') AS D'
+ CHAR(10)
;
SELECT @INSERTSQLSTRING
If you want to Merge the data, you need to know the Key columns on which you need to join the Original table and the ##table.
And as it seems your schema would be identical on both the Source and Destination Table. If this is the thing you can generate the Merge Statement like this.
DECLARE @TBLNAME varchar(8000) = 'Employee'
DECLARE@SchemaName varchar(200) = 'dbo'
DECLARE @InsertColumnList nvarchar(max)
DECLARE @SelColumnList nvarchar(max)
DECLARE @INSERTSQLSTRING nvarchar(max)
DECLARE @UpdateSQLSTRING nvarchar(max)
DECLARE @MergeSQLSTRING nvarchar(max)
DECLARE @Schema varchar(200) = @SchemaName+'.'
DECLARE @Tablename varchar(8000) = '##'+@TBLNAME
--SELECT @Tablename
SELECT
@InsertColumnList = COALESCE(@InsertColumnList + ', ','') + COLUMN_NAME
, @UpdateSQLSTRING = COALESCE(@UpdateSQLSTRING, ', d.' + COLUMN_NAME + ' = s.' + COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_Schema = @SchemaName AND TABLE_NAME = @TBLNAME
ORDER BY ORDINAL_POSITION---- Added to maintain the
;
SELECT
@InsertColumnList
SELECT @SelColumnList = COALESCE(@SelColumnList + ',','')
+ 'cast(col'+ CAST(ORDINAL_POSITION AS varchar(12)) + ' ' + 'AS' + ' '
+ CASE
WHEN (DATA_TYPE='numeric' OR DATA_TYPE='decimal') THEN DATA_TYPE +
'('+CAST(NUMERIC_PRECISION AS varchar(36))+','+CAST(NUMERIC_SCALE AS varchar(12))+')'
WHEN (CHARACTER_MAXIMUM_LENGTH = -1) THEN DATA_TYPE + '(MAX)'
WHEN (CHARACTER_MAXIMUM_LENGTH IS NULL) THEN DATA_TYPE
ELSE DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(12)) + ')'
END + ')' + ' AS ' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_Schema = @SchemaName AND TABLE_NAME = @TBLNAME
SELECT @SelColumnList
SET @INSERTSQLSTRING =
'SELECT '+ @InsertColumnList +' INTO '+@Tablename+ ' FROM ('
+ CHAR(10) + ' Select ' + @SelColumnList
+ CHAR(10) + 'from dbo.TransformTable ) AS D'
--+ CHAR(10) + 'WHERE NOT EXISTS (SELECT 1 FROM ' + @Schema + @TBLNAME + ' A WHERE ' + @BuildCondition +')' +') AS D'
+ CHAR(10)
SELECT @INSERTSQLSTRING
Declare @primarykey-2 varchar(8000)
SELECT @primarykey-2 = COALESCE(@PrimaryKey, ', s.' + column_name +' = d.'+ column_name)
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
and ku.table_name='Employee'
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;
select @primarykey-2 = stuff(@PrimaryKey,1,1,'')
select @primarykey-2 = replace(@PrimaryKey, ',', ' and ')
select @UpdateSQLSTRING = stuff(@UpdateSQLSTRING,1,1,'')
select @MergeSQLSTRING =
'Merge ' + CONCAT(@SchemaName, '.', @TBLNAME) + ' d' + CHAR(10) +
' USING ' + @Tablename + ' s ' + CHAR(10) +
' on ' + @primarykey-2 + CHAR(10) +
' WHEN MATCHED THEN ' + CHAR(10) +
' UPDATE SET ' + @UpdateSQLSTRING + CHAR(10) +
' WHEN NOT MATCHED BY TARGET THEN INSERT (' + @InsertColumnList + ') ' + CHAR(10) + 'VALUES (' + @InsertColumnList + ');'
Select @MergeSQLSTRING