 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Services / DTS / July 2008After Insert trigger copy rows to a file
I would like all records inserted into a database table to be copied into a text file. (A 3rd party application will move this file and import the records into another database). I've seen how to create a trigger to copy inserted records into another table, but I have no idea how to do copy the data to a file. Any help will be much appreciated. > I would like all records inserted into a database table to be copied into a > text file. (A 3rd party application will move this file and import the [quoted text clipped - 3 lines] > table, but I have no idea how to do copy the data to a file. Any help will be > much appreciated. Hi Mark, Do you need the file to be created in realtime? Or just exported once an minute / hour / day / etc? Also, what version of SQL Server are you using? Good luck! J Thanks for the reply. It's SQL server 2000. We would probably want the records exported every 5 minutes or so, although this is not critical. Mark > > I would like all records inserted into a database table to be copied into a > > text file. (A 3rd party application will move this file and import the [quoted text clipped - 12 lines] > Good luck! > J Hi Mark, DTS should be fine for this - but I would suggest a minor change to your design... Instead of having a trigger, simply add an "InsertedDatetime" column to the table you are monitoring and set a default on the column of GETDATE(). This will allow you to extract the required data without the need for a trigger or temporary staging table. You would need some way of determining when the last set of data was extracted - either in another table or just an environment variable or something. The DTS package can simply use a SQL statement like "SELECT * FROM <table> WHERE InsertedDatetime >= <prev run datetime>" to pull the new rows and dump them straight into a flat file for you. Once the package is created, you can schedule it to run every 5 mins (or whatever) using SQL Agent Good luck! J Hi Mark, Like Jhofm says, I wouldn't use a trigger too. But in a similar situation (ie, employee bookings froma db to a flat file every 30 minutes) I use a column in the source table. There I insert/update a simple 3 state Flag instead of a date field (which can be useful indeed). Eg: 1 for 'To be processed', 2 for 'in processing' and 3 for 'processed'. Then, at the top of your DTS you would attach a Simple Execute sql task that performs an update of the status flag from 1 (every newly inserted column must have this value, this can easily be done, as stated by J., with the default value feature) to 2. And only when the file has been created or more easily at the end of your DTS create another Execute SQL task that updates this flag from 2 to 3. When you SELECT the data to be transferred you have to bear in mind to SELECT * FROM yourSourceTable WHERE StatusFlag IN (1,2) The main advantage is that if the process fails once, twice, etc, the next time it runs it will surely "pick up" all the data of the previous failed executions (that should remains flagged as 2). Sorry for bad english. Hope this helps ;) M. > Hi Mark, > [quoted text clipped - 18 lines] > Hope this helps ;) > M. I have a similar situation, however, I don't have the option of adding columns to the tables. I have created an ugly process of comparing a text file to the source table to see what is new, then spit changes to a csv, and then append that csv to the original 'master' csv. Really ugly. If there is a better way of doing this, please, please share. Thanks, Aaron > I have a similar situation, however, I don't have the option of adding > columns to the tables. I have created an ugly process of comparing a [quoted text clipped - 4 lines] > Thanks, > Aaron Hi Aaron, I miss something... can you please explain us how the process should work or what is your need? Do you need to compare a text file with a source table (source of the text file?) and then insert new records (and updated ones?) into a .csv file? Bye, M. > > I have a similar situation, however, I don't have the option of adding > > columns to the tables. I have created an ugly process of comparing a [quoted text clipped - 14 lines] > Bye, > M. M. I created a 'master' csv dump of the table. Now, once a day I check the records in the master csv against the table. Any records in the table that are not in the csv, get copied to a temp csv. A copy of the temp csv is sent to me via mail. Then the records in the temp csv are copied to the master csv. I am just trying to find a better process to get a daily update. > > > I have a similar situation, however, I don't have the option of adding > > > columns to the tables. I have created an ugly process of comparing a [quoted text clipped - 24 lines] > > I am just trying to find a better process to get a daily update. Aaron if you need to track down new/updated rows of a table you can build a trigger (ON INSERT/ON UPDATE) to copy your newly inserted/ updated row into another table. If the table has a lot of traffic during the day and/or because of trigger can slow down on-line operations, you might consider building a process that lets you know as a daily basis what changed in this table. For simplicity I'm going to consider relevant only newly inserted rows and not also updated ones. Doing so the whole can be (very) much easier... If so, let me know if this might fit your needs: [If you can create a sort of backup table in the same DB and use it to track down changes, well... that'll be great and much more convenient]. Whatever the format of the dump (table or file), I would completely refresh it every day, instead of going crazy to see what rows are changed or added. So let's say one day you'll start with the dump and the table, identical. During the day the table does grow in rows, and so doesn't the dump. At the end of the day you can run your process and in it I would: - select the differences between the dump (D) and table (T) with a outer join - copy the result into a csv and send a mail with this csv attached - rebuild the dump from the original table And so on the day after... M. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.