MS SQL Server - Inserting Multiple Rows into one table (with calculated fields)

Author

Inserting Multiple Rows into one table (with calculated fields)

Mohd Al Junaibi

2006-11-29, 7:12 pm

Hello all,

I'm currently working on stored procedure where I translated some
reporting language into T-SQL and currently facing a problem.

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.

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 =3D inventory.itemnum )
and ( item.in9 <> 'I' or item.in9 is null )
and ( inventory.location =3D 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,@StrRet
urns,@StrTransfersIn
,@StrWritesOn,@StrIs
sues,@STrTransf=
=ADersOut,@StrWrites
Off,@LastClose,@StrC
alculatedBal,@StrMax
InvVal,@startDa=
te,=AD@endDate

)

TYPICAL CALCULATION (there's 8 others!):

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

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. I want to ensure faster optimization of this procedure...any
ideas?

Tracy McKibben

2006-11-29, 7:12 pm

Mohd Al Junaibi wrote:
> Hello all,
>
>
> I'm currently working on stored procedure where I translated some
> reporting language into T-SQL and currently facing a problem.
>
> 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.
>
> 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,@StrRet
urns,@StrTransfersIn
,@StrWritesOn,@StrIs
sues,@STrTransf_ersO
ut,@StrWritesOff,@La
stClose,@StrCalculat
edBal,@StrMaxInvVal,
@startDate,_@endDate

>
> )
>
>
> TYPICAL CALCULATION (there's 8 others!):
>
> 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 )
>
>
> 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. I want to ensure faster optimization of this procedure...any
> ideas?
>

Is the WHERE clause the same for all nine calculations? If so, can you
calculate them ALL in one query? If you can do that, then you can
eliminate the cursor altogether and do this with a single query:

INSERT INTO TableB
(ID, Sum1, Sum2, Sum3)
SELECT
ID,
SUM(Col1) AS Sum1,
SUM(Col2) AS Sum2,
SUM(Col3) AS Sum3
FROM TableA
WHERE conditions

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Sponsored Links