Update From OPENQUERY - 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 2008 Forums

Transact-SQL (2008)

Update From OPENQUERY

Author

Topic

poser
Posting Yak Master

124 Posts

Posted - 2013-02-11 : 09:09:15

Good morning!
I'm trying to insert into a sql table from and linked oracle server using openquery.
I tried the Insert and the Update and bot error out..

Insert into SQLTable
From openquery (linkedserver, 'select lname, fname, address from
TACK.T_Tack')

Thank you for any help you can give me in this..
R/P

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 10:26:48

whats the error? please post exact error message

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

poser
Posting Yak Master

124 Posts

Posted - 2013-02-11 : 10:36:49

My exact update statement:

UPDATE SQLTable
From openquery (linkedserver, 'select lname, fname, address from
TACK.T_Tack')

I'm getting a syntax error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.

Thank you for helping with this!
R/p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 10:40:05

it should be like


UPDATE t
SET t.field1 = ...,
t.Field2 = ...
From SQLTable t
INNER JOIN openquery (linkedserver, 'select lname, fname, address from
TACK.T_Tack')q
ON.... condition

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

poser
Posting Yak Master

124 Posts

Posted - 2013-02-11 : 11:12:10

Will this work if I truncate this table before load?
Thats all I'm trying to do is to load the oracle data into an empty SQL table daily.
SELECT INTO wouldn't work because the table in sql already existed.
Thanks, p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 23:07:22

quote:
Originally posted by poser

Will this work if I truncate this table before load?
Thats all I'm trying to do is to load the oracle data into an empty SQL table daily.
SELECT INTO wouldn't work because the table in sql already existed.
Thanks, p

nope...it wont

UPDATE means modifying existing data. If data is not there anymore you cant modify

in that case what you should be using is INSERT...SELECT

ie


INSERT INTO SQLTable(columns...)
SELECT columns...
FROM openquery (linkedserver, 'select lname, fname, address from
TACK.T_Tack')q

make sure you replace columns placeholder with actual column names

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

poser
Posting Yak Master

124 Posts

Posted - 2013-02-12 : 08:44:08

Thank you so much!!!
R/p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources