Unanswered: How to Extract a View into a Flat File
I have a view that I created in SQL Server 2000 that I need exported into a pipe delimited ascii file. This is the first time I have performed something like this. Can someone please help me with the logic that I need to follow to perform the extraction.
My plan was to create a trigger on my view that inserts any new rows or updated rows into another table that I created. I was then planning on creating a DTS Package to select everything in my temp table (updated by trigger on my view) and then export the data into a flat file. Once the data is exported I would delete all the record in my temp table.
Is what I am planning above the best way to perform the extraction? I created an 'instead of update' trigger on my view but it doesn't seen to be updating records into my temp table when I modify some of the fields through my app. Am I using the correct type of trigger?
DTS job with a
Connections Properties object
Text File (Destination)
and a transform data task, you can set up delimiters in the Text File and simply put the select query from the view in the transform data task.
or you can BCP out, from command line, to a pipe delimited file
bcp databasename..viewname out filename.txt -Uuser -Ppassword -SServer -t"|" -c
Thanks for the procedural advise. The problem that I have conceptualizing is with extracting only new data from the view that I created. I tried accomplishing this with an insert trigger on my view that insert new and modified records into a new table that my DTS job would query and then delete but it looks like I can't create insert and update trigger on a view? Can you suggest another way that I can extract only new and updated records from my view?
You can still create your trigger, but create the trigger on the base table and not the view.
Another idea would be to create a datetimestamp column in your base table which will hold the date and time the row was inserted or updated. Then you can modify the view to select only the data in the specified date range.
What I'm trying to do is feed a new system that I am trying to implement with data from another system through the use of ascii files. I'll try working on solutions using your suggestions. Thanks to everyone for their input.