Select Distinct and Insert into existing table.

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 2008 Forums

Transact-SQL (2008)

Select Distinct and Insert into existing table.

Author

Topic

kory76
Starting Member

2 Posts

Posted - 2011-04-24 : 16:10:29

I have written this, which I was hoping would work, but doesn't.
The goal is to select records, which I have duplicate product_id's for multiple records. I'd like to select the distinct records and take each row and insert into the product table, which product_id is primary.

Any ideas?

Code is below. Thanks so much.

INSERT INTO dfac1_jo151.jos_vm_product (Artist,product_name,product_id,product_height,product_width,ProductType,FileName,`ListPrice in USD`,`OurPrice in USD`,Rank,product_full_image,ProductLink,product_desc,ProductHeight_cm,ProductWidth_cm)
SELECT Distinct product_id,Artist,product_name,product_height,product_width,ProductType,FileName,`ListPrice in USD`,`OurPrice in USD`,Rank,product_full_image,ProductLink,product_desc,ProductHeight_cm,ProductWidth_cm
FROM NewProductInformation

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-24 : 16:36:25

selec t*
from (
SELECT Distinct product_id,Artist,product_name,product_height,product_width,ProductType,FileName,`ListPrice in USD`,`OurPrice in USD`,Rank,product_full_image,ProductLink,product_desc,ProductHeight_cm,ProductWidth_cm
,seq = row_number() over(partition by product_id order by ...)
FROM NewProductInformation
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-25 : 06:09:13

quote:
Originally posted by nigelrivett

select * from (
SELECT Distinct product_id,Artist,product_name,product_height,product_width,ProductType,FileName,`ListPrice in USD`,`OurPrice in USD`,Rank,product_full_image,ProductLink,product_desc,ProductHeight_cm,ProductWidth_cm
,seq = row_number() over(partition by product_id order by ...)
FROM NewProductInformation
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

--------------------------
http://connectsql.blogspot.com/

kory76
Starting Member

2 Posts

Posted - 2011-04-25 : 08:38:52

I ran this:

select * from (SELECT Distinct product_id,Artist,product_name,product_height,product_width,ProductType,FileName,`ListPrice in USD`,`OurPrice in USD`,Rank,product_full_image,ProductLink,product_desc,ProductHeight_cm,ProductWidth_cm,seq = row_number() over(partition by product_id order by ...)
FROM NewProductInformation) a
where seq = 1

Got this error:

row_number() over(partition by product_id order by ...)
FROM NewProductInformation) a
where seq = 1

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by product_id order by ...)
FROM NewProductInformation) a
where seq' at line 1

Any insight?

Thanks.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-25 : 08:40:20

quote:
Originally posted by kory76

row_number() over(partition by product_id order by ...)
FROM NewProductInformation) a
where seq = 1

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(partition by product_id order by ...)
FROM NewProductInformation) a
where seq' at line 1

Any insight?

Thanks.

---------------------
in SQL Server
---------------------
row_number() over(partition by product_id order by ...)
"..." means column/columns on which you need sorting
e.g. row_number() over (partition by product_id order by product_height,product_width,ProductType)
-----

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-25 : 08:49:51

Kory,
This is a Microsoft SQL Server forum. You may be better served at a MySQL forum. www.dbforums.com is a good place to start.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources