Select into-like statement for an existing table ?? 1

[Ruairi]

I have created a table in my database to report on errors during an invoicing operation. I am using multiple joins and CASE statements to build a table with all the information needed to print an invoice. The resulting temp table has a column named VALUE that is set based on many different prices in the database and inputs from a client app. If a price is found that matches a record in the temp table the value of that record is calculated and inserted into the VALUE column. All is well so far, now the problem. If no price can be found for a record there are different error messages to be printed at the client. (I put a specific value, for example -914, into the VALUE column to indicate a given error)

Ex:
* No Length/Diameter price for customer CUSTOMERNAME, brand BRANDNAME, sort SORTNAME, destination DESTNAME, ....

* No Species/Grade price for species SPECIESNAME, grade GRADENAME, customer CUSTOMERNAME

I created a table for this purpose with a varchar field ERRORSTRING to print a report on. I want to achieve this kind of functionality (this querey may not be exactly right, i'm just showing generally what i want to do. I know how to do this with a select into)

select 'ERRORSTRING'= 'No Length/Diameter price for customer ' + ##INVTABLE.custname + ', brand ' + ##INVTABLE.brandname + ', sort ' + ##INVTABLE.sortname + ', destination ' + ##INVTABLE.destname

into INVOICEERRORS
from ##INVTABLE
where ##INVTABLE.VALUE = -914

but i need to get all errors into one table instead of creating a new temp table each time i check for different errors. I could use a 'update INVOICEERRORS set ERRORSTRING = where ' type operation, but this does not allow new inserts, does it? Any ideas?
TIA
Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[tlbroadbent]

Just use an INSERT statement.

Insert Table1 (col1, col2, col3, ..., colN)
Select col1, col2, col3, ..., colN
From Table2

Or

Insert INVOICEERRORS
Select 'No Length/Diameter price for customer ' +
##INVTABLE.custname + ', brand ' +
##INVTABLE.brandname + ', sort ' +
##INVTABLE.sortname + ', destination ' +
##INVTABLE.destname
From ##INVTABLE
Where ##INVTABLE.VALUE = -914 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Ruairi]

OK, thanks terry. I have never used a select statement in an isert that way, didn't think to try it. Thanks again
Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com