CODEWITHSUNDEEP
sqlsql-server
framara
framara

Reputation: 2963

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?

Upvotes: 25

Views: 11274

Answers (4)

Suraj Singh
Suraj Singh

Reputation: 4069

    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 .

Upvotes: 1

SteveB
SteveB

Reputation: 1514

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

Upvotes: 2

podiluska
podiluska

Reputation: 51514

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.

Upvotes: 4

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18431

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)

Upvotes: 12

Related Questions