SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


insert into temp table


insert into temp table

Author
Message
David McKinney
David McKinney
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2857 Visits: 2090
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
matt32
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 172
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 :-P
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
Hugo Kornelis
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19201 Visits: 12426
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Jonas.SQL
Jonas.SQL
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 59
I got it wrong as well, even though I "cheated" and tried it out before answering Smile I didn't have rows in myanothertable and then of course the not null constraint didn't trigger.

Thanks for enlightning me.
ma-516002
ma-516002
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2601 Visits: 321
Oops Smile Your right... it's way too early in the morning...
matt32
matt32
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 172
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
Koen Verbeeck
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64938 Visits: 13298
Good question.
Lose one point but learn a useful fact. A good trade-off :-D


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
sharath.chalamgari
sharath.chalamgari
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2028 Visits: 798
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
Toreador
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3878 Visits: 8129
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
dave.farmer
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1341 Visits: 795
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.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search