SQL Query update multiple rows with different data

Question

Because of an old application with a lot of errors I'm often forced to update multiple (100-1000) rows in our database manually. Usually I receive the values I have to change in a Excel sheet.

I currently update them by doing something like this. Imagine that there are hundreds of rows instead of two. I usually edit them in VSCODE with regex and paste them in. But this feels clunky and prone to errors. Doing this to hundreds of rows its easy to accidentally delete one number or add another.

Is there a better way? I fully understand that the real answer is to fix all the bad code, and we are working on that. But in the mean time this is my reality.

BEGIN TRANSACTION
CREATE TABLE #TMP_PEOPLE
(
    TMPEmployeeID INT,
    TMPAddress NVARCHAR(60)

)
INSERT INTO #TMP_PEOPLE
VALUES (1, '123 Fo-bar way'),
       (2, '321 Bar-fo Street')


UPDATE Northwind.dbo.Employees
    SET Northwind.dbo.Employees.Address = TMPAddress
    FROM #TMP_PEOPLE
        INNER JOIN Northwind.dbo.Employees ON EmployeeID = #TMP_PEOPLE.TMPEmployeeID

COMMIT  TRANSACTION

If you have an algorithm that you can formalize so what stops you from using Excel formulas or PowerQuery to generate 1000 update statements and execute them via console? This way you'll find the exact place in case of errors and can save it for history or rerun as much times as you need. — astentx
– astentx, Commented Nov 25, 2020 at 20:42

CoffeeNeedCoffee · Accepted Answer · 2020-11-25 20:40:34Z

Your UPDATE statement is as succinct as it is going to get. If you worry about the part where you paste in the rows into TMP_PEOPLE, I recommend exporting your Excel sheet into a pipe-delimited or tab-delimited format and then BULK INSERT into your temp table, to reduce the chance of error. This will only error out if your source file contains pipes or tabs (I prefer pipes).

/**     IMPORT FROM FILE via BULK INSERT    **/

IF OBJECT_ID('tempdb..#TMP_PEOPLE') IS NOT NULL
    DROP TABLE #TMP_PEOPLE

CREATE TABLE #TMP_PEOPLE
(
    TMPEmployeeID INT,
    TMPAddress NVARCHAR(60)

)
    
BULK INSERT #TMP_PEOPLE
FROM 'C:\SourceFile.txt'
WITH (
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '\n',
    FirstRow = 1 -- make this 2 if you have a header you need to skip
)

--Check the content of the table.
SELECT * FROM #TMP_PEOPLE

-- Now do your update statement

Other options include bcp, sqlcmd, osql, powershell, and more, but BULK INSERT is the easiest imo. Just make sure your server/service account have access to the file. Security for it is explained here if you have permission issues.

Collectives™ on Stack Overflow

SQL Query update multiple rows with different data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related