I am building a bulk insert query in SQL 2012.
Everything I want has been accomplished except for the item below:
When I run the bulk insert I need a way to
1.) create a primary key based on 2 of the columns in the flat file (I have attached this in an excel file as an example of how it looks).
2.) If I run the bulk insert query again I want the rows to fail that have the same primary key. For example, if records have been imported with the DTAL_RRN = 48232604 and EXTERNAL_SN = 2085 already I would want these records to fail on import if someone tries to run the query again. I used this example because it is the first line in the attached file.
The Bulk insert looks like this:
bulk insert DTAL_ALL
(DATAFILETYPE = 'char',
FIRSTROW = 2,
FIELDTERMINATOR = ','
I also was having trouble removing quotes (") using the FieldTerminator so I wrote another query to remove the quotes from the fields with the replace function:
DTAL_RRN=replace (DTAL_RRN ,'"',' ')
I don't know if I am being to specific here - all I really need is to create that primary key mentioned above and once created have it reject rows with the same primary key when another bulk insert is run.