Problem with inserting duplicate values in SQL table

[Shweta]

Hi All,

I have create a table using the below syntax:

create table tblProductSales (ProductID int, QuantitySold numeric)

I have not defined any column as Primary key.

Below are the values I am inserting in the above table.

insert into tblProductSales
select '1','10' union
select '3','23' union
select '4','21' union
[COLOR=#ff0000]select '2','12' union[/COLOR]
select '1','13' union
select '3','12' union
select '4','13' union
select '1','11' union
[COLOR=#ff0000]select '2','12' union[/COLOR]
select '1','14'

Highlighted two rows have duplicate entries for both the columns. While executing it, it inserts only 9 rows in the table instead of 10 and ignoring the one complete row from the highlighted ones.

Second issue is, it is storing the data in a sorted manner by ProductId. However, I don't want that. There is no cluster index then why it is doing so. Please suggest.

Thanks in advance!!

Regards,
Shweta Jain

 

[mole999]

The order the data is stored should be irrelevant, as your queries are where you extract at in the order you want

as for the unallowed duplicate, are there any settings on the table that prevent duplicates ?

 

[Ombir]

Union operator remove the duplicates. Use Union all instead of Union.

For sorting problem you can also use this:

Create table tblProductSales (ProductID int, QuantitySold numeric)
Insert into tblProductSales
values
    ('1','10'),
    ('3','23'),
    ('4','21'),
    ('2','12'),
    ('1','13'),
    ('3','12'),
    ('4','13'),
    ('1','11'),
    ('2','12'),
    ('1','14')

 

[Shweta]

Thank you so much Ombir!

Your solution works perfectly fine for me. Sorting problem also got resolved by using union all.