Problem INSERTING duplicate Rows

I'm trying to populate a temporary table with unique data, however my
populating query produces duplicated data. I've tried using a sub query
(i.e. querying the temp table during the INSERT to see if the data
already exists), but that doesn't stops the primary key violation
error. I've included the code below. tblSearchProduct stores a list of
words, along with the ID of the product title that word appears in. I'm
doing a search on products with certain words in the titles, so
therefore don't want the same product to appear multiple times. Any
suggestions? Ideally I want to tell SQL Server to just ignore the error
and move onto the next record in the result set, but I'm not sure how
to achieve that.
CREATE TABLE #tResults (
Position int NOT NULL identity(1,1),
[ID] varchar(50) NOT NULL,
PRIMARY KEY([ID])
)
INSERT INTO #tResults([ID])
SELECT s.[ID]
FROM
tblSearchProduct s
INNER JOIN tblContractProduct c
ON s.ID = c.ContractProductID
INNER JOIN tblProduct p
ON p.ProductID = c.ProductID
WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
AND [ID] NOT IN (SELECT [ID] FROM #tResults)
ORDER BY ProductNameand...@.thevalley.f9.co.uk wrote:
> I'm trying to populate a temporary table with unique data, however my
> populating query produces duplicated data. I've tried using a sub
query
> (i.e. querying the temp table during the INSERT to see if the data
> already exists), but that doesn't stops the primary key violation
> error. I've included the code below. tblSearchProduct stores a list
of
> words, along with the ID of the product title that word appears in.
I'm
> doing a search on products with certain words in the titles, so
> therefore don't want the same product to appear multiple times. Any
> suggestions? Ideally I want to tell SQL Server to just ignore the
error
> and move onto the next record in the result set, but I'm not sure how
> to achieve that.
> CREATE TABLE #tResults (
> Position int NOT NULL identity(1,1),
> [ID] varchar(50) NOT NULL,
> PRIMARY KEY([ID])
> )
> INSERT INTO #tResults([ID])
>
> SELECT s.[ID]
> FROM
> tblSearchProduct s
> INNER JOIN tblContractProduct c
> ON s.ID = c.ContractProductID
> INNER JOIN tblProduct p
> ON p.ProductID = c.ProductID
> WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
> AND [ID] NOT IN (SELECT [ID] FROM #tResults)
> ORDER BY ProductName
Try the following select stmt.

> SELECT distinct s.[ID]
> FROM
> tblSearchProduct s
> INNER JOIN tblContractProduct c
> ON s.ID = c.ContractProductID
> INNER JOIN tblProduct p
> ON p.ProductID = c.ProductID
> WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
> AND [ID] NOT IN (SELECT [ID] FROM #tResults)
> ORDER BY ProductName|||SELECT distinct s.[ID]
FROM
tblSearchProduct s
INNER JOIN tblContractProduct c
ON s.ID = c.ContractProductID
INNER JOIN tblProduct p
ON p.ProductID = c.ProductID
WHERE 'the' LIKE Word+'%' OR Word LIKE 'the%'
AND [ID] NOT IN (SELECT [ID] FROM #tResults)
ORDER BY ProductName|||for some reason the update query is not appearing, anyway try distinct
after select, like
select distinct s.[ID]|||INSERT INTO #tResults([ID])
SELECT DISTINCT S.[id]
FROM ... etc
You don't need ORDER BY. It does nothing useful for you.
David Portas
SQL Server MVP
--|||This is not a RDBMS design at all, but a sequential file system in a
bad disguise. Stop using the "universal magical" VARCHAR(50) when you
know it is oversized. Stopping putting silly redundant "t_" and "tbl_"
prefixes on table names in violation of ISO-11179. Stop using IDENTITY.
Why did you put an ORDER BY on an INSERT INTO statement? Tables are
not sequential files; they have no ordering.
CREATE TABLE #Results
(product_id CHAR(13) NOT NULL PRIMARY KEY); -- I'll guess you use EAN
INSERT INTO #Results(product_id)
SELECT DISTINCT S.product_id
FROM SearchProducts AS S,
ContractProducts AS C,
Products AS P
WHERE P.word LIKE '% the %'
AND P.product_id = C.product_id
AND S.product_id = C.product_id;
But now I have to ask, why are SearchProducts, ContractProducts and
mere plain old Products logically different? I'll bet they are all the
same kind of entities, but you just put them into different tables
based on some status. This how we did it with punch cards and tape
files. Likewise, why is the same data element called ID, product_id
and contract_product_id in the same schema?
I think you need some serious help with database design.