access thread: select after insert in Access
Message #1 by "Alan Grobert" <alangrobert@h...> on Fri, 11 May 2001 12:05:00 -0400
|
|
The code listing below stops after process the second INSERT INTO.
The only way I've been able to complete this code is to put the
last SELECT statement into a separate sproc which is not the way
it should work.
Thanks
Alan
Alter Procedure sp_cusiphist
as
DELETE tcusiphist
CREATE TABLE #tpchist(tpcusip nvarchar(10) null,
tQty float null)
INSERT INTO #tpchist
SELECT DISTINCT cusip, quantity
FROM seelaus
WHERE substring(acctnumber,6,1)='9' AND tradeside = 'B' AND
Seelaus.InputDate = CONVERT(datetime,(CONVERT(varchar,GetDate(),101)))
INSERT INTO tcusiphist
SELECT clients.name1, Clients.Name2, seelaus.ae, Seelaus.Cusip,
Seelaus.TradeSide, Seelaus.Quantity,
Seelaus.TradeDate, Seelaus.Settlement, Seelaus.SecyDesc, Seelaus.Price,
Seelaus.Secytypecode, Seelaus.ContraLedger, Seelaus.ContraAcct,
#tpchist.tQty, Seelaus.Acctnumber
FROM #tpchist JOIN (Clients RIGHT JOIN seelaus ON Clients.AcctNumber =
Seelaus.AcctNumber) ON tpcusip = cusip
WHERE seelaus.tradedate < GetDate()-4 AND tradeside = 'B' AND
substring(seelaus.acctnumber,6,2) <> '91'
SELECT tCusipHist.Name1, tCusipHist.AE, tCusipHist.cusip,
tCusipHist.quantity, tCusipHist.tradeside, tCusipHist.tradedate,
tCusipHist.settlement, tCusipHist.Secydesc, tCusipHist.Price,
tTrdgAccts.Address1
FROM tTrdgAccts JOIN tCusipHist ON tTrdgAccts.Ledger =
tCusipHist.ContraLedger AND tTrdgAccts.Acctnumber = tCusipHist.ContraAcct
return
Message #2 by "Pardee, Roy E" <roy.e.pardee@l...> on Fri, 11 May 2001 12:52:51 -0700
|
|
I *think* you're asking more of a T-SQL question than an Access
question--you might get better (or any!) advice on a different message
list...
Cheers,
-Roy
-----Original Message-----
From: Alan Grobert [mailto:alangrobert@h...]
Sent: Friday, May 11, 2001 9:05 AM
To: Access
Subject: [access] select after insert in Access
The code listing below stops after process the second INSERT INTO.
The only way I've been able to complete this code is to put the
last SELECT statement into a separate sproc which is not the way
it should work.
Thanks
Alan
Alter Procedure sp_cusiphist
as
DELETE tcusiphist
CREATE TABLE #tpchist(tpcusip nvarchar(10) null,
tQty float null)
INSERT INTO #tpchist
SELECT DISTINCT cusip, quantity
FROM seelaus
WHERE substring(acctnumber,6,1)='9' AND tradeside = 'B' AND
Seelaus.InputDate = CONVERT(datetime,(CONVERT(varchar,GetDate(),101)))
INSERT INTO tcusiphist
SELECT clients.name1, Clients.Name2, seelaus.ae, Seelaus.Cusip,
Seelaus.TradeSide, Seelaus.Quantity,
Seelaus.TradeDate, Seelaus.Settlement, Seelaus.SecyDesc, Seelaus.Price,
Seelaus.Secytypecode, Seelaus.ContraLedger, Seelaus.ContraAcct,
#tpchist.tQty, Seelaus.Acctnumber
FROM #tpchist JOIN (Clients RIGHT JOIN seelaus ON Clients.AcctNumber =
Seelaus.AcctNumber) ON tpcusip = cusip
WHERE seelaus.tradedate < GetDate()-4 AND tradeside = 'B' AND
substring(seelaus.acctnumber,6,2) <> '91'
SELECT tCusipHist.Name1, tCusipHist.AE, tCusipHist.cusip,
tCusipHist.quantity, tCusipHist.tradeside, tCusipHist.tradedate,
tCusipHist.settlement, tCusipHist.Secydesc, tCusipHist.Price,
tTrdgAccts.Address1
FROM tTrdgAccts JOIN tCusipHist ON tTrdgAccts.Ledger =
tCusipHist.ContraLedger AND tTrdgAccts.Acctnumber = tCusipHist.ContraAcct
return
Message #3 by "Wade Weal" <wadeweal@h...> on Sat, 12 May 2001 08:36:09 -0400
|
|
This one threw me a bit, but if I understand it correctly you should try 'SELECT INTO tablename', as
opposed to 'INSERT INTO . . .', followed by a SELECT. INSERT INTO is probably looking for a 'VALUES . . .' clause, while SELECT INTO
will create the table structure and load the tables in one shot.
SELECT INTO #tpchist cusip, quantity FROM seelaus WHERE substring(acctnumber,6,1)='9' AND tradeside = 'B' AND Seelaus.InputDate =
CONVERT(datetime,(CONVERT(varchar,GetDate(),101)))
Hope it helps.
>From: "Alan Grobert"
>Reply-To: "Access"
>To: "Access"
>Subject: [access] select after insert in Access
>Date: Fri, 11 May 2001 12:05:00 -0400
>
>The code listing below stops after process the second INSERT INTO.
>The only way I've been able to complete this code is to put the
>last SELECT statement into a separate sproc which is not the way
>it should work.
>Thanks
>
>Alan
>
>
>Alter Procedure sp_cusiphist
>as
>DELETE tcusiphist
>CREATE TABLE #tpchist(tpcusip nvarchar(10) null,
> tQty float null)
>INSERT INTO #tpchist
>SELECT DISTINCT cusip, quantity
>FROM seelaus
>WHERE substring(acctnumber,6,1)='9' AND tradeside = 'B' AND
>Seelaus.InputDate =
>CONVERT(datetime,(CONVERT(varchar,GetDate(),101)))
>
>INSERT INTO tcusiphist
>SELECT clients.name1, Clients.Name2, seelaus.ae, Seelaus.Cusip,
>Seelaus.TradeSide, Seelaus.Quantity,
>Seelaus.TradeDate, Seelaus.Settlement, Seelaus.SecyDesc,
>Seelaus.Price,
>Seelaus.Secytypecode, Seelaus.ContraLedger, Seelaus.ContraAcct,
>#tpchist.tQty, Seelaus.Acctnumber
>FROM #tpchist JOIN (Clients RIGHT JOIN seelaus ON Clients.AcctNumber
>=
>Seelaus.AcctNumber) ON tpcusip = cusip
>WHERE seelaus.tradedate < GetDate()-4 AND tradeside = 'B' AND
>substring(seelaus.acctnumber,6,2) <> '91'
>
>SELECT tCusipHist.Name1, tCusipHist.AE, tCusipHist.cusip,
>tCusipHist.quantity, tCusipHist.tradeside, tCusipHist.tradedate,
>tCusipHist.settlement, tCusipHist.Secydesc, tCusipHist.Price,
>tTrdgAccts.Address1
>FROM tTrdgAccts JOIN tCusipHist ON tTrdgAccts.Ledger =
>tCusipHist.ContraLedger AND tTrdgAccts.Acctnumber =
>tCusipHist.ContraAcct
>return
|
View Cart
