Update Column With Openrowset - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Column With Openrowset

Author

Topic

patelb
Starting Member

16 Posts

Posted - 2009-01-21 : 12:56:54

What would be the syntax to update a column in a sql table using a column from a .mdb file using openrowset? I did this and its not working:

UPDATE dbo.test
SET report = (
SELECT REPORT
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\user\Desktop\egcrime\file.mdb';
'admin';'',theTable))

Is it even possible?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 13:00:25

[code]
UPDATE t
SET report =
FROM dbo.testt
JOIN (
SELECT REPORT,linkingcol
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\user\Desktop\egcrime\file.mdb';
'admin';'',theTable))tmp
ON tmp.linkingcol=t.linkingcol
[/code]

linkingcol represents column by which table and mdb can be related

patelb
Starting Member

16 Posts

Posted - 2009-01-21 : 13:11:45

So do I have to change the value of linkingcol to a column that exists in the sql table? I copy and pasted the snippet you gave me and it doesnt work?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 13:13:14

yup. you've to. replace tmp.linkingcol by column in mdb and t.linkincol by column in your table which relates to former one in mdb.

patelb
Starting Member

16 Posts

Posted - 2009-01-21 : 13:21:50

Im still getting a syntax error:

UPDATE dbo.test
SET report =
FROM dbo.test
JOIN (
SELECT REPORT,linkingcol
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\user\Desktop\egcrime\EG_CRIME_STATS_01_09_09.mdb';
'admin';'',EG_CRIME_STATS))tmp
ON tmp.REPORT=test.report

Here is the error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'tmp'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 13:25:21

[code]
UPDATE t
SET t.report = tmp.REPORT
FROM dbo.test t
JOIN (
SELECT t1.REPORT
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Documents and Settings\user\Desktop\egcrime\EG_CRIME_STATS_01_09_09.mdb';
'admin';'',EG_CRIME_STATS)t1
)tmp
ON tmp.REPORT=t.report
[/code]
i dont think you should be using same columns in on. dont you have any columns by which you can join both table and mdb?

patelb
Starting Member

16 Posts

Posted - 2009-01-21 : 13:34:39

I don't have any common columns that I can join on. The table relationship is really weird...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 13:37:32

then how will you decide which value should be updated to which records column?

patelb
Starting Member

16 Posts

Posted - 2009-01-21 : 13:48:19

Basically what I am doing is creating a whole bunch of blank records into my sql table. I have to create these blank records because there are encrypted relationship keys for certain fields in the table. Once I create these so called blank records I want to go through and update the rest of the fields in the table using the .mdb file. Basically I am inserting records into the table, but I cant just straight insert records into the sql table from the .mdb file because I first have to create these blank records that have the encrypted relationship keys.

So here is the process I must go through:
1. Look at the .mdb file and see how many records exist in it
2. Create that many blank records in the sql table(I am doing this with a stored procedure)
3. Go through and update the rows in the sql table using the values from the .mdb file

So if 10 records exist in the .mdb file, I would run my stored procedure 10 times to create 10 blank records. Then I would update those 10 records with the values that exist in the .mdb file.

This is why I have no common field to join on.

patelb
Starting Member

16 Posts

Posted - 2009-01-21 : 13:56:14

I guess you can call the encrypted relation ship keys unique identifiers. That is how the column is defined in the table definition. And those columns have the not null property, so I have to create all the blank records before I can insert values from the .mdb file.

patelb
Starting Member

16 Posts

Posted - 2009-01-21 : 13:57:04

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-21 : 14:37:13

If possible,Why don't you migrate Access data to SQL Server with SSIS?and then compare with your SQL table and get output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources