SQL UPDATE Database from Excel Table

[jimserac]

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]

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


<ji******@yahoo .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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

[jimserac]

John Bell wrote:

Hi 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 ),
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

<ji******@yahoo .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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]

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

<ji******@yahoo .com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

John Bell wrote:

Hi

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 ),

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

<ji******@yahoo .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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
>

[Stu]

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]

John Bell wrote:

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 )

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: 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


<ji******@yahoo .com> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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]

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]

>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.

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]

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]

>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.
Thanks again for most helpful clarification!! This is helping me
think of it properly.
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.

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