Solved

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

Posted on 2010-09-14
30
473 Views
Last Modified: 2013-12-19
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

0
Comment
Question by:dboyd02
  • 13
  • 6
  • 6
  • +2
30 Comments
 
LVL 20

Expert Comment

by:Marten Rune
ID: 33680059
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
 
LVL 12

Expert Comment

by:patrikt
ID: 33680235
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 33680705
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
 

Author Comment

by:dboyd02
ID: 33726779
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
 
LVL 2

Expert Comment

by:akramryk
ID: 33733583
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
 

Author Comment

by:dboyd02
ID: 33734467
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 33734538
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 33734639
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 33734736
Aahhh, I see now :)
Thanks Martenrune
0
 

Author Comment

by:dboyd02
ID: 33734966
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 33735021
create a temptable with a varcharmax field, populate this with @sql, then select * from temptable.

//Marten
0
 
LVL 12

Expert Comment

by:patrikt
ID: 33735151
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
 

Author Comment

by:dboyd02
ID: 33735296
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
 

Author Comment

by:dboyd02
ID: 33735369
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 33735587
does this query: "select TABLE_NAME from information_schema.columns where TABLE_CATALOG = 'SiteMinder_FULL' group by TABLE_NAME"
give any result?

//Marten
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:dboyd02
ID: 33735647
mmmm. It does NOT.....
0
 

Author Comment

by:dboyd02
ID: 33735682
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 33735964
Well, there's your problem. You shouldn't close ')' inside the string.
0
 

Author Comment

by:dboyd02
ID: 33736012
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 33736086
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
 
LVL 12

Expert Comment

by:patrikt
ID: 33736093
Just correct ' at the end of string
set @SQL = 'select * into ##TempOracleTBL from openquery (OCX,''select * from SST.' + @tblName + ''')'
0
 
LVL 12

Expert Comment

by:patrikt
ID: 33736106
Just correct ' at the end of string
set @SQL = 'select * into ##TempOracleTBL from openquery (OCX,''select * from SST.' + @tblName + ''')'
0
 

Author Comment

by:dboyd02
ID: 33736136
Patrikt,

That is what I currently have in the code.
0
 

Author Comment

by:dboyd02
ID: 33736152
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
 
LVL 20

Expert Comment

by:Marten Rune
ID: 33736203
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
 

Author Comment

by:dboyd02
ID: 33736389
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 33736499
Don't loose time with that now. Just change the name to something like:
 ##Temp_Oracle_TBL
0
 
LVL 20

Accepted Solution

by:
Marten Rune earned 250 total points
ID: 33737205
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
 

Author Comment

by:dboyd02
ID: 33738306
#Temp_Oracle_TBL may have worked. I am now changing the code to put into my sql tables rather than temp table
0
 

Author Closing Comment

by:dboyd02
ID: 33833308
Problem solved
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now