SQLTeam.com | Weblogs | Forums

Delete based on .csv file


#1

Hi There,

Need your help please.
I have a .csv file that has records with an id field.
I need to delete from a table if a matching id is found in the .csv file.

Would anyone know how I can achieve that?

Thanks for your help.

Best Regards,


#2

Import the .csv file then use a join. If this is something that will recurring will the file name be the same?


#3

Hi There,
Thanks for the reply.

Yes it will be the same filename.
What would the delete join code be?

Best Regards,

Steve.


#4

So you could save the SSIS and execute it to import the table. An example of code, something like:

DELETE Alias1 
FROM OriginalTable Alias1 
INNER JOIN CSVTable Alias2 ON Alias1.ID = Alias2.ID;

How this helps


#5

Hi There,

I got that working using your code, thank you.
The problem is, the reason I am doing this is to clear out old records and make space on the server. Importing the .csv to a table reduces the space on the server temporarily.
Is there a way to not import the .csv file and just do it from within the database. I created the .csv by exporting the records I was going to delete from my invoice table and then using that resulting .csv to remove the corresponding Rental records.
Is there a way in sql code to delete any record from the Invoice Table with a date < 01/07/2015 and delete the matching Rental record with a matching InvoiceId.
Thanks for the help, it's appreciated.

Best Regards,
Steve.


#6

Import the csv into tempdb. You'll already have unused space in that db to temporarily store the ids until the deletes are done.