SQL: Temp tables and script failing on second use, but works on the first-CodePudding

I have a script that makes use of temp tables. When I disconnect from the server, connect, and run the script, it works as expected. If I run the same script twice, it works the first time, and then on the second time, complains that;

Column name or number of supplied values does not match table definition.

I believe I am dropping the table explicitly below. I also have at the start of the script a handful of extra drop table commands for the temp tables as good measure. How can I ensure the temp tables are removed so the procedure is repeatable?

-- create table of differences
drop table if exists #TMP;
select A.*,'table A' TABLE_NAME INTO #TMP from  #compare1 a
full outer join #compare2 b on a.hash = b.hash
where ( a.hash is null or b.hash is null );

INSERT INTO #TMP -- FAILS ON THIS LINE; only one the second try!
select B.*,'table B' from  #compare1 a
full outer join #compare2 b on a.hash = b.hash
where ( a.hash is null or b.hash is null );

CodePudding user response：

Drop the table at the end of the script not at the beginning.

If you must drop it at the beginning put a GO after the drop table if exists so it runs in its own batch before trying to compile the rest of the script.

Also consider using a more specific name than #TMP - it looks like there must be another instance of that table name created in a parent scope that is visible when compiling the batch and causing the error. (or you are altering the schema of the table in code you have not shown us)

CodePudding user response：

I normally do the following if I want to run it multiple times in the query window of SQL Server management Studio. This will then always drop the temp table if it exists before running the second one.

IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects O WHERE O.xtype in ('U') AND O.id = object_id(N'tempdb..#MyTempTable'))
BEGIN
    PRINT 'Removing temp table #MyTempTable'
    DROP TABLE #MyTempTable;
END

CREATE TABLE #MyTempTable(
    MyId BIGINT, 
    SomeOtherId BIGINT      
)

CodePudding user response：

Based on the script what I could see is that in the first Query You are using Compare1.* and the 2nd one it's Compare2.*. So looks like both tables are having a different structure. So My suggestion is to give the exact column names and map it to the actual destination than just putting *.

something like this Should Help

IF OBJECT_ID('Tempdb..#TMP;') IS NOT NULL
    DROP TABLE #TMP;
select 
    Destination1 = A.Column1,
    Destination2 = A.Column2,
    Destination3 = 'table A'
    INTO #TMP 
    from  #compare1 a
        full outer join #compare2 b 
            on a.hash = b.hash
    where ( a.hash is null or b.hash is null );

INSERT INTO #TMP
(
    Destination1,
    Destination2,
    Destination3
)
select 
    B.Column1,
    B.Column2,
    'table B' 
    from  #compare1 a
        full outer join #compare2 b 
            on a.hash = b.hash
    where ( a.hash is null or b.hash is null );