Writing a record within a select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Writing a record within a select statement

Author

Topic

rjackman1959
Yak Posting Veteran

60 Posts

Posted - 2009-01-09 : 09:55:13

Does anyone know how I can write a record to another table after doing a select statement? I am trying to keep tract of records sent by searching a table for the record, selecting it if not found then adding the order number to another table. Can this be done from the same select table that I am doing to generate the report?

declare @totalchg varchar(7)
set @totalchg = '0000000'
declare @accchg varchar(5)
set @accchg = '00000'
select
CONVERT(CHAR(8),h.IVH_printdate,112),
h.ivh_invoicenumber,
' ',
h.ivh_ref_number,
h.ivh_totalvolume,
REPLACE(STR(10000 * h.ivh_rate, 8, 0), ' ', '0'),
d.ivd_refnum,
' ',
CONVERT(CHAR(8),h.IVH_deliverydate,112),
(SELECT REPLACE(STR(100 * d2.ivd_charge , 8, 0), ' ', '0')--d2.ivd_charge
from detail as d2
where d.ivh_hdrnumber = d2.ivh_hdrnumber and d2.ivd_reftype <> 'BL#'),
REPLACE(STR(100 * h.ivh_totalcharge, 12, 0), ' ', '0')
from header as h inner join detail as d on h.ivh_hdrnumber = d.ivh_hdrnumber
where NOT EXISTS (SELECT sent_rec FROM orders_sent WHERE sent_rec=h.ivh_invoicenumber)

‘*******I would like to write the order number to orders_sent at this point******

order by h.IVH_printdate, h.ivh_invoicenumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 09:57:30

nope you cant missed assignment and select columns in same select statement, if it is what you mean.
If not, please elaborate on what you mean by write a record to another table after doing a select statement

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-09 : 10:20:27

I may not understand you right but you can INSERT using a SELECT statement as the value.
If a single row returned by SELECT:

INSERT INTO newTable1
(textString,testSelect)

--additional info here eg timestamp etc
Select 'INSERT test',
--YOUR SELECT statement returning one row here:
(Select top 1 testmoney from TableB
where id =1)

Select * from newTable1

If your select returns more than 1 row you may need to create a temporary table - with a SELECT INTO statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-09 : 11:29:48

quote:
Originally posted by darkdusky

I may not understand you right but you can INSERT using a SELECT statement as the value.
If a single row returned by SELECT:

INSERT INTO newTable1
(textString,testSelect)

--additional info here eg timestamp etc
Select 'INSERT test',
--YOUR SELECT statement returning one row here:
(Select top 1 testmoney from TableB
where id =1)

Select * from newTable1

If your select returns more than 1 row you may need to create a temporary table - with a SELECT INTO statement.

if it returns more values, then you should be joining onto query and taking result

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources