Posted by Ed Murphy on 04/04/07 13:44

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.

[Back to original message]