Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Append record


Append record

Author
Message
dosia2
dosia2
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 39
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
rxm119528
rxm119528
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 804
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
dosia2
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 39
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
rxm119528
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 804
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
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840
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
dosia2
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 39
Ok I don't understand the first line of code select '[' + name + '] = 0,'

name I think is a column name?
gbritton1
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840
Look at the columns in the view sys.columns
dosia2
dosia2
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 39
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
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840
I told you how in my last post. Follow that.
gbritton1
gbritton1
Old Hand
Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)Old Hand (369 reputation)

Group: General Forum Members
Points: 369 Visits: 840
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;



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search