June 9, 2010 at 5:44 am
Hi All,
I am using oracle 9.2.0.7 and SQL server 2008 in windows server 2003.
My requirment is like below.
1) I would liek to insert data from the Oracle database to SQL Server.
For the above requirement we have created the ODBC, then configure the listener file, tns file, and created the db link. This part is working fine as I am able to select the data from the SQL Server.
But when I am inserting the record from the oracle database to the SQL Server I am getting the error, (ORA-00904) Here oracle can not identified the column name.
My insert statement is like below.
insert into test@SQLTEST (name, fullname)
values ('Oracle', 'Oracle server data');
Test is the SQL server table name and sqltest is the db link name.
If I am select the data
select * from test@SQLTEST from Oralce I am getting the data properly.
Thanks,
SUN
June 29, 2010 at 11:08 am
Did you find any solution for this? I too am in need for this solution.
Thanks,
June 29, 2010 at 11:17 am
rkpuhan (6/9/2010)
My insert statement is like below.insert into test@SQLTEST (name, fullname)
values ('Oracle', 'Oracle server data');
Test is the SQL server table name and sqltest is the db link name.
If I am select the data
select * from test@SQLTEST from Oralce I am getting the data properly.
Thanks,
SUN
Try this way...
insert into MyLocalTable(name,fullname) select name,fullname from test@SQLTEST
Don't have table schemas so please check column names.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 29, 2010 at 11:22 am
Paul,
In the below command:
insert into MyLocalTable(name,fullname) select name,fullname from test@SQLTEST
what is MylocalTable? Is this table to be created on Sql server ?
Thanks,
June 29, 2010 at 11:32 am
damodar.kamat (6/29/2010)
Paul,In the below command:
insert into MyLocalTable(name,fullname) select name,fullname from test@SQLTEST
what is MylocalTable? Is this table to be created on Sql server ?
MyLocalTable is the local, pre-created, empty SQL Server side table.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 29, 2010 at 12:07 pm
Paul,
In the below insert that we are trying to do:
insert into test@SQLTEST (name, fullname)
values ('Oracle', 'Oracle server data');
- test@sqltest is a table "test" on sql server.
This gives error when we execute the above insert command in ORACLE.
Please suggest how do we do this insert.
Regards,
June 30, 2010 at 1:04 am
Check how the data types are mapped and then use SQL Server mingration assistance tool.
"More Green More Oxygen !! Plant a tree today"
June 30, 2010 at 9:31 am
damodar.kamat (6/29/2010)
insert into test@SQLTEST (name, fullname)values ('Oracle', 'Oracle server data');
- test@sqltest is a table "test" on sql server.
Does
select name, fullname from test@SQLTEST
work? (Even if it returns 0 rows.) Are name and fullname the exact physical column names in SQL Server? I wonder if it's case sensitivity, which you can force using double quotes, e.g.
insert into "Test"@SQLTEST ("Name", "FullName")
values ('Oracle', 'Oracle server data');
HTH
June 30, 2010 at 11:14 am
Thanks a lot, the "" idea worked......
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply