Code to create MERGE statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Code to create MERGE statements

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-15 : 06:48:49

Hi all

As we've got a new database to populate, I need to write around 500 MERGE statements to keep our copy up to date.
Unfortunately, I can't use transaction log shipping or SSIS packages to do this for me so I have to writ them all out by hand.

Is there any way I can code someting to create the MERGE statements for me?

The connection string will remain the same (we'll call it [connection] for now) and I need it to pick up the field(s) in the primary key to usde in the ON statements for the join.

I'm only using the WHEN MATCHED THEN and WHEN NOT MATCHED BY TARGET sections.

The table name will obviously change, but they are identical on both sides.

Any help gratefully received.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-15 : 06:51:13

You possibly can generate the merge statements from a query or script. Can you post examples of the statements you want to generate and examples of the data (table names etc.) that will be used to generate the merge statements?

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-15 : 07:26:48

Here's one I created earlier (I've used the script from the create statement):-


USE [111_Adastra_Local]
GO

/****** Object:  StoredProcedure [dbo].[sp_Merge_adaGroupRights]    Script Date: 10/15/2012 11:03:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_Merge_AgencyTypeQuestion]

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	merge [111_Adastra_Local].dbo.[AgencyTypeQuestion] as to_table
	using [connection].dbo.[AgencyTypeQuestion] as from_table
		on to_table.[AgencyTypeQuestionRef]=from_table.[AgencyTypeQuestionRef]

	when not matched by target then
		insert (
			[AgencyTypeQuestionRef]
			,[Question]
			,[QuestionType]
			,[MinValue]
			,[MaxValue]
			,[Sort]
			,[DecimalPlaces]
			,[Obsolete]
			,[Units]
			,[AgencyTypeRef]
			,[CueBanner]
)
	values (
		from_table.[AgencyTypeQuestionRef]
		,from_table.[Question]
		,from_table.[QuestionType]
		,from_table.[MinValue]
		,from_table.[MaxValue]
		,from_table.[Sort]
		,from_table.[DecimalPlaces]
		,from_table.[Obsolete]
		,from_table.[Units]
		,from_table.[AgencyTypeRef]
		,from_table.[CueBanner]
)
	when matched then
	update set
		to_table.[AgencyTypeQuestionRef]=from_table.[AgencyTypeQuestionRef]
		,to_table.[Question]=from_table.[Question]
		,to_table.[QuestionType]=from_table.[QuestionType]
		,to_table.[MinValue]=from_table.[MinValue]
		,to_table.[MaxValue]=from_table.[MaxValue]
		,to_table.[Sort]=from_table.[Sort]
		,to_table.[DecimalPlaces]=from_table.[DecimalPlaces]
		,to_table.[Obsolete]=from_table.[Obsolete]
		,to_table.[Units]=from_table.[Units]
		,to_table.[AgencyTypeRef]=from_table.[AgencyTypeRef]
		,to_table.[CueBanner]=from_table.[CueBanner]
;
END




GO

All the tables are in the 111_Adastra_Local databases and the SPs need to be in the same one.
The ON statement needs to pick up the primary key field(sd) and be relatively dynamic.

Obviously the field names will need to picked up as well and this list will change in volume for each table.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-15 : 08:46:13

You can get the tables and their primary keys using the following query. You should be able to write the merge statement using those. If there are composite keys where the primary key consists of more than one column, more work will need to be done to take care of those.

SELECT 
	k.table_name,
	k.column_name
FROM
	information_schema.key_column_usage k
	INNER JOIN information_schema.table_constraints c ON
		c.CONSTRAINT_NAME = k.CONSTRAINT_NAME
WHERE
	c.CONSTRAINT_TYPE = 'PRIMARY KEY'

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-15 : 09:20:42

Around half the tables have composite keys.
I'd be grateful for any assistance with the code you can give.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-15 : 09:32:35

What I meant is something like the query shown below - you can run it and see what it generates - it generates the first part of the merge statement including the join conditions.

HOWEVER, this feels like it is the wrong way to achieve the goal. You said you are not able to use SSIS or log-shipping. Is replication a possibility? That would do what you want to do seamlessly and elegantly.

;WITH cte1 AS
(
	SELECT 
		k.table_name,
		k.column_name
	FROM
		information_schema.key_column_usage k
		INNER JOIN information_schema.table_constraints c ON
			c.CONSTRAINT_NAME = k.CONSTRAINT_NAME
	WHERE
		c.CONSTRAINT_TYPE = 'PRIMARY KEY'
)
SELECT
	'MERGE [111_Adastra_Local].dbo.'
	+ QUOTENAME(a.table_name)
	+ ' AS to_table using [connection].dbo.'
	+ QUOTENAME(a.table_name)
	+ ' AS from_table ON '
	+ STUFF(b.join_condition,1,4,'') AS join_condition
FROM
	(SELECT DISTINCT table_name FROM cte1) a
	CROSS APPLY
	(
		SELECT 
			' AND to_table.' 
			+ QUOTENAME(c1.column_name) + ' = from_table.'
			+ QUOTENAME(c1.column_name)
		FROM
			cte1 c1
		WHERE c1.table_name = a.table_name
		FOR XML PATH('')
	) b(join_condition)

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-15 : 10:12:42

That's brilliant!
All I need to do now is sort out the rest of the statement (the WHEN MATCHED and WHEN NOT MATCHED BY TARGET sections).

Any advice on how to go about this bit?

::edit::
Unfortunately I can't use replication either.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-16 : 06:45:52

Do you need to merge all the other columns in the table except the primary keys?

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-16 : 07:23:59

I need to merge all the columns (including primary keys) in each table with either an insert or update as appropriate.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-16 : 07:42:19

Ok, I added two more CTE's to do the WHEN MATCHED condition. the WHEN NOT MATCHED should be similar.

I know I am repeating myself and probably annoying you to death in doing so, but there has to be some other way you can accomplish this. While the code may work, and even may pass some testing, it seems like a very unnatural and error-prone way to do this.

;WITH cte1 AS
(
	SELECT 
		k.table_name,
		k.column_name
	FROM
		information_schema.key_column_usage k
		INNER JOIN information_schema.table_constraints c ON
			c.CONSTRAINT_NAME = k.CONSTRAINT_NAME
	WHERE
		c.CONSTRAINT_TYPE = 'PRIMARY KEY'
),
cte2 AS
(
	SELECT
		a.table_name,
		'MERGE [111_Adastra_Local].dbo.'
		+ QUOTENAME(a.table_name)
		+ ' AS to_table using [connection].dbo.'
		+ QUOTENAME(a.table_name)
		+ ' AS from_table ON '
		+ STUFF(b.join_condition,1,4,'') AS JOIN_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte1) a
		CROSS APPLY
		(
			SELECT 
				' AND to_table.' 
				+ QUOTENAME(c1.column_name) + ' = from_table.'
				+ QUOTENAME(c1.column_name)
			FROM
				cte1 c1
			WHERE c1.table_name = a.table_name
			FOR XML PATH('')
		) b(join_condition)
),
cte3 AS
(
	SELECT k.table_name,k.column_name
	FROM INFORMATION_SCHEMA.[COLUMNS] k
),
cte4 AS 
(
	SELECT
		a.table_name,
		' when not matched by target then insert ('
		+ REPLACE(STUFF(b.column_list,1,1,''),'alias.','')
		+ ') values ('
		+ REPLACE(STUFF(b.column_list,1,1,''),'alias','from_table')
		+') ' AS WHEN_NOT_MATCHED_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte3) a
		CROSS APPLY
		(
			SELECT 
				',alias.' +
				+ QUOTENAME(c3.column_name)
			FROM
				cte3 c3
			WHERE c3.table_name = a.table_name
			FOR XML PATH('')
		) b(column_list)
)
SELECT 
	c2.table_name,
	JOIN_CONDITION + WHEN_NOT_MATCHED_CONDITION
FROM
	cte2 c2
	INNER JOIN cte4 c4 ON c2.table_name = c4.table_name;

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-16 : 08:15:54

Repeating yourself is not a problem to me, I've been telling my boss this for a couple of weeks.
Apparently (due to IT constraints) we can't use log shipping, replication or SSIS packages. If you have any other ideas, I'm all ears.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-16 : 08:35:10

quote:
Originally posted by rmg1

Repeating yourself is not a problem to me, I've been telling my boss this for a couple of weeks.
Apparently (due to IT constraints) we can't use log shipping, replication or SSIS packages. If you have any other ideas, I'm all ears.

I know how that goes with bosses :)

In any case, I guess with enough testing this approach can work and work reliably. Just is a shame that you are forced to use it when Microsoft provides many other features to accomplish this same goal.

If you have difficulty writing the query for the WHEN MATCHED section, please reply.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-16 : 08:52:02

I'm glad you said that :)
I've written what I thought was right, but it's listing all the to_table stuff followed by all the from_table stuff instead of matching them up.
This is what I have now (complete code) :-


;WITH cte1 AS
(
	SELECT 
		k.table_name,
		k.column_name
	FROM
		information_schema.key_column_usage k
		INNER JOIN information_schema.table_constraints c ON
			c.CONSTRAINT_NAME = k.CONSTRAINT_NAME
	WHERE
		c.CONSTRAINT_TYPE = 'PRIMARY KEY'
),
cte2 AS
(
	SELECT
		a.table_name,
		'MERGE [111_Adastra_Local].dbo.'
		+ QUOTENAME(a.table_name)
		+ ' AS to_table using [connection].dbo.'
		+ QUOTENAME(a.table_name)
		+ ' AS from_table ON '
		+ STUFF(b.join_condition,1,4,'') AS JOIN_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte1) a
		CROSS APPLY
		(
			SELECT 
				' AND to_table.' 
				+ QUOTENAME(c1.column_name) + ' = from_table.'
				+ QUOTENAME(c1.column_name)
			FROM
				cte1 c1
			WHERE c1.table_name = a.table_name
			FOR XML PATH('')
		) b(join_condition)
),
cte3 AS
(
	SELECT k.table_name,k.column_name
	FROM INFORMATION_SCHEMA.[COLUMNS] k
),
cte4 AS 
(
	SELECT
		a.table_name,
		' when not matched by target then insert ('
		+ REPLACE(STUFF(b.column_list,1,1,''),'alias.','')
		+ ') values ('
		+ REPLACE(STUFF(b.column_list,1,1,''),'alias','from_table')
		+') ' AS WHEN_NOT_MATCHED_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte3) a
		CROSS APPLY
		(
			SELECT 
				',alias.' +
				+ QUOTENAME(c3.column_name)
			FROM
				cte3 c3
			WHERE c3.table_name = a.table_name
			FOR XML PATH('')
		) b(column_list)
)
,cte5 AS
(
	SELECT k.table_name,k.column_name
	FROM INFORMATION_SCHEMA.[COLUMNS] k
),
cte6 AS 
(
	SELECT
		a.table_name,
		' when matched then update set '
		+ REPLACE(STUFF(b.column_list,1,1,''),'alias.','to_table.')
		+ '='
		+ REPLACE(STUFF(b.column_list,1,1,''),'alias','from_table')
		+'' AS WHEN_MATCHED_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte3) a
		CROSS APPLY
		(
			SELECT 
				',alias.' +
				+ QUOTENAME(c3.column_name)
			FROM
				cte3 c3
			WHERE c3.table_name = a.table_name
			FOR XML PATH('')
		) b(column_list)
)
SELECT 
	c2.table_name,
	JOIN_CONDITION + WHEN_NOT_MATCHED_CONDITION + WHEN_MATCHED_CONDITION
FROM
	cte2 c2
	INNER JOIN cte4 c4 ON c2.table_name = c4.table_name
	inner join cte6 c6 on c2.TABLE_NAME=c6.TABLE_NAME;

I've written (badly) CTE's 5 and 6.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-16 : 09:31:03

I think it should be more like this:

;WITH cte1 AS
(
	SELECT 
		k.table_name,
		k.column_name
	FROM
		information_schema.key_column_usage k
		INNER JOIN information_schema.table_constraints c ON
			c.CONSTRAINT_NAME = k.CONSTRAINT_NAME
	WHERE
		c.CONSTRAINT_TYPE = 'PRIMARY KEY'
),
cte2 AS
(
	SELECT
		a.table_name,
		'MERGE [111_Adastra_Local].dbo.'
		+ QUOTENAME(a.table_name)
		+ ' AS to_table using [connection].dbo.'
		+ QUOTENAME(a.table_name)
		+ ' AS from_table ON '
		+ STUFF(b.join_condition,1,4,'') AS JOIN_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte1) a
		CROSS APPLY
		(
			SELECT 
				' AND to_table.' 
				+ QUOTENAME(c1.column_name) + ' = from_table.'
				+ QUOTENAME(c1.column_name)
			FROM
				cte1 c1
			WHERE c1.table_name = a.table_name
			FOR XML PATH('')
		) b(join_condition)
),
cte3 AS
(
	SELECT k.table_name,k.column_name
	FROM INFORMATION_SCHEMA.[COLUMNS] k
),
cte4 AS 
(
	SELECT
		a.table_name,
		' when not matched by target then insert ('
		+ REPLACE(STUFF(b.column_list,1,1,''),'alias.','')
		+ ') values ('
		+ REPLACE(STUFF(b.column_list,1,1,''),'alias','from_table')
		+') ' AS WHEN_NOT_MATCHED_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte3) a
		CROSS APPLY
		(
			SELECT 
				',alias.' +
				+ QUOTENAME(c3.column_name)
			FROM
				cte3 c3
			WHERE c3.table_name = a.table_name
			FOR XML PATH('')
		) b(column_list)
)
,cte5 AS
(
	SELECT k.table_name,k.column_name
	FROM INFORMATION_SCHEMA.[COLUMNS] k
),
cte6 AS 
(
	SELECT
		a.table_name,
		' when matched then update set '
		+ STUFF(b.column_list,1,1,'') AS WHEN_MATCHED_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte3) a
		CROSS APPLY
		(
			SELECT 
				',to_table.' +
				+ QUOTENAME(c3.column_name)
				+ ' = '
				+ 'from_table.'+
				QUOTENAME(c3.column_name)
			FROM
				cte3 c3
			WHERE c3.table_name = a.table_name
			FOR XML PATH('')
		) b(column_list)
)
SELECT 
	c2.table_name,
	JOIN_CONDITION + WHEN_NOT_MATCHED_CONDITION + WHEN_MATCHED_CONDITION
FROM
	cte2 c2
	INNER JOIN cte4 c4 ON c2.table_name = c4.table_name
	inner join cte6 c6 on c2.TABLE_NAME=c6.TABLE_NAME;

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-16 : 09:54:43

That's genius!
I've just checked the first one and it appears to write the code for me.

Thank you!

(Any chance of an explanation of how it works please?)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-16 : 11:13:40

Glad to hear :)

If you consider each of the cte's individually, it is taking the table name (in the red part in the example below) and using the APPLY operator to put together a string that has all the columns of that table along with table qualifiers and the "=" string. The black part is using that data to generate the query string.

If you look up APPLY operator on MSDN, that will give you some insights into how it works. APPLY operator is sort of like a join operator, but not quite.

Within the apply operator, the FOR XML PATH construct lets you concatenate multiple rows into one row.

cte6 AS 
(
	SELECT
		a.table_name,
		' when matched then update set '
		+ STUFF(b.column_list,1,1,'') AS WHEN_MATCHED_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte3) a
		CROSS APPLY
		(
			SELECT 
				',to_table.' +
				+ QUOTENAME(c3.column_name)
				+ ' = '
				+ 'from_table.'+
				QUOTENAME(c3.column_name)
			FROM
				cte3 c3
			WHERE c3.table_name = a.table_name
			FOR XML PATH('')
		) b(column_list)

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-17 : 02:33:24

Thanks again for the explanation, I think I see how this works now.
From other bits of research I've done, the CROSS APPLY section gives an almost cartesian product where every record as brought back against every other record.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-18 : 04:08:33

Can I beg a little more assistance with this please?
I'm trying to insert line feeds in relevant places to make the final output look a little neater for future alterations.
This is the code I currently have:-


drop table #temptable
declare @sql as varchar(max)
;WITH cte1 AS
(
	SELECT 
		k.table_name,
		k.column_name
	FROM
		information_schema.key_column_usage k
		INNER JOIN information_schema.table_constraints c ON
			c.CONSTRAINT_NAME = k.CONSTRAINT_NAME
	WHERE
		c.CONSTRAINT_TYPE = 'PRIMARY KEY'
),
cte2 AS
(
	SELECT
		a.table_name,
		'MERGE [111_Adastra_Local].dbo.'
		+ QUOTENAME(a.table_name)
		+ ' AS to_table [crlf]using [connection].dbo.'
		+ QUOTENAME(a.table_name)
		+ ' AS from_table [crlf]ON '
		+ STUFF(b.join_condition,1,4,'') AS JOIN_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte1) a
		CROSS APPLY
		(
			SELECT 
				' [crlf]AND to_table.' 
				+ QUOTENAME(c1.column_name) + ' = from_table.'
				+ QUOTENAME(c1.column_name)
			FROM
				cte1 c1
			WHERE c1.table_name = a.table_name
			FOR XML PATH('')
		) b(join_condition)
),
cte3 AS
(
	SELECT k.table_name,k.column_name
	FROM INFORMATION_SCHEMA.[COLUMNS] k
),
cte4 AS 
(
	SELECT
		a.table_name,
		' when not matched by target then [crlf]insert ([crlf]'
		+ REPLACE(STUFF(b.column_list,1,1,''),'alias.','')
		+ ') [crlf]values ([crlf]'
		+ REPLACE(STUFF(b.column_list,1,1,''),'alias','from_table')
		+'[crlf]) ' AS WHEN_NOT_MATCHED_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte3) a
		CROSS APPLY
		(
			SELECT 
				',alias.' +
				+ QUOTENAME(c3.column_name)
			FROM
				cte3 c3
			WHERE c3.table_name = a.table_name
			FOR XML PATH('')
		) b(column_list)
)
,cte5 AS
(
	SELECT k.table_name,k.column_name
	FROM INFORMATION_SCHEMA.[COLUMNS] k
),
cte6 AS 
(
	SELECT
		a.table_name,
		' [crlf]when matched then [crlf]update set [crlf]'
		+ STUFF(b.column_list,1,1,'') AS WHEN_MATCHED_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte3) a
		CROSS APPLY
		(
			SELECT 
				'[crlf],to_table.' +
				+ QUOTENAME(c3.column_name)
				+ ' = '
				+ 'from_table.'+
				QUOTENAME(c3.column_name)
			FROM
				cte3 c3
			WHERE c3.table_name = a.table_name
			FOR XML PATH('')
		) b(column_list)
)
SELECT 
	c2.table_name
	,'alter procedure sp_merge_'+c2.table_name+' as [crlf]begin '
	+ JOIN_CONDITION + WHEN_NOT_MATCHED_CONDITION + WHEN_MATCHED_CONDITION
	+ '; end' as merge_string
into #temptable
FROM
	cte2 c2
	INNER JOIN cte4 c4 ON c2.table_name = c4.table_name
	inner join cte6 c6 on c2.TABLE_NAME=c6.TABLE_NAME;

update #temptable set
	#temptable.merge_string=
		replace(
			replace(
				replace(
					replace(
						replace(
							replace(
								replace(
									replace(
										replace(
											replace(
												replace(
													replace(
														replace(#temptable.merge_string,'[crlf]',CHAR(13)+char(10))
													,'[crlf]',CHAR(13)+char(10))
												,'[crlf]',CHAR(13)+char(10))
											,'[crlf]',CHAR(13)+char(10))
										,'[crlf]',CHAR(13)+char(10))
									,'[crlf]',CHAR(13)+char(10))
								,'[crlf]',CHAR(13)+char(10))
							,'[crlf]',CHAR(13)+char(10))
						,'[crlf]',CHAR(13)+char(10))
					,'[crlf]',CHAR(13)+char(10))
				,'[crlf]',CHAR(13)+char(10))
			,'[crlf]',CHAR(13)+char(10))
		,'[crlf]',CHAR(13)+char(10))
select * from #temptable

set @sql=(select top 1 merge_string from #temptable)
while @sql is not null
	begin try
		select @sql
		execute (@sql)
		delete from #temptable where merge_string=@sql
		set @sql=(select top 1 merge_string from #temptable)
	end try
	begin catch
		delete from #temptable where merge_string=@sql
		set @sql=(select top 1 merge_string from #temptable)
	end catch

And this is a sample output


USE [111_Adastra_Local]
GO
/****** Object:  StoredProcedure [dbo].[sp_Merge_AbandonedCase]    Script Date: 10/18/2012 09:07:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_Merge_AbandonedCase] as 
 begin MERGE [111_Adastra_Local].dbo.[AbandonedCase] AS to_table using [connection].dbo.[AbandonedCase] AS from_table ON  to_table.[AbandonedCaseRef] = from_table.[AbandonedCaseRef] when not matched by target then insert ([AbandonedCaseRef],[AbandonReasonRef],[EntryDate],[ServiceRef],[CaseTypeRef],[ProviderRef],[ProviderGroupRef],[DOB],[AgeOnly],[UserRef],[Forename],[Surname],[SurnamePrefix],[Maiden],[MaidenPrefix],[CallerRelationshipRef],[CallerName],[CallerPhone],[CallerExtn],[InsuranceType],[InsuranceCompanyRef],[InsuranceNumber],[HomeAddressRef],[CurrentLocationAddressRef],[ContactPhone],[HomePhone],[MobilePhone],[OtherPhone],[Sex],[EthnicityRef],[HumanLanguageRef],[LocalLanguageSpoken],[UserComments]) values (from_table.[AbandonedCaseRef],from_table.[AbandonReasonRef],from_table.[EntryDate],from_table.[ServiceRef],from_table.[CaseTypeRef],from_table.[ProviderRef],from_table.[ProviderGroupRef],from_table.[DOB],from_table.[AgeOnly],from_table.[UserRef],from_table.[Forename],from_table.[Surname],from_table.[SurnamePrefix],from_table.[Maiden],from_table.[MaidenPrefix],from_table.[CallerRelationshipRef],from_table.[CallerName],from_table.[CallerPhone],from_table.[CallerExtn],from_table.[InsuranceType],from_table.[InsuranceCompanyRef],from_table.[InsuranceNumber],from_table.[HomeAddressRef],from_table.[CurrentLocationAddressRef],from_table.[ContactPhone],from_table.[HomePhone],from_table.[MobilePhone],from_table.[OtherPhone],from_table.[Sex],from_table.[EthnicityRef],from_table.[HumanLanguageRef],from_table.[LocalLanguageSpoken],from_table.[UserComments])  when matched then update set to_table.[AbandonedCaseRef] = from_table.[AbandonedCaseRef],to_table.[AbandonReasonRef] = from_table.[AbandonReasonRef],to_table.[EntryDate] = from_table.[EntryDate],to_table.[ServiceRef] = from_table.[ServiceRef],to_table.[CaseTypeRef] = from_table.[CaseTypeRef],to_table.[ProviderRef] = from_table.[ProviderRef],to_table.[ProviderGroupRef] = from_table.[ProviderGroupRef],to_table.[DOB] = from_table.[DOB],to_table.[AgeOnly] = from_table.[AgeOnly],to_table.[UserRef] = from_table.[UserRef],to_table.[Forename] = from_table.[Forename],to_table.[Surname] = from_table.[Surname],to_table.[SurnamePrefix] = from_table.[SurnamePrefix],to_table.[Maiden] = from_table.[Maiden],to_table.[MaidenPrefix] = from_table.[MaidenPrefix],to_table.[CallerRelationshipRef] = from_table.[CallerRelationshipRef],to_table.[CallerName] = from_table.[CallerName],to_table.[CallerPhone] = from_table.[CallerPhone],to_table.[CallerExtn] = from_table.[CallerExtn],to_table.[InsuranceType] = from_table.[InsuranceType],to_table.[InsuranceCompanyRef] = from_table.[InsuranceCompanyRef],to_table.[InsuranceNumber] = from_table.[InsuranceNumber],to_table.[HomeAddressRef] = from_table.[HomeAddressRef],to_table.[CurrentLocationAddressRef] = from_table.[CurrentLocationAddressRef],to_table.[ContactPhone] = from_table.[ContactPhone],to_table.[HomePhone] = from_table.[HomePhone],to_table.[MobilePhone] = from_table.[MobilePhone],to_table.[OtherPhone] = from_table.[OtherPhone],to_table.[Sex] = from_table.[Sex],to_table.[EthnicityRef] = from_table.[EthnicityRef],to_table.[HumanLanguageRef] = from_table.[HumanLanguageRef],to_table.[LocalLanguageSpoken] = from_table.[LocalLanguageSpoken],to_table.[UserComments] = from_table.[UserComments]; end

As you can see, it's inserting the first line break but none of the subsequent ones.
Any ideas why?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 07:00:59

There are code-formatting tools available, both free and commercial. So you could possibly take the stand that, this is auto-generated code, if someone wants to make it readable, use one of those tools.

Regardless, the first characters in the CROSS APPLY section are special (see red) because the first four characters get stripped off by the STUFF function (see in green). So I moved the [crlf] to the end in the cross apply (see blue).

Also, you need only one replace in the update #temptable statement because replace replaces all instances of the match string

drop table #temptable
declare @sql as varchar(max)
;WITH cte1 AS
(
	SELECT 
		k.table_name,
		k.column_name
	FROM
		information_schema.key_column_usage k
		INNER JOIN information_schema.table_constraints c ON
			c.CONSTRAINT_NAME = k.CONSTRAINT_NAME
	WHERE
		c.CONSTRAINT_TYPE = 'PRIMARY KEY'
),
cte2 AS
(
	SELECT
		a.table_name,
		'MERGE [111_Adastra_Local].dbo.'
		+ QUOTENAME(a.table_name)
		+ ' AS to_table [crlf]using [connection].dbo.'
		+ QUOTENAME(a.table_name)
		+ ' AS from_table [crlf]ON '
		+ STUFF(b.join_condition,1,4,'') AS JOIN_CONDITION
	FROM
		(SELECT DISTINCT table_name FROM cte1) a
		CROSS APPLY
		(
			SELECT 
				' AND to_table.' 
				+ QUOTENAME(c1.column_name) + ' = from_table.'
				+ QUOTENAME(c1.column_name) + '[crlf]'
			FROM
				cte1 c1
			WHERE c1.table_name = a.table_name
			FOR XML PATH('')
		) b(join_condition)
),

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2012-10-18 : 08:27:07

Thank you (again).
It works brilliantly now and it's readable as well if anyone needs to make any adjustments.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-18 : 08:56:43

very welcome .)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources