Search code examples
sqlsql-serverdynamicbcp

SQL Server : Dynamic Queries and String Separator

I am looking for a tip of when to use the string separator for dynamic queries. In my case I am creating a dynamic query for the bcp utility.

With a basic query I manage it to make it work but as the query gets more complex the dynamic query / bcp is not working.

For instance, I have the following query that is working Ok:

SELECT @bcp = 'bcp "select COUNT(''Id'') as NumberOfIds from [kat].[dbo].[DataId] union all select  null" queryout "D:\Testing\Test.csv" -T -c -t;'

However, the moment I add more lines I cannot make it work anymore. The following query is not working: 

SELECT @bcp = 'bcp "SELECT
  ("select  COUNT(''Id'')  FROM  [kat].[dbo].[DataId] )", 
  ("select COUNT( ''Name'')  FROM  [kat].[dbo].[Person])",
  ("select COUNT(''Surname'')  FROM  [kat].[dbo].[FamilyName]")" queryout "D:\Testing\Test.csv" -T -c -t;'

Many thanks in advance,

Kat

Solution

  • Your query should be in single line. Multiline queries not accepting.

    SELECT @bcp = 'bcp "SELECT ("select  COUNT(''Id'')  FROM  [kat].[dbo].[DataId] )", ("select COUNT( ''Name'')  FROM  [kat].[dbo].[Person])", ("select COUNT(''Surname'')  FROM  [kat].[dbo].[FamilyName]")" queryout "D:\Testing\Test.csv" -T -c -t;'