1

I'm trying run basic Insert sql query.

Here is the insert sql.

Insert into tblABC( X1,X2,X3) 
Select X1,X2,X3 from tblXX

Problem is tblXX doesn't have X3 column.

Sometimes X3 will be there and sometimes it will be missing. When column is missing then push a default value such as 0.

Trying to use Case statement 

CASE WHEN EXISTS (SELECT 1 From information_schema.Columns  C   
Inner Join  Sys.Objects  O  
            On  ( O.Object_Id    = Object_Id(N'tblXX') And  O.Name   = C.Table_Name) 
and C.Column_Name   = 'X3')  THEN X3
               ELSE 0
          END

I get an Invalid column name error on X3 column

Dan
  • 4,312
  • 16
  • 28
user1810575
  • 823
  • 3
  • 24
  • 45
  • How do you push a default value into a column that doesn't exist? – Mike Cheel Sep 03 '14 at 14:37
  • 1
    It would have to be separate statement, you can check if column exists and execute appropriate code depending on the result. See http://stackoverflow.com/questions/133031/how-to-check-if-column-exists-in-sql-server-table – InitK Sep 03 '14 at 14:41

3 Answers3

4

You can try the following:

if (exists (select 1 from information_schema.columns where table_name = 'tblXX' and column_name = 'X3'))
begin
    Insert into tblABC(X1, X2, X3) 
        Select X1, X2, X3
        from tblXX;
end
else
begin
    Insert into tblABC(X1, X2, X3) 
        Select X1, X2, 0
        from tblXX;
end;

If this doesn't work (because the if fails), then you would need to resort to dynamic SQL.

An important note: you would seem to have a poor application design if you are randomly adding and removing columns from tables. I would suggest that you fix this problem by fixing the underlying design of the system, so such efforts are not needed.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I wish it was that simple but thanks for your help. Although this didn't help me at least i know now i was heading in right direction. I wanted to make sure i didn't miss anything. Thanks!! – user1810575 Sep 03 '14 at 15:03
  • Will try to change the design, I was using Pivot sql not so easy but will give it a shot. – user1810575 Sep 03 '14 at 15:04
0

I'm not sure about what you're looking for, if you just want to fill the table tblABC with data from table tblXX that have no value for the third column, here is the query that use a default value:

INSERT INTO tblABC(X1, X2, X3)
SELECT T.X1
    ,T.X2
    ,0
FROM tblXX T

If you just want to use a default value if not present in tblXX, the query will be the following:

INSERT INTO tblABC(X1, X2, X3)
SELECT T.X1
    ,T.X2
    ,CASE
      WHEN T.X3 IS NULL THEN 0
      ELSE T.X3
    END
FROM tblXX T

Hope this will help you.

Joël Salamin
  • 3,538
  • 3
  • 22
  • 33
0

What you can do is when you create the table you can put a default value of 0 in the third column so you don't have to use an if statement. Then you can just specify the columns you want to insert values into or put null for the values which you have no value.

refer to this thread :

How to insert default values in SQL table?

Hope it helps,

Community
  • 1
  • 1
user3609544
  • 1