Skip unique row on BULK INSERT, any way?

[realty205]

I have a table that has a unique primary key. When I do a BULK INSERT from a text file if an identical row/primary key is found the INSERT errors out and nothing gets added. I need to just skip that row from the text file and keep on trucking. Does anyone have an idea?

BULK INSERT Property
FROM 'c:\PristineTest.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
)

 

[bborissov]

Instead on BULK INSERT you could use OPENQUERY()

Also how about to use Temp table?

SELECT * INTO #Test FROM Property WHERE 1 = 0
BULK INSERT #Test
    FROM 'c:\PristineTest.txt'
    WITH
    (
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = '\n'
    )

INSERT INTO Property
SELECT DISTINCT *
FROM #Test

DROP TABLE #Test

NOT TESTED! If the distinct didn't work you should filter PKs other way.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[realty205]

I could use a temp table but don't know how. Could you give me some syntax?

Also, what is this doing?

SELECT * INTO #Test FROM Property WHERE 1 = 0

 

[realty205]

I get the following when I run that query:

(0 row(s) affected)

(21 row(s) affected)
Msg 2627, Level 14, State 1, Line 10
Violation of PRIMARY KEY constraint 'PK_Property_1'. Cannot insert duplicate key in object 'dbo.Property'.
The statement has been terminated.

 

[realty205]

Really what I need to do is check against the SQL table "property". That's where the error will occur. If I try to add the same data currently in the database from a textfile.

 

[bborissov]

What is your PK?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[realty205]

MLS_ID

 

[bborissov]

And if you have more than one MLS_ID in that Text file wich one you want to import and witch one you want to skip?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[SajidAttar]

Which version of SQL Server you are using? if its 2005 then you can give a try to following code...

SELECT * INTO #Test FROM Property WHERE 1 = 0
BULK INSERT #Test
    FROM 'c:\PristineTest.txt'
    WITH
    (
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = '\n'
    )



;With Test as (
	Select Top 1 *
	From #Test
		Union All
	Select Top 1 *
	From #Test
	Where MLS_ID NOT IN 
		(Select MLS_ID From Test) )
INSERT INTO Property
SELECT *
FROM Test



DROP TABLE #Test

NOTE: this is edited version of Borislav's code.. where I am using CTE to get the unique record for each MLS_ID.. it will pick up randum record for each pk if there are duplicagtes..

code note tested..
hth.

Regards,


"Dream not what makes your sleep a pleasure, but what makes you work
hard to achieve it and forget your sleep (untill you achieve it)." -- SJD