Temp Tables 1

[AccessSQLUser]

I need to create 2 temp tables based on data from 2 separate databases and then I need to compare the values in the 2 tables. Since the keyword USE cannot be used in a stored procedure, I place the "Use db1..Select ... Into .. From ... Where..." statement into a string and then execute that string. The problem is that after I execute the string, the temp table is no longer available. How do I ensure that the table will be available until after the procedure is over?

 

[Tim1]

Hiya,

The easiest way around your problem is to create the tables in a known database, as opposed to tempdb, and then reference that in the normal way eg.

SELECT *
INTO database..table_name
FROM database..table_name

That table will then exist until you specifically send a DROP TABLE command.

Tim

 

[JNC73]

Hi,

The way to get the data from different databases is to add the database name in the select statement. An example is

CREATE TABLE #Table1
AS (
Field1 INT,
field2 VAR(3)
)

INSERT INTO #TABLE1
SELECT x.field1, y.field2
FROM dbname..TABLE2 x, dbname..TABLE3 y
WHERE etc.

After this the temporary table is still active in the SP untill the SP is ended or the table is dropped in the SP.

Hope this helps,

JNC73

 

[tlbroadbent]

You can also create the temp tables prior to excuting the SQL string containing the USE and INSERT INTO statements. The temp tables would still exist after executing the SQL string.

However, if you create the tables first, you would then do Insert #tmp1 Select ... From rather than Insert Into #tmp1 From.

It would be easier and preferred to use the fully qualified table names as recommend by Tim1 and JNC73. Terry

http://members.home.net/tlbroadbent/index.htm