Append record

  • I heed to append a record to my table. It contains may columns (about 90) .The fist column is CustomerCode which is supplied by parameter.The rest of the columns should be 0, I can't change the Table Design to have Zero as Default, I need to insert Zero to every column except the first one. Can this be done with a loop, is there a quick way of doing this without specifying all column names.

    The code below inserts a record.

    insert into myTable (CustCode)

    select @CustCode

  • Try this. I will not update but generates the script for update columns

    select '[' + name + '] = 0'

    from sys.columns

    WHERE object_id = OBJECT_ID(N'[dbo].[myTable]')

    and name != 'CustCode'

    Thanks

  • This is what I have , still no Zeros in the columns

    select '[' + name + '] = 0'

    from sys.columns

    WHERE object_id = OBJECT_ID(N'[dbo].[ArCustomerBal]')

    and name != ' @sx_customer_code'

  • Okay we will go step by step.

    First what is the out put that you are seeing for this statement

    select '[' + name + '] = 0,'

    from sys.columns

    WHERE object_id = OBJECT_ID(N'[dbo].[ArCustomerBal]')

    and name != ' @sx_customer_code'

  • You can do it without dynamic sql using SSMS. For any table, click on the Columns heading and drag to the query window. You'll get all the columns separated by commas. Next, type "0," ten times, then copy what you have and paste it on the end of itself 9 times. Now you have all the column names and ninety zeros to match.

    Put them in their proper places in an INSERT statement and you're done:

    INSERT INTO yourtable (...drag and drop column list here...)

    SELECT ...90 copies of "0,"...

    Now, you only have to take care of the one column you really want to control. Find it in the column list and move it to the front of the list, then change your select to

    SELECT @mydata, ...89 copies of "0,"...

  • Ok I don't understand the first line of code select '[' + name + '] = 0,'

    name I think is a column name?

  • Look at the columns in the view sys.columns

  • OK I did there is a name in there name(sysname(nvarchar(128)),null)

    I need to insert zero to every column except the first one which contains primary key.

    thanks

  • I told you how in my last post. Follow that.

  • Here is a complete dynamic SQL solution to the problem:

    declare @CustCode int = 1;

    declare @sql nvarchar(4000);

    with cols(col) as (

    select name

    from sys.columns

    where object_id = OBJECT_ID(N'[dbo].[myTable]')

    and name <> 'CustCode'

    ),

    collist(c) as (select ',' + QUOTENAME(col) from cols for xml path('')

    ),

    zeros(z) as (select ',0' from cols for xml path('')

    )

    select @sql = 'insert into mytable (CustCode' +(select c from collist) + ')'

    + 'select ' + str(@CustCode) + (select z from zeros)

    ;

    exec (@sql);

    select * from mytable;

Viewing 10 posts - 1 through 9 (of 9 total)

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