Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Append record Expand / Collapse
Author
Message
Posted Thursday, August 14, 2014 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 7, Visits: 31
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
Post #1603385
Posted Thursday, August 14, 2014 11:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 216, Visits: 574
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
Post #1603405
Posted Thursday, August 14, 2014 12:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 7, Visits: 31
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'
Post #1603437
Posted Thursday, August 14, 2014 12:34 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 6:58 AM
Points: 216, Visits: 574
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'
Post #1603443
Posted Thursday, August 14, 2014 12:35 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
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,"...
Post #1603444
Posted Thursday, August 14, 2014 12:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 7, Visits: 31
Ok I don't understand the first line of code select '[' + name + '] = 0,'

name I think is a column name?
Post #1603446
Posted Thursday, August 14, 2014 12:46 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
Look at the columns in the view sys.columns
Post #1603452
Posted Thursday, August 14, 2014 4:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 12:17 PM
Points: 7, Visits: 31
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
Post #1603496
Posted Thursday, August 14, 2014 6:41 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
I told you how in my last post. Follow that.
Post #1603536
Posted Friday, August 15, 2014 6:24 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
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;


Post #1603665
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse