Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    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?

    Please help!


    Thanks,

    DB

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    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
    Last edited by PMASchmed; 01-21-09 at 13:52.

  3. #3
    Join Date
    Nov 2005
    Posts
    91
    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?


    Thanks,

    DB

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The trigger on the view is not going to fire unless your application modifies records through the view. Modifying data on the base tables will not kick off the trigger on the view.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Nov 2005
    Posts
    91
    Thanks for the clarification blindman. So I guess creating a view is definitely not the solution. Is there another way to extract only new and modified data from my view?


    Thanks,

    DB

  6. #6
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    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.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Agree with both those suggestions.
    What is the purpose of this little exercise?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2005
    Posts
    91
    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.


    DB

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is the new system built on SQL Server? You could use replication to accomplish this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •