0

I have a two table named test1 and test2 i want to move data from test1 to test2 in a way like if a condition matches update data else insert in to database.I have successfully done the oracle query which i posted down.I have to achieve two more tasks

**1>I have to move the operation in to console c# application

2>i have to remove the leading blank spaces for entries t2_fNAME and ACCOUNT_NUMBER** How i can achieve this task do i need to do ado.net c# code if so how to do it

merge into test2 a
using test1 b
   on (a.t2_NAME = b.t1_NAME)
when matched then update
  set a.t2_fNAME = b.t1_fNAME,
      a.ACCOUNT_NUMBER = b.ACCOUNT_NO,

when not matched then
insert (t2_slno,t2_NAME,t2_fNAME,ACCOUNT_NUMBER)
values (t2_NODE_SEQ.NEXTVAL, b.t1_NAME,b.t1_fNAME,b.ACCOUNT_NO);
peter
  • 8,158
  • 21
  • 66
  • 119
  • 1
    You can create a stored procedure using the above query and call that procedure from C# application. – SelvaS Apr 22 '15 at 16:24
  • How to remove leading blank space? or trim the entries – peter Apr 22 '15 at 16:47
  • @Selva TS i will do as you said but how to remove the blank spaces from the beginning of t2_fNAME,ACCOUNT_NUMBER while inserting or updating – peter Apr 22 '15 at 17:00
  • 1
    Use `TRIM` function in Oracle http://www.techonthenet.com/oracle/functions/trim.php – SelvaS Apr 22 '15 at 17:02
  • @Selva TS how to use trim while updating , i am getting error while doing like this set TRIM(' a.t2_fNAME ')= b.t1_fNAME – peter Apr 22 '15 at 17:15
  • I have posted answer. Its not tested, please modify the code according your requirement. – SelvaS Apr 22 '15 at 17:22
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/75954/discussion-between-selva-ts-and-peter). – SelvaS Apr 22 '15 at 17:26

1 Answers1

1

  1. You can create a Console application and Use ADO.Net to execute the query

  2. Use Trim Function in Oracle to remove leading blank spaces.

Here is the code (Not tested as I don't have Oracle DB)

using System;
using System.Data;
using System.Data.OracleClient;

namespace TestApp
{
    class Program
    {
        static void Main()
        {
            string connectionString = "Data Source=ThisOracleServer;Integrated Security=yes;";
            string queryString = @"merge into test2 a
                                    using test1 b
                                        on (a.t2_NAME = b.t1_NAME)
                                    when matched then update
                                        set a.t2_fNAME = TRIM(b.t1_fNAME),
                                            a.ACCOUNT_NUMBER = TRIM(b.ACCOUNT_NO),

                                    when not matched then
                                    insert (t2_slno,t2_NAME,t2_fNAME,ACCOUNT_NUMBER)
                                    values (t2_NODE_SEQ.NEXTVAL, b.t1_NAME,TRIM(b.t1_fNAME),TRIM(b.ACCOUNT_NO));";

            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                using (OracleCommand command = connection.CreateCommand())
                {
                    command.CommandText = queryString;

                    try
                    {
                        connection.Open();
                        command.ExecuteScalar();
                    }
                    catch (Exception ex)
                    {
                        //Log Exception here;
                        throw;
                    }
                }
            }
        }
    }
}

References

  1. MSDN
  2. Oracle TRIM Function
SelvaS
  • 2,105
  • 1
  • 22
  • 31
  • which is best selva , hard coding or creating stored procedure? – peter Apr 22 '15 at 17:26
  • 1
    I prefer Stored Procedure, but there are lots of discussions in net SP Vs inline queries. Refer http://dba.stackexchange.com/questions/44544/stored-procedures-vs-inline-sql http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code – SelvaS Apr 22 '15 at 17:29
  • 1
    Search in google https://www.google.co.in/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=inline%20query%20vs%20stored%20procedure – SelvaS Apr 22 '15 at 17:32
  • when i truncated table and used the above query, it is getting unique constraint violated exception ,how to prevent duplicates in above query.t2_slno is primary key here – peter Apr 22 '15 at 18:01
  • What are your primary key and unique keys here? Use the PK and unique keys for your condition. – SelvaS Apr 22 '15 at 18:06
  • t2_slno is primary key here – peter Apr 22 '15 at 18:09
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/75958/discussion-between-peter-and-selva-ts). – peter Apr 22 '15 at 18:09