SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 CSV export for updated/ inserted row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 10/15/2013 :  08:46:47  Show Profile  Reply with Quote
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 pointers

Thanks

James K
Flowing Fount of Yak Knowledge

3659 Posts

Posted - 10/15/2013 :  10:43:56  Show Profile  Reply with Quote
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?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/15/2013 :  10:49:11  Show Profile  Reply with Quote
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 Optimizer
TG
Go to Top of Page

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 10/15/2013 :  13:06:08  Show Profile  Reply with Quote
Thanks Guys

I 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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 10/15/2013 :  14:55:17  Show Profile  Reply with Quote
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 Optimizer
TG
Go to Top of Page

Kyle Doouss
Yak Posting Veteran

United Kingdom
53 Posts

Posted - 10/16/2013 :  04:08:37  Show Profile  Reply with Quote
Thanks, what you say seems logical to me
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000