March 7, 2006 at 2:13 pm
Goal: import daily CSV file into table using a stored procedure
Dilemma: new imports should UPDATE where necessary not INSERT based on a key columns. DTS not an available option.
What is the best way to import a CSV file quickly (10s of thousands of records) into a table while not overwriting indexed records that are already there? Some of the daily CSVs to be imported have actual new data based on a key column, but if the record already exists in the database, then the table needs to be updated. Looping through each record is not a good option since the performance is terrible using that method.
DTS is not available else it would be heavily considered.
Is BULK INSERT the way to go? Temp tables?
thanks for any suggestions &/or script samples
March 8, 2006 at 7:09 am
Yes, you can use BULK INSERT to load the csv into a temp table. Then update or insert to your table like this:
UPDATE A
SET valueCol1=B.valueCol1, valueCOl2=B.valueCol2,...
FROM yourTable A, yourTempTable B
WHERE A.Key=B.Key
INSERT yourTable (...)
SELECT ...
FROM yourTempTable
WHERE NOT EXISTS ( SELECT 1 FROM yourTable WHERE yourTable .key=yourTempTable.Key)
March 8, 2006 at 7:19 am
Thanks for the quick reply! Just o it is straight in my head, I create the temp table, BULK INSERT into it, then either UPDATE or INSERT based on if the record already exists. How do I get it to loop through each record from the temp table to determine whether I should UPDATE or INSERT?
It looks like I need to use a Format File for the BULK INSERT. Have you had any luck on this? Everything is pretty much "," (double quote comma double quote) separated except the first value which only has a comma after it. I have tried many enumerations of the Format File without any success for the first value.
thanks again
March 8, 2006 at 11:02 am
Maybe use a front-end to import all the data? Or do you need to do it on a Bulk Insert?
March 8, 2006 at 11:30 am
Well, I chose BULK INSERT since I figured it would be the fastest way to get the data into SQL from a CSV. I am now trying to BULK INSERT into a temp table then UPDATE or INSERT into the real table based on if the record already exists. Having the stored procedure look to see if a record already exists is my current problem. To answer your question, to keep things as streamlined & as fast as possible, I would rather not use a front end since it would involve way too many trips to the database server. Any other suggestions would be appreciated!
March 8, 2006 at 12:19 pm
I did something like this before, but you have to make sure and insert an identity row in your tempe table and use that as your UniqueID:
declare @counter int
declare @length int
declare @UniqueID int
declare @subjname varchar(10)
declare @MyMessage varchar(1000)
set @length = (select count(CaseNumber) from tblEmail)
set @counter = 0
set @UniqueID = (select top 1 UniqueID from tblEmail Order By UniqueID)
set @subjname = (select CaseNumber from tblEmail where UniqueID =
@UniqueID)
set @MyMessage = ' A payment is due, in 7 days, for Case Number ' + @subjname + '.'
while @counter < @length
begin
exec master..xp_sendmail
@recipients = 'blah@blah.com',
@message = @MyMessage
set @counter = @counter + 1
set @UniqueID = @UniqueID + 1
set @subjname = (select CaseNumber from tblEmail where UniqueID =
@UniqueID)
set @MyMessage = ' A payment is due, in 7 days, for Case Number ' + @subjname + '.'
end
March 8, 2006 at 1:31 pm
Why don't you really look at the solution peterhe posted? It's complete and solves all of your needs. There's really not the slightest need for a loop.
As to the bulk insert: you can as well attach the file as a linked server and refer to that.
_/_/_/ paramind _/_/_/
March 8, 2006 at 1:49 pm
lemme check into it & I will get back to you
thanks!
March 13, 2006 at 2:41 pm
The code sample is too much pseudo-code for me to figure out. Looks like I am going to need some more Ibuprofin....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply