June 7, 2002 at 7:03 am
I am creating a temp table I then want to populate this table with the results from a select statement. Not sure how to write the code for this. Here is what I have to create the table and then perform the SELECT statement.
USE MedInsightData
go
if exists
(select name from sysobjects where name = 'TempRX_NDC')
drop table dbo.TempRX_NDC
go
create table dbo.TempRX_NDC
(
rx_ndc varchar(15),
rx_strength varchar(15)
)
go
SELECT RX_NDC, Rx_Strength FROM SERVICES
WHERE RX_NDC IS NOT NULL AND Rx_NDC <> ' '
GROUP BY RX_NDC, RX_Strength
INSERT INTO TempRX_NDC
(rx_ndc, rx_strength)
VALUES ()
June 7, 2002 at 7:11 am
INSERT TempRX_NDC(rx_NDC,rx_strength)
SELECT RX_NDC, Rx_Strength FROM SERVICES
WHERE RX_NDC IS NOT NULL AND Rx_NDC <> ' '
GROUP BY RX_NDC, RX_Strength
However, if you are using SQL2000 and this temporary table is only going to be used in this procedure, I strongly recommend using a variable of table type, as this is not physically created, and therefore saves on this "write to cache/disk" overhead.
So your code would become:
DECLARE @temprx_ndc table(rx_ndc varchar(15),rx_strength varchar(15))
INSERT @TempRX_NDC(rx_NDC,rx_strength)
SELECT RX_NDC, Rx_Strength FROM SERVICES
WHERE RX_NDC IS NOT NULL AND Rx_NDC <> ' '
GROUP BY RX_NDC, RX_Strength
then use the table variable as you would the ordinary table.
Regards
Simon
June 7, 2002 at 11:06 am
You can also skip the table creation steps and select directly into the table:
Select <myfields> Into ##NewTable From <myTable> etc.
This does create a temporary table to file. It's structure is defined by the fields referenced in the query.
June 7, 2002 at 7:46 pm
Personally avoid the SELECT INTO method as it will issue a lock on the original table and will hold it until you drop the temporary table blocking other transactions. This is a known problem and the work around is to build the temp table.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 11, 2002 at 5:30 am
This has worked for me, if you must keep this TempRX_NDC table otherwise using #RX_NDC as a temp table is better if you don't wanna keep the temp table.
INSERT INTO TempRX_NDC
SELECT RX_NDC, Rx_Strength FROM SERVICES
WHERE RX_NDC IS NOT NULL AND Rx_NDC <> ' '
GROUP BY RX_NDC, RX_Strength
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply