SQL Server Performance Forum – Threads Archive
Copying – comparing and more copying
I would like some advice on importing data into an existing table. I think the fastest way is through bcp or bulk insert. However below is what i want to do:1 – Input the data into a temporary table (multi – user needs to be allowed)
2 – Compare the data in the temporary table to that in the existing table and delete any duplicate values found in the temporary table.
3 – Insert all the remaining data from the temporary table into the main table. Is it possible to perform this task in one stored procedure? How best is it to make the comparison i require between the two tables? I have tried testing a stored procedure with just the bulk insert as below: BULK INSERT TestTable From ‘C:Test.txt’
WITH (
DATAFILETYPE = ‘char’,
FIELDTERMINATOR = ‘ ‘,
ROWTERMINATOR = ‘
‘
)
However, i get the error when i try running it from the query analysiser that it can’t open the text file. Any ideas where i am going wrong with this command? Thanks CE
I don’t see any issue in your approach and I think it would be best to differentiate the BULK INSERT process from steps 1 & 2. You could use BULK INSERT to load the file into a #temp table, and then insert into <TargetTable>
select distinct field1, field2 from #temp a
where not exists (select 1 from <TargetTable> where field1 = a.field1 and field2 = a.field2) The user account used must be a member of bulk administrator role to do bulk insert. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
quote:Originally posted by Evansosteopath
I would like some advice on importing data into an existing table. I think the fastest way is through bcp or bulk insert. However below is what i want to do:
1 – Input the data into a temporary table (multi – user needs to be allowed)
2 – Compare the data in the temporary table to that in the existing table and delete any duplicate values found in the temporary table.
3 – Insert all the remaining data from the temporary table into the main table. Is it possible to perform this task in one stored procedure? How best is it to make the comparison i require between the two tables? I have tried testing a stored procedure with just the bulk insert as below: BULK INSERT TestTable From ‘C:Test.txt’
WITH (
DATAFILETYPE = ‘char’,
FIELDTERMINATOR = ‘ ‘,
ROWTERMINATOR = ‘
‘
)
However, i get the error when i try running it from the query analysiser that it can’t open the text file. Any ideas where i am going wrong with this command? Thanks CE
1 – Input the data into a temporary table (multi – user needs to be allowed)
2 – Compare the data in the temporary table to that in the existing table and delete any duplicate values found in the temporary table.
3 – Insert all the remaining data from the temporary table into the main table. Is it possible to perform this task in one stored procedure? How best is it to make the comparison i require between the two tables? I have tried testing a stored procedure with just the bulk insert as below: BULK INSERT TestTable From ‘C:Test.txt’
WITH (
DATAFILETYPE = ‘char’,
FIELDTERMINATOR = ‘ ‘,
ROWTERMINATOR = ‘
‘
)
However, i get the error when i try running it from the query analysiser that it can’t open the text file. Any ideas where i am going wrong with this command? Thanks CE
Have considered using DTS to do this? Two tools I found very handy is SQL Compare (www.red-gate.com) and SQLDiff (www.adeptsql.com) Regards,
Andy Ong ‘sapere aude.’
DTS is far slower if you want to import large number of rows, BCP and BULK INSERT are minimally logged operations if you choose to run under BULK LOGGED recovery. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
The bulk insert method works really well when inserting into an empty table. However, i’m wanting to insert the data into a table which has already alot of data into it and also has got indexes on the table. I keep on getting the error message that the transaction log is full even when i try bulkinsert with batchsize = 1000. Any ideas how i can overcome this problem?
CE
Ensure to perform Transaction log backup in between the import or set the recovery model to SIMPLE until this operations finishes. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
]]>