October 23, 2007 at 1:31 am
I have a database which contains around 140 tables. Each contains an average record count of 1000. I will download flat files from mainframe which will be stored under the name of the corresponding table name as its file name. This flat files will have some changes like,
1. New records may be added.
2. one or more records may be deleted.
3. one or more records may be modified.
I want to reflect only these changes in my old table, rest of the records should be kept as it is. What shall i do now. It is a tedious job to delete all the records from the table and then loading this new flat file in to it. Some friends told me to use configuration table as i am new to SQL Server i do not have much knowledge in it.
Thanking in Advance,
Sarvan
October 23, 2007 at 1:42 am
try to implement DTS package for pulling the records / editing or modifiying the records of the existing table in the databases. Or try to use SSIS for this kind of scenario. Better take help of some body who is good in sql for implementing this stuff.
October 23, 2007 at 2:56 am
Hi thanks for your response. I first used SSIS package to export the flat file data in to the table and i have successfully implemented. But now i have some changes in those flat files which is newly downloaded so i have to reflect only those changes in the table. So i think we cannot use DTS or SSIS packages. In case if i am wrong correct me.
Sarvan.M
October 23, 2007 at 3:12 am
In this case, better create a temp table so as to update the records as when the correction / modification is done to the source file. Then, dump the source to the destination initially to the temp table and ensure that the values appearing on the same table is correct/wrong. Then using DTS/SSIS move the REAL data to the main table. Since, you are saying oftenly the corrections has to be carried over to the source table, thats the reason I'm much more specific about temp table. This will work for your scenario.
October 23, 2007 at 4:16 am
I have given in my post that i am downloading the flat files from mainframe, this downloading is done every month so the newly downloaded files may have been updated. I have given 140 files for one application but totally i have around 700 files.
I am confused while reading your reply, "better create a temp table so as to update the records as when the correction / modification is done to the source file". How do i know the correction/ modification in flat files. Treat Mainframe as a front-end here. So i cannot track the changes made in the mainframe, thats why i am asking how can i know these changes and reflect this in my table.
October 23, 2007 at 10:06 pm
To answer your question "How do i know the correction/ modification in flat files?"
Use the BINARY_CHECKSUM(*) function, you will need to work through how to "stage" the data. But the process can be used for all tables once you have defined it.
Cheers
October 24, 2007 at 1:32 am
BINARY_CHECKSUM(*) is used with the tables in the SQL Server, but i want to know the changes between the new flat file of that particular table and the records which are sored already in the table. How will i use this BINARY_CHECKSUM(*) function here to meet my requirement.
For clear understanding i have given my flat file record (first column of each row is set to bold)
*******************************************************************
ACCTNGBORIS JOURNALENTRYHEADER JEN 001
ACCTNGBORIS JOURNALENTRYLINES TEST 00120070814 BORIS 20070814 BORIS
ACCTNGDTRIP JOURNALENTRYHEADER JOURNAL ENTRY 001
PROCURBORIS ORDERGOODSCATALOGSEARCH O 00120070430 BORIS 20070430 BORIS
PROCURFOUTZG ORDERGOODSCATALOGSEARCH SAFL ORDER TITLE 001
PROCURFOUTZG ORDERGOODSCATALOGSEARCH SAFL ORDER TITLE 002
PROCURFOUTZG ORDERGOODSCATALOGSEARCH SAFL ORDER TITLE 003
*******************************************************************
I have stored these data in to the table. When i download these same file after a month i might have changes in it. Say for example 4th row and 5th row might be deleted and 2nd row might be updated and 5 rows has been newly added.
I want only these changes to be made in my table is it possible using the BINARY_CHECKSUM(*) function. The changes are given in a temp table with row values as 1 and 2. I cannot manually check these changes and update my table since i handle a large volume of data and multiple tables.
October 24, 2007 at 7:18 am
the easy way to do this is to write a generic "load" process that will load all your files into corresponding tables, then use a checksum compare as previously suggested. You don't need 140 load processes, only one that is flexible.
Another way would be to use a file comparison utility. Keep an old version of each file and use the utility to compare the files and write any changes to a new "delta" file. This approach is problematic if you have some "updated" rows, or if any new rows get "inserted" into the middle of the file instead of appended at the end.
I think you are asking for a way to do a SQL-like comparison to a flat file, and unfortunately I think the best answer to your question is to load each table into a staging table first.
You could also write a script in whatever scripting language you want that would read the file, look at each row, and then compare it to the database, but given the volumes you are talking about I'm guessing it would be easier AND faster to stage all the data first. 140 tables x 1000 rows is only 140,000 rows. You can then write a generic load process and a generic compare process (look into using property expressions to set the SQL query of an Execute SQL task, or perhaps set variables and pass them in as parameters to your task).
October 24, 2007 at 2:47 pm
Try using the slowly changing dimension method in SSIS. Sounds like this does exactly what you need.
P.S. You can also use this to get a record of how many updates / inserts you did.
October 25, 2007 at 1:08 am
You have already loaded your database from the flat files once.
Assuming the database files don't change, truncate all the tables and then re-run the process to load the database.
The truncate will be fast, and you already know how long the database load will take.
Don't do this if the database tables are updated:)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply