insert into temp table

Question

insert into temp table

Viewing 15 posts - 31 through 45 (of 52 total)

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

matt32 SSC Veteran Points: 264 More actions · Answer 1

Lisa Phillip (6/18/2010)
I chose both tables... I believe that is ALSO a correct answer!
Think about it... where in this question did it say that col1 in the first table didn't have nulls?
I want my points!!!

hey Lisa

😀 this was the trap i want you not run into with your maybe code ,,, so losing one point

will you make remember this case 😉

.. never ever use select .. into to create a table or temptable.

kind regards Matthias

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 2

Great question. Confirmed what I already knew. Point collected.

Converting oxygen into carbon dioxide, since 1955.

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

Lisa Phillip (6/18/2010)
I chose both tables... I believe that is ALSO a correct answer!
Think about it... where in this question did it say that col1 in the first table didn't have nulls?
I want my points!!!

For this question, it is completely irrelevant if col1 in the first table has nulls. It is in fact even irrelevant if there is a col1 column in that table at all.

The first SELECT uses "SELECT 1 AS col1, ...". Check the higlighted bit - the Col1 does not come from the first table, but is an alias for the expression "1". And that expression can and will never be NULL.

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/

vk-kirov SSCertifiable Points: 7686 More actions · Answer 4

Thanks to the author! That was a good lesson about the SELECT INTO statement.

VM-723206 SSCrazy Points: 2964 More actions · Answer 5

VM-723206

SSCrazy

Points: 2964

June 21, 2010 at 2:15 am

#1182573

Thanks! Good question.

forjonathanwilson SSC Enthusiast Points: 139 More actions · Answer 6

In 2008 creating temp tables on the fly has been severely restricted. creating more than a list of id's (or any other single column with first ordinal position) in this manner will generate an error. If anyone knows different please let me know. We just had to do a code wide change removing all "select into" temporary table generation left over from 6 years ago...

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

Nice question - thanks.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

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

forjonathanwilson (6/21/2010)
In 2008 creating temp tables on the fly has been severely restricted. creating more than a list of id's (or any other single column with first ordinal position) in this manner will generate an error. If anyone knows different please let me know. We just had to do a code wide change removing all "select into" temporary table generation left over from 6 years ago...

This is the first time I hear about this. Could you provide some more information? (preferably a code example)

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/

Lisa Phillip SSCrazy Points: 2991 More actions · Answer 9

If the column 1 in the table HAD a null value for one of the rows, how could the resulting table POSSIBLY end up being defined as NOT NULL?

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

Lisa Phillip (6/21/2010)
If the column 1 in the table HAD a null value for one of the rows, how could the resulting table POSSIBLY end up being defined as NOT NULL?

Because the first column in the result table is NOT created from the first column of the first table, but from the constant expression "1" (with AS Col1 added to give it a column name).

There is a difference between

SELECT 1 AS Col1, Col2 FROM SomeTable;

and

SELECT Col1, Col2 FROM SomeTable;

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/

Lisa Phillip SSCrazy Points: 2991 More actions · Answer 11

Lisa Phillip

SSCrazy

Points: 2991

June 21, 2010 at 11:36 am

#1182893

AHHHHH... yes! Why did I miss that?!

Thanks!

UMG Developer SSChampion Points: 13482 More actions · Answer 12

UMG Developer

SSChampion

Points: 13482

June 21, 2010 at 12:13 pm

#1182908

Nice question, thanks!

Kangana Beri SSCrazy Points: 2184 More actions · Answer 13

Kangana Beri

SSCrazy

Points: 2184

June 21, 2010 at 12:44 pm

#1182926

Good question, I learned something new. Thanks!

gagan623 Grasshopper Points: 20 More actions · Answer 14

Can you beifly Explain this with an example I didn't the reason ?

gagan623 Grasshopper Points: 20 More actions · Answer 15

I totally agree with the Example. I have scenrio where I am creating Temo table using Select into #temp and later on using same table to insert data using Insert into Select from table.