Upate column in a table based on query

Question

Post reply

Upate column in a table based on query

Stephen crocker

SSCrazy

Points: 2512
More actions
January 1, 2008 at 1:03 pm

#185240

I need to update a column in one table in the database based on values in an excel spread sheet. The excel spreadsheet contains the primary key of the database and a date/time column that needs to be updated based on the primary key.
What is the best way to do this and avoid SSIS if possible.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

OpenRowSet is one fine way... please the following URL...

http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx

That'll get you started... there are a couple of "finesse" tricks that we can talk about later on, but you need the basics mention in the URL first. There's 3 pages in the URL... I'd recommend studying and playing with all 3. Setup a couple of simple tests using their examples so you understand everything that's going on.

Also, if you're not login in with SA privs, it can still be done with OpenRowSet, but we'll need to make a simple registry change and a permissions change on the Temp/TMP directory of your server.

A bit of a pain to setup if not using as SA... but once setup, it's very well worth it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 2

Also, if a fellow by the name of "Madhivanan" responds to this thread, he's got some very good advice on OpenRowSet...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stephen crocker SSCrazy Points: 2512 More actions · Answer 3

excellent, thank you for the advise, I will try that, I didn't want to create a temp table and then drop it, we will only be running this file a couple of times and wanted something simple and to the point

Again thank you and have a Great 2008

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

Ok, call me a hack, but I have to do this a lot and I cheat.

In a new column on the spreadsheet, enter a formula like this:

="UPDATE MyTable SET MyValue='" & B2 & "' WHERE MyID=" & A2

Copy and paste the formula to every row in that column.

When you copy the column and paste it into Query Analyzer or a Management Studio query, it will get the results of the formula in the copy buffer, not the formula.

So, you will end up with a series of single row updates.

It's a bit of a hack, but for single instances of updates it is really easy to do and takes a few seconds of writing T-SQL.

sara karasik SSCommitted Points: 1573 More actions · Answer 5

Ok, call me a hack, but I have to do this a lot and I cheat.

No, you are neither a hack nor a cheater. You ARE making the best use of your time. Fast is better than slow, and writing code for one time use is seldom worth the time it takes.

Having excel write the code, and pasting works; with larger datasets, I'd recommend microsoft access for a one time import.

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 6

I never said anything about being a hack or a cheat as being a bad thing.

And I freely admit that I don't always make the best use of my time.

Thanks for the support though.