0

I'm trying to replicate in C# the following simple SQL statement:

INSERT INTO T1 SELECT * FROM T2;

One thing to notice here is that T1 and T2 are not only in different schemas and DB's, but also in different servers, and dblinks are NOT an option.

All the documentation and examples I've found online use explicit column names, which I don't know at design time (I rely on the caller of my script to make sure T1 and T2 are compatible; if not, I just return an error).

Here is the basic code I've been playing around with; the reading part works just fine, but the writing part doesn't work at all (no exceptions thrown, the execution completes fine but T2 doesn't get populated):

        OracleDataAdapter od = new OracleDataAdapter();

        tr.SourceConnection.Open();
        OracleCommand selectCommand = BuildCommand(tr.SourceConnection, string.Format("SELECT * FROM {0}", tr.SourceTable));
        od.SelectCommand = selectCommand;
        DataTable srcTb = new DataTable(tr.SourceTable);
        od.Fill(srcTb);
        tr.SourceConnection.Close();

        // Everything works well up to here, so "SELECT * FROM {0}" seems to be the right query

        tr.TargetConnection.Open();
        DataTable tgtTb = new DataTable(tr.TargetTable);
        OracleCommand insertCommand = BuildCommand(tr.TargetConnection, string.Format("INSERT INTO {0}", tr.TargetTable));
        od.InsertCommand = insertCommand;
        od.Update(tgtTb);
        tr.TargetConnection.Close();

        // The data doesn't get pushed to the DB table; what do I need to use instead of "INSERT INTO {0}"?
        // Or do I need a totally different approach?

Thanks a lot in advance!

davidfg
  • 1
  • 2
  • When you say "The first step works well", I assume that means that your source DataTable is not empty? Secondly, what are you inserting? – Peter Abolins Oct 03 '17 at 05:49
  • You know what the SQL is supposed to look like: `INSERT INTO T1 SELECT * FROM T2;`. But your `insertCommand` looks like this: `"INSERT INTO {0}"`. I don't know enough about c# to understand why that doesn't throw an exception but it seems fairly obvious what you need to fix. – APC Oct 03 '17 at 06:02
  • Although it does seem likely that you are trying to reinvent `SqlBulkCopy` like [this other SO thread](https://stackoverflow.com/a/20108861/146325) – APC Oct 03 '17 at 06:03
  • My second question above was rhetorical. There is no connection between the data you read from the source table, and the code to insert data into the target table. One option would be to use the `ImportRow` method on the target datatable, to copy from your source datatable to your target datatable before updating. – Peter Abolins Oct 03 '17 at 06:06
  • Yes, the DataTable gets populated just fine. As for your second question, I just want to insert everything from the DataTable into the Oracle DB table without having to explicitly list the columns (i.e. insert into foo select * from bar). – davidfg Oct 03 '17 at 06:07
  • @APC The question relates to `oracle`. `SQLBulkCopy` is a SQLServer function only. – Peter Abolins Oct 03 '17 at 06:07
  • @APC: is that thread applicable to Oracle too? If that's the case, I'll take a look. Thanks! – davidfg Oct 03 '17 at 06:10
  • This might help: https://forums.asp.net/t/1883597.aspx?Is+sqlBulkCopy+possible+in+oracle+ – Peter Abolins Oct 03 '17 at 06:11
  • @PeterAbolins: so there is no way to simply "dump" a table in memory into a table in the DB without going through some "massaging" first? – davidfg Oct 03 '17 at 06:12
  • @PeterAbolins - yes you're right. davidfg - Oracle does provide a library, ODP, which seems to provide similar functionality. [Check it out](https://docs.oracle.com/cd/E11882_01/win.112/e23174/bulkcopy.htm#ODPNT0021) – APC Oct 03 '17 at 06:14
  • I have not personally done anything like what you are trying to do. I have used `SQL Loader` which inserts into an Oracle DB from external files. That is fast, but doesn't match your situation. The ImportRow method obviously requires you to iterate over the source data. How much data is in T1? – Peter Abolins Oct 03 '17 at 06:24

0 Answers0