Inserting Multiple Rows into one table (with calculated fields)

[Mohd Al Junaibi]

Hello all,

my first post here...hope it goes well. I'm currently working on
stored procedure where I translated some reporting language into T-SQL

The logic:

I have a group of tables containing important values for calculation.
I run various sum calculations on various fields in order to retrieve
cost calculations ...etc.

1) There is a select statement which gathers all the "records" which
need calculations.
ex: select distinct Office from Offices where OfficeDesignati on =
'WE' or OfficeDesignati on = 'BE...etc.
As a result I get a list of lets say 5 offices which need to be
calculated!

2) A calculation select statement is then run on a loop for each of
the returned 5 offices (@OfficeName cursor used here!) found above.An
example can be like this

(* note that @WriteOff is a variable storing the result):

"select @WriteOff = sum(linecost * (-1))
From Invtrans , Inventory
Where ( transtype in ('blah', 'blah' , 'blah' ) )
and ( storeloc = @OfficeName )
and ( Invtrans.lineco st <= 0 )
and ( Inventory.locat ion = Invtrans.storel oc )
and ( Inventory.itemn um = Invtrans.itemnu m )"...etc

This sample statement returns a value and is passed to the variable
@WriteOff (for each of the 5 offices mentioned in step 1). This is done
around 9 times for each loop! (9 calculations)

3) At the end of each loop (or each office), we do an insert statement
to a table in the database.

>>>END of Logic<<

Problem:

This kind of dataset or report usually takes alot of time, and I need
to have the ability to storing all the calculated variables for each
"Office" in an "array" so that I can do ONE INSERT STATEMENT LOOP as
opposed to doing one insert statement at a time, in a loop.

Basically, a loop to calculate and save into an array, and then one
loop for insert statements.

Any suggestions gentlemen?

[David Portas]

Mohd Al Junaibi wrote:

Hello all,

my first post here...hope it goes well. I'm currently working on
stored procedure where I translated some reporting language into T-SQL

The logic:

I have a group of tables containing important values for calculation.
I run various sum calculations on various fields in order to retrieve
cost calculations ...etc.

1) There is a select statement which gathers all the "records" which
need calculations.
ex: select distinct Office from Offices where OfficeDesignati on =
'WE' or OfficeDesignati on = 'BE...etc.
As a result I get a list of lets say 5 offices which need to be
calculated!

2) A calculation select statement is then run on a loop for each of
the returned 5 offices (@OfficeName cursor used here!) found above.An
example can be like this

(* note that @WriteOff is a variable storing the result):

"select @WriteOff = sum(linecost * (-1))
From Invtrans , Inventory
Where ( transtype in ('blah', 'blah' , 'blah' ) )
and ( storeloc = @OfficeName )
and ( Invtrans.lineco st <= 0 )
and ( Inventory.locat ion = Invtrans.storel oc )
and ( Inventory.itemn um = Invtrans.itemnu m )"...etc

This sample statement returns a value and is passed to the variable
@WriteOff (for each of the 5 offices mentioned in step 1). This is done
around 9 times for each loop! (9 calculations)

3) At the end of each loop (or each office), we do an insert statement
to a table in the database.

>>END of Logic<<

Problem:

This kind of dataset or report usually takes alot of time, and I need
to have the ability to storing all the calculated variables for each
"Office" in an "array" so that I can do ONE INSERT STATEMENT LOOP as
opposed to doing one insert statement at a time, in a loop.

Basically, a loop to calculate and save into an array, and then one
loop for insert statements.

Any suggestions gentlemen?

It seems very likely that you could retrieve the whole result in one
query without looping through an array several times. This is a key
difference between procedural
languages and SQL. Stop thinking procedurally (loops and arrays) and
start thinking about set-based queries instead! :-)

Unfortunately, you haven't given enough information to get a full
answer. What we need to know are: the base table structures (post some
simplified CREATE TABLE statements but make sure you include the keys);
some sample data (post INSERT statements); your expected end result.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[David Portas]

Here's a guess. I am assuming that Office is the key of the Offices
table even though the presence of DISTINCT in your original query makes
me doubt it (an example of why it's important to include DDL with keys
in your posts).

SELECT T.storeloc, -SUM(linecost) AS WriteOff
FROM Invtrans AS T
JOIN Inventory AS I
ON I.location = T.storeloc
AND I.itemnum = T.itemnum
JOIN Offices AS O
ON T.storeloc = O.Office
WHERE transtype IN ('blah','blah', 'blah')
AND T.linecost <= 0
AND O.OfficeDesigna tion IN ('WE','BE')
GROUP BY T.storeloc ;

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

[Ed Murphy]

Mohd Al Junaibi wrote:

my first post here...hope it goes well. I'm currently working on
stored procedure where I translated some reporting language into T-SQL

The logic:

I have a group of tables containing important values for calculation.
I run various sum calculations on various fields in order to retrieve
cost calculations ...etc.

1) There is a select statement which gathers all the "records" which
need calculations.
ex: select distinct Office from Offices where OfficeDesignati on =
'WE' or OfficeDesignati on = 'BE...etc.
As a result I get a list of lets say 5 offices which need to be
calculated!

2) A calculation select statement is then run on a loop for each of
the returned 5 offices (@OfficeName cursor used here!) found above.An
example can be like this

(* note that @WriteOff is a variable storing the result):

"select @WriteOff = sum(linecost * (-1))
From Invtrans , Inventory
Where ( transtype in ('blah', 'blah' , 'blah' ) )
and ( storeloc = @OfficeName )
and ( Invtrans.lineco st <= 0 )
and ( Inventory.locat ion = Invtrans.storel oc )
and ( Inventory.itemn um = Invtrans.itemnu m )"...etc

This sample statement returns a value and is passed to the variable
@WriteOff (for each of the 5 offices mentioned in step 1). This is done
around 9 times for each loop! (9 calculations)

3) At the end of each loop (or each office), we do an insert statement
to a table in the database.

>>>END of Logic<<

Problem:

This kind of dataset or report usually takes alot of time, and I need
to have the ability to storing all the calculated variables for each
"Office" in an "array" so that I can do ONE INSERT STATEMENT LOOP as
opposed to doing one insert statement at a time, in a loop.

Basically, a loop to calculate and save into an array, and then one
loop for insert statements.

I believe your ideal solution will look something like this:

insert into some_other_tabl e (storeloc, WriteOff)
select it.storeloc, -sum(linecost)
from Invtrans it
join Inventory i on it.itemnum = i.itemnum
and it.storeloc = i.location
join Offices o on it.storeloc = o.storeloc
where it.transtype in ('blah','blah', 'blah')
and it.linecost <= 0
and i.ItemStatus = 'Active'
and o.OfficeDesigna tion in ('WE','BE')

[Ed Murphy]

Ed Murphy wrote:

Mohd Al Junaibi wrote:

> my first post here...hope it goes well. I'm currently working on
stored procedure where I translated some reporting language into T-SQL

The logic:

I have a group of tables containing important values for calculation.
I run various sum calculations on various fields in order to retrieve
cost calculations ...etc.

1) There is a select statement which gathers all the "records" which
need calculations.
ex: select distinct Office from Offices where OfficeDesignati on =
'WE' or OfficeDesignati on = 'BE...etc.
As a result I get a list of lets say 5 offices which need to be
calculated!

2) A calculation select statement is then run on a loop for each of
the returned 5 offices (@OfficeName cursor used here!) found above.An
example can be like this

(* note that @WriteOff is a variable storing the result):

"select @WriteOff = sum(linecost * (-1))
From Invtrans , Inventory
Where ( transtype in ('blah', 'blah' , 'blah' ) )
and ( storeloc = @OfficeName )
and ( Invtrans.lineco st <= 0 )
and ( Inventory.locat ion = Invtrans.storel oc )
and ( Inventory.itemn um = Invtrans.itemnu m )"...etc

This sample statement returns a value and is passed to the variable
@WriteOff (for each of the 5 offices mentioned in step 1). This is done
around 9 times for each loop! (9 calculations)

3) At the end of each loop (or each office), we do an insert statement
to a table in the database.

>>>>END of Logic<<

Problem:

This kind of dataset or report usually takes alot of time, and I need
to have the ability to storing all the calculated variables for each
"Office" in an "array" so that I can do ONE INSERT STATEMENT LOOP as
opposed to doing one insert statement at a time, in a loop.

Basically, a loop to calculate and save into an array, and then one
loop for insert statements.

I believe your ideal solution will look something like this:

insert into some_other_tabl e (storeloc, WriteOff)
select it.storeloc, -sum(linecost)
from Invtrans it
join Inventory i on it.itemnum = i.itemnum
and it.storeloc = i.location
join Offices o on it.storeloc = o.storeloc
where it.transtype in ('blah','blah', 'blah')
and it.linecost <= 0
and i.ItemStatus = 'Active'
and o.OfficeDesigna tion in ('WE','BE')

Oops, append the following:

group by it.storeloc

[Mohd Al Junaibi]

Hi David,

Thanks for the swift response. There are 3 primary tables (Inventory,
Companies, and Items) in the query.

Table Structures (I've simplified the tables greatly..they are more
complicated than the descriptions below):

CREATE TABLE [companies] (
[rowstamp] [timestamp] NOT NULL ,
[company] [varchar] (20) NOT NULL ,
[type] [varchar] (1) ,
[name] [varchar] (75) ,
[address1] [varchar] (40),
[address2] [varchar] (40) ,
[address3] [varchar] (40),
[address4] [varchar] (40)
[contact] [varchar] (50) ,
[phone] [varchar] (20) ,
[registration2] [varchar] (20) ,
[registration3] [varchar] (20) ,
) ON [PRIMARY]
CREATE TABLE [inventory] (
[rowstamp] [timestamp] NOT NULL ,
[itemnum] [varchar] (30)
[location] [varchar] (20) ,
[sstock] [decimal](15, 2) NULL ,
[sourcesysid] [varchar] (10) ,
[ownersysid] [varchar] (10) ,
[externalrefid] [varchar] (10) ,
[apiseq] [varchar] (50) ,
[interid] [varchar] (50) ,
[migchangeid] [varchar] (50) ,
[sendersysid] [varchar] (50)
) ON [PRIMARY]

CREATE TABLE [item] (
[rowstamp] [timestamp] NOT NULL ,
[itemnum] [varchar] (30) NOT NULL
[description] [varchar] (200) ,
[rotating] [varchar] (1) NOT NULL ,
[msdsnum] [varchar] (1) AS NULL ,
[outside] [varchar] (1) NOT NULL ,
[in14] [varchar] (12) NULL ,
) ON [PRIMARY]
The query used:

select distinct inventory.locat ion store
from item, inventory, companies cmp
where ( item.itemnum = inventory.itemn um )
and ( item.in9 <'I' or item.in9 is null )
and ( inventory.locat ion = cmp.company )
and inventory.locat ion not in('WHHQ')
and cmp.registratio n2 not in
('MAIN','DISPOS AL','SURPLUS',' PROJECT','COMPL ETED')
group by cmp.registratio n2, inventory.locat ion

This query returns the inventories I need to calculate on, and I place
a cursor based on the above query, and run through each calculation
with the cursor.

At the end of each calculation, an INSERT statement is done to one
table:

INSERT STATEMENT:

insert into invsum ( STORELOC, RECEIPTS, RETURNS, TRANSFERIN, WRITEON,
ISSUES, TRANSFEROUT, WRITEOFF, OPENBAL, CALCLOSEBAL, INVENTORYVAL,
OPENBALDATE, CLOSEBALDATE ) values(@StoreNa me2,
@StrReceipts,@S trReturns,@StrT ransfersIn,@Str WritesOn,@StrIs sues,@STrTransf ersOut,@StrWrit esOff,@LastClos e,@StrCalculate dBal,@StrMaxInv Val,@startDate, @endDate
)

Each @ variable from a particular calculation.

How can I optimize my cursor? I'm thinking of making a variable of the
above query with varchar (300)..and then running it into...ok...I'm
lost.

Thanks for the response anyways.

[Mohd Al Junaibi]

One error:

What I meant was at the end of EACH LOOP...an INSERT statement is run.

[Ed Murphy]

Mohd Al Junaibi wrote:

select distinct inventory.locat ion store
from item, inventory, companies cmp
where ( item.itemnum = inventory.itemn um )
and ( item.in9 <'I' or item.in9 is null )
and ( inventory.locat ion = cmp.company )
and inventory.locat ion not in('WHHQ')
and cmp.registratio n2 not in
('MAIN','DISPOS AL','SURPLUS',' PROJECT','COMPL ETED')
group by cmp.registratio n2, inventory.locat ion

This query returns the inventories I need to calculate on, and I place
a cursor based on the above query, and run through each calculation
with the cursor.

At the end of each calculation, an INSERT statement is done to one
table:

INSERT STATEMENT:

insert into invsum ( STORELOC, RECEIPTS, RETURNS, TRANSFERIN, WRITEON,
ISSUES, TRANSFEROUT, WRITEOFF, OPENBAL, CALCLOSEBAL, INVENTORYVAL,
OPENBALDATE, CLOSEBALDATE ) values(@StoreNa me2,
@StrReceipts,@S trReturns,@StrT ransfersIn,@Str WritesOn,@StrIs sues,@STrTransf ersOut,@StrWrit esOff,@LastClos e,@StrCalculate dBal,@StrMaxInv Val,@startDate, @endDate
)

Each @ variable from a particular calculation.

We need to see these calculations.

How can I optimize my cursor? I'm thinking of making a variable of the
above query with varchar (300)..and then running it into...ok...I'm
lost.

You want to /eliminate/ all cursors, if at all possible.

Possible approach:

// Populate the STORELOC column
insert into invsum (STORELOC)
select inventory.locat ion
from item
join inventory on item.itemnum = inventory.itemn um
join companies on inventory.locat ion = cmp.company
where not (item.in9 = 'I')
// alternative: where coalesce(item.i m9,'') <'I'
and inventory.locat ion <'WHHQ'
and cmp.registratio n2 not in
('MAIN','DISPOS AL','SURPLUS',' PROJECT','COMPL ETED')
group by inventory.locat ion

// Populate the first couple calculations
update invsum
set receipts = it_receipts, returns = it_returns
from invsum join (
select storeloc,
sum(case when amount 0 then amount end) receipts,
sum(case when amount < 0 then amount end) returns
from invtran
group by storeloc
) on invsum.storeloc = invtran.storelo c

This is more complex SQL than I usually have occasion to write,
so proofreading would be much appreciated.

[Mohd Al Junaibi]

Thanks Ed,

We need to see these calculations.

They are 9 calculations. Too large to post. So I'll post one of them
(in this case @StrReceipts):

select @StrReceipts = sum(LOADEDCOST) From Matrectrans A , item B
Where ( issuetype = 'RECEIPT' )
and ( Tostoreloc = @StoreName1 )
and ( issue = 'N' )
and (transdate @startDate)
and (transdate <= @endDate)
and ( A.itemnum = B.itemnum )
and ( B.in9 <'I' or B.in9 is null )
and ( A.gldebitacct not like '%249001' or A.gldebitacct is null )
and ( A.gldebitacct not like '%249002' or A.gldebitacct is null )
and ( A.glcreditacct not like '%249001' or A.glcreditacct is null )
and ( A.glcreditacct not like '%249002' or A.glcreditacct is null )

Possible approach:

// Populate the STORELOC column
insert into invsum (STORELOC)
select inventory.locat ion
from item
join inventory on item.itemnum = inventory.itemn um
join companies on inventory.locat ion = cmp.company
where not (item.in9 = 'I')
// alternative: where coalesce(item.i m9,'') <'I'
and inventory.locat ion <'WHHQ'
and cmp.registratio n2 not in
('MAIN','DISPOS AL','SURPLUS',' PROJECT','COMPL ETED')
group by inventory.locat ion

// Populate the first couple calculations
update invsum
set receipts = it_receipts, returns = it_returns
from invsum join (
select storeloc,
sum(case when amount 0 then amount end) receipts,
sum(case when amount < 0 then amount end) returns
from invtran
group by storeloc
) on invsum.storeloc = invtran.storelo c

Thanks for the code, I will try it out and update the query
accordingly. Your efforts are very much appreciated.

[Hugo Kornelis]

On 29 Nov 2006 01:39:15 -0800, Mohd Al Junaibi wrote:

>Thanks Ed,

>We need to see these calculations.

They are 9 calculations. Too large to post. So I'll post one of them
(in this case @StrReceipts):

(snip)

Hi Mohd,

The most straightforward conversion to a setbased solution would be to
replace each variable in the final INSERT statement with a subquery that
is easily adopted from these calculations, like this:

INSERT INTO invsum
(STORELOC, RECEIPTS, RETURNS, TRANSFERIN, WRITEON,
ISSUES, TRANSFEROUT, WRITEOFF, OPENBAL, CALCLOSEBAL,
INVENTORYVAL, OPENBALDATE, CLOSEBALDATE )
SELECT inv.Location, &#&#&#&#&#&# &#
-- Calculation for Receipts below
(SELECT sum(LOADEDCOST)
FROM Matrectrans A , item B
WHERE issuetype = 'RECEIPT'
AND Tostoreloc = inv.Location
AND issue = 'N'
AND transdate @startDate
AND transdate <= @endDate
AND A.itemnum = B.itemnum
AND ( B.in9 <'I'
OR B.in9 IS NULL )
AND ( A.gldebitacct NOT LIKE '%249001'
OR A.gldebitacct IS NULL )
AND ( A.gldebitacct NOT LIKE '%249002'
OR A.gldebitacct IS NULL )
AND ( A.glcreditacct NOT LIKE '%249001'
OR A.glcreditacct IS NULL )
AND ( A.glcreditacct NOT LIKE '%249002'
OR A.glcreditacct IS NULL ) )
-- Calculation for Returns below
(SELECT ....)
-- Calculation for TransferIn below
(SELECT ....)
(etc)
-- Calculation for CloseBalDate below
(SELECT ....)
FROM Item AS i, Inventory AS inv, Companies AS cmp
WHERE i.itemnum = inv.itemnum
AND ( i.in9 <'I' OR i.in9 IS NULL )
AND inv.location = cmp.company
AND inv.location <'WHHQ'
AND cmp.registratio n2 NOT IN
('MAIN','DISPOS AL','SURPLUS',' PROJECT','COMPL ETED')
GROUP BY inv.location;

However, this is only the start. You'll robably see some performance
gain, but not much. The real fun starts when you start comparing the
subqueries for the various calculations. Chances are that many have
elements in common - and in that case, you can get a tremendous
performance gain by moving the common elements from the subqueries to
the outer query.

Unfortunately, since you chose not to post the other calculations, I
can't offer any more specific advice that this.

--
Hugo Kornelis, SQL Server MVP