Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

insert into temp table Expand / Collapse
Author
Message
Posted Friday, June 18, 2010 12:59 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, April 17, 2014 6:56 AM
Points: 641, Visits: 1,794
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

Post #939386
Posted Friday, June 18, 2010 1:09 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 5:32 AM
Points: 61, Visits: 171
Old Hand was a attentive reader and found the bug 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
Post #939389
Posted Friday, June 18, 2010 1:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:24 AM
Points: 5,794, Visits: 8,009
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
Post #939392
Posted Friday, June 18, 2010 1:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, December 30, 2011 6:21 AM
Points: 21, Visits: 59
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.
Post #939394
Posted Friday, June 18, 2010 1:50 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 03, 2014 12:21 AM
Points: 2,111, Visits: 306
Oops :) Your right... it's way too early in the morning...
Post #939401
Posted Friday, June 18, 2010 1:52 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 5:32 AM
Points: 61, Visits: 171
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
Post #939403
Posted Friday, June 18, 2010 1:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:22 AM
Points: 12,206, Visits: 9,170
Good question.
Lose one point but learn a useful fact. A good trade-off




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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #939406
Posted Friday, June 18, 2010 2:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:33 AM
Points: 1,128, Visits: 756
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
Post #939420
Posted Friday, June 18, 2010 2:41 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:26 AM
Points: 1,658, Visits: 6,002
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.
Post #939428
Posted Friday, June 18, 2010 4:06 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, March 28, 2014 3:57 AM
Points: 867, Visits: 641
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.
Post #939451
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse