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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy