SELECT INTO with a twist - 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

SQL Server 2000 Forums

SQL Server Development (2000)

SELECT INTO with a twist

Author

Topic

baja_yu
Starting Member

24 Posts

Posted - 2010-10-13 : 17:13:05

Hey people. I've been banging my head trying to do something. Here's the setup. Let's say I have two tables, Table1 and Table2 with columns: ID (primary ID, autoincrementing), Name, Number. (and some other stuff that's not important)

I have a set of data in both, mostly identical, but in Table2 the data in column Number changes for some items.

Now, I have a third table with columns ID (primID, autoincr), PersonID, NewNumber.

What I'd like is a query that will analyize Table2, and for each entry where Number from Table2 is different to Number in table1 (with same IDs), copy the ID of that item to PersonID and Number to NewNumber in the third (log) table.

It it doable with one query? I've been trying with SELECT INTO but with not much luck.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-13 : 17:29:51

insert table3 (PersonId,NewNumber)
select
t2.ID,
t2.Number
from table2 as t2
join table1 as t1
on t1.ID = t2.ID and t1.Number <> t2.Number

No, you're never too old to Yak'n'Roll if you're too young to die.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-13 : 17:30:08

Does this get you what you want?

select ...
from table1 t1
join table2 t2
on t1.ID = t2.ID
where t1.Number <> t2.Number

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

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-13 : 17:31:38

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

Subscribe to my blog

baja_yu
Starting Member

24 Posts

Posted - 2010-10-13 : 17:49:11

Thanks for the replies.

Both suggestions worked the same, but did not return the set of results I expected. Table1 has about 120K and Table2 133K records. The difference is in about 9K records. Both suggestions returned exactly 155.

EDIT: The SELECT from returns 155 items but the join doubled all the columns. Since table1 and table2 have identical structure the JOIN returned each column twice, one original and one with _1 sufix.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-13 : 17:51:46

You'll need to show us a data example then as our solutions should work based upon your original post.

Our solutions are actually equivalent. The location of t1.Number <> t2.Number only matters for an outer join.

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

Subscribe to my blog

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-13 : 17:53:50

Then you must have a select that shows you the expected rows otherwise you can't say that our result is wrong.
So take that select and use it for your insert.

No, you're never too old to Yak'n'Roll if you're too young to die.

baja_yu
Starting Member

24 Posts

Posted - 2010-10-13 : 17:57:31

Here's a sample data set

Table1 (ID - Name - Number)

1 - Bob - 1
2 - Mike - 1
3 - Emma - NULL
4 - John - 4
5 - Ed - 4
6 - Sean - NULL

Table 2
1 - Bob - 1
2 - Mike - 1
3 - Emma - 4
4 - John - 4
5 - Ed - 1

Changes were to items with ID 3 and 5 in table 2. So resulting table3 should be

Table3 (ID - PersonID - NewNumber)
1 - 3 - 4
2 - 5 - 1
(ID is autogenerated here, PersonID and NewNumber are from Table2)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-13 : 18:03:23

You didn't tell us about the nulls.

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

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-13 : 18:07:04

I added in an extra row into Table2 in case the Number column there can contain nulls too. A null is not equal to anything even another null, but I suspect in your situation it is.


create table #Table1 (ID tinyint, Name varchar(10), Number tinyint)
create table #Table2 (ID tinyint, Name varchar(10), Number tinyint)
create table #Table3 (ID tinyint identity(1, 1), PersonID tinyint, NewNumber tinyint)

insert into #Table1 values (1, 'Bob', 1)
insert into #Table1 values (2, 'Mike', 1)
insert into #Table1 values (3, 'Emma', null)
insert into #Table1 values (4, 'John', 4)
insert into #Table1 values (5, 'Ed', 4)
insert into #Table1 values (6, 'Sean', null)


insert into #Table2 values (1, 'Bob', 1)
insert into #Table2 values (2, 'Mike', 1)
insert into #Table2 values (3, 'Emma', 4)
insert into #Table2 values (4, 'John', 4)
insert into #Table2 values (5, 'Ed', 1)
insert into #Table2 values (6, 'Sean', null)

--select * from #Table1
--select * from #Table2

insert into #Table3 (PersonID, NewNumber)
select t1.ID, t2.Number
from #table1 t1
join #table2 t2
on t1.ID = t2.ID
where t1.Number <> t2.Number or (t2.Number IS NOT NULL AND t1.Number IS NULL)

select * from #Table3

drop table #Table1, #Table2, #Table3

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

Subscribe to my blog

baja_yu
Starting Member

24 Posts

Posted - 2010-10-13 : 18:12:55

Right on the money. Most differences in Table2 compared to Table1 are the NULL values in Table1. So the previous return of 155 was correct but did not include those. I expected to compare NULL to a number and get a positive result. I'll keep that in mind from now on.
This works like a charm. Thanks a million once again :)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-10-13 : 18:14:10

You're welcome, glad to help.

If you ever need to switch this to an outer join, move my where clause into the ON portion of the JOIN like in webfred's solution. It makes a big difference.

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

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources