SQL Insert depending if a column exists

Question

I need to do one INSERT or another depending if a column exist because of different versions of the same table.

I did the approach at this thread but SQL Server's pre check or 'sort of compilation' detects an error that would not fail during execution time.

Here's some code

IF COL_LENGTH('TableA', 'Column2') IS NOT NULL BEGIN  INSERT INTO [dbo].[TableA]([Column1], [Column2], [Column3], [Column4]) SELECT value1, value2, value3, value4  END ELSE BEGIN  INSERT INTO [dbo].[TableA]([Column1], [Column3], [Column4]) SELECT value1, value3, value4  END 

Any workaround?

Answer 1

SQL will know that the column doesn't exist so it won't let you run the query. The solution would be to execute a dynamic query.

DECLARE @value1 AS VARCHAR(50) DECLARE @value2 AS VARCHAR(50) DECLARE @value3 AS VARCHAR(50) DECLARE @value4 AS VARCHAR(50)  SET @value1 = 'somevalue1' SET @value2 = 'somevalue2' SET @value3 = 'somevalue3' SET @value4 = 'somevalue4'  DECLARE @SQL AS VARCHAR(MAX)  IF COL_LENGTH('TableA', 'Column2') IS NOT NULL     BEGIN          SET @SQL =              'INSERT INTO [dbo].[TableA]([Column1], [Column2], [Column3], [Column4])             SELECT ' + @value1 + ', ' + @value2 + ', ' + @value3 + ', ' + @value4      END  ELSE     BEGIN          SET @SQL =              'INSERT INTO [dbo].[TableA]([Column1], [Column3], [Column4])             SELECT ' + @value1 + ', ' + @value3 + ', ' + @value4      END  EXEC(@SQL)