add extra columns in a SELECT INTO statement

Question

I am doing a SELECT INTO statement to create a new table from some values in another table. I want to add two extra columns onto the newly created table (pol_eff_dt, pol_exp_dt) and make them all NULL initially (these columns also exist in the source table so they would otherwise pull that data). I also need to make sure they come over as DATETIME types. How can I cast them this way? I've tried a lot of things, but nothing will compile.

SELECT DISTINCT bnd_ser_nbr,
trans_nbr,
uws_product_cd,
bill_id,
NULL AS pol_eff_dt, 
NULL AS pol_exp_dt
INTO ER_RO_urs_prem_detail
FROM urs_prem_detail_interim

You can ALTER resulting table and add the columns after INSERT — Yuriy Galanter, Jun 19 '14 at 16:57

score 13 · Accepted Answer · answered Jun 19 '14 at 17:00

You can try casting the NULL explicitly as DATETIME, like so:

SELECT DISTINCT bnd_ser_nbr,
trans_nbr,
uws_product_cd,
bill_id,
CAST(NULL AS DATETIME) AS pol_eff_dt, 
CAST(NULL AS DATETIME) pol_exp_dt
INTO ER_RO_urs_prem_detail
FROM urs_prem_detail_interim

Demo here. In the demo, if you change the type that the 2 extra columns are cast to, and then try to assign a DATETIME value, you will get a conversion error.

add extra columns in a SELECT INTO statement

1 Answers1