How to insert into tempoary table twice

I've picked up some SQL similar to the following:

IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name Like N'#tmp%'
 and id=object_id('tempdb..#tmp'))
DROP TABLE #tmp

into #tmp
select * from permTable

I need to a开发者_如何学运维dd more data to #tmp before continuing processing:

insert into #tmp
select * from permTable2

But this gives errors because SQL has assumed sizes and types for #tmp columns (e.g. if permTable has a column full of ints but permTable2 has column with same name but with a NULL in one record you get "Cannot insert the value NULL into column 'IsPremium', table 'tempdb.dbo.#tmp").

How do I get #tmp to have the types I want? Is this really bad practise?

---

Have you considered creating a table var instead? You can declare the columns like such

declare @sometable table(
     SomeField [nvarchar](15),
     SomeOtherField [decimal](15,2));

---

This is why select into is a poor idea for your problem. Create the table structure specifically with a create table command and then write two insert statements.

---

It isn't possible.

If you need to generate the table definition typelist now, create a view from the select statement, and read the columns and their definition from information_schema... (this work of art won't consider decimal and/or datetime2)

Note: this will give you the lowest possible field-length for varchar/varbinary columns you currently selected.
You need to adjust them manually...

SELECT 
      ','
    + COLUMN_NAME 
    + ' ' 
    + DATA_TYPE
    + ' '
    + ISNULL
    (
          '(' 
          + 
          CASE 
            WHEN CHARACTER_MAXIMUM_LENGTH = -1 
                THEN 'MAX' 
            ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(36)) 
            END 
        + ')' 
        , '' 
    ) 
    + ' '
    + CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT NULL' ELSE '' END 
FROM information_schema.columns 
WHERE table_name = '________theF'
ORDER BY ORDINAL_POSITION

And the field-list for the insert-statement:

SELECT 
      ',' + COLUMN_NAME 
FROM information_schema.columns 
WHERE table_name = '________theF'
ORDER BY ORDINAL_POSITION