Using select into with except - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using select into with except

Author

Topic

kopfgeldjagar
Starting Member

14 Posts

Posted - 2009-10-07 : 15:20:14

Is it possible to use select into with an except statment? for example, I am trying to find the differences between two tables using:

(select stand, asset, dateon into tempchanges from newlist
except
select stand, asset, dateon from oldlist)

This works without the "into tempchanges" statment, but when I try to put my results into a temp table so I can run a date query against it, i only get nulls...
Any ideas?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-07 : 16:18:39

Perhaps try this:

select *
into tempchanges
from (
select stand, asset, dateon
from newlist
except
select stand, asset, dateon
from oldlist) t

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-10-07 : 16:21:14

Your code (as is) works for me:


declare @newlist table (stand int, asset int, dateon datetime)
declare @oldlist table (stand int, asset int, dateon datetime)

insert @newList values (1,1,getdate())
insert @newList values (2,2,getdate())

insert @oldList values (2,2,getdate())

(select stand, asset, dateon into tempchanges from @newlist
except
select stand, asset, dateon from @oldlist)

print 'select * from tempchanges'
select * from tempchanges

drop table tempchanges

output:
select * from tempchanges
stand       asset       dateon
----------- ----------- -----------------------
1           1           2009-10-07 16:19:22.570

Perhaps you can post some sample data which illustrates your problem...

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources