Inserting Multiple Rows into one table (with calculated fields)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mohd Al Junaibi - 29 Nov 2006 06:26 GMT

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 OfficeDesignation =
'WE' or OfficeDesignation = '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.linecost <= 0 )
            and ( Inventory.location = Invtrans.storeloc )
            and ( Inventory.itemnum = Invtrans.itemnum )"...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?

Reply to this Message

David Portas - 29 Nov 2006 07:08 GMT

> Hello all,
>
[quoted text clipped - 48 lines]
>
> 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/ms130214(en-US,SQL.90).aspx
--

Reply to this Message

David Portas - 29 Nov 2006 07:26 GMT

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.OfficeDesignation 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/ms130214(en-US,SQL.90).aspx
--

Reply to this Message

Mohd Al Junaibi - 29 Nov 2006 07:47 GMT

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.location store
from item, inventory, companies cmp
where ( item.itemnum = inventory.itemnum )
and ( item.in9 <> 'I' or item.in9 is null )
and ( inventory.location = cmp.company )
and inventory.location not in('WHHQ')
and cmp.registration2 not in
('MAIN','DISPOSAL','SURPLUS','PROJECT','COMPLETED')
group by cmp.registration2, inventory.location

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(@StoreName2,
@StrReceipts,@StrReturns,@StrTransfersIn,@StrWritesOn,@StrIssues,@STrTransfersOut,@StrWritesOff,@LastClose,@StrCalculatedBal,@StrMaxInvVal,@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.

Reply to this Message

Mohd Al Junaibi - 29 Nov 2006 07:58 GMT

One error:

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

Reply to this Message

Ed Murphy - 29 Nov 2006 09:12 GMT

> select distinct inventory.location store
> from item, inventory, companies cmp
[quoted text clipped - 22 lines]
>
> 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.location
from item
join inventory on item.itemnum = inventory.itemnum
join companies on inventory.location = cmp.company
where not (item.in9 = 'I')
// alternative: where coalesce(item.im9,'') <> 'I'
and inventory.location <> 'WHHQ'
and cmp.registration2 not in
('MAIN','DISPOSAL','SURPLUS','PROJECT','COMPLETED')
group by inventory.location

// 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.storeloc

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

Reply to this Message

Mohd Al Junaibi - 29 Nov 2006 09:39 GMT

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:
>
[quoted text clipped - 21 lines]
> group by storeloc
> ) on invsum.storeloc = invtran.storeloc

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

Reply to this Message

Hugo Kornelis - 30 Nov 2006 21:40 GMT

>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.registration2 NOT IN
('MAIN','DISPOSAL','SURPLUS','PROJECT','COMPLETED')
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.

Signature

Hugo Kornelis, SQL Server MVP

Reply to this Message

Ed Murphy - 29 Nov 2006 07:35 GMT

> my first post here...hope it goes well. I'm currently working on
> stored procedure where I translated some reporting language into T-SQL
[quoted text clipped - 44 lines]
> 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_table (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.OfficeDesignation in ('WE','BE')

Reply to this Message

Ed Murphy - 29 Nov 2006 07:36 GMT

>> my first post here...hope it goes well. I'm currently working on
>> stored procedure where I translated some reporting language into T-SQL
[quoted text clipped - 57 lines]
> and i.ItemStatus = 'Active'
> and o.OfficeDesignation in ('WE','BE')

Oops, append the following:

group by it.storeloc

Reply to this Message