 | |
After Insert trigger copy rows to a file
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#61
 
| |||
| |||
|
Re: After Insert trigger copy rows to a file -
07-31-2008
, 11:10 AM
|
On Jul 22, 5:47 am, matteus <matteog... (AT) gmail (DOT) com> wrote: 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 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. 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. |
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.
#62
| |||
| |||
|
|
On Jul 22, 5:47 am, matteus <matteog... (AT) gmail (DOT) com> wrote: 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 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. 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. |
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.
#63
| |||
| |||
|
|
On Jul 22, 5:47 am, matteus <matteog... (AT) gmail (DOT) com> wrote: 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 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. 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. |
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.
#64
| |||
| |||
|
|
On Jul 22, 5:47 am, matteus <matteog... (AT) gmail (DOT) com> wrote: 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 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. 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. |
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.
#65
| |||
| |||
|
|
On Jul 22, 5:47 am, matteus <matteog... (AT) gmail (DOT) com> wrote: 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 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. 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. |
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.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.