Alter ADD tempDB inside SELECT?

Question

How can I alter tempdb inside a select?

I want a single query is that possible?

SELECT cust_ac_no, ord_no, ref_no, net_svc_id, job_type, ord_status, ord_status_date, ord_crt_date
INTO tempdb..xtiankwiksetreport
ALTER table tempdb..xtiankwiksetreport
ADD serial_no varchar(25) null,
    msisdn varchar(25) null,
    imsi varchar(25) null,
    bts_id varchar(25) null
FROM wo_order
WHERE job_type IN ('EXTR', 'EXTC')
AND svc_type='4G'
AND ref_no=2
AND ord_status IN ('PL', 'JL')

I am pretty confident saying "no" in every DBMS. But you could use a `store procedure` to execute a "script" using one `exec ` — AxelH, Nov 25 '19 at 07:12

score 0 · Answer 1 · edited Jun 20 '20 at 09:12

I have started to test this in Sybase before you added the sql-server tag so I will provide both, the first one explain the idea, the second will prove this is possible in both DBMS.

Note that the solution is working but I would not use this as a long term solution. I didn't research on the possibilities of this to be a documented behavior, and could fail in the future (or already, based on my database version)

Sybase

Just use a convert function on a null value to add the empty columns.

select 'a' as foo, 
    convert(varchar(25), null) as serial_no
    into #temp

Then to test it, I insert a long value :

insert into #temp values ('b', 'abcdefghijklmnopqrstuvwxyz')

And the result :

select * from #temp

foo,serial_no
'a',
'b','abcdefghijklmnopqrstuvwxy'

The 26th character is missing, we have a varchar(25)

SQL-Server

The same code will provide an error due to the truncated value

select 'a' as foo, convert(varchar(25), null) as serial_no
    into temp_foo

insert into temp_foo values ('b', 'abcdefghijklmnopqrstuvwxyz')
insert into temp_foo values ('c', 'abcdefghijklmnopqrstuvwxy')

select * from temp_foo

The line with b provide a value to long and gives me :

Msg 8152, Level 16, State 14, Line 4
String or binary data would be truncated.
The statement has been terminated.

But the line with c with 25 character fits.

foo,serial_no
'a',
'c','abcdefghijklmnopqrstuvwxy'

SELECT 
    cust_ac_no, ord_no, ref_no, net_svc_id, job_type, ord_status, ord_status_date, ord_crt_date,
    convert(varchar(25), null) as serial_no,
    convert(varchar(25), null) as msisdn,
    convert(varchar(25), null) as imsi,
    convert(varchar(25), null) as bts_id,
INTO tempdb..xtiankwiksetreport
FROM wo_order
WHERE job_type IN ('EXTR', 'EXTC')
AND svc_type='4G'
AND ref_no=2
AND ord_status IN ('PL', 'JL')

score 0 · Answer 2 · edited Nov 25 '19 at 07:48

0

SELECT cust_ac_no, ord_no, ref_no, net_svc_id, job_type, ord_status, ord_status_date, ord_crt_date,
serial_no=convert(varchar(25), null), msisdn-convert(varchar(25), null), imsi=convert(varchar(25), null), bts_id=convert(varchar(25), null)
INTO  tempdb..xtiankwiksetreport
FROM wo_order
WHERE job_type IN ('EXTR', 'EXTC')
AND svc_type='4G'
AND ref_no=2
AND ord_status IN ('JL', 'PL')

edited Nov 25 '19 at 07:48

Dale K

25,246
15
42
71

answered Nov 25 '19 at 07:32

Drin

21
4

This doesn't "alter" anything, though. It creates a new table, or gives an error if the table already exists. Maybe your question is misleading. It is not possible to alter a database or table's metadata (as in, add new columns) with a `SELECT .. INTO`. – Jeroen Mostert Nov 25 '19 at 10:06

Alter ADD tempDB inside SELECT?

2 Answers2

Sybase

SQL-Server