Create MERGE statements with data!
-
Comments posted to this topic are about the item Create MERGE statements with data!
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
EDIT: In case this doesn't make it into the actual article, I wanted to clarify a few items.
1. The script has a few restricting conditions that you may want to remove. Specifically, the check for “IsActive” or “Active” columns.
2. The output, if copied and pasted into a new window should retain the proper formatting.
3. Your table MUST have a Primary Key defined.
4. Replacing the final select (that returns the statement) with the below code will handle >, < and & conversions caused by the use of XML datatype.
BEGIN
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(@sqlTextFirst +
STUFF((SELECT
UsingList AS [text()]
FROM
#DataToMerge
FOR XML PATH(''))
,1,1,'')+
@sqlTextSecond
,'USING (VALUES ,(','USING (VALUES ('), '<', '<'), '>', '>'), '&', '&')
END
** The USING clause should look like this.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
For those of you that would like a single procedure that doesn't depend upon the Extended Properties and will work for a single table.
Here's what we use:
/****** Object: StoredProcedure [Admin].[CreateMerge] Script Date: 9/3/2015 10:07:29 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
=============================================
Author: Jason L. Selburg
Create date: 07/25/2014
Description: Returns the data for the program income receivables report
- The 'Created','LastEdited','IsTiedToBL','TS' and GUID columns are ignored
- @ExcludeData is set to 1, then only the merge statement is created with a single row with placeholders
- @ReverseOrder and @TopCount are used when the data in the table exceeds the output length
- @ReverseOrder orders the data by the PK DESC
- @Topcount only return this number of rows. ONLY used when @ReverseOrder is set
- @CustomWhere to hold a custom where claues for tables with large amounts of data
*/
ALTER PROCEDURE [Admin].[CreateMerge]
(@TableName NVARCHAR(256)
,@Schemaname NVARCHAR(256) = N'dbo'
,@ExcludeData INT = 0
,@ReverseOrder INT = 0
,@TopCount INT = 10
,@CustomWhere NVARCHAR(4000) = ''
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@i INT
,@sqlTextFirst VARCHAR(MAX)
,@sqlTextSecond VARCHAR(MAX)
,@justDataList VARCHAR(MAX)
,@EXECText NVARCHAR(MAX)
,@MergeText VARCHAR(MAX)
,@Filename VARCHAR(256)
,@Debug INT = 0;
IF LEFT(@CustomWhere,6) <> 'WHERE ' AND LEN(@CustomWhere) > 1
BEGIN
SELECT 'Invalid WHERE Clause'
RETURN
END;
IF OBJECT_ID('tempdb..#DataToMerge') IS NOT NULL
DROP TABLE #DataToMerge
CREATE TABLE #DataToMerge
(nDex INT IDENTITY
,UsingList NVARCHAR(MAX));
WITH TableInfoStart
AS (SELECT DISTINCT
t.object_id
,s.name AS schemaName
,t.name AS tableName
,icc.name AS PK
,icc.is_identity AS PKisID
,STUFF((SELECT
',[' + c.Name + ']' AS [text()]
FROM
sys.COLUMNS AS c
WHERE
t.OBJECT_ID = c.OBJECT_ID
AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')
AND c.NAME NOT LIKE '%GUID'
FOR
XML PATH('')
),1,1,'') AS columnlist
,'''' + CHAR(10) + ' ,('' + ' + STUFF((SELECT
CASE WHEN st.precision = 0 OR
st.user_type_id IN (40,41,42,43,8,11,241,58,61,189,98) THEN ---- needs wrapped in quotes
' + '','' + ISNULL('''''''' + REPLACE([' + c.Name + '],'''''''','''''''''''') + '''''''',''NULL'')'
ELSE
' + '','' + ISNULL(REPLACE(CAST([' + c.Name + '] AS VARCHAR(MAX)),'''''''',''''''''''''),''NULL'')'
END AS [text()]
FROM
sys.COLUMNS AS c
INNER JOIN sys.types AS st
ON c.user_type_id = st.user_type_id
WHERE
t.OBJECT_ID = c.OBJECT_ID
AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')
AND c.NAME NOT LIKE '%GUID'
AND ( (c.name <> icc.name AND icc.is_identity = 1) OR icc.is_identity = 0)
FOR
XML PATH('')
),1,8,'') + ' + '')''' AS selectlist
,CHAR(10) + ' (' + STUFF((SELECT
CASE WHEN st.precision = 0 OR
st.user_type_id IN (40,41,42,43,8,11,241,58,61,189,98) THEN ---- needs wrapped in quotes
','''+ c.Name +''''
ELSE
','+ c.Name
END AS [text()]
FROM
sys.COLUMNS AS c
INNER JOIN sys.types AS st
ON c.user_type_id = st.user_type_id
WHERE
t.OBJECT_ID = c.OBJECT_ID
AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')
AND c.NAME NOT LIKE '%GUID'
FOR
XML PATH(''))
,1,1,'') + ') ---- Place your data here' AS justDatalist
,STUFF(STUFF((SELECT
' AND [TGT].[' + col.COLUMN_NAME + '] = ' + '[SRC].[' + col.COLUMN_NAME + ']' + CHAR(10) AS [text()]
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
ON Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Col.CONSTRAINT_SCHEMA = Tab.TABLE_SCHEMA
WHERE
tab.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND tab.TABLE_SCHEMA = s.NAME
AND tab.TABLE_NAME = t.name
FOR
XML PATH('')
),1,8,''),1,1,'') AS JoinClause
FROM
sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.indexes AS i
ON i.object_id = t.object_id
AND i.is_primary_key = 1
INNER JOIN sys.index_columns AS ic
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
INNER JOIN sys.columns AS icc
ON ic.column_id = icc.column_id
AND ic.object_id = icc.object_id
WHERE
s.name = @SchemaName
AND
t.name = @TableName)
,TableInfo
AS (SELECT
object_id
,schemaName
,tableName
,PK
,PKisID
,CASE WHEN PKisID=1 THEN REPLACE(columnList,'[' + PK + '],','') ELSE columnList END AS columnList
,CASE WHEN PKisID=1 THEN REPLACE(selectlist,'[' + PK + '],','') ELSE selectlist END AS selectlist
,CASE WHEN PKisID=1 THEN REPLACE(justDatalist,',(' + PK + ',','(') ELSE justDatalist END AS justDatalist
,JoinClause
FROM TableInfoStart)
SELECT
@FileName = 'C:\SupportingData\' + schemaName + '.' + tableName + '.sql'
,@sqlTextFirst =
'/*' + CHAR(10) +
schemaName + '.' + tableName + CHAR(10) +
'*/' + CHAR(10) + CHAR(10) +
' MERGE INTO [' + schemaName + '].[' + tableName + '] AS [TGT]' + CHAR(10) +
' USING (VALUES '
,@sqlTextSecond = CHAR(10) +
' ) AS [SRC] ' + CHAR(10) +
' (' + columnList + ')' + CHAR(10) +
' ON' + JoinClause + CHAR(10) +
' ---- When the record does not exist... INSERT' + CHAR(10) +
' WHEN NOT MATCHED BY TARGET THEN' + CHAR(10) +
' INSERT ' + CHAR(10) +
---- handle the PK being an identitiy
' (' + CASE WHEN PKisID=1 THEN REPLACE(columnList,'[' + PK + '],','') ELSE columnList END + ') ' + CHAR(10) +
' VALUES ' + CHAR(10) +
---- handle the PK being an identitiy
' (' + CASE WHEN PKisID=1 THEN REPLACE(columnList,'[' + PK + '],','') ELSE columnList END + ');' + CHAR(10) +
'GO' + CHAR(10)
,@justDataList = justDataList
,@EXECText = CHAR(10) + +
CASE WHEN @TopCount = 1 THEN 'SELECT TOP (' + CAST(@TopCount AS VARCHAR(9)) + ') ' ELSE 'SELECT ' END
+ ISNULL(selectList,'') + CHAR(10) +
'FROM [' + schemaName + '].[' + tableName + '] ' + ISNULL(@CustomWhere,'') + ' ORDER BY ' + ISNULL(PK,'1') +
CASE WHEN @ReverseOrder = 1 THEN ' DESC' ELSE ' ASC' END
FROM TableInfo AS s;
PRINT @exectext
IF @ExcludeData = 0
BEGIN
INSERT #DataToMerge
EXEC sp_executesql @EXECText
IF EXISTS (SELECT NULL FROM #DataToMerge AS dtm)
BEGIN
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@sqlTextFirst +
STUFF(STUFF((SELECT
UsingList AS [text()]
FROM
#DataToMerge
FOR XML PATH(''))
,1,1,''), 12,1,CHAR(10) + ' ')+
@sqlTextSecond
,'USING (VALUES (','USING (VALUES ('), '<', '<'), '>', '>'), '&', '&')
END
ELSE
SELECT 'No Data'
END
ELSE
BEGIN
SELECT
REPLACE(@sqlTextFirst
+ @JustDataList
+ @sqlTextSecond ,'USING (VALUES (','USING (VALUES (')
END;
END
GO
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
Wow, this looks really risky.
-
Iwas Bornready (9/8/2015)
Wow, this looks really risky.Risky? It doesn't run any code. It simply creates the code for you. We've used in production for over a year now and it works beautifully.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg -
I tried the CreateMerge procedure since it might be useful.
The procedure however has a few issues:
[1] - the column order of the source is (sometimes?) alphabetically, and the identity column is not removed from the column list;
[2] - an identity column is omitted from the source, but when it's also the primary key the join clause is incorrect;Table + dummy data (version: SQL Server 2012 SP3 CU9)
CREATE TABLE dbo.languages (
language_id INT IDENTITY NOT NULL CONSTRAINT pk_languages PRIMARY KEY CLUSTERED,
isocode CHAR(2) NOT NULL,
[language] VARCHAR(100) NOT NULL,
in_use BIT NOT NULL CONSTRAINT df_languages_in_use DEFAULT 1);INSERT INTO dbo.languages (isocode, [language], in_use)
VALUES
('en', 'English', 1),
('nl', 'Dutch', 1),
('pd', 'ProtoDutch (Dunglish)', 0);EXEC [Admin].CreateMerge @TableName = 'languages'The output:
MERGE INTO [dbo].[languages] AS [TGT]
USING (VALUES
('en','English',1)
,('nl','Dutch',1)
,('pd','ProtoDutch (Dunglish)',0)
) AS [SRC]
([in_use],[isocode],[language],[language_id]) /* wrong order, and identity column not removed */
ON [TGT].[language_id] = [SRC].[language_id] /* problem */---- When the record does not exist... Etcetera
Issue [1] can easily be fixed by adding:
ORDER BY c.column_id
in:SELECT
',[' + c.Name + ']' AS [text()]
FROM sys.columns AS c
WHERE t.OBJECT_ID = c.OBJECT_ID
AND c.NAME NOT IN ('Created','LastEdited','IsTiedToBL','TS')
AND c.NAME NOT LIKE '%GUID'
ORDER BY c.column_id /* + */
FOR XML PATH('')The new output:
MERGE INTO [dbo].[languages] AS [TGT]
USING (VALUES
('en','English',1)
,('nl','Dutch',1)
,('pd','ProtoDutch (Dunglish)',0)
) AS [SRC]
([isocode],[language],[in_use]) /* better */
ON [TGT].[language_id] = [SRC].[language_id] /* .. but still a problem */---- When the record does not exist... Etcetera
Issue [2]: For tables without an identity property the proc yielded in a few simple tests a correct MERGE statement. In other cases the join clause will have to be modified manually. Well, as the author said: "This script does NOT handle all situations." It's given me some food for thought. 🙂
Dutch Anti-RBAR League -
Thanks gserdijn for the catch and fix!
It's also good to hear it's inspired your thinking of other uses.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply