0

I am writing a query in SQL Server that its output a table.

I want the query result delimiter to be #|#, how can I do that?

So if I output it to txt/csv file, it should look like this:

Output result

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Python Kfir
  • 3
  • 3
  • 5
    SQL Server doesn't output to CSV (files); so define in what ever your ETL layer is that the delimiter is `#|#`. How you do that completely depends on the application you're using for your ETL; you haven't told us what that is. – Thom A May 26 '22 at 13:37
  • Queries return results sets, not tables, and have no delimiters. Client tools display the results using grids or text, or saving the results to text files like CSVs – Panagiotis Kanavos May 26 '22 at 13:40
  • If you use Python, you can use Pandas to load query results and save it as CSV. In all supported SQL Server versions (ie 2017 and later) you can execute Python scripts through the `sp_execute_external_script` stored procedure – Panagiotis Kanavos May 26 '22 at 13:43
  • Thanks for both. For now I just want to it inside the SSMS if it will work I will move it to app. there is any way to that ? using command or something? – Python Kfir May 26 '22 at 13:48
  • The Import/Export Wizard in SSMS can export results as text and allows you to specify delimiters. The wizard actually generates an SSIS package that can be saved and executed again, eg on a schedule. It can also be edited using SQL Server Database Tools, a separate download – Panagiotis Kanavos May 26 '22 at 13:54
  • What does `#|#` get you that `|` doesn't? Why add 3 bytes per column when 1 byte will do? – Stu May 26 '22 at 14:59

1 Answers1

0

If you want your query to be run in an app then you could use a simple query such as

declare @demo table ([name] nvarchar(30), [lastname] nvarchar(50));
insert into @demo([name],[lastname]) values
('Catherine','Aragorn'),
('Anne','Boleyn'),
('Jane','Seymour'),
('Anne','Cleeves');

select concat([name],'#|#',[lastname])
from @demo;

Or You can export results to a csv using csv.py using #|# as the delimiter - see CSV File Reading and Writing

CHill60
  • 1,180
  • 8
  • 14