-1

I have an Excel file with approximately 300k records and I want to delete these rows from a SQL Server table.

I tried using INSERT for each record but that takes way too long. I also tried to create a temporary table with the Excel data so I can just delete from the main table by matching it with the temp table, but inserting records into the temp table also took too long.

I don't have the necessary permission to directly load Excel data into the database either. And bulk insert only takes 1000 records apparently so that doesn't help.

Does anyone know what the most efficient way to delete these records is? The dataset contains records that are identified as unique through 3 or more columns, so each of my INSERT statements would need at least two WHERE conditions.

Improve this question
4
  • 2
    "bulk insert only takes 1000 records apparently" where did you get that from? Commented Jun 9 at 20:31
  • I tried it, then it errored out and told me I cannot insert more than 1000 records in the same statement. Commented Jun 9 at 20:40
  • You mean values (...) bulk? Not real bulk insert Commented Jun 9 at 20:44
  • 2
    I would talk to your DBA and ask them for suggestions. The easiest path here is to import this data into a table so you use it to join against. Either have them load the data for you or give you temporary permission. Commented Jun 9 at 20:59

1 Answer 1

Reset to default
0

You can try generating a series of multi-valued (1000 max) INSERT INTO ... VALUES ... statements by filling a column with the following Excel formula or similar:

=IF(MOD(ROW(A2), 1000) = 2, "INSERT INTO #TempIds(Id) VALUES ", "")
   & "(" & A2 & ")"
   & IF(MOD(ROW(A2), 1000) = 1, "", ",")

(Line breaks above are for readability only.)

This assumes that you have a collection of values in column A starting at row 2.

  • The IF(MOD(ROW(A2), 1000) = 2, "INSERT INTO #TempIds(Id) VALUES ", "") portion will generate an INSERT statement before the first of every 1000 values.
  • The "(" & A2 & ")" generates the value. If your ID values are GUIDs or some other non-integer value, add single quotes around the generated values - "('" & A2 & "')". If there is any chance that the ID values themselves might contain single quotes, you can double them up using "('" & SUBSTITUTE(A2, "'", "''") & "')".
  • The IF(MOD(ROW(A2), 1000) = 1, "", ",") adds a trailing comma to all values except the last of each group of 1000.

If your source excel file layout is different, you may need to change the = 2 and = 1 conditions.

The result would be something like:

INSERT INTO #TempIds(Id) VALUES (1000),
(1001),
(1002),
...
(1998),
(1999)
INSERT INTO #TempIds(Id) VALUES (2000),
(2001),
(2002),
...
(2998),
(2999)
...
INSERT INTO #TempIds(Id) VALUES (300000),
...
(300999)

You can then cut & paste the generated SQL into a script window, remove the last trailing comma if necessary, and use that to populate your temp ID table that then feeds your delete logic.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

INSERT... SELECT ... FROM (VALUES ... allows more than 1000

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.