SQL UPDATE Database from Excel Table

[jimserac@yahoo.com]

I had previously posted this in an Access forum
with negative results so will try here.

Although this question specifies an Access database,
I also wish to accomplish this with a large MS SQL Server
database that we have.

Question follows:

The following SQL statement, used in VBScript,
will COPY a table from Excel to an Access mdb.

SQL = "SELECT * INTO C1R0" & _
" FROM [C1R0$] IN ''" & _
" 'Excel 8.0;database=c: \excel\UpdateFi nal1.xls';"

What is the SQL statement that will
UPDATE an already existing Access table
with all rows from Excel spreadsheet?

The columns of both Spreadsheet and database are the
same.

Thanks
Jim

[John Bell]

Re: SQL UPDATE Database from Excel Table

Hi

They are normally good on this in the access forums! So you will have to
make do with a non-access person's reply!!

In SQL Server you have

UPDATE A
SET col1 = E.col1,
col2 = E.col2,
.....
FROM C1R0 A
JOIN [C1R0$] E ON E.PK = A.PK

I am not sure where the filename would go!!

You can also use something like:

UPDATE C1R0
SET col1 = ( SELECT col1 FROM [C1R0$] IN 'Excel
8.0;database=c: \excel\UpdateFi nal1.xls'; WHERE [C1R0$].PK = C1R0.PK ),
col2 = ( SELECT col2 FROM [C1R0$] IN 'Excel
8.0;database=c: \excel\UpdateFi nal1.xls'; WHERE [C1R0$].PK = C1R0.PK ),
....

John




<jimserac@yahoo .com> wrote in message
news:1117817010 .651125.209050@ f14g2000cwb.goo glegroups.com.. .[color=blue]
>I had previously posted this in an Access forum
> with negative results so will try here.
>
> Although this question specifies an Access database,
> I also wish to accomplish this with a large MS SQL Server
> database that we have.
>
> Question follows:
>
> The following SQL statement, used in VBScript,
> will COPY a table from Excel to an Access mdb.
>
> SQL = "SELECT * INTO C1R0" & _
> " FROM [C1R0$] IN ''" & _
> " 'Excel 8.0;database=c: \excel\UpdateFi nal1.xls';"
>
> What is the SQL statement that will
> UPDATE an already existing Access table
> with all rows from Excel spreadsheet?
>
> The columns of both Spreadsheet and database are the
> same.
>
> Thanks
> Jim
>[/color]

[jimserac@yahoo.com]

Re: SQL UPDATE Database from Excel Table

John Bell wrote:[color=blue]
> Hi[/color]
[color=blue]
> UPDATE C1R0
> SET col1 = ( SELECT col1 FROM [C1R0$] IN 'Excel
> 8.0;database=c: \excel\UpdateFi nal1.xls'; WHERE [C1R0$].PK = C1R0.PK ),
> col2 = ( SELECT col2 FROM [C1R0$] IN 'Excel
> 8.0;database=c: \excel\UpdateFi nal1.xls'; WHERE [C1R0$].PK = C1R0.PK ),[/color]

Many thanks, I will give this a shot.

I think that the place for referencing the location of the
Excel spreadsheet is provided for by an extension
to SQL (Transact-SQL?) but I have not done a whole
heck of alot of SQLing to be sure of all the details
and am learning it now.

I was hoping, given the elegant simplicity of the SQL
table copy code, that there might be an equally elegant
UPDATE SQL, particularly since I wanted to update the entire
table.

Will post if I find it.

Thanks again
Jim





[color=blue]
> <jimserac@yahoo .com> wrote in message
> news:1117817010 .651125.209050@ f14g2000cwb.goo glegroups.com.. .[color=green]
> >I had previously posted this in an Access forum
> > with negative results so will try here.
> >
> > Although this question specifies an Access database,
> > I also wish to accomplish this with a large MS SQL Server
> > database that we have.
> >
> > Question follows:
> >
> > The following SQL statement, used in VBScript,
> > will COPY a table from Excel to an Access mdb.
> >
> > SQL = "SELECT * INTO C1R0" & _
> > " FROM [C1R0$] IN ''" & _
> > " 'Excel 8.0;database=c: \excel\UpdateFi nal1.xls';"
> >
> > What is the SQL statement that will
> > UPDATE an already existing Access table
> > with all rows from Excel spreadsheet?
> >
> > The columns of both Spreadsheet and database are the
> > same.
> >
> > Thanks
> > Jim
> >[/color][/color]

[John Bell]

Re: SQL UPDATE Database from Excel Table

Hi

Transac-SQL is for SQL Server I believe it may be called Access-SQL for
access!

One thing you may want to do is to create a staging table using you original
statement and then us that table to do the updates, that may avoid any
restrictions with the extensions.

John

<jimserac@yahoo .com> wrote in message
news:1117818971 .431454.223740@ z14g2000cwz.goo glegroups.com.. .[color=blue]
> John Bell wrote:[color=green]
>> Hi[/color]
>[color=green]
>> UPDATE C1R0
>> SET col1 = ( SELECT col1 FROM [C1R0$] IN 'Excel
>> 8.0;database=c: \excel\UpdateFi nal1.xls'; WHERE [C1R0$].PK = C1R0.PK ),
>> col2 = ( SELECT col2 FROM [C1R0$] IN 'Excel
>> 8.0;database=c: \excel\UpdateFi nal1.xls'; WHERE [C1R0$].PK = C1R0.PK ),[/color]
>
> Many thanks, I will give this a shot.
>
> I think that the place for referencing the location of the
> Excel spreadsheet is provided for by an extension
> to SQL (Transact-SQL?) but I have not done a whole
> heck of alot of SQLing to be sure of all the details
> and am learning it now.
>
> I was hoping, given the elegant simplicity of the SQL
> table copy code, that there might be an equally elegant
> UPDATE SQL, particularly since I wanted to update the entire
> table.
>
> Will post if I find it.
>
> Thanks again
> Jim
>
>
>
>
>
>[color=green]
>> <jimserac@yahoo .com> wrote in message
>> news:1117817010 .651125.209050@ f14g2000cwb.goo glegroups.com.. .[color=darkred]
>> >I had previously posted this in an Access forum
>> > with negative results so will try here.
>> >
>> > Although this question specifies an Access database,
>> > I also wish to accomplish this with a large MS SQL Server
>> > database that we have.
>> >
>> > Question follows:
>> >
>> > The following SQL statement, used in VBScript,
>> > will COPY a table from Excel to an Access mdb.
>> >
>> > SQL = "SELECT * INTO C1R0" & _
>> > " FROM [C1R0$] IN ''" & _
>> > " 'Excel 8.0;database=c: \excel\UpdateFi nal1.xls';"
>> >
>> > What is the SQL statement that will
>> > UPDATE an already existing Access table
>> > with all rows from Excel spreadsheet?
>> >
>> > The columns of both Spreadsheet and database are the
>> > same.
>> >
>> > Thanks
>> > Jim
>> >[/color][/color]
>[/color]

[Stu]

Re: SQL UPDATE Database from Excel Table

It doesn't sound as if you are doing an UPDATE, but rather a "replace
all the contents" move. An UPDATE statement assumes that you have
matching data between your source and targets, and can link to change
the vaules of that data. It sounds as if you just want to delete the
data, and start over.

You have a couple of options, but they will differ between Access and
SQL Server. SQL Server will allow you to issue multiple SQL statements
in a single transaction by using the .; seperator. I don't believe
that Access will.

In either case, you could either DROP the table, and then recreate it
using the SELECT...INTO syntax, or simply DELETE the data, and INSERT
the new data. Your choice may depend on what rights you have, how much
data there is, whether there are indexes on the table, etc...

Just offering some ideas.

[jimserac@yahoo.com]

Re: SQL UPDATE Database from Excel Table

John Bell wrote:
[color=blue]
> You can also use something like:
>
> UPDATE C1R0
> SET col1 = ( SELECT col1 FROM [C1R0$] IN 'Excel
> 8.0;database=c: \excel\UpdateFi nal1.xls'; WHERE [C1R0$].PK = C1R0.PK ),
> col2 = ( SELECT col2 FROM [C1R0$] IN 'Excel
> 8.0;database=c: \excel\UpdateFi nal1.xls'; WHERE [C1R0$].PK = C1R0.PK )[/color]


Well I thought I would just be conservative and try and update
only one column. It gets past the SQL syntax parser OK but then
craps out with "Invalid Filename" error message (see next line).

UPDATE C1R0 SET QTY = (SELECT QTY FROM [C1R0$] IN 'Excel
8.0;HDR=Yes;DAT ABASE=c:\excelx xx\UpdateFinal1 .xls')

I'll be damned if I can see anything wrong with the file name.
Just to be safe, I changed the name of the folder to excelxxx but
it still does not like the file name.

I guess I'm in the realm of vendor specific SQL extension syntax
tricks - just where microsoft wants me.

Thanks
Jim


John Bell wrote:[color=blue]
> Hi
>
> They are normally good on this in the access forums! So you will have to
> make do with a non-access person's reply!!
>
> In SQL Server you have
>
> UPDATE A
> SET col1 = E.col1,
> col2 = E.col2,
> ....
> FROM C1R0 A
> JOIN [C1R0$] E ON E.PK = A.PK
>
> I am not sure where the filename would go!!
>
> You can also use something like:
>
> UPDATE C1R0
> SET col1 = ( SELECT col1 FROM [C1R0$] IN 'Excel
> 8.0;database=c: \excel\UpdateFi nal1.xls'; WHERE [C1R0$].PK = C1R0.PK ),
> col2 = ( SELECT col2 FROM [C1R0$] IN 'Excel
> 8.0;database=c: \excel\UpdateFi nal1.xls'; WHERE [C1R0$].PK = C1R0.PK ),
> ...
>
> John
>
>
>
>
> <jimserac@yahoo .com> wrote in message
> news:1117817010 .651125.209050@ f14g2000cwb.goo glegroups.com.. .[color=green]
> >I had previously posted this in an Access forum
> > with negative results so will try here.
> >
> > Although this question specifies an Access database,
> > I also wish to accomplish this with a large MS SQL Server
> > database that we have.
> >
> > Question follows:
> >
> > The following SQL statement, used in VBScript,
> > will COPY a table from Excel to an Access mdb.
> >
> > SQL = "SELECT * INTO C1R0" & _
> > " FROM [C1R0$] IN ''" & _
> > " 'Excel 8.0;database=c: \excel\UpdateFi nal1.xls';"
> >
> > What is the SQL statement that will
> > UPDATE an already existing Access table
> > with all rows from Excel spreadsheet?
> >
> > The columns of both Spreadsheet and database are the
> > same.
> >
> > Thanks
> > Jim
> >[/color][/color]

[John Bell]

Re: SQL UPDATE Database from Excel Table

Hi

In that case you may want to do as I suggested in my other post and
load the data into a staging table and work from there.

John

[jimserac@yahoo.com]

Re: SQL UPDATE Database from Excel Table

>It doesn't sound as if you are doing an UPDATE, but rather a "replace[color=blue]
>all the contents" move. An UPDATE statement assumes that you have
>matching data between your source and targets, and can link to change
>the vaules of that data. It sounds as if you just want to delete the
>data, and start over.[/color]
[color=blue]
>You have a couple of options, but they will differ between Access and
>SQL Server. SQL Server will allow you to issue multiple SQL statements
>in a single transaction by using the .; seperator. I don't believe
>that Access will.[/color]

Many thanks for the ideas.

I am indeed doing an UPDATE of all rows and columns
since the update excel sheet will never have ALL of the
rows that the Access database to be updated has.

SQL should handle it in one gulp (theoretically, heh heh)
rather than need mulitple queries. As the original
post shows, I can sure copy a table from Excel to Access
in one shot - so why not update it that easily (with the
proviso that I'm updating everything) too?

Thanks Again.
J

[Stu]

Re: SQL UPDATE Database from Excel Table

SQL doesn't work like that; what you call a copy of a table is actually
a combination of two seperate SQL commands: a CREATE TABLE and an
INSERT statement. SQL Server and Access have created a shortcut syntax
for these two commands (the SELECT...INTO). SELECT... INTO assumes
that the target table does not exist, and that you want to create it.
If it doesn exist, it will fail.

In the scenario you're describing, it's not an UPDATE. An UPDATE SQL
statement assumes that you're going to find a particular piece (or
pieces) of data, and replace an old value with a new value. You're
not adding rows when you execute an UPDATE statement; you are simply
finding a value and replace it.

If you wat to replace all of the rows of a target table AND add new
rows, there is no one-shot command to do it. You have to a) empty the
table and add all the rows you want, or b) destroy the table, and
reissue the SELECT...INTO shortcut syntax to recreate the table with
the new data set. You could do as John suggested and UPDATE each
column in your target table with matching data from your source table,
but you have to match a key column between the two tables (or your
results will be unpredictable), and an UPDATE will not add any new
data.

I hope that clarifies a bit.

Stu

[jimserac@yahoo.com]

Re: SQL UPDATE Database from Excel Table

>SQL doesn't work like that; what you call a copy of a table is actually[color=blue]
>a combination of two seperate SQL commands: a CREATE TABLE and an
>INSERT statement. SQL Server and Access have created a shortcut syntax
>for these two commands (the SELECT...INTO). SELECT... INTO assumes
>that the target table does not exist, and that you want to create it.
>If it doesn exist, it will fail.[/color]

Thanks again for most helpful clarification!! This is helping me
think of it properly.
[color=blue]
>In the scenario you're describing, it's not an UPDATE. An UPDATE SQL
>statement assumes that you're going to find a particular piece (or
>pieces) of data, and replace an old value with a new value. You're
>not adding rows when you execute an UPDATE statement; you are simply
>finding a value and replace it.[/color]

I am doing UPDATE but probably said it wrong in my posting.
I have, say 1000 rows of data in Access table.
The Excel spreadsheet has maybe 20 rows of updated data -> and
each of the 20 rows has index number which already exists in key field
of Access,
so no new records are comming in - just updates. Of the 20 rows in
Excel spreadsheet, pehaps 3 or 4 of the 32 columns (or fields) in each
of the 20 rows has changed data which must update the corresponding
fields in the corresponding rows of the Access database and replacing
an entire row in the update is just fine because the other fields that
did not change are exactly the same between Excel and Access.

I am looking at using the approach suggested in this thread as being
the most viable - use SQL to copy excel data to Access Temporary table
and then use a 2d SQL operation to update main table from temporary
one.

I am not entirely sure how to use SQL to update one table from another
but will figure it out.

I am doing this from a VBScript and wanted to avoid recordsets and the
goofy necessity of naming every single one of the 32 columns in the
update code
but apparently even in SQL this will be necessary - I would have
thought that there would be more sophisticated way since I am OK with
updating ALL the fields of the change rows. Again, I just looked at
the elegant simplicity of the "SELECT *" operator
in copying all the table to another place and thought that there should
be an equally elegant UPDATE operation so I'm just letting my
imagination get ahead of reality.


Again many thanks to you and everyone for the assistance.

Jimserac

[Stu]

Re: SQL UPDATE Database from Excel Table

You know, I'm wonderign if I'm looking too closely at the tree and am
missing the forest. You want to update a table in Access with values
from an Excel spreadsheet, right? I'm wondering if you could simply
establish a connection between the two (using the linked table function
in Access). This way, when you update your spreadsheet and save it,
the data will be automatically updated in Access. This is probably the
simplest solution. Of course, it means that the Excel table will be an
exact replica of the Access table, so if there's 1000 records in
Access, then they'll all show up in the Excel sheet. You could copy
only the rows you need to update from one Access table to another, and
then update between the two tables, but I'm not sure if that's a good
solution or not.

I guess I'm a bit confused (it's late, I'm tired, and I am probably
overthinking it); where does the data come from? Are you using Excel
as a data import and recording tool? Or is it that you have data in an
Access table that you want to manipulate in Excel and then move back?

Of course, with SQL Server, it gets a bit more complicated. You could
do a linked table, but the performance would suffer. The whole point
of SQL Server is that your data should be easily accessible to a great
deal of users, and having them all hit an Excel spreadsheet doesn't
sound like it would work so well. In that case, you're better off
thinking about a stored procedure or DTS package to upload the data
from your Excel sheet to the SQL Server database. Both of those
options require a bit more study than you'll probably find in this
post, but if you play around with it, I'm sure you'll get it :)

Stu

[jimserac@yahoo.com]

Re: SQL UPDATE Database from Excel Table


Stu wrote:
[color=blue]
>You know, I'm wonderign if I'm looking too closely at the tree and am
>missing the forest. You want to update a table in Access with values
>from an Excel spreadsheet, right? I'm wondering if you could simply
>establish a connection between the two (using the linked table function
>in Access).[/color]

Thanks, I had already considered and rejected the possibility of making
a link between Excel and Access. The Excel spreadsheet will have
only
some of the rows that the Access spreadsheet has and our Excel sheets
are comming from several users who build them either manually or else
get the data from a program. So, I really need to do the update of
the Access table from VBScript.

I do think that mesages such as "UPDATE syntax error" from Access are
woefully inadequate and I will actively seek open source alternatives
to Microsoft Office database products in the future.


Thanks Again
Jim

[jimserac@yahoo.com]

Re: SQL UPDATE Database from Excel Table

Just as a final note, and again thanks to everyone for the helpful
suggestions.

The following method worked immediately:

'STEP 1, COPY EXCEL INTO UNIQUE ACCESS TABLE
SQL = "SELECT * INTO UpdTbl" & _
" FROM [C1R0$] IN ''" & _
" 'Excel 8.0;database=c: \excel\UpdateFi nal1.xls';"

'STEP 2 UPDATE MAIN ACCESS TABLE WITH TABLE FROM STEP 1
SQL = "UPDATE C1R0 INNER JOIN UpdTbl ON C1R0.[Index] = UpdTbl.[Index]"
&_
" SET C1R0.QTY = UpdTbl.QTY, C1R0.MFG = UpdTbl.MFG"

where "QTY" and "MFG" are just some fields common to both tables that
I put in to verify that the updates where working.

A few caveats along the way:
1. Let Access build its freaking autonumber index column - I had wanted
to use my own index column, which also had unique numbers, but it
refused to work with it.
2. Forget about importing directly from the Excel spreadsheet unless
you enjoy reading goofy error messages. Even if the spreadsheet is
structured exactly the same (same columns and types of data) as the
main Access table.
3. Step back and wonder how the hell managers can spend tens of
thousands of dollars on this nonsense - be on the lookout for open
source or Cache or some better way of doing these things. If nothing
else turns up, I may write it myself.

Jim