Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create MERGE statements with data! Expand / Collapse
Author
Message
Posted Friday, May 23, 2014 2:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:50 PM
Points: 2,717, Visits: 3,854
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1574229
Posted Wednesday, May 28, 2014 6:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:50 PM
Points: 2,717, Visits: 3,854
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. 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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1575165
Posted Wednesday, July 16, 2014 9:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:50 PM
Points: 2,717, Visits: 3,854
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:

ALTER PROCEDURE [Admin].[CreateMerge] 
(@TableName NVARCHAR(256)
,@Schemaname NVARCHAR(256) = N'dbo'
,@ExcludeData INT = 0
,@ReverseOrder INT = 0
,@TopCount INT = 10
)
AS
/*
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

*/
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 OBJECT_ID('tempdb..#DataToMerge') IS NOT NULL
DROP TABLE #DataToMerge

CREATE TABLE #DataToMerge
(nDex INT IDENTITY
,UsingList NVARCHAR(MAX));

WITH TableInfo
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'
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)
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 @ReverseOrder = 1 THEN 'SELECT TOP (' + CAST(@TopCount AS VARCHAR(9)) + ') ' ELSE 'SELECT ' END
+ ISNULL(selectList,'') + CHAR(10) +
'FROM [' + schemaName + '].[' + tableName + '] ORDER BY ' + ISNULL(PK,'1') +
CASE WHEN @ReverseOrder = 1 THEN ' DESC' ELSE ' ASC' END
FROM TableInfo AS s;

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((SELECT
UsingList AS [text()]
FROM
#DataToMerge
FOR XML PATH(''))
,1,1,'')+
@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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1593127
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse