Posted by Mohd Al Junaibi on 11/29/06 07:47

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.

• Next in forum: Re: Continuously querying a datastream using SQL Server 2005
• Prev in forum: Re: Continuously querying a datastream using SQL Server 2005
• Thread view: Re: Inserting Multiple Rows into one table (with calculated fields)

[Reply to this message]