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