June 27, 2016 at 6:16 am
Hi
I want to dynamically generate the table and use in merge command.
Employee Table -- Source
CREATE TABLE [dbo].[Employee](
[EmpID] [int] NOT NULL,
[FirstName] [varchar](500) NOT NULL,
[LastName] [varchar](500) NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Employee]
VALUES
(1,'DD','D'),
(2,'AA','A'),
(3,'BB','B')
GO
Destination Table -- Employee_DEST , In real scenario both soure and destination has same table name no changes
CREATE TABLE [dbo].[Employee_DEST](
[EmpID] [int] NOT NULL,
[FirstName] [varchar](500) NOT NULL,
[LastName] [varchar](500) NULL,
CONSTRAINT [PK_Employee_Dest] PRIMARY KEY CLUSTERED
(
[EmpID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Employee__DEST]
VALUES
(1,'DD','D'),
(2,'AAAAAA','Z')
GO
Transform table this is used to avoid METADATA conflict as explained in this link
CREATE TABLE [dbo].[TransformTable](
[COL1] [nvarchar](max) NULL,
[COL2] [nvarchar](max) NULL,
[COL3] [nvarchar](max) NULL,
[COL4] [nvarchar](max) NULL,
[COL5] [nvarchar](max) NULL,
[COL6] [nvarchar](max) NULL,
[COL7] [nvarchar](max) NULL,
[COL8] [nvarchar](max) NULL,
[COL9] [nvarchar](max) NULL,
[COL10] [nvarchar](max) NULL,
[COL11] [nvarchar](max) NULL,
[COL12] [nvarchar](max) NULL,
[COL13] [nvarchar](max) NULL,
[COL14] [nvarchar](max) NULL,
[COL15] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The Employee table is Source and there is same destination table will be available in archival server.Both Source and Destination table are in different server and there is no linked server
I need to dynamically create Merge command, for this i am dynamically creating the Source table in archival server and then use this table for MERGE operation with Destination table and then after MERGE drop this table.
below is the code where i am trying to create the source table dynamically but because the TansformTable has columns different with Source table it showing error.
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))
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
Thanks
June 27, 2016 at 6:27 am
If there is no linked server, how are you doing the transfer? Why does this need to be dynamic - does the structure of the source table change on a regular basis?
John
Edit - removed a question that had been answered in the original post.
June 27, 2016 at 6:59 am
John Mitchell-245523 (6/27/2016)
If there is no linked server, how are you doing the transfer? Why does this need to be dynamic - does the structure of the source table change on a regular basis?John
I am using the SSIS package as expalined in this link
There is 100 table to load and it will dynamically loaded, if it is just insert then i can follow the above link, but in some tables there in INSERT/UPDATE operation happens
I just want to create the table dynamically so that i can use it for MERGE operation
June 27, 2016 at 7:10 am
Thanks for the link, but if you don't make it clickable, people aren't going to bother reading it. Anyway, why not create 100 staging tables - one for each destination table? You can put them in a separate database if that makes things easier or cleaner. You then don't have to assign permissions to create and dropp (deliberate spelling error to get past company web gateway) tables that's required by your method, and you'll probably find that the whole thing runs faster.
John
June 27, 2016 at 7:34 am
John Mitchell-245523 (6/27/2016)
Thanks for the link, but if you don't make it clickable, people aren't going to bother reading it. Anyway, why not create 100 staging tables - one for each destination table? You can put them in a separate database if that makes things easier or cleaner. You then don't have to assign permissions to create and dropp (deliberate spelling error to get past company web gateway) tables that's required by your method, and you'll probably find that the whole thing runs faster.John
Thanks for the suggestion , If i create 100 staging tables then when new tables created then again i have to manually create that in archival server and that means redeployment in production.
If somehow column names generated in this variable @InsertColumnList assigned to this variable @SelColumnList some thing like this
Now i am getting this result
@InsertColumnList = EmpID, FirstName, LastName
@SelColumnList=cast(col1 AS int),cast(col2 AS varchar(500)),cast(col3 AS varchar(500))
If the above @SelColumnList result can changed into the below code
cast(col1 AS int) AS EmpID,cast(col2 AS varchar(500)) AS FirstName,cast(col3 AS varchar(500)) AS LastName
June 27, 2016 at 8:23 am
I'm not sure whether there's a question there, but I still don't recommend doing it like that. Yes, if a new table is created then you have to create the corresponding staging table, but that doesn't happen often, does it? And you don't need to redeploy, just create the new staging table as part of the process to create the source table.
John
June 27, 2016 at 10:52 pm
Don't create the table dynamically.
Create a table once, make it have all the columns ever used in all the integrations where it's used.
Make non-mandatory columns nullable.
Then drop the part of your script which creates @SelColumnList.
And change the last part of the script to this:
SET @INSERTSQLSTRING =
'INSERT INTO ' + @Tablename + '(' + @InsertColumnList + ')' + CHAR(10) +
'Select ' + @InsertColumnList
+ CHAR(10) + 'from dbo.TransformTable '
--+ CHAR(10) + 'WHERE NOT EXISTS (SELECT 1 FROM ' + @Schema + @TBLNAME + ' A WHERE ' + @BuildCondition +')' +') AS D'
+ CHAR(10)
SELECT @INSERTSQLSTRING
This will populate all the columns which are populated in the source table, leaving the rest of the columns with NULLs.
_____________
Code for TallyGenerator
June 28, 2016 at 3:21 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply