-- No database context is required for this query
-- but the OrclDB linked server must be created as described in 
-- Creating a SQL Server 2014 Linked Server for an Oracle 11g Database tip

-- Confirm the successful creation of the OrclDB linked server
-- It returns 3 rows
-- DO NOT RUN SUBSEQUENT QUERIES UNTIL THIS QUERY SUCCEEDS

-- Required caps for schema and table names
SELECT TOP 3 * FROM OrclDB..HR.EMPLOYEES






-- Conditionally drop and then restore source database 
-- with data for copying between SQL Server and 
-- Oracle database servers
USE [master]
GO

IF EXISTS(select * from sys.databases where name='SSandOracleDataExchange')
DROP DATABASE SSandOracleDataExchange

RESTORE DATABASE SSandOracleDataExchange FROM  
DISK = N'C:\SSandOracleDataExchange\SSandOracleDataExchange.bak' 


USE SSandOracleDataExchange
GO

-- Show values for 3 rows from the 
-- SQL_SERVER_DATA_FOR_ORACLE table
-- in the SSandOracleDataExchange database
SELECT TOP 3 *
FROM [SQL_SERVER_DATA_FOR_ORACLE]

-- There are 200000 rows in the 
-- SQL_SERVER_DATA_FOR_ORACLE
SELECT COUNT(*) Count_of_rows_in_SQL_SERVER_DATA_FOR_ORACLE 
FROM [SQL_SERVER_DATA_FOR_ORACLE]

-- Each of the 200000 rows has a distinct 
-- set of FIRST_NAME, LAST_NAME, and BIRTH_DATE values
SELECT COUNT(*) Count_of_distinct_rows_in_SQL_SERVER_DATA_FOR_ORACLE
FROM
(
SELECT 
DISTINCT  
 FIRST_NAME
,LAST_NAME
,BIRTH_DATE
FROM [SQL_SERVER_DATA_FOR_ORACLE]
) for_distinct_rows



-- Displays p1 value 0 if Oracle table does not exist
EXEC (
'
SELECT COUNT(*) p1
FROM
(
SELECT table_name
FROM sys.user_tables
WHERE table_name = ''NAMESANDBIRTHDATES''
)
') at OrclDB


-- Drop Oracle table if it does already exists
EXEC ('DROP TABLE NAMESANDBIRTHDATES') at OrclDB

-- Create Oracle table for holding data exported 
-- from SQL Server
EXECUTE  (
'
CREATE TABLE NAMESANDBIRTHDATES(
First_Name varchar2(50)
,Last_Name VARCHAR2(50)
,Birth_Date DATE)
'
)
at OrclDB

-- Insert data into Oracle table
-- from SQL Server table
INSERT INTO OrclDB..HR.NAMESANDBIRTHDATES
SELECT [FIRST_NAME]
      ,[LAST_NAME]
      ,[BIRTH_DATE]
FROM [SQL_SERVER_DATA_FOR_ORACLE]


-- Verified all rows in Oracle table match rows
-- in SQL Server table
SELECT COUNT(*) [Count of rows matched from SQL Server to Oracle]
FROM
(
SELECT * FROM OrclDB..HR.NAMESANDBIRTHDATES

INTERSECT

SELECT [FIRST_NAME]
      ,[LAST_NAME]
      ,[BIRTH_DATE]
FROM [SQL_SERVER_DATA_FOR_ORACLE]
) matching_rows



-- Drop SQL Server table for receiving rows
-- from Oracle if it already exists
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = N'ORACLE_DATA_FOR_SQL_SERVER')
DROP TABLE ORACLE_DATA_FOR_SQL_SERVER

-- Copy rows from Oracle table to SQL Server table
-- Required caps for schema and table names
SELECT * INTO ORACLE_DATA_FOR_SQL_SERVER FROM OrclDB..HR.NAMESANDBIRTHDATES

-- Verified all rows copied from Oracle table match rows
-- in original source SQL Server table
SELECT COUNT(*) [Count of rows matched from Oracle to SQL Server]
FROM
(
SELECT * FROM ORACLE_DATA_FOR_SQL_SERVER

INTERSECT 

SELECT [FIRST_NAME]
      ,[LAST_NAME]
      ,[BIRTH_DATE]
FROM [SQL_SERVER_DATA_FOR_ORACLE]
) matching_rows