Update More than 1 Fieds - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update More than 1 Fieds

Author

Topic

MattieBala69
Starting Member

30 Posts

Posted - 2005-03-03 : 16:41:41

Hey ,
Is there a way to update more than 1 field to a temp table that .
Let me explain my problem. I had at first created 13 diff store proc for each subreport for this one report its a scorecard report. But it takes too long to run. So i was thinking of putting all these 13 stor proc into one. SO for this in the end i created a tabel there are about 50 fields where i need to update it. If someone could help me out would be great.
Thank you,
Mattie

nr
SQLTeam MVY

12543 Posts

Posted - 2005-03-03 : 16:47:30

Without knowing a bit more about what you are trying to do it's difficult to say.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MattieBala69
Starting Member

30 Posts

Posted - 2005-03-07 : 18:09:45

Hey,
Sorry I could not get back to u regarding this earlier . I hope this helps
THis is what i have for eg
insert into #Scorecard (ID,LenderNumber, ApprovedDate, MedalClass, LenderName, Status,
StatusDate, QCStatus, QCStatusDate, SourceOfBusiness)
select 1, LenderNumber, ApprovedDate, MedalClass, LenderName, Status,
StatusDate, QCStatus, QCStatusDate, SourceOfBusiness
from #ClientInfo

--2
insert into #Scorecard (ID, CPStatus, CPCount, CPVolume, PIPCount, PIPVolume)
Select 2, cpstatus, cpcount, cpvolume, pipcount, pipvolume
From #CPV

I have 14 more inserts . THe way i need it is there anyway to to mass update to a table instead of insert. I wrote the stor proce to be used for Crystal report.
If someone could help me it would be a great help
Thank you,

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-03-07 : 19:55:09

You still need to elaborate a bit on what it is you are trying to accomplish. When you say "mass update" do you really mean an UPDATE statement or do you want to perform a large INSERT? What problem are you trying to solve? Performance?

Take a minute, remember that we don't know your situation at all. Now try to explain what it is that is causing you pain. Some sample data and desired output would go a long way towards allowing us to grasp your specifics.

HTH

=================================================================
I never desire to converse with a man who has written more than he has read. -Samuel Johnson, lexicographer (1709-1784)

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-03-07 : 20:42:29

Have you looked at UNION and UNION ALL in Books Online? See if that solved your issue. If not, you need to provide DDL, DML, and expected output. Here is an example of DDL and DML:


--Setup a sample transactions table.
DECLARE @transactions TABLE(
	transaction_id INT IDENTITY(1,1) PRIMARY KEY,
	customer_id VARCHAR(55),
	transaction_date DATETIME,
	transaction_amount MONEY)

INSERT @transactions(customer_id, transaction_date, transaction_amount)	
	SELECT 'Customer1','05/15/04',10000 UNION ALL
	SELECT 'Customer1','05/15/04',10000 UNION ALL
	SELECT 'Customer1','05/15/04',10000 UNION ALL
	SELECT 'Customer1','05/15/04',10000 UNION ALL
	SELECT 'Customer1','05/15/04',10000 UNION ALL
	SELECT 'Customer1','05/15/04',10000 UNION ALL
	SELECT 'Customer1','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000 UNION ALL
	SELECT 'Customer2','05/15/04',10000

--Verify it's what you want.
SELECT * FROM @transactions

--****This actually begins the query.
--Declare the grouping number.
DECLARE @grouping INT

--This is the number you want to group your transactions by.
SELECT @grouping = 6

--This is an example of what this grouping number provides you.
SELECT (1-1)/@grouping
SELECT (2-1)/@grouping
SELECT (3-1)/@grouping
SELECT (4-1)/@grouping
SELECT (5-1)/@grouping
SELECT (6-1)/@grouping
SELECT (7-1)/@grouping
SELECT (8-1)/@grouping
SELECT (9-1)/@grouping
SELECT (10-1)/@grouping
SELECT (11-1)/@grouping
SELECT (12-1)/@grouping
SELECT (13-1)/@grouping
SELECT (14-1)/@grouping
SELECT (15-1)/@grouping
SELECT (16-1)/@grouping
SELECT (17-1)/@grouping
SELECT (18-1)/@grouping
SELECT (19-1)/@grouping
SELECT (20-1)/@grouping

--Create a temp table and insert so all customer_ids are grouped together and the ordinal can be used.
DECLARE @transaction_grouping TABLE(
	transaction_grouping INT IDENTITY(1,1) PRIMARY KEY,
	customer_id VARCHAR(55),
	transaction_date DATETIME,
	transaction_amount MONEY)

INSERT @transaction_grouping(
	customer_id,
	transaction_date,
	transaction_amount)

	SELECT
		customer_id,
		transaction_date,
		transaction_amount
	FROM
		@transactions
	ORDER BY
		customer_id,
		transaction_date
		
SELECT
	(tg.transaction_grouping-mp.min_point)/@grouping AS group_id,
	tg.customer_id,
	tg.transaction_date,
	SUM(tg.transaction_amount) AS transaction_charge
FROM 
	@transaction_grouping tg
	INNER JOIN (
		SELECT MIN(transaction_grouping) AS min_point, customer_id, transaction_date
		FROM @transaction_grouping
		GROUP BY
			customer_id,
			transaction_date) mp ON tg.customer_id = mp.customer_id
		AND tg.transaction_date = mp.transaction_date
GROUP BY
	(tg.transaction_grouping-mp.min_point)/@grouping,
	tg.customer_id,
	tg.transaction_date
ORDER BY 
	tg.customer_id,
	tg.group_id

You should then have a sample return set to explain the end you are trying to reach. You will then find you receive prompt and accurate help in almost any forum.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources