September 10, 2008 at 9:05 am
As part of a data import process I am importing a tab delimited tab file into a temporary table using this:
CREATE TABLE #Import (
item varchar(42),
description varchar(100),
price varchar(100),
discount varchar(100),
buy varchar(100)
)
DECLARE @SQL varchar(2000)
SET @SQL = 'BULK INSERT #Import FROM ''' +@filename+ ''' WITH (FIRSTROW = 2, FIELDTERMINATOR = ''\t'')'
EXEC (@SQL)
Before I insert the data I clean it up. I then check it for duplicates using this:
SELECT item AS DuplicateItem
FROM (SELECT DISTINCT item, description FROM #Import) AS dImport
GROUP BY item
HAVING COUNT(item) > 1
This just produces the list of duplicates items with different descriptions, which is fine, but I want to output this only if there are duplicates, if there are not, I want the data to be inserted. How can I use my code to create a conditional insert? This is going in a stored procedure.
I have tried this:
IF (SELECT item AS DuplicateItem
FROM (SELECT DISTINCT item, description FROM #Import) AS dImport
GROUP BY item
HAVING COUNT(item) > 1) < 1
BEGIN
INSERT...
END
But this fails, as I need to count the rows, but I can't seem to work it out. Can anyone point in the right direction?
Cheers,
Steve
September 10, 2008 at 9:10 am
A quick solution that I have just figured out is this
SELECT item AS DuplicateItem
FROM (SELECT DISTINCT item, description FROM #Import) AS dImport
GROUP BY item
HAVING COUNT(item) > 1
IF @@ROWCOUNT < 1
BEGIN...Is this a good solution?
Steve
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply