C#•3y ago

Update SQL database rows with a dictionary [Answered]

I have the following SQL server table:

id | name | url
--------------
1  | John |
2  | Jane |
3  | Jack |
4  | Jill |

id | name | url
--------------
1  | John |
2  | Jane |
3  | Jack |
4  | Jill |

Where the URL column is empty for all rows. I have an excel sheet with an id and a url:

1   http://www.google.com
2   http://www.yahoo.com
3   http://www.bing.com

1   http://www.google.com
2   http://www.yahoo.com
3   http://www.bing.com

How can I create an UPDATE / MERGE statement where it will put all the correct URLs in my table? I know you can do an INSERT like

INSERT INTO Users
    (Id, Url)
VALUES
    (1, 'http://www.google.com'),
    (2, 'http://www.yahoo.com'),
    (3, 'http://www.bing.com')

INSERT INTO Users
    (Id, Url)
VALUES
    (1, 'http://www.google.com'),
    (2, 'http://www.yahoo.com'),
    (3, 'http://www.bing.com')

But I can't find whether you can do this with an UPDATE statement. You can do

UPDATE Users
SET Url = (
    SELECT Url
    FROM ???
    WHERE Id = Users.Id
)

UPDATE Users
SET Url = (
    SELECT Url
    FROM ???
    WHERE Id = Users.Id
)

But how do I get my data in that query?

4 Replies

Cracker•3y ago

So you want to retrieve data from excel and use it on update statement ? There are many packages you can use to read data from excel file and map it to C# object I would worry about not to lock database. If there are lots of records then you should use bulk insert once you create script.

Tvde1OP•3y ago

I should have mentioned this, I don't want to do this with C#, we have a bunch of SQL files that are ran after deployments but if no such thing exists, I'll just make a bunch of UPDATE TABLE Users SET url = X WHERE id = Y UPDATE TABLE Users SET url = X WHERE id = Y

Cracker•3y ago

I find OPENROWSET and OPENDATASOURCE for MSSQL to read excel data from SQL script you can read excel rows with OPENROWSET then update your existing table make your script transactional and use iteration. For example very 1000 iteration run commit in order to not lock database

Accord•3y ago

✅ This post has been marked as answered!

Gaming

Programming

Update SQL database rows with a dictionary [Answered]