Sql-server – T-SQL OUTPUT DELETED columns by information schema

sql servert-sql

I have two tables:

enter image description here

Whenewher I'm removing some records from TableFirst I need to insert deleted record data into TableSecond. But they have different number of columns.

Is it possible to output deleted values of common columns using information schema(except Id of course) in delete statement?

DELETE FROM TableFirst 
OUTPUT DELETED.* INTO TableSecond
WHERE Id = 1

I'm trying to avoid naming each column (I'll have up to 50 columns in my project).

Best Answer

I find out the way to do it with the dynamic sql, we just need to compose query, where we can select all required column names using information schema and execute it.

Get column names first:

DECLARE @ColumnNamesTable Table([Name] NVARCHAR(MAX));
INSERT INTO @ColumnNamesTable
SELECT [Name] FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'TableFirst') AND [Name] <> 'Id' 
INTERSECT
SELECT [Name] FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'TableSecond')

Compose OUTPUT DELETED and column name strings:

DECLARE @OutputDeleted NVARCHAR(MAX), @ColumnNames NVARCHAR(MAX);

SELECT @OutputDeleted = STUFF((SELECT ', DELETED.' + Name FROM @ColumnNamesTable  FOR XML PATH ('')), 1,1,'')
SELECT @ColumnNames = STUFF((SELECT ',' + Name FROM @ColumnNamesTable  FOR XML PATH ('')), 1,1,'')

Compose query:

DECLARE @Query NVARCHAR(MAX), @MyId INT = 5;
SELECT @Query = CONCAT('DELETE FROM dbo.TableFirst OUTPUT', ' ', @OutputDeleted, ' ','INTO dbo.TableSecond(', @ColumnNames,') WHERE Id = ', @MyId)

Finaly we just execute the composed query:

exec sp_executesql @Query;

But we can get issues when there are some FKs.

Related Question