Append record

Question

Post reply

Append record

dosia2

SSC Journeyman

Points: 80
More actions
August 14, 2014 at 10:48 am

#145212

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

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

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

rxm119528 SSCrazy Points: 2960 More actions · Answer 1

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

dosia2 SSC Journeyman Points: 80 More actions · Answer 2

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'

rxm119528 SSCrazy Points: 2960 More actions · Answer 3

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'

gbritton1 SSCertifiable Points: 6522 More actions · Answer 4

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,"...

dosia2 SSC Journeyman Points: 80 More actions · Answer 5

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

name I think is a column name?

gbritton1 SSCertifiable Points: 6522 More actions · Answer 6

gbritton1

SSCertifiable

Points: 6522

August 14, 2014 at 12:46 pm

#1738194

Look at the columns in the view sys.columns

dosia2 SSC Journeyman Points: 80 More actions · Answer 7

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 8

gbritton1

SSCertifiable

Points: 6522

August 14, 2014 at 6:41 pm

#1738250

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 9

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;