Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
 Posted - 2013-10-15 : 08:46:47
|
| Hello,I am trying to find a way to export to a CSV from a view just the data on a row which has been inserted or updated.I would be glad of any pointersThanks |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-15 : 10:43:56
|
| Is there a rule that you can use to identify which rows have been inserted or updated? Perhaps a timestamp column, or other indication that would let you write a queryt that would return only the qualifying rows?Or, are you thinking about doing an export as part of an insert or update? |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-15 : 10:49:11
|
| Your question is very vague and covers several areas so it is difficult to know how to answer it. You should read up on the words I capitalized for a deeper understanding on any of these concepts.Regarding: "on a row which has been inserted or updated":To know which row(s) have been recently modified you would need to do one of a couple things:- add a column to the table [LastModifiedDate] which would need to be populated either by a DEFAULT or a TRIGGER or by the code you use to maintain the table.- create an AUDIT TABLE which would be populated by a trigger on your table.Regarding: "export to CSV":Search this site for "CSV" there are lots of topics on how to generate and read from csv.Be One with the OptimizerTG |
|
|
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2013-10-15 : 13:06:08
|
| Thanks GuysI am currently using a last edited datetime stamp. This works but is not perfect.James K, yes if we could do an export as part of an insert or update that would be good. To give you a bit more background it is to create CSV files which our Warehouse Management System picks up. Therefore, ideally I would like to create a file on the Stock Code which as just been updated/inserted. I currently have a view which creates the CSV file in the right layout and use BCP to export it. If it was possible to pick up the Stock Code on the line updated or inserted and put that into the where clause of the view inside the BCP string that would work. Sorry I might be going about this the wrong way and there is probably a better way of achieving what I am trying to. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-15 : 14:55:17
|
| Some thoughts:If this table is modified by users then I would not make the export part of the insert/update. That usually means via a trigger which would tie this export to the user transaction. That could make the insert take way too long and if anything failed on the export the user transaction would be rolled back. I doubt you want that.Perhaps a new table or a new column to track what has been sent to the WMS. The export process could be a scheduled sql job. That export process would send all the rows that haven't been sent yet and of course update the "what has been sent" info. You'll have to build in something to detect when the export or the WMS import process fails for any reason so that your "what has been sent" info is accurate for what has actually arrived.Be One with the OptimizerTG |
|
|
|
Kyle Doouss
Yak Posting Veteran
64 Posts |
Posted - 2013-10-16 : 04:08:37
|
| Thanks, what you say seems logical to me |
|
|
|
|
|
|
CSV export for updated/ inserted row