insert into temp table

Question

Post reply

insert into temp table

Viewing 15 posts - 16 through 30 (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

thx to Hugo for the great explanation ! 🙂

I recommand to create also temptables with create table so it's possible to get indexes and pkeys ..

but this was dicussed here many times so far ...

I got this error from some dynamic sql unfrequently and it was hard to find ... learned my lesson the hard way

kind regards Matthias

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Good question.

Lose one point but learn a useful fact. A good trade-off 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

David McKinney (6/18/2010)
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

Thanks for the correction,i missed it

Toreador SSChampion Points: 11368 More actions · Answer 4

Hugo Kornelis (6/18/2010)
even in that case using CREATE TABLE followed by INSERT SELECT is often the better option.

I always do an explicit create - it's more code, but it means it's clear what's happening, and there's no scope for errors due to failing to notice what the default data type will be.

dave.farmer SSCrazy Points: 2431 More actions · Answer 5

I tried to replicate this (in SQL Server 2008), and I didn't get a unique constraint violation, I got

"An explicit value for the identity column in table '#fred' can only be specified when a column list is used and IDENTITY_INSERT is ON."... so my temp table was created with an identity column (despite the fact that all 10 rows in it had the same value from the select into!).

My (anonymised - I did use real integer ID and text value tables) code. Did I miss something?:

SELECT 1 AS col1, st.IDColumn, st.TextColumn

INTO #fred

FROM SomeTable st

INSERT INTO #fred

SELECT NULL, sot.IDColumn, sot.TextColumn

FROM SomeOtherTable sot

and the results:

(10 row(s) affected)

Msg 8101, Level 16, State 1, Line 5

An explicit value for the identity column in table '#fred' can only be specified when a column list is used and IDENTITY_INSERT is ON.

David McKinney SSChampion Points: 10472 More actions · Answer 6

My guess is that it's the second field in the temp table which is created as an identity column - as the table from which it was created had the identity column. (SomeTable.IDColumn.)

dave.farmer SSCrazy Points: 2431 More actions · Answer 7

dave.farmer (6/18/2010)
I tried to replicate this (in SQL Server 2008), and I didn't get a unique constraint violation, I got
"An explicit value for the identity column in table '#fred' can only be specified when a column list is used and IDENTITY_INSERT is ON."... so my temp table was created with an identity column (despite the fact that all 10 rows in it had the same value from the select into!).

Ah, I've found my error... the id column in the first table is an identity, so SQL Server has created that second column of my temp table as an identity column!

The things you discover on this site...!

EDIT: ...and clearly David got there first 😎

John McC Hall of Fame Points: 3410 More actions · Answer 8

Good question and good explanation by Hugo.

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

prashant.bhatt SSC-Addicted Points: 476 More actions · Answer 9

Very nice question.

Basic and something good to learn

Prashant Bhatt
Sr Engineer - Application Programming

Rose Bud Hall of Fame Points: 3147 More actions · Answer 10

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

Nice catch!

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Another good straight forward question. Thanks!!!

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Bradley Deem SSCrazy Points: 2565 More actions · Answer 12

Good question, I learned something too. For those looking to reproduce the error run the following.

select 1 as col1, col2, col3 into #mytmp

from (select 1 col1,2 col2,3 col3) myothertab

insert into #mytmp

select null,col2,col3

from (select 4 col1,5 col2,6 col3) myanothertab

magarity kerns SSCrazy Points: 2763 More actions · Answer 13

I selected the correct answer because I thought 'myothertab' was from another tab, as in a different query tab. The question seemed to be about whether a temp table in one session was available to another session. With only one # in front of the table names, they weren't global and thus accessible only in their home tab and so the second answer had to be it. It took me a while to reverse engineer the given explaination to figure out what was really being asked.

KevinC. SSCommitted Points: 1570 More actions · Answer 14

Good question. I also learned something new.

And thanks, as always, to Hugo for the great explanation.

🙂

Lisa Phillip SSCrazy Points: 2991 More actions · Answer 15

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!!!