Dynamic SQL – Export Data

As specified in the previous post, the method we use to write SQL queries so that they are dynamically built alongside application actions is known as “dynamic SQL.”

In other words, With dynamic SQL, you can create software that makes use of SQL statements whose entire text won’t be known until runtime. Dynamic SQL allows for the creation of flexible SQL queries, and when an application is executed, the names of any variables or other parameters are passed.

Below is another example of Dynamic SQL which will help in exporting data from SQL Server to flat-file (CSV/TXT).

DECLARE @OutputFilePath nvarchar(max);
SET @OutputFilePath = ‘C:\Users\Dell\Downloads’
DECLARE @ExportSQL nvarchar(max);
SET @ExportSQL = N’EXEC master.dbo.xp_cmdshell ”bcp “SELECT * FROM dbSchoolProject.dbo.tbStudentDetails ORDER BY 1” queryout “‘ + @OutputFilePath + ‘\testdata.txt” -T -c -t -S DESKTOP-B7JOQEE”’
EXEC(@ExportSQL)

Notes:

• Do not forget to specify the database name and database owner along with the table name.
• DESKTOP-B7JOQEE is one of the machines in which the SQL Server is running. Hence change it accordingly.
• The output file path should be accessible by SQL Server. Provide necessary (read and write) permissions.
• The file should exist in the specified folder.
• If xp_cmdshell is disabled in your machine, then enable it using the following commands.

EXEC sp_configure 'show advanced options', '1'
RECONFIGURE
-- this enables xp_cmdshell
EXEC sp_configure 'xp_cmdshell', '1' 
RECONFIGURE

Hope you find this article helpful.