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?

where do you get your values? is it some parameters, like @value1...? — roman
– roman, Commented Aug 21, 2013 at 9:14
@framara Curious case i tried with the same code but its working , However you can first check for if column exist than go for column length . — Suraj Singh
– Suraj Singh, Commented Aug 21, 2013 at 9:54

Giannis Paraskevopoulos · Accepted Answer · 2013-08-21 09:18:35Z

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)

I finally used the EXEX('INSERT... ') with escape for single quotes. Thanks!

podiluska · Accepted Answer · 2013-08-21 09:22:17Z

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.

SteveB · Accepted Answer · 2013-08-21 09:48:21Z

Check if Column2 of TableA exists using INFORMATION_SCHEMA.COLUMNS and use that in your original query to switch between the 2 inserts

Suraj Singh · Accepted Answer · 2013-08-21 10:06:22Z

 IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLEA' AND COLUMN_NAME = 'COLUMNNAME') BEGIN IF COL_LENGTH('TableA', 'Column2') IS NOT NULL BEGIN INSERT INTO [dbo].[TableA]([Column1], [Column2], [Column3], [Column4]) SELECT value1, value2, value3, value4

Check first if column exists or not than go check for its length . However i don't see anything wrong in checking by COL_LENGTH .

-Hope it helps .

Collectives™ on Stack Overflow

SQL Insert depending if a column exists

4 Answers 4

2 Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Linked

Related