How do I properly SELECT INTO @TempTable Multiple times to return a single resultset?

Question

I'm trying to write a stored procedure for Crystal Reports by combining multiple queries into a single resultset (Crystal doesn't support multiple results in one report).

The result set I'm trying to get combines columns from both tables.

In the SP, I declare @temptable and the columns (because the two tables i'm querying have different columns).

DECLARE @TEMPNEWBILLING TABLE
(
    ACCOUNT DECIMAL null,
    CLIENT NVARCHAR null,
    TIMESTAMP INT null,
    BILLING DECIMAL null, 
    CALLKIND INT null,
    HITK1 DECIMAL null,
    HITK2 DECIMAL null, 
    HIDISC DECIMAL null, 
    HITALK DECIMAL null, 
    HIPTCH DECIMAL null,
    HICONF DECIMAL null,
    HIHOLD DECIMAL null,
    PTCH DECIMAL null,
    SUPERTIME DECIMAL null
)

I then SELECT from both tables INTO the temp table:

SELECT Account, Client, Timestamp, Billing, CallKind, HiTk1, HiTk2, HiDisc, HiTalk, HiPtch,     HiConf, HiHold, Ptch
 INTO TEMPNEWBILLING 
 FROM   
        mCallEnd
 WHERE  billing = cast(@BILLINGNUMBER as decimal)
    AND Timestamp > @STARTITIME
    AND Timestamp < @ENDITIME
    AND CallKind  in (0,1,2,3,4,16)

SELECT 
     Billing, SuperTime
 INTO TEMPNEWBILLING 
FROM
        mClientMaint
 WHERE  billing = cast(@BILLINGNUMBER as decimal)
    AND Timestamp > @STARTITIME
    AND Timestamp < @ENDITIME

And finally, I just get all data from the temp table.

SELECT * FROM @TEMPNEWBILLING

Unfortunately, something is going wrong, as when I run the SP, I get an error that

There is already an object named 'TEMPNEWBILLING' in the database.

I've checked it out and it seems that the first query is running, but the error gets thrown at the second Select Into. I must be doing this wrong, as I get the same error if I use # tables or @ tables (i.e. delcare the table vs. create the table).

Is the prospect of filling a temp table with the results of two queries simply not possible? Am I using the wrong tool for the job?

Answer 1

SELECT... INTO creates a new table.

You'll want to reverse it:

INSERT INTO @TEMPNEWBILLING 
(Columns...)
SELECT (your select query here)

You'll want to declare the table (technically it's a table variable since you're using the @ sign) as you did. Then use INSERT INTO... SELECT... for all of your inserts.

Answer 2

In your code, you are not using the variable table youe defined, instead you are trying to put the results into the same physical table. Try this instead:

INSERT INTO @TEMPNEWBILLING(Account, Client, Timestamp, Billing, CallKind, HiTk1, HiTk2, HiDisc, HiTalk, HiPtch,     HiConf, HiHold, Ptch)
SELECT Account, Client, Timestamp, Billing, CallKind, HiTk1, HiTk2, HiDisc, HiTalk, HiPtch,     HiConf, HiHold, Ptch
 FROM   
        mCallEnd
 WHERE  billing = cast(@BILLINGNUMBER as decimal)
    AND Timestamp > @STARTITIME
    AND Timestamp < @ENDITIME
    AND CallKind  in (0,1,2,3,4,16)

INSERT INTO @TEMPNEWBILLING(Billing, SuperTime)
SELECT 
     Billing, SuperTime
FROM
        mClientMaint
 WHERE  billing = cast(@BILLINGNUMBER as decimal)
    AND Timestamp > @STARTITIME
    AND Timestamp < @ENDITIME

Answer 3

You need to use INSERT once the table is already created. Also, you're using a table variable, so you need to include the @ at the beginning of the name when referring to it. Since you're declaring the table variable at the start, both statements should actually be INSERT and not SELECT INTO.

SELECT INTO tries to create a new table. In your code you basically declare a table variable (which never gets used), then your first SELECT INTO creates a permanent table with the name TEMPNEWBILLING, then your second SELECT INTO tries to create a table with the same exact name - hence the error.