SQL merge function without specifying column names

Question

I am making SQLBulkCopy from my web application and inserting records into a staging table. This is my first experience with intermediate tables. A live table that will receive data has about 200 fields and may change in the future. When this change occurs, I did not want to overwrite the merge operator.

I came up with this SQL that mimics merge functionality but does not require me to describe table columns. I am not an expert on SQL and I do not want anyone to look and let me know if you find problems that may occur when using this SQL, because I have not seen examples of this and many people.

Note that entries in the staging table with a null identification field must be inserted.

-- set the table names, primary key field & vars to hold query parts DECLARE @LiveTable varchar(20) = 'Test' DECLARE @StagingTable varchar(20) = 'TestStaging' DECLARE @PKField varchar(20) = 'TestPK' DECLARE @SQLSet nvarchar(MAX) = '' DECLARE @SQLInsertFields nvarchar(MAX) = '' -- get comma delimited field names DECLARE @Fields nvarchar(MAX) = (SELECT dbo.fn_GetCommaDelimitedFieldNames(@LiveTable)) -- loop through fields generating set clause of query to execute WHILE LEN(@Fields) > 0 BEGIN DECLARE @Field varchar(50) = left(@Fields, CHARINDEX(',', @Fields+',')-1) IF @Field <> @PKField -- the primary key field cannot be updated BEGIN SET @SQLSet += ', ' + @LiveTable + '.' + @Field + ' = ' + @StagingTable + '.' + @Field SET @SQLInsertFields += ', ' + @Field END SET @Fields = STUFF(@Fields, 1, CHARINDEX(',', @Fields+','), '') END -- remove the leading comma SET @SQLSet = SUBSTRING(@SQLSet,3,LEN(@SQLSet)) SET @SQLInsertFields = SUBSTRING(@SQLInsertFields,3,LEN(@SQLInsertFields)) -- update records from staging table where primary key is provided DECLARE @SQL nvarchar(MAX) = N'UPDATE ' + @LiveTable + ' SET ' + @SQLSet + ' FROM ' + @LiveTable + ' INNER JOIN ' + @StagingTable + ' ON ' + @LiveTable + '.' + @PKField + ' = ' + @StagingTable + '.' + @PKField -- insert records from staging table where primary key is null SET @SQL += '; INSERT INTO ' + @LiveTable + ' (' + @SQLInsertFields + ') SELECT ' + @SQLInsertFields + ' FROM ' + @StagingTable + ' WHERE ' + @PKField + ' IS NULL' -- delete the records from the staging table SET @SQL += '; DELETE FROM ' + @StagingTable -- execute the sql statement to update existing records and insert new records exec sp_executesql @SQL; 

If anyone sees any performance issues or anything else, I appreciate the insight.

Answer 1

Do not do this. Indeed. You work very hard to avoid a rare problem that you probably won't handle when the time comes.

If the goal table changes, as you know, it will change so that your fantastic dynamic SQL will work correctly? How can you be sure that this will not work, i.e. Will work, syntactically, but actually do the wrong thing? If and when the target table changes, you don’t have to change the application and the staging table? What's all in the air that adds another SET item?

At the same time, how can anyone expect to read this gobbledygook (and not your fault, really, this SQL syntax)? The standard swamp insert will be very clear and reliable.

And fast. SQL Server cannot optimize a dynamic query. You used bcp to increase efficiency, and now you defeat it with well-designed reliability.