getting data from two tables - 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

getting data from two tables

Author

Topic

Yellowdog
Starting Member

34 Posts

Posted - 2008-12-24 : 12:13:20

I have a set of two tables that I need to get data from and I want to pull it from both tables and insert into one, but there is a lot of duplicate data in the tables.

I am working with raw data and need to standardize the colors and update accordingly.

I am just wondering how I can pull the data and insert into another table without getting duplicates.

for instance I know I can do someting like

select distinct color
from downloaded_sales_data
where color like '%red%'

and then

select distinct color
from downloaded_sales_data
where color like '%red%'

logic tells me that a query like

select
distinct downloaded_sales_data.color as sales, downloaded_service_data.color as service
from downloaded_service_data, downloaded_sales_data
where downloaded_sales_data.color like '%red%' and downloaded_service_data.color like '%red%'

should work, but the smart people here can obviously see that it does not.

So my question is, how can I get data from two tables of raw data to be displayed into one column with all of the rows containing unique data?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 13:10:37

Can you give sample data and expected output so we can help?

Yellowdog
Starting Member

34 Posts

Posted - 2008-12-24 : 13:48:23

Sorry if I was not really clear.

So to make it simple there are two tables containing multiple columns, one of which is color. That is each of the two tables has a column named color.

I am looking to get the unique data from each of the tables and insert that data into a new table. I am not worried about the insert as I am sure that I can figure that much out. I need some help with the select statement.

select distinct color from downloaded_sales_data

will give me the info I need from one table

select distinct color from downloaded_service_data

will give me the data I need from the other.

How can I take the data from both of those querys and get them into one table with all uniqe data. In other words if the sales data and service data both have the same color as part of its own unique data, how do I get just on "copy" (for lack of a better term) into the new table.

Wow that may be more confusing than my original post....

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 13:57:00

I think you need this:

Select * into Newtable
from
(Select color from Download_sales_date
union
Select Color from Download_service_data)t

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-24 : 15:58:47

Perhaps also this.

select color into NEWTABLE from TABLE1
insert into NEWTABLE select color from TABLE2

TABLE1 and TABLE2 must be structurally the same.. ie..same columns in both.

You may also need to enable identity insert for the insert part of the query.

SET IDENTITY_INSERT NEWTABLE ON
insert into NEWTABLE select color from TABLE2
SET IDENTITY_INSERT NEWTABLE OFF

r&r

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 16:01:03

quote:
Originally posted by revdnrdy

Perhaps also this.

select * into NEWTABLE from TABLE1
insert into NEWTABLE select * from TABLE2

TABLE1 and TABLE2 must be structurally the same.. ie..same columns in both.

r&r

But OP needs Distinct records on New table.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-24 : 16:06:32

in that case just add one more select..

select color into NEWTABLE from TABLE1
insert into NEWTABLE select color from TABLE2
select distinct color from NEWTABLE

both ways work I think. One may execute faster than the other but I have not tested. My way I think still needs the identity insert turned on.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-24 : 16:09:20

There is no need for identity Enable/Disable over here. You are putting distinct records in newtable which doesn't have identity enabled.

timothymoses
Starting Member

2 Posts

Posted - 2008-12-26 : 00:13:51

wow its great to know good information about this...

www.ipodenergy.com/

Yellowdog
Starting Member

34 Posts

Posted - 2008-12-29 : 10:54:31

quote:
Originally posted by sodeep

I think you need this:

Select * into Newtable
from
(Select color from Download_sales_date
union
Select Color from Download_service_data)t

this ^

thanks so much.

I added distinct in the union so my final looks like

select * into Newtable 
from 
(select distinct color from downloaded_sales_data
union
select distinct color from downloaded_service_data)t

Thanks again for all your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-29 : 11:57:58

quote:
Originally posted by Yellowdog

quote:
Originally posted by sodeep

I think you need this:

Select * into Newtable
from
(Select color from Download_sales_date
union
Select Color from Download_service_data)t

this ^

thanks so much.

I added distinct in the union so my final looks like

select * into Newtable
from
(select distinct color from downloaded_sales_data
union
select distinct color from downloaded_service_data)t

Thanks again for all your help

you dont need distincts there as union already returns only distinct of values.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources