Post reply

check column exist or not if not exist assign null values for that column

  • September 18, 2014 at 10:53 pm

    #311633

    Hi Friends i have small doubt in sql wheil load data from one table to another table i faced one issuse

    first table: emp

    id | name | sal | deptno | loc | referby

    1 | abc | 100 |10 | hyd | xyz

    2 | mnc | 200 |20 |chen | pqr

    second table:emprefers

    id | name | sal | deptno | loc | referby

    now i want load emp table data into emprefers table that time i write query like

    insert into emprefers select * from emp after ran qury data load into emprefers table data like below

    id | name | sal |deptno | loc |referby

    1 | abc | 100 |10 | hyd | xyz

    2 | mnc | 200 |20 |chen | pqr

    now i ran same query second time query has failed.reason is name column is deleted from emp table

    now i edit the query like

    insert into emprefers select id,'null'as name,sal,deptno,loc,referby from emp

    after edit query again i ran query now records are loading in emprefers table and data look like

    id | name | sal |deptno | loc |referby

    1 | null | 100 |10 | hyd | xyz

    2 | null | 200 |20 |chen | pqr

    every time before loading emprefers table i truncate emprefers table data. and emprefers table structure never changed.

    again third time i ran same query again query has failed reason for missing sal,deptno columns in emp table now i donot want edit query again reason is we donot know which columns deletes from emp table .that time we want solve the issue and we want load data into second table if the columns avilable in emp table then load data other wise we need to pass null or empty values for that columns. please tell me how to write query to check column exist or not if exist retrive same column other wise assign null values for that column.

  • September 19, 2014 at 12:08 am

    #1746591

    Hi asrinu13,

    May you have some constraint in ref table, Please check it.

    Other wise no problem in transferring data from one table to another.

    Create table #temp1 (id int, name nvarchar(20), salary float)

    Insert into #temp1

    Select 1,'Mac',2000.00

    Union all

    Select 2, 'Jack',15000.50

    Union all

    Select 3, 'Jill',30000

    Go

    Select * from #temp1

    Go

    Create table #temp1ref (id int, name nvarchar(20), salary float)

    Insert into #temp1ref

    Select * from #temp1

    select * from #temp1ref

    Go

    Insert into #temp1ref

    Select * from #temp1

    Select * from #temp1ref

    Go

    Insert into #temp1

    Select 7,null,2000.00

    Go

    Insert into #temp1ref

    Select * from #temp1

    Select * from #temp1ref

    Go

    Drop table #temp1

    Drop table #temp1ref

    Vimal LohaniSQL DBA | MCP (70-461,70-462)==============================The greatest barrier to success is the fear of failure ** Success is a journey not a destination**Think before you print, SAVE TREES, Protect Mother Nature

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply