Inserting Records based on a Table

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

Transact-SQL (2000)

Inserting Records based on a Table

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-17 : 00:34:01

I'm trying to write a sproc that inserts several records based on the number of records I select.

Table:


id	salesrepid	invoiceid	rate	amount
--	----------	---------	----	------
1	1		1		.1	50
2	2		1		.15	100
3	1		2		.1	12
4	1		3		.1	15

All I know is the invoiceidand some other amount, which i must multiply by the rate. So if @invoiceid = 1 and @Amount = 100, then I want the following BOLD rows to be inserted:


id	salesrepid	invoiceid	rate	amount
--	----------	---------	----	------
1	1		1		.1	50
2	2		1		.15	100
3	1		2		.1	12
4	1		3		.1	15
5	1		1		.1	10 -- row rate * @Amount
6	2		1		.15	15 -- row rate * @Amount

Notice rows 1 and 5, and row 2 and 6 match with the salesrepid and invoiceid and rate, the new amount is the rate multiplied by the @Amount of 100 as described above.

All I can think of is some kind of looping the the records, but that requires a cursor. Another option I thought about, was making a comma deliminated value with the appropriate id's (may have to research how to do this) then using a function of mine that loops thru the comma deliminated value and gets the record for the first value and inserts the appropriate row, then get's then next value and so forth. Remembe All I know is the invoice id and I do not know how many rows are associated with that invoiceid.

Thanks.

- RoLY roLLs

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-17 : 00:42:26

darnit, i hate when I search and search and then when I post I find an answer!

declare @amount money
select @amount = 400
insert into salesrepcommission
(
			SalesRepID
			, ClientInvoiceID
			, SalesRepCommissionRate
			, SalesRepCommissionAmount
)
select
	SalesRepID
	, ClientInvoiceID
	, SalesRepCommissionRate
	, @amount * SalesRepCommissionRate
from
	salesrepcommission
where
	ClientInvoiceID = 1

- RoLY roLLs

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-17 : 00:44:24

Is this what you want?

INSERT INTO table (SalesRepID, InvoiceID, Rate, Amount)
 SELECT SalesRepID, InvoiceID, Rate, Rate * @Amount
  FROM table 
 WHERE InvoiceID = @InvoiceID

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-17 : 01:00:26

yep! thanks timmy, i guess the answer swooped into my head as you were writing it