Posted by thelawrencebishop on 04/05/07 00:44

On Apr 4, 8:44 am, Ed Murphy <emurph...@socal.rr.com> wrote:
> 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

[Back to original message]