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 Thursday, June 17, 2010 9:55 PM
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
Comments posted to this topic are about the item insert into temp table
Post #939335
Posted Thursday, June 17, 2010 10:17 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:53 PM
Points: 262, Visits: 300
Nice question! Thanks.
Post #939341
Posted Thursday, June 17, 2010 10:56 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 3:03 AM
Points: 450, Visits: 842
Thanks,
got it wrong and learning something :)
Post #939348
Posted Thursday, June 17, 2010 11:09 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 1,971, Visits: 369
good question... thanks


Post #939352
Posted Thursday, June 17, 2010 11:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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)
Post #939358
Posted Thursday, June 17, 2010 11:20 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Good Question.. i learn something new...



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #939359
Posted Thursday, June 17, 2010 11:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, March 3, 2014 12:21 AM
Points: 2,122, Visits: 306
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?
Post #939373
Posted Friday, June 18, 2010 12:20 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
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
Post #939379
Posted Friday, June 18, 2010 12:36 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 8:03 AM
Points: 645, Visits: 1,846
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.
Post #939382
Posted Friday, June 18, 2010 12:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:49 AM
Points: 1,179, Visits: 783
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.....

Post #939385
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse