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)

OPENQUERY

Author

Topic

jszulc
Yak Posting Veteran

66 Posts

Posted - 2011-07-30 : 07:08:34

I am executing a statement against Oracle db which is similar to the one shown below. I would like to create a logic to always have DepartmentsUsingOpenQuery data available for the website. Since the Oracle dump takes around 3 hrs I cannot afford to drop the table first. (users in UK would be not able to access teh data :-)). I would like to wrap everything up in one SQL TRANSCATION or possibly by table renames? Any ideas? Appreciated.

DROP dbo.DepartmentsUsingOpenQuery

SELECT *
INTO dbo.DepartmentsUsingOpenQuery
FROM OPENQUERY(MyLinkServer, 'SELECT *
FROM AdventureWorks2008R2.HumanResources.Department');
GO

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-07-30 : 11:30:56

I guess I don't really follow - are you saying the query using OPENQUERY takes 3 hours to process? If so, you can change this to create a temp table first. Once you have the data locally, truncate the local table and insert into the local table from the temp table.

Or, if that is still taking too long - you can use 2 tables and alternate your code between the two tables. On Odd days, truncate and insert into the table dbo.DepartmentsUsingOpenQueryOdd - and Even days, truncate and insert into dbo.DepartmentsUsingOpenQueryEven.

Once the insert is completed, modify the SYNONYM to use the newly updated table. Example:

TRUNCATE TABLE dbo.DepartmentsUsingOpenQueryOdd;
INSERT INTO dbo.DepartmentsUsingOpenqueryOdd (...)
SELECT *
FROM OPENQUERY(OracleServer, 'SELECT ...');
GO

DROP SYNONYM dbo.DepartmentsUsingOpenQuery;
CREATE SYNONYM dbo.DepartmentsUsingOpenQuery FOR dbo.DepartmentsUsingOpenQueryOdd;
GO

You could expand on this by using logic to create another SYNONYM to identify the table to be inserted into, that way you would only have the single code:

DROP SYNONYM dbo.DepartmentsUsingOpenQueryInsert;
CREATE SYNONYM dbo.DepartmentsUsingOpenQueryInsert FOR dbo.DepartmentsUsingOpenQueryOdd -- or Even, depending on day

TRUNCATE TABLE dbo.DepartmentsUsingOpenQueryInsert;
INSERT INTO dbo.DepartmentsUsingOpenqueryInsert (...)
SELECT *
FROM OPENQUERY(OracleServer, 'SELECT ...');
GO

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources