473,320 Members | 1,950 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Inserting Multiple Rows into one table (with calculated fields)


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?

Nov 29 '06 #1
9 4984
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 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?
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
--

Nov 29 '06 #2
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/m...S,SQL.90).aspx
--

Nov 29 '06 #3
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 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.
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')
Nov 29 '06 #4
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 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.

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')
Oops, append the following:

group by it.storeloc
Nov 29 '06 #5

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,@StrWrite sOn,@StrIssues,@STrTransfersOut,@StrWritesOff,@Las tClose,@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.

Nov 29 '06 #6
One error:

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

Nov 29 '06 #7
Mohd Al Junaibi wrote:
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,@StrWrite sOn,@StrIssues,@STrTransfersOut,@StrWritesOff,@Las tClose,@StrCalculatedBal,@StrMaxInvVal,@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.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.
Nov 29 '06 #8
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.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
Thanks for the code, I will try it out and update the query
accordingly. Your efforts are very much appreciated.

Nov 29 '06 #9
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.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.

--
Hugo Kornelis, SQL Server MVP
Nov 30 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Srinadh | last post by:
Hi all, We have files with about 20 to 30 fields per row. We are trying to update such files with about 60 rows as contiguous data in a CLOB field. It passes through. But when we try...
3
by: Steven Stewart | last post by:
Hi there, I have posted about this before, but yet to arrive at a solution. I am going to try to explain this better. I have an Employees table and a Datarecords table (one-to-many...
0
by: MHenry | last post by:
Hi, I know virtually nothing about creating Macros in Access. I would appreciate some help in creating a Macro or Macros that automatically run(s) 14 Queries (three Make Table Queries, and 11...
2
by: Will | last post by:
I have a table, tblManinstructions with fields Code & InstructionID, one Code can have many InstructionID. I also have tblinstructions (fields instructionID & instruction). What I want to do is...
8
by: Stefan Mueller | last post by:
I'm really very confused. With the following code I can add rows/fields in frame 1 and 2. If I use IE, Mozilla or Opera the new rows/fields get added in ascending order. However, if I use Safari...
18
by: TORQUE | last post by:
Hi, Im wondering if anyone can help me with a problem. I have a form with more than 50 unbound fields. Some of the fields will be blank from time to time. This seems to be where im having...
52
by: MP | last post by:
Hi trying to begin to learn database using vb6, ado/adox, mdb format, sql (not using access...just mdb format via ado) i need to group the values of multiple fields - get their possible...
92
by: bonneylake | last post by:
Hey Everyone, Well i was hoping someone could explain the best way i could go about this. i have a few ideas on how i could go about this but i am just not sure if it would work. Right now i...
482
by: bonneylake | last post by:
Hey Everyone, Well i am not sure if this is more of a coldfusion problem or a javscript problem. So if i asked my question in the wrong section let me know an all move it to the correct place. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.