insert into temp table

Question

Post reply

insert into temp table

matt32

SSC Veteran

Points: 264
More actions
June 17, 2010 at 9:55 pm

#236615

Comments posted to this topic are about the item insert into temp table

Viewing 15 posts - 1 through 15 (of 52 total)

You must be logged in to reply to this topic. Login to reply

antony-688446 Ten Centuries Points: 1221 More actions · Answer 1

antony-688446

Ten Centuries

Points: 1221

June 17, 2010 at 10:17 pm

#1181903

Nice question! Thanks.

davoscollective SSCertifiable Points: 6405 More actions · Answer 2

davoscollective

SSCertifiable

Points: 6405

June 17, 2010 at 10:56 pm

#1181910

Thanks,

got it wrong and learning something 🙂

ziangij SSCrazy Eights Points: 8066 More actions · Answer 3

ziangij

SSCrazy Eights

Points: 8066

June 17, 2010 at 11:09 pm

#1181912

good question... thanks 🙂

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 4

got it wrong and i thought that, 1 as something in select into statement by default it will allows.but learned that it will not allows null.I worked more on select ..into but still got wrong.

good question.learned one point.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 5

Good Question.. i learn something new...:-)

Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

ma-516002 Hall of Fame Points: 3359 More actions · Answer 6

declare @myothertab table (

col2 varchar(20),

col3 varchar(20)

)

insert into @myothertab

select 'A','B' union all

SELECT 'W','Q'

;

declare @myanothertab table (

col2 varchar(20),

col3 varchar(20)

)

insert into @myothertab

select 'A','B' union all

SELECT 'W','Q';

select 1 as col1, col2, col3 into #mytmp

from @myothertab

insert into #mytmp

select null,col2,col3

from @myanothertab

select * from #mytmp

returns 4 rows on SS2008 contrary to your explanation. why?

matt32 SSC Veteran Points: 264 More actions · Answer 7

Hi,

the point is the creation of a table column with a constant. - eg 1 or 'A' -

in this case a not null constraint is added to this column when select .. into is used.

on msdn nor the books online i've found why this happened.

learned that by getting errors 😛

kind regards Matthias

David McKinney SSChampion Points: 10472 More actions · Answer 8

ma-516002 (6/17/2010)
declare @myothertab table (
col2 varchar(20),
col3 varchar(20)
)
insert into @myothertab
select 'A','B' union all
SELECT 'W','Q'
;
declare @myanothertab table (
col2 varchar(20),
col3 varchar(20)
)
insert into @myothertab
select 'A','B' union all
SELECT 'W','Q';
select 1 as col1, col2, col3 into #mytmp
from @myothertab
insert into #mytmp
select null,col2,col3
from @myanothertab
select * from #mytmp
returns 4 rows on SS2008 contrary to your explanation. why?

There's an error in your sql above. You're inserting twice into the same table (@myothertab) and not at all into @myanothertab.

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 9

i tried with "ma-516002" query in the 2005 version the results are same i am able to get the records from both the tables. when i tried something simila with my local database tables i am getting records from only one table.

in both the cases it is creating table with the not null constraint

looks Weird.....

David McKinney SSChampion Points: 10472 More actions · Answer 10

sharath.chalamgari (6/18/2010)
i tried with "ma-516002" query in the 2005 version the results are same i am able to get the records from both the tables. when i tried something simila with my local database tables i am getting records from only one table.
in both the cases it is creating table with the not null constraint
looks Weird.....

use the corrected code below, and see what you get.

if object_id('tempdb.dbo.#mytmp') is not null

drop table #mytmp

go

declare @myothertab table (

col2 varchar(20),

col3 varchar(20)

)

insert into @myothertab

select 'A','B' union all

SELECT 'W','Q'

;

declare @myanothertab table (

col2 varchar(20),

col3 varchar(20)

)

insert into @myanothertab

select 'A2','B2' union all

SELECT 'W2','Q2';

select 1 as col1, col2, col3 into #mytmp

from @myothertab

insert into #mytmp

select null,col2,col3

from @myanothertab

select * from #mytmp

matt32 SSC Veteran Points: 264 More actions · Answer 11

Old Hand was a attentive reader and found the bug :w00t: in Ten Centuries code as follows.

i must say a don't read the cod ereally because i got this constraint violation error many times in the past 😛

declare @myothertab table (

col2 varchar(20),

col3 varchar(20)

)

insert into @myothertab

select 'A','B' union all

SELECT 'W','Q'

;

declare @myanothertab table (

col2 varchar(20),

col3 varchar(20)

)

insert into @myanothertab /* < should be*/

select 'A','B' union all

SELECT 'W','Q';

select 1 as col1, col2, col3 into #mytmp

from @myothertab

insert into #mytmp

select null,col2,col3

from @myanothertab

select * from #mytmp

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 12

Nice question!

I believe that far too many people use select into without really understanding what it does. This is not documented very explicitly; in ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/cc9bdf10-edfc-41a5-adf1-aa9715ed8d71.htm (link for SQL 2005 BOL), it says:

"The structure of the new table is defined by the attributes of the expressions in the select list."

This means that:

1. Datatypes of the columns will be inferred from the expressions in the SELECT list. If it's just a column reference, the new table wil get the same data type. For an expression, the data type of the result expression will be used. And that includes constants. The constant 1 will be considered int; 'a' will be varchar(1) (yes, you read it right - varchar, not char), and 2.5 will be numeric(2,1).

2. Nullability of the columns will also be inferred from the expressions. If the expression can (theoretically) return null, the column will be nullable, otherwise it won't. Constant expressions can never return null, nor can a simple reference to a column withh a NOT NULL constraint. Due to some weird implementation details, COALESCE is always nullable; ISNULL is nullable if and only if the second argument is nullable. That makes SELECT INTO one of the two cases where I use ISNULL instead of the more standard and more versatile COALESCE if I want to make a column NOT NULL.

3. No other constraints are ever generated. No PRIMARY KEY, no FOREIGN KEY, no UNIQUE, no DEFAULT - nothing. Because of that limitation, I really recommend anyone to use SELECT INTO only for temporary tables, and even in that case using CREATE TABLE followed by INSERT SELECT is often the better option.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Jonas.SQL SSC Veteran Points: 263 More actions · Answer 13

I got it wrong as well, even though I "cheated" and tried it out before answering 🙂 I didn't have rows in myanothertable and then of course the not null constraint didn't trigger.

Thanks for enlightning me.

ma-516002 Hall of Fame Points: 3359 More actions · Answer 14

Oops 🙂 Your right... it's way too early in the morning...