Select Non-Duplicate rows

[SpaceMonkey4969]

Hello

Ok here's my question, I sure hope you guys can help me.

I'm writting a query to Select the data from 1 table (TblData) and Insert it into 2 separate tables (tblSep1 & tblSep2).

The 1st table that I'm going to insert into contains the Main types of Units, The second table will contain all the different Quantity's and Prices for that Unit.

EXAMPLE:
TblData -- Desc Size Qty Price SKU UPC
Oil 6 pack 30ml 9.99 1122 2222
Oil 12 Pack 60ml 14.99 2211 3333
Oil 24 Pack 90 ml 20.99 3322 4444
fuel 6 pack 60ml 10.00 4445 5555

I would insert into tblSep1 (which is the Main Type of unit) the Desc, Qty(min)
Desc Qty
OIL 30ml
fuel 60ml
This table would have an ID for this type of Item lets say the id will be 1234 for OIl and 5678 for fuel

And Insert into tblSep2
ID size qty price

1234 6 pack 30ml 9.99
1234 12 pack 60ml 14.99
1234 24 pack 90ml 20.99
5678 6 pack 60ml 10.00

I need this query to do this all without any user input, Just press RUN and have it do the correct amout of inserts for each table.

If there is only one qty for a certain ITEM, then only do one insert(like the fuel item), but if there is more then 1 quantity (OIL) then do more then insert into tblSep2.


HUGE! thank you in advance

 

[ClaireHCM]

Try this,If you want to insert.All you have to do is first create two table tblSep1. and tblSep2. then insert into tblSep1 and tblSep2 by the select statement below~~

create table tbl(Des varchar(10),Size varchar(10),Qty varchar(10),Price decimal(5,2),SKU int,UPC int)
go
insert into tbl select 'Oil','6 pack','30ml',9.99,1122,2222
insert into tbl select 'Oil','12 Pack','60ml',14.99,2211,3333
insert into tbl select 'Oil','24 Pack','90ml',20.99,3322,4444
insert into tbl select 'fuel','6 pack','60ml',10.00,4445,5555
go
select des,min(qty) from tbl group by des
go
select (case des when 'oil' then 1234 when 'fuel' then 5678 end) as ID,
size,qty,price
from tbl group by des,qty,size,price order by id
go

 

[SpaceMonkey4969]

Opps sorry, should of gave more information. the main table of data has hundreds of rows. So i need some sort of insert/select that can identify when there is Multiple descriptions, and when there is, only insert the 1st record into tblSep1, and insert all the different Quantity's into tblSep2 (tblSep1 is for all the different products we carry, tblSep2 is for all the different Qty/sizes of that product we carry)

 

[ClaireHCM]

But I did understand the same way,what is the difference from the desired output and the output so far?

Sorry I need some more explanation...

 

[plantj]

Are you trying to populate a normalised 2 table schema from an unnormalised single table that has repeating information? Your new tables should be something more like the following I think.

create table tblProduct(
id int
,description varchar(20)
,minqty int
)

create table tblPrice(
idProduct int
,qty int
,size varchar(20)
,price smallmoney
)

Then populate by

insert into tblProduct(
description
,minqty
) select
description
,min(qty)
from
tblData

How you set the product ids and populate tblPrice depends on your circumstances: There are a few options. Does the product id mean something in the real world?