6 Replies Latest reply: May 15, 2014 10:00 AM by Gareth Horton RSS

    Urgent - Delete data not table (SQL Server)

    Viv _

      I want to set up my projects (ver 7) so, that when data is exported into my SQL Server tables, the existing data is overwritten, not the table itself.

       

      In the previous version of Data Pump, if I selected 'Overwrite Existing' the existing data was replaced with the new but, in version 7, the actual table is deleted and recreated.

       

      Is there a way of configuring projects in the new version so that only the data in the table is overwritten ? I need the actual structure of the tables - Data types etc. to remain the same.

       

      It is essential that I can achieve this or I will not be able to use version 7 and will have to stick with version 6.

        • Urgent - Delete data not table (SQL Server)
          Darren _

          Viv,

           

          Within the "Table Info Screen" in MDP7 there is the "When Output Tables Exist" options to deal with this very problem (at least for me!) The option is only accessible if you selected the "Add Data to File" option on the File Info screen. Here you have the ability to Overwrite the existing table, skip, or append data to the existing table. I'm thinking you want to utilize the first option here of "Overwriting the Existing Table" where MDP overwrites the data in the existing table with the data in the export table.

           

          I have also uses the append function and find it useful, where MDP adds the export table data to the end of the selected existing table.

           

          Hope this helps you out!   [img]smile.gif[/img]

          • Urgent - Delete data not table (SQL Server)
            Viv _

            Darren - thanks very much for your reply. Unless I am doing something wrong, I think that I was doing as you suggested.

             

            When I use "Overwrite the existing table", a new table (same name) replaces my existing one, in the my SQL Server database. My SQL Server Nvarchar datatypes are changed to Char, the field lengths are changed to the field lengths in my Monarch mod file. I have other systems that use these tables and their structure must remain the same as currently.

             

            Append works fine except I need the existing records to be deleted first.

             

            I suspect that version 7 of Data Pump is using the DROP rather than the DELETE command on my existing SQL Server tables. Version 6 did use DELETE, so the table structures were left intact.

             

            I need the new version to behave the same way.

            • Urgent - Delete data not table (SQL Server)
              Lanette _

              I have not experienced what you finding, but I have not tried the "overwrite" I always do an "append" and I am using Oracle.  If this can't be fixed, another option to try before going back to DP6 is to write a simple stored procedure in SQL Server that will delete the records in the table.  You could then have you DP process call the stored procedure in a PreExport script before executing the process, then you could just append the data to the table.

               

              I have used the PostExport script part to call an Oracle PL/SQL stored procedure via executing an external command line and in an initial test it seems to be working fine.  What I do for Oracle is execute a command line that calls a .bat file I created.  The .bat file accepts an argument passed in, and calls SQL+ to execute a SQL script file.  In the SQL script file is a command to execute the stored procedure.

               

              This would probably need to be modified for SQL Server...you may have to do some searching in other posts or online to find out how to execute a SQL Server stored procedure remotely.  If you are interested though, let me knw in a private email and I'll send you my .bat and .sql file.

              • Urgent - Delete data not table (SQL Server)
                Darren _

                Lanette's comments would also be my next course of action before reverting back to MDP6. Create some sort of batch file or stored procedure within your SQL server. I have also done this before - but my database is Progress based - not SQL. I execute a file within Progress that basically overwrites the existing data within the table, but not the table format, and the table's structural integrity is kept.

                • Urgent - Delete data not table (SQL Server)
                  Viv _

                  The Data Pump server also has SQL Server installed on it enabling me to run DTS packages from Data Pump using the SQL Server command line utility, DTS Run. There is another utility called isqlw which should enable me to execute a sql statement for deleting the records directly from Data Pump, in the same way (before the append takes place).

                   

                  Thanks very much both of you, for pointing me in the right direction.

                  • Urgent - Delete data not table (SQL Server)
                    Gareth Horton

                    Viv

                     

                    This will be fixed in MDP v7.01 due in the next few weeks.

                     

                    Data Pump currently interprets the “Overwrite the existing table” option as “drop the old table and create a new one with the same name, then write the new rows”.

                     

                    This continues to be the behavior when exporting via the Jet engine, but we have changed the default behavior when exporting via OleDb. The new behavior follows a less-intrusive interpretation: specifically “Overwrite the existing table” now is interpreted as “delete any existing rows in the old table, then append the new rows”.

                     

                    This approach has the advantage that one can overwrite the table’s data without destroying the data types, additional columns, indexes, triggers, etc. that may be associated with the table.

                     

                    The new interpretation applies only to OleDb exports, and is controllable via the following registry setting

                     

                    HKEY_CURRENT_USERSoftwareDatawatchMonarch ProExportOLEDBDropTableWhenOverwriting

                     

                    This setting is a DWORD with the following meaning: 0 (the default) means don’t drop the table when overwriting (i.e., the new behavior), non-0 means drop the table when overwriting (i.e., the old behavior). This setting affects all OleDb exporting, regardless of the target provider.

                     

                    This entry only needs to be created if you need to set the old Drop behavior.  If the setting is not present, then Data Pump opts for the delete approach.

                     

                    It should be noted that this introduces the possibility that an “Overwrite the existing table” export may fail. This can happen if the target table exists but has a structure that is incompatible with the fields being exported. When this happens you’ll get the same error that you would have gotten had the export option been “Append to the existing table”.

                     

                    Gareth

                     

                    Originally posted by Viv:

                    I want to set up my projects (ver 7) so, that when data is exported into my SQL Server tables, the existing data is overwritten, not the table itself.

                     

                    In the previous version of Data Pump, if I selected 'Overwrite Existing' the existing data was replaced with the new but, in version 7, the actual table is deleted and recreated.

                     

                    Is there a way of configuring projects in the new version so that only the data in the table is overwritten ? I need the actual structure of the tables - Data types etc. to remain the same.

                     

                    It is essential that I can achieve this or I will not be able to use version 7 and will have to stick with version 6. /b[/quote]