Post reply

Column Existence and insert....

  • March 2, 2006 at 4:14 pm

    #111863

    Hi All,

    Iam trying to insert into a table if that particular column exists....It doesn't work....Please let me know the work around....

    This is my script:

    exec sp_columns @TABLE_NAME = 'account', @Column_name = 'account_id'

    If @@ROWCOUNT = 1

    insert into account_detail (account_id,fst_name,lst_name)

    select account_id,fst_name,lst_name from account

    I have used information_schema.columns and syscolumns to check the existence of column. But the insert is happening no matter whatever it is.

    If I use a print then it doesn't execute....

    Please explain what's the reason for it....Try this example for the negative condition I mean if the column doesn't exists......

    Thanks,

    Ganesh

     

     

     

     

  • March 2, 2006 at 4:20 pm

    #624492

    Hi Db Gurus,

    Atleast I would expect a compile time error, whereas it gives me a run time error. That's wierd.

    Please help me out.

    Thanks,

    Ganesh

     

  • March 2, 2006 at 6:57 pm

    #624509

    I have tried on my tables:

    exec sp_columns 'Address', @Column_name = 'Id'

    IF @@RowCount = 1

    select GetDate()

    exec sp_columns 'Address', @Column_name = 'No_Id'

    IF @@RowCount = 1

    select GetDate()

    Works fine.

    Check your tables.

    _____________
    Code for TallyGenerator

  • March 3, 2006 at 8:15 am

    #624626

    It works fine in my DB as well. Did you cut and paste the actual script that is not working, or did you edit it to make it more simple?

    If you edited it, I think the problem could be caused by not using BEGIN .... END around the INSERT block. If there is "something" right after the If @@ROWCOUNT = 1 that you didn't show in your post, then it would behave precisely in the way you described. IF affects only the next command, if you don't specify a statement block with BEGIN...END.

    Also, I'm not sure why do you check whether the column exists. You don't know whether there is a column named Account_id in the table Account? It can happen, that this column is sometimes missing? And I really mean column, not the value in it... Or is that a part of some dynamic SQL statement, where the table/column name can change?

    Hm, and are your sure that you tested the statement with a column name that does not exist in the table?

    Maybe some additional explanation would help us to identify the problem better.

    Vladan

  • March 3, 2006 at 8:23 am

    #624630

    Hi,

    Use this kind of Query ?

    if exists(select * from information_schema.columns where table_name='???' and column_name='????')

    begin

    //////// if part

    end

    else

    //////// else part

    begin

    end

     

    Regards

    Amit Gupta

     

Viewing 5 posts - 1 through 4 (of 4 total)

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