0

I wanted to insert into certain column in SQL Server from Oracle. It shows error (always the last column choose error with : invalid identifier). Can guide me on how to insert some values into a SQL Server table from Oracle?

INSERT INTO acc_trx_pymt_noti_ack@mssql (ATPNA_ERN, ATPNA_Status, ATPNA_Err_Desc, ATPNA_Create_Dt)
   SELECT 
       ord_key, truck_id, create_by, create_dt
   FROM 
       ORD_PYMT;

p/s: it works when I insert all values into SQL Server table

BEGIN
   FOR rec IN (SELECT ord_key, truck_id, create_by, create_dt, sysdate, backup_truck FROM ORD_PYMT)
LOOP
  INSERT INTO "acc_trx_pymt_noti_ack"@mssql
  VALUES   rec;
END LOOP;

COMMIT;
END;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jumajiSis
  • 11
  • 5
  • In the second example, you're using a case-sensitive identifier for the table name. If that works, the first example which does not double quote the table name won't work. I'd expect it to raise an ORA-00942 error. I'm guessing that this is a transcription error, though. I'm also guessing that whichever column you're getting an error with is specified incorrectly-- either the name is wrong, it's not quoted, the casing doesn't match SQL Server, etc. – Justin Cave Aug 12 '15 at 04:10
  • hi justin, the naming is correct. the case sentitive is all the same. – jumajiSis Aug 12 '15 at 06:16
  • The identifier `acc_trx_pymt_noti_ack@mssql` is completely different than the identifier '"acc_trx_pymt_noti_ack"@mssql`. In Oracle, double-quoted identifiers are case sensitive. Non-quoted identifiers are not. They can't both work (or at least they can't both point at the same object) – Justin Cave Aug 12 '15 at 14:34
  • I even change acc_trx_pymt_noti_ack@mssql to double-quoted identifier. I have added double-quoted or removed. The error still there. the error still ATPNA_Create_Dt: invalid identifier – jumajiSis Aug 13 '15 at 02:36
  • describe the table (i.e. run `desc <>`). My guess is that you have a quoted (or non-quoted) identifer issue with that column as well.
     – Justin Cave Aug 13 '15 at 02:43
  • Justin, is my first syntax correct? to insert into sql server from oracle. – jumajiSis Aug 13 '15 at 02:58
  • i have remove the quoted and add quoted to tried. but it still don't work. – jumajiSis Aug 13 '15 at 02:59
  • If you're getting an ORA-00904 error, I would guess that the first block's syntax error is that the column isn't correct. My guess is that the column that isn't working needs to be double quoted. If the column names and table name was correct, the first syntax should work. If you edit your question to include the output of the `describe`, we can do more than guess. – Justin Cave Aug 13 '15 at 03:01
  • I even added double quoted on the column that isn't working. but still not working. – jumajiSis Aug 13 '15 at 03:07
  • Post the output of `describe`. The column name you're using isn't right. I would guess that's it's just a matter of case sensitivity. But that's just a guess without more information. – Justin Cave Aug 13 '15 at 03:08
  • Thanks. I have found the solution already. Needed to put all double-quoted. – jumajiSis Aug 13 '15 at 03:08
  • Cool. You may want to look at this http://stackoverflow.com/questions/563090/oracle-what-exactly-do-quotation-marks-around-the-table-name-do/563126#563126 for more background on the general issue – Justin Cave Aug 13 '15 at 03:10
  • Thanks for the help from Justin Cave, I would like to shared the solution with you guys regarding the method to insert some values from oracle into sql server. BEGIN FOR rec IN (SELECT ord_key, truck_id, create_by, create_dt FROM ORD_PYMT) LOOP INSERT INTO "ACC_TRX_PYMT_NOTI_ACK"@mssql ("ATPNA_ERN", "ATPNA_Status", "ATPNA_Err_Desc", "ATPNA_Create_Dt") VALUES (rec.ord_key, rec.truck_id, rec.create_by, rec.create_dt); END LOOP; COMMIT; END; / – jumajiSis Aug 13 '15 at 03:52

0 Answers0