SQL Insert depending if a column exists
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?
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)
Rather than approaching this dynamically, I would create stored procedures with a common signature, and add the appropraiate version to your various versions of the database
eg:
create proc TableAInsert
(
@col1 int,
@col2 int,
@col3 int,
@col4 int
)
In this fashion, you create an interface definition for your database.
If your database changes again, you can create a new version of this procedure with an optional parameter with a default value, and carry on calling it in the same manner as before.
使用INFORMATION_SCHEMA.COLUMNS检查TableA的Column2是否存在，并在原始查询中使用它来在两个插入之间切换
下一篇: SQL插入取决于列是否存在