| Author |
 Topic  |
|
|
poser
Posting Yak Master
124 Posts |
 Posted - 02/11/2013 : 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
India
47173 Posts |
Posted - 02/11/2013 : 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 - 02/11/2013 : 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
India
47173 Posts |
Posted - 02/11/2013 : 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 - 02/11/2013 : 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
India
47173 Posts |
Posted - 02/11/2013 : 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 - 02/12/2013 : 08:44:08
|
Thank you so much!!!
R/p |
|
|
| |
Topic |
|
Update From OPENQUERY
