Performing Data Transformation using stored Procedure

  • I regularly receive dbf(dbase III) /Text files(fixed size records) from various locations which I need to add to my central SQL Server database. Sometimes(and many times) the same data is sent again and again mixed with new data. For example, data from 01/04/2005 to 15/04/2005 and again from 07/04/2005 to 20/07/2005.

    So I can not use DTS Inport/Export Wizard, as it will only insert records and will not check for the presence of records and accordingly update it.

    Hence, I need to write a stored procedure.

    How to write a stored procedure to do this, which command/s are to be used.

    (It was very easy in Sybase SQLAnywhere, import ....file into.... format etc.etc.)

    Regards,

    Dilip Nagle

  • Use DTS to import into a staging table. The run appropriate insert/update/delete T-SQL statements to update the production table.

    For inserts, you would left join from staging to production and insert records that don't exist in production.

    For updates, you have an inner join between the tables and update where the fields aren't equal.

    For deletes, you the reverse join for inserts and delete where records aren't in staging.

     

    --------------------
    Colt 45 - the original point and click interface

  • Please use following link and do store proceture

     

    http://www.sql-server-performance.com/rd_delete_duplicates.asp

     

     

    Regards

    shashank


    Regards,

    Papillon

  • After running into same scenario (insert only) I went to a DDQT (Data Driven Query Task). Then after finding I needed an identifier on each record to determine insert/update, which I didn't,  I worked out using a call with key data fileds to a function that looked up the record to determine db action. My import files are not large so pings on DB aren't signifigant.

    '**********************************************************************

    '  Visual Basic Transformation Script

    '************************************************************************

    '  Copy each source column to the destination column

    Function Main()

     Dim dbAction

     DTSDestination("Department") = DTSSource("Department")

     DTSDestination("BMPaid") = DTSSource("BMPaid")

     DTSDestination("BMWrkd") = DTSSource("BMWrkd")

     DTSDestination("per_no") = DTSSource("per_no")

     DTSDestination("rpt_per_yr") = DTSSource("rpt_per_yr")

     .

     .

      

     dbAction = get_dbAction(DTSSource("Department") , DTSSource("rpt_per_yr"),  DTSSource("per_no"))

    Select Case UCase(dbAction)

      Case "UPDATE"

       Main = DTSTransformstat_UpdateQuery

       

      Case "INSERT"

       Main = DTSTransformstat_InsertQuery

      Case "ERROR"

       '

     End Select      

    End Function

    Function get_dbAction(d,y,p )

     dim cs , db , rs ,sql

     

     cs = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=<catalog>;Data Source=<DataSource>"

     

    sql = "SELECT * FROM <table> where department = '" & d &  "' And rpt_per_yr = " & y & " And per_no = " & p 

    set db = CreateObject("ADODB.Connection")

    db.Open cs

    set rs = db.Execute(sql)

    if (rs.EOF = True) or (rs.BOF = True) then

            get_dbAction =  "INSERT"

    else

            get_dbAction =  "UPDATE"

    end if

    rs.Close

    db.Close

    set rs = nothing

    set db = nothing

    End Function

    Maybe this is a way you can go.

    Randy.

     

  • You are aware that using the Data Driven Query task makes the Datapump work in row-by-row mode?

    It would be much more efficient to bulk-load the data straight into a staging table and then use standard T-SQL insert/update statements to process the data in a set based method.

     

    --------------------
    Colt 45 - the original point and click interface

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply