August 28, 2014 at 6:51 am
I was trying to figure out what the OPENQERY Syntax is to Insert into SQL Server Table from Oracle Linked Server.
I had an example but I can't find it.
Any help would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 28, 2014 at 6:53 am
I just found the code.
INSERT INTO [table1]
(
col1
, col2
, col3
SELECT oq.c1
, oq.c2
, oq.c3
FROM OPENQUERY(DW_DB,
'
SELECTDISTINCT
c1
,c2
,c3
FROM dw.sourcetable
') oq
LEFT OUTER JOIN SSDB.dbo.table1 AS t1
ON oq.c1 = t1.col1
WHERE t1.col1 IS NULL
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 28, 2014 at 6:58 am
The example that I provided is not actually what I need. I want the Insert columns to be all from the Oracle Table.
Any ideas would be greatly appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 28, 2014 at 7:12 am
Welsh Corgi (8/28/2014)
The example that I provided is not actually what I need. I want the Insert columns to be all from the Oracle Table.Any ideas would be greatly appreciated.
Remove the join and the WHERE clause.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 28, 2014 at 7:43 am
ChrisM@Work (8/28/2014)
Welsh Corgi (8/28/2014)
The example that I provided is not actually what I need. I want the Insert columns to be all from the Oracle Table.Any ideas would be greatly appreciated.
Remove the join and the WHERE clause.
I do not get the reason for SELECT Distinct?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 28, 2014 at 7:48 am
Welsh Corgi (8/28/2014)
ChrisM@Work (8/28/2014)
Welsh Corgi (8/28/2014)
The example that I provided is not actually what I need. I want the Insert columns to be all from the Oracle Table.Any ideas would be greatly appreciated.
Remove the join and the WHERE clause.
I do not get the reason for SELECT Distinct?
There may be duplicates across the columns selected from the remote. DISTINCT will eliminate those duplicates. Unless the count of such dupes is of interest to you, it makes sense to do this. In any case, it would be more network-efficient to count any dupes at source.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 28, 2014 at 7:51 am
ChrisM@Work (8/28/2014)
Welsh Corgi (8/28/2014)
ChrisM@Work (8/28/2014)
Welsh Corgi (8/28/2014)
The example that I provided is not actually what I need. I want the Insert columns to be all from the Oracle Table.Any ideas would be greatly appreciated.
Remove the join and the WHERE clause.
I do not get the reason for SELECT Distinct?
There may be duplicates across the columns selected from the remote. DISTINCT will eliminate those duplicates. Unless the count of such dupes is of interest to you, it makes sense to do this. In any case, it would be more network-efficient to count any dupes at source.
Or, looking at the sample query provided by Welsh Corgi, they only wanted distinct rows across c1, c2, c3.
August 28, 2014 at 7:57 am
Lynn Pettis (8/28/2014)
ChrisM@Work (8/28/2014)
Welsh Corgi (8/28/2014)
ChrisM@Work (8/28/2014)
Welsh Corgi (8/28/2014)
The example that I provided is not actually what I need. I want the Insert columns to be all from the Oracle Table.Any ideas would be greatly appreciated.
Remove the join and the WHERE clause.
I do not get the reason for SELECT Distinct?
There may be duplicates across the columns selected from the remote. DISTINCT will eliminate those duplicates. Unless the count of such dupes is of interest to you, it makes sense to do this. In any case, it would be more network-efficient to count any dupes at source.
Or, looking at the sample query provided by Welsh Corgi, they only wanted distinct rows across c1, c2, c3.
Yes, that too.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply