0

Can someone help point me in the write direction. I want to schedule this query to run and out put to a csv. How can I adapt the query to output to a csv?

SELECT d.result1
FROM
  (SELECT COUNT(*) AS result1
   FROM
     ( SELECT test.dbo.OrderedDocuments.UserDocumentID,
              test.dbo.OrderedDocuments.OrderGroupID,
              test.dbo.OrderGroups.TimePlaced
      FROM test.dbo.OrderedDocuments
      INNER JOIN test.dbo.OrderGroups ON test.dbo.OrderedDocuments.OrderGroupID = test.dbo.OrderGroups.OrderGroupID
      WHERE test.dbo.OrderGroups.TimePlaced >'2018-05-17') mySubQuery) d
Andy K
  • 4,944
  • 10
  • 53
  • 82
James Harding
  • 69
  • 1
  • 8
  • What DBMS? What layout of CSV? What example input data, current output. and desired output? Right now it looks like you want a CSV with one column, which isn't really a CSV, is it? – underscore_d May 18 '18 at 08:28
  • Using Microsoft SQL Managment Studio 2016. I just need the result in a comma delimited CSV in order that I can ingest into a digital dashbard. – James Harding May 18 '18 at 08:29
  • What result? `d.result1` is a single column, so there's nothing to separate, was my (pedantic?) point. – underscore_d May 18 '18 at 08:30
  • OK. The full query uses CROSS JOIN's to gather the same information from multiple databases. Is then displays the results for each database in a column. Currently I run this in SQL Managment Studio, I get a column for each database telling me the order total for a given time period. Just looking to automate this a litte. Thanks for you help – James Harding May 18 '18 at 08:35
  • Possible duplicate of [Export query result to .csv file in SQL Server 2008](https://stackoverflow.com/questions/3169220/export-query-result-to-csv-file-in-sql-server-2008) – tukan May 18 '18 at 08:44
  • 1
    Use SQLCMD utility and SQL Server Agent to automate this https://learn.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-2017 – Denis Rubashkin May 18 '18 at 08:57

1 Answers1

0

You can do this by creating a SSIS package and schedule it.

  1. Right click database you are using > Tasks > Export Data
  2. Check server, authentication, and database details are correct > Next
  3. Select Flat File Destination as the destination, and enter the file name of the csv file you wish to output to > Next
  4. Choose the second option, to write a query > Next
  5. Enter the query > Parse to ensure the query is valid > Next
  6. Click Preview to ensure the data is what you expect > Next
  7. Click the 'Save SSIS Package' checkbox > Select where you want to save it
  8. Give the file a name> Next
  9. Double check the package > Finish

Use this link for reference on how to schedule a job using SQL Server Management Studio

Then Just Schedule the Job, SQL Select SIS Package you just created.

Tanveer Singh Bhatia
  • 426
  • 2
  • 8