update all records in sql server database from a linked oracle server

I need to pull all records from an oracle database linked server and update/insert records into a sql server that I have setup like a mirror.
There are 132 tables with many fields in each table. The script below dynamically gets all table names and field names for each but it is not updating the sql server with the results.

Thanks,
--declare all variables used

declare @tblName varchar(150) 
declare @fields varchar(150)
declare @sql varchar (2000)

DECLARE c1 CURSOR READ_ONLY
FOR
(select TABLE_NAME from information_schema.columns where TABLE_CATALOG = 'SiteMinder_FULL' group by TABLE_NAME )


OPEN c1

FETCH NEXT FROM c1
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
	

  


  

select COLUMN_NAME from information_schema.columns where TABLE_NAME = @tblName

 set @sql = 'select '+@fields+' into ' + @tblName + ' from  OCX..SST.' + @tblName + '' 
 
exec (@sql)



----first cursor
	FETCH NEXT FROM c1
	INTO @tblName

	

END

Open in new window

dboyd02Asked:
Who is Participating?
 
Marten RuneConnect With a Mentor SQL Expert/Infrastructure ArchitectCommented:
I agree, but a ##name = global temp, #name = this session. And destroyes when the session is closed. so stick with: #Temp_Oracle_TBL. And alter the drop code to use this name too. Then you'll be fine.

The solution is as I stated before, capture your dynamic tsql, and testrun it under the same context, and the same DBName. Then you'll find out why it wont work.

This is a generic technique, that you can use in a variety of cases.

//Marten
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Change
exec (@sql)
to
print @sql
Now test the various printlines. and you'll find some errors I belive.
Come back with followup questions and please post whats the sql code against the linked server.

//Marten
0
 
patriktCommented:
You have two options.
First is to construct INSERT INTO <sql table> (<sql columns>) SELECT <columns> FROM <linked server>...<oracle table>. This command should be than called by EXEC().
Second is to use OPENQUERY(). It is litle dfferent because there you send query in oracle language, but in this case there is no difference in select.
INSERT INTO <sql table> (<columns>) SELECT  * FROM OPENQUERY(<linked server>,<Oracle query>)
Also look on SSIS capabilities. It has simple task for database copy. It can be helpfull.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
How you want to update the SQL Server database if you don't have an INSERT or UPDATE statement?
You can also do this using Data Import/Export wizard.
0
 
dboyd02Author Commented:
Sorry guys. I had surgery last week and I am just now getting back to work. I will try both solutions today. Thank you very much for your input.

0
 
akramrykProject ManagerCommented:
Dear you may use Oracle Hetrogenous services to create a DB link betwenn your Oracle Serve and SQL Server database.

Now you can  play with PL/SQL in Oracle database to fetch any SQL server or Oracle database tables information. Also you can issue update or insert into SQL server.


0
 
dboyd02Author Commented:
Okay I added a openquery. The data does not get copied but I get the message: Command(s) completed successfully.

Thoughts?
--declare all variables used

declare @tblName varchar(150) 
declare @fields varchar(150)
declare @sql varchar (2000)

DECLARE c1 CURSOR READ_ONLY
FOR
(select TABLE_NAME from information_schema.columns where TABLE_CATALOG = 'SiteMinder_FULL' group by TABLE_NAME )


OPEN c1

FETCH NEXT FROM c1
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
	

  


  
set @fields = 'select COLUMN_NAME from information_schema.columns where TABLE_NAME = '+@tblName+''

 --set @sql = 'select '+@fields+' into ' + @tblName + ' from  OCX..SST.' + @tblName + '' 
 
 set @SQL = 'select * into ' + @tblName + ' from openquery (OCX,''select * from SST.' + @tblName + ')'''
 
exec (@sql)
print (@sql)





----first cursor
	FETCH NEXT FROM c1
	INTO @tblName

	

END

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Like I said before, you can't update data without the correct commands. You only have SELECT's.
Now that you brougth data from database what you want to do with that?
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
What do you get printed for command? Try this and you'll see why it doesn't update.

VMontalvao: Scroll down and you'll see:
"set @SQL = 'select * into ' + @tblName + ' from openquery (OCX,''select * from SST.' + @tblName + ')'''"
wich does take care of the inserts.

Try the printed command, with the same credentials and using the same database and you will find out where it errors out.

//Marten
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Aahhh, I see now :)
Thanks Martenrune
0
 
dboyd02Author Commented:
With the following code and using Print (@SQL) I do not get any errors. I get : Command(s) completed successfully.

Thoughts?
--declare all variables used

declare @tblName varchar(150) 
declare @fields varchar(150)
declare @sql varchar (2000)

DECLARE c1 CURSOR READ_ONLY
FOR
(select TABLE_NAME from information_schema.columns where TABLE_CATALOG = 'SiteMinder_FULL' group by TABLE_NAME )


OPEN c1

FETCH NEXT FROM c1
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
	

  


  
--set @fields = 'select COLUMN_NAME from information_schema.columns where TABLE_NAME = '+@tblName+''

 --set @sql = 'select '+@fields+' into ' + @tblName + ' from  OCX..SST.' + @tblName + '' 
 
 set @SQL = 'select * into ' + @tblName + ' from openquery (OCX,''select * from SST.' + @tblName + ')'''
 
--exec (@sql)
print (@sql)





----first cursor
	FETCH NEXT FROM c1
	INTO @tblName

	

END

Open in new window

0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
create a temptable with a varcharmax field, populate this with @sql, then select * from temptable.

//Marten
0
 
patriktCommented:
Your query looks OK.
As martenrune said, there is no insert without proper command.
Uncomment the EXEC and test. All tables should not exest in case you use "Select Into".
If you get an error post it there, it will show us what is wrong.
0
 
dboyd02Author Commented:
I uncommented the EXEC and ran. The tables do not exist and they were not created, but I did not get any errors??????
--declare all variables used

declare @tblName varchar(150) 
declare @fields varchar(150)
declare @sql varchar (2000)

DECLARE c1 CURSOR READ_ONLY
FOR
(select TABLE_NAME from information_schema.columns where TABLE_CATALOG = 'SiteMinder_FULL' group by TABLE_NAME )


OPEN c1

FETCH NEXT FROM c1
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
	

  


  
--set @fields = 'select COLUMN_NAME from information_schema.columns where TABLE_NAME = '+@tblName+''

 --set @sql = 'select '+@fields+' into ' + @tblName + ' from  OCX..SST.' + @tblName + '' 
 
 set @SQL = 'select * into ' + @tblName + ' from openquery (OCX,''select * from SST.' + @tblName + ')'''
 
exec (@sql)
print (@sql)





----first cursor
	FETCH NEXT FROM c1
	INTO @tblName

	

END

Open in new window

0
 
dboyd02Author Commented:
I created a temp table. No records displayed and no errors???
use SiteMinder_TEST

create table ##TempOracleTBL 
(Fieldnames varchar(max) ) 


--declare all variables used

declare @tblName varchar(150) 
declare @fields varchar(150)
declare @sql varchar (2000)

DECLARE c1 CURSOR READ_ONLY
FOR
(select TABLE_NAME from information_schema.columns where TABLE_CATALOG = 'SiteMinder_FULL' group by TABLE_NAME )


OPEN c1

FETCH NEXT FROM c1
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
	

  


  

 
 --set @SQL = 'select * into ' + @tblName + ' from openquery (OCX,''select * from SST.' + @tblName + ')'''
 
 set @SQL = 'select * into ##TempOracleTBL from openquery (OCX,''select * from SST.' + @tblName + ')'''
 
exec (@sql)
select * from ##TempOracleTBL





----first cursor
	FETCH NEXT FROM c1
	INTO @tblName

	

END

Open in new window

0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
does this query: "select TABLE_NAME from information_schema.columns where TABLE_CATALOG = 'SiteMinder_FULL' group by TABLE_NAME"
give any result?

//Marten
0
 
dboyd02Author Commented:
mmmm. It does NOT.....
0
 
dboyd02Author Commented:
Okay, I changed that select. Now I get table names and errors when I run the entire procedure.
Here are some of the errors:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'select * from SST.ACTIVITY_GROUP)'.

(0 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'select * from SST.ACTIVITY_POOL)'.

(0 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'select * from SST.ACTIVITY_POOL_ACTIVITY)'.

(0 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'select * from SST.ACTIVITY_PRICE_LIST)'.

(0 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'select * from SST.ACTIVITY_PRICE_LIST$JN)'.


0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Well, there's your problem. You shouldn't close ')' inside the string.
0
 
dboyd02Author Commented:
Okay now I get the following error after changing the  ')''' to ''')'.


Msg 2714, Level 16, State 6, Line 1
There is already an object named '##TempOracleTBL' in the database.

use SiteMinder_FULL

create table ##TempOracleTBL 
(Fieldnames varchar(max) ) 


--declare all variables used

declare @tblName varchar(150) 
declare @fields varchar(150)
declare @sql varchar (2000)

DECLARE c1 CURSOR READ_ONLY
FOR
(select TABLE_NAME from information_schema.columns where TABLE_CATALOG = 'SiteMinder_FULL' group by TABLE_NAME )


OPEN c1

FETCH NEXT FROM c1
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
	

  


  

 
 --set @SQL = 'select * into ' + @tblName + ' from openquery (OCX,''select * from SST.' + @tblName + ')'''
 
 set @SQL = 'select * into ##TempOracleTBL from openquery (OCX,''select * from SST.' + @tblName + ''')'
 
exec (@sql)
select * from ##TempOracleTBL





----first cursor
	FETCH NEXT FROM c1
	INTO @tblName

	

END

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In the end of the script you should have:
 drop table ##TempOracleTBL

Now you need to run this command alone and then rerun the script.

Good luck
0
 
patriktCommented:
Just correct ' at the end of string
set @SQL = 'select * into ##TempOracleTBL from openquery (OCX,''select * from SST.' + @tblName + ''')'
0
 
patriktCommented:
Just correct ' at the end of string
set @SQL = 'select * into ##TempOracleTBL from openquery (OCX,''select * from SST.' + @tblName + ''')'
0
 
dboyd02Author Commented:
Patrikt,

That is what I currently have in the code.
0
 
dboyd02Author Commented:
Vmontalvao,

I added that and ran that to drop the temp. I even shout down the query then opened it back up, but still get this error:
Msg 2714, Level 16, State 6, Line 1
There is already an object named '##TempOracleTBL' in the database.



use SiteMinder_FULL

create table ##TempOracleTBL 
(Fieldnames varchar(max) ) 


--declare all variables used

declare @tblName varchar(150) 
declare @fields varchar(150)
declare @sql varchar (2000)

DECLARE c1 CURSOR READ_ONLY
FOR
(select TABLE_NAME from information_schema.columns where TABLE_CATALOG = 'SiteMinder_FULL' group by TABLE_NAME )


OPEN c1

FETCH NEXT FROM c1
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
	

  


  

 
 --set @SQL = 'select * into ' + @tblName + ' from openquery (OCX,''select * from SST.' + @tblName + ')'''
 
 set @SQL = 'select * into ##TempOracleTBL from openquery (OCX,''select * from SST.' + @tblName + ''')'


exec (@sql)
select * from ##TempOracleTBL





----first cursor
	FETCH NEXT FROM c1
	INTO @tblName

	
drop table ##TempOracleTBL 

END

Open in new window

0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
before you create the temptable, add

IF OBJECT_ID('tempdb..##TempOracleTBL') IS NOT NULL
DROP TABLE ##TempOracleTBL

and at the end after select * from ##TempOracleTBL
add
drop ##TempOracleTBL

//Marten
0
 
dboyd02Author Commented:
Martenrune,
I added the following code attached.

I still get this error

Msg 2714, Level 16, State 6, Line 1
There is already an object named '##TempOracleTBL' in the database.

use SiteMinder_FULL

IF OBJECT_ID('tempdb..##TempOracleTBL') IS NOT NULL
DROP TABLE ##TempOracleTBL


create table ##TempOracleTBL 
(Fieldnames varchar(max) ) 


--declare all variables used

declare @tblName varchar(150) 
declare @fields varchar(150)
declare @sql varchar (2000)

DECLARE c1 CURSOR READ_ONLY
FOR
(select TABLE_NAME from information_schema.columns where TABLE_CATALOG = 'SiteMinder_FULL' group by TABLE_NAME )


OPEN c1

FETCH NEXT FROM c1
INTO @tblName

WHILE @@FETCH_STATUS = 0
BEGIN
	

  


  

 
 --set @SQL = 'select * into ' + @tblName + ' from openquery (OCX,''select * from SST.' + @tblName + ')'''
 
 set @SQL = 'select * into ##TempOracleTBL from openquery (OCX,''select * from SST.' + @tblName + ''')'


exec (@sql)
select * from ##TempOracleTBL
drop table ##TempOracleTBL





----first cursor
	FETCH NEXT FROM c1
	INTO @tblName

	


END

Open in new window

0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Don't loose time with that now. Just change the name to something like:
 ##Temp_Oracle_TBL
0
 
dboyd02Author Commented:
#Temp_Oracle_TBL may have worked. I am now changing the code to put into my sql tables rather than temp table
0
 
dboyd02Author Commented:
Problem solved
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.