How to update selected columns of a table in SQL server db using data from a Excel file?

[urprettyfriend]

Hi,

I have an Excel file with 400 rows of old values and the corresponding
new values. My table currently has 10 columns out of which 3 columns
use the old value specified in the excel file. I need to update those
old values in the columns with the new values from the Excel file.
Please guide me as to how to proceed with this.

Thanks in advance!

[Ed Murphy]

urprettyfriend wrote:

I have an Excel file with 400 rows of old values and the corresponding
new values. My table currently has 10 columns out of which 3 columns
use the old value specified in the excel file. I need to update those
old values in the columns with the new values from the Excel file.
Please guide me as to how to proceed with this.

Import the data into a second table, then do something like this:

update Table1
set t1.c4 = t2.c4,
t1.c5 = t2.c5,
t1.c6 = t2.c6,
t1.c7 = t2.c7,
t1.c8 = t2.c8,
t1.c9 = t2.c9,
t1.c10 = t2.c10
from Table1 t1
join Table2 t2 on t1.c1 = t2.c1
and t1.c2 = t2.c2
and t1.c3 = t2.c3

[urprettyfriend]

On Apr 3, 11:55 pm, Ed Murphy <[e-mail]:

urprettyfriend wrote:

I have an Excel file with 400 rows of old values and the corresponding
new values. My table currently has 10 columns out of which 3 columns
use the old value specified in the excel file. I need to update those
old values in the columns with the new values from the Excel file.
Please guide me as to how to proceed with this.

Import the data into a second table, then do something like this:

update Table1
set t1.c4 = t2.c4,
t1.c5 = t2.c5,
t1.c6 = t2.c6,
t1.c7 = t2.c7,
t1.c8 = t2.c8,
t1.c9 = t2.c9,
t1.c10 = t2.c10
from Table1 t1
join Table2 t2 on t1.c1 = t2.c1
and t1.c2 = t2.c2
and t1.c3 = t2.c3

Ed,

Thanks for ur solution. But I can't create a temp table in the db....I
already asked if I can do that, Unfortunately, I am not allowed to do
that. Please tell me if there is any other way to do this.

Thanks!

[Ed Murphy]

urprettyfriend wrote:

Thanks for ur solution. But I can't create a temp table in the db....I
already asked if I can do that, Unfortunately, I am not allowed to do
that. Please tell me if there is any other way to do this.

Try creating a temp table whose name starts with a # (it will go away
automatically when your session closes). They might let you do that.

Failing that, I've used this method on small files:

1) In Excel, move the three columns to the end
2) Insert a blank column between each pair of data columns
3) Edit the new blank cells in row 1 so that it looks like this:

[A1] update Table1 set c4 = '
[A2] <data>
[A3] '', c5 = '
[A4] <data>
(similarly for c6 through c10)
[A15] '' where c1 = '
[A16] <data>
[A17] '' and c2 = '
[A18] <data>
[A19] '' and c3 = '
[A20]
[A21] ''

4) Copy+paste these to the other rows
5) Copy+paste the whole thing into Notepad
6) Use search+replace to strip out all the tabs
7) Copy+paste the result into Query Analyzer and execute it

Note that you'll have to manually escape things like ' within data
fields.

[thelawrencebishop]

On Apr 4, 8:44 am, Ed Murphy <[e-mail]:

urprettyfriend wrote:

Thanks for ur solution. But I can't create a temp table in the db....I
already asked if I can do that, Unfortunately, I am not allowed to do
that. Please tell me if there is any other way to do this.

Try creating a temp table whose name starts with a # (it will go away
automatically when your session closes). They might let you do that.

Assuming DTS is being used to import the Excel workbook, a temp #table
won't work. DTS (as well as BCP and BULK INSERT) require a physical/
persistent table as a destination.

If you're not able to create a table, have the admins create a table
for you that you can use as a destination during the DTS import.

OR

Have the admins import the workbook into the database for you which
you can later use in your UPDATE.

Failing that, I've used this method on small files:

1) In Excel, move the three columns to the end
2) Insert a blank column between each pair of data columns
3) Edit the new blank cells in row 1 so that it looks like this:

[A1] update Table1 set c4 = '
[A2] <data>
[A3] '', c5 = '
[A4] <data>
(similarly for c6 through c10)
[A15] '' where c1 = '
[A16] <data>
[A17] '' and c2 = '
[A18] <data>
[A19] '' and c3 = '
[A20]
[A21] ''

4) Copy+paste these to the other rows
5) Copy+paste the whole thing into Notepad
6) Use search+replace to strip out all the tabs
7) Copy+paste the result into Query Analyzer and execute it

Note that you'll have to manually escape things like ' within data
fields.

Creative solution, but a lot of unnecessary work. An admin can push
the data into the database in 30 seconds using DTS.

All the best,
Lawrence Bishop