Sql-server – Using Variables to contain column names for SQL Server Merge statemtns

insertmergesql serversql server 2014

I'm trying to automate the column list grab for a merge job, and running into a bit of a head scratcher. I don't want to have to manually type out all of the column names, and thought I may be able to pass them through via a variable. When selecting the content of my variables, its lists correctly. I know the update component is not written correctly (I'll gladly take feedback on how to streamline that), but what I'm concerned about is the "Insert Values(@sourcecolumns)" which is throwing an error: "Column name or number of supplied values does not match table definition."

What I'm passing through is below:

CREATE PROCEDURE sys_sp_merge_table_name

@targcolumns varchar(max),
@targtable varchar(50),
@sourcecolumns varchar(max)

AS
BEGIN
SET NOCOUNT ON;
set @targcolumns =  '' --column names for the table
set @targtable = 'targ_table_name' -- target table name
set @sourcecolumns = ''

--assigning column names to pass to variables using "Tsource" as alias for table name 
select @targcolumns = @targcolumns + column_name+',' from INFORMATION_SCHEMA.columns where table_name = @targtable
select @sourcecolumns = @sourcecolumns + 'Tsource.'+column_name+',' from INFORMATION_SCHEMA.columns where table_name = @targtable

--trim the trailing commas from target and source table
set @sourcecolumns = Left(@sourcecolumns,len(@sourcecolumns)-1)
set @targcolumns = Left(@targcolumns,len(@targcolumns)-1)

--run merge job, zdata is our linked server from which the data is imported
merge targ_table_name
using (select * from openquery(zdata,'select * from source_table_name'))as Tsource 
on (targ_table_name.ID = Tsource.ID) 
when matched then
Update set @targcolumns = @sourcecolumns
when not matched by target then
insert values (@sourcecolumns);
END
GO

EDIT:
@Max Vernon That is correct regarding updating the set piece. Once this is created, its created, but to avoid having to type this out for 205 columns I'd like to automate it. Additionally this will be reapplied to other tables, so I'd like to know the best/most efficient way to do it. How to automate the parameters so that:

Update set @targcolumns.A = @sourcecolumns.A, @targcolumns.B=@sourcecolumns.B, etc.

There are 205 columns in this particular table, and unfortunately table structure is not something we have control over. All in all there will be about 150 tables being merged routinely.

Edit2: Final Edit – I just wanted to give the solution I'm using for anyone who comes across this in the future. This contains the dynamic SQL that Max recommended as well as my query to set up the SET statements

Declare
@targcolumns varchar(max),
@targtable varchar(50),
@sourcecolumns varchar(max),
@runmerge nvarchar(max),
@setstatement nvarchar(max)


SET NOCOUNT ON;
set @targcolumns =  '' --column names for the table
set @targtable = 'target_table_name' -- target table name
set @sourcecolumns = ''

--assigning column names to pass to variables using "Tsource" as alias for table name 
select @targcolumns = @targcolumns + column_name+',' from INFORMATION_SCHEMA.columns where table_name = @targtable
select @sourcecolumns = @sourcecolumns + 'Tsource.'+column_name+',' from INFORMATION_SCHEMA.columns where table_name = @targtable
select @setstatement = @setstatement + '[' + column_name + ']' + ' = ' + 'Tsource.'+'[' + column_name+']'+','from INFORMATION_SCHEMA.columns where table_name = @targtable

--trim the trailing commas from target and source table
set @sourcecolumns = Left(@sourcecolumns,len(@sourcecolumns)-1)
set @targcolumns = Left(@targcolumns,len(@targcolumns)-1)
set @setstatement = Left(@setstatement,len(@setstatement)-1)

--run merge job, zdata is our linked server from which the data is imported
set @runmerge=' merge target_table_name
using (select * from openquery(zdata,''select * from target_table_name''))as Tsource 
on (target_table_name.ID = Tsource.ID) 
when matched then
Update set ' + @setstatement + '
when not matched by target then
insert values ('+@sourcecolumns+');';

exec sp_executesql @runmerge;

Note that I don't use all of the variables in this example, but do have other examples where they are all necessary. Hope this helps and thanks Max for setting me on the right path.

Best Answer

Instead of typing the entire list of column names, you can simply drag-and-drop the column list from the Object Explorer window in SQL Server Management Studio. In the image below, you drag-and-drop the highlighted section over to the query window:

However, if you insist on automatically creating the list of columns, you'll need to run the MERGE command using dynamic SQL. Erland Sommarskog has the definitive article on how to use dynamic SQL, along with a great set of advice on how not to do it, at http://www.sommarskog.se/dynamic_sql.html

Something like this:

DECLARE @cmd NVARCHAR(MAX);
SET @cmd = 'merge targ_table_name
using (select * from openquery(zdata,''select * from source_table_name'')) 
     as Tsource 
on (targ_table_name.ID = Tsource.ID) 
when matched then
Update set ' + @TargetToSourceColumns + '
when not matched by target then
insert values (' + @SourceColumns + ');';

EXEC sp_executesql @cmd;

The above statement is not syntactically correct, but essentially shows how you need to concatenate parts of the query that are dynamic. You'll need to compile the @TargetToSourceColumns and @SourceColumns variables as needed.

Related Solutions

Sql-server – How to create implicit select statements

Not sure that this is simpler than a cursor solution, but here is what I would do:

Before we begin we need a few tables in a SQL Fiddle:

MS SQL Server 2012 Schema Setup

CREATE TABLE dbo.tbl1(c1 INT, c2 INT, c3 INT);
CREATE TABLE dbo.tbl2(c4 INT, c5 INT, c6 INT);
CREATE TABLE dbo.tbl3(c7 INT, c8 INT, c9 INT);

First we need the list of all user tables. For that we can use the sys.tables catalog view. The OBJECT_SCHEMA_NAME() function gets us the schema name, the OBJECT_NAME() function the table name. The QUOTENAME() function quotes the names correctly, in case some of those contain special characters, key words or spaces. (In this example the use of OBJECT_NAME() is not strictly necessary as sys.tables has a name column, but I left it in as you can use this pattern with any catalog view that has an object_id column.)

Query 1:

SELECT 
  QUOTENAME(OBJECT_SCHEMA_NAME(T.object_id))+'.'+
  QUOTENAME(OBJECT_NAME(T.object_id)) AS quoted_table_name,
  T.object_id
FROM sys.tables AS T;

Results:

| QUOTED_TABLE_NAME | OBJECT_ID |
|-------------------|-----------|
|      [dbo].[tbl1] | 245575913 |
|      [dbo].[tbl2] | 261575970 |
|      [dbo].[tbl3] | 277576027 |

The next step is to get the list of column names, again quoted. We can use the sys.columns catalog view for that.

Query 2:

SELECT C.name,C.column_id 
  FROM sys.columns AS C
 WHERE C.object_id = OBJECT_ID('dbo.tbl1');

Results:

| NAME | COLUMN_ID |
|------|-----------|
|   c1 |         1 |
|   c2 |         2 |
|   c3 |         3 |

The next hurdle is to get those columns in a comma separated list. There is no string concatenation aggregate function build in so we have to use a trick:

Query 3:

SELECT STUFF((
  SELECT ','+QUOTENAME(name)
    FROM sys.columns AS C
   WHERE C.object_id = OBJECT_ID('dbo.tbl1')
   ORDER BY C.column_id
     FOR XML PATH(''),TYPE
  ).value('.','NVARCHAR(MAX)'),1,1,'') AS clomun_list;

Results:

|    CLOMUN_LIST |
|----------------|
| [c1],[c2],[c3] |

With that all pieces are in place and we just have to put them all together:

Query 4:

SELECT 'SELECT ' +
       CL.column_list +
       ' FROM ' +
       QUOTENAME(OBJECT_SCHEMA_NAME(T.object_id)) + '.' +
       QUOTENAME(OBJECT_NAME(T.object_id)) +
       ';' AS select_statement
FROM sys.tables AS T
CROSS APPLY (
  SELECT STUFF((
    SELECT ','+QUOTENAME(name)
      FROM sys.columns AS C
     WHERE C.object_id = T.object_id
     ORDER BY C.column_id
       FOR XML PATH(''),TYPE
    ).value('.','NVARCHAR(MAX)'),1,1,'') AS column_list
  )CL;

Results:

|                         SELECT_STATEMENT |
|------------------------------------------|
| SELECT [c1],[c2],[c3] FROM [dbo].[tbl1]; |
| SELECT [c4],[c5],[c6] FROM [dbo].[tbl2]; |
| SELECT [c7],[c8],[c9] FROM [dbo].[tbl3]; |

This example will work in SQL 2005 and later, assuming you are on the latest service pack. There is no clean solution to achieve this with SQL Server 2000. In that case you need to go back to your cursor solution.

Sql-server – How to query a remote server using a local function

Seems (based on the name, anyway) like a pretty basic scalar UDF that just massages strings, won't be subject to a lot of changes over time, and doesn't rely on any data. So, I suggest you make a copy of the function on the source server, and fight whatever rule is preventing you from doing so (it shouldn't be hard if you can justify the use of the function, and describe what kind of hack you're trying to use as a workaround to the rule). Note that the function doesn't have to live in the same database, it just needs to be on the same server.

(Even if the function does rely on data, and it's clear in your case it doesn't, the local copy of the function could reference the data in the other server, using 4-part name(s), without referencing any functions.)

DRY is not always worth pursuing.

Best Answer

Related Solutions

Sql-server – How to create implicit select statements

Sql-server – How to query a remote server using a local function

Related Question