Create MERGE statements with data!

Question

Create MERGE statements with data!

Jason Selburg

SSC-Insane

Points: 24641
More actions
May 23, 2014 at 2:08 pm

#293667

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 3

akljfhnlaflkj

SSC Guru

Points: 76202

September 8, 2015 at 2:55 pm

#1825266

Wow, this looks really risky.

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 4

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

gserdijn Hall of Fame Points: 3290 More actions · Answer 5

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

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 6

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

Create MERGE statements with data!

Cookies on SQLServerCentral