Use parameter value in .csv output filename

  • Table_1

    ESTAB_IDESTAB_CODEFORENAMESURNAME

    19009001JohnJones

    19009001MikeSmith

    19009001MaryYates

    29009005AnnEnnis

    29009005KellyHomes

    39009014HarryBrand

    39009014JamesCasey

    39009014ChrisBalls

    Table_1 contains a list of people belonging to different establishments. The ESTAB_ID is a unique sequential identifier. The ESTAB_CODE is an integer value and not sequential. I have written the following to extract subsets of the table (by ESTAB_CODE) and output them as .csv files.

    DECLARE @ESTAB_CODE VARCHAR (7)

    SET @ESTAB_CODE =

    (SELECT ESTAB_CODE FROM TABLE_1 WHERE ESTAB_ID = 1 GROUP BY ESTAB_CODE)

    SELECT

    [ESTAB_CODE]

    ,[first_name]

    ,[last_name]

    FROM TABLE_1

    WHERE ESTAB_CODE = @ESTAB_CODE

    :OUT C:\FOLDER\9009001.csv

    I set SQLCMD mode to 'ON'.

    I manually change the ESTAB_ID number in the SET line to produce the different subset tables - I am happy to do that at this stage.

    I get the correct output in the correct destination folder in .csv format.

    ESTAB_CODEFORENAMESURNAME

    9009001 John Jones

    9009001 Mike Smith

    9009001 Mary Yates

    My problem is the filename for the .csv file. I want to use the ESTAB_CODE number for the filename and have to imput it manually. Is there any way this filename can be changed automatically to the current ESTAB_CODE using the @ESTAB_CODE parameter value when I execute the code?

    I am very new to SQL having spent many years in a SAS environment so tend to expect SQL to work in a similar way - which is causing me problems!!

    Any assistance would be much appreciated.

  • Use sqlcmd variables. Here are some examples:

    http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/3031a9fd-f3de-4482-b8f6-67f033300ae8/

    http://msdn.microsoft.com/en-us/library/ms188714.aspx

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths
  • OK, here's a start. I'm sure the gurus will find a way to maximize this such as getting rid of the WHILE loop. I tried to do it without a loop but had spent too much time on it already so took the easy way out.

    I use my own sample data and tables here (actually the test results table from Jeff Moden's DelimitedSplit8K Splitter Test BTW) but I think it will be easy to convert to your schema. This code uses XP_CmdShell and BCP so you may need to add some additional procedures and permissions which I provide below.

    First some sample data:

    USE LocalTestDB --use your db of course

    CREATE TABLE [dbo].[TestResults](

    [RowNum] [int] IDENTITY(1,1) NOT NULL,

    [SplitterName] [varchar](50) NULL,

    [NumberOfRows] [int] NULL,

    [NumberOfElements] [int] NULL,

    [MinElementLength] [int] NULL,

    [MaxElementLength] [int] NULL,

    [Duration] [decimal](9, 5) NULL,

    [MinLength] [int] NULL,

    [AvgLength] [int] NULL,

    [MaxLength] [int] NULL,

    [Run] [int] NULL,

    PRIMARY KEY CLUSTERED

    (

    [RowNum] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET IDENTITY_INSERT [dbo].[TestResults] ON

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (1, N'Split', 1000, 1, 1, 10, CAST(0.01300 AS Decimal(9, 5)), 1, 5, 10, 1)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (2, N'DelimitedSplit8K', 1000, 1, 1, 10, CAST(0.01000 AS Decimal(9, 5)), 1, 5, 10, 1)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (3, N'tvfDelimitedSplitXML1', 1000, 1, 1, 10, CAST(0.44000 AS Decimal(9, 5)), 1, 5, 10, 1)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (4, N'Split', 1000, 2, 1, 10, CAST(0.03600 AS Decimal(9, 5)), 3, 11, 21, 2)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (5, N'DelimitedSplit8K', 1000, 2, 1, 10, CAST(0.40000 AS Decimal(9, 5)), 3, 11, 21, 2)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (6, N'tvfDelimitedSplitXML1', 1000, 2, 1, 10, CAST(0.41000 AS Decimal(9, 5)), 3, 11, 21, 2)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (7, N'Split', 1000, 4, 1, 10, CAST(0.07300 AS Decimal(9, 5)), 9, 24, 41, 3)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (8, N'DelimitedSplit8K', 1000, 4, 1, 10, CAST(0.03600 AS Decimal(9, 5)), 9, 24, 41, 3)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (9, N'tvfDelimitedSplitXML1', 1000, 4, 1, 10, CAST(0.47300 AS Decimal(9, 5)), 9, 24, 41, 3)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (10, N'Split', 1000, 8, 1, 10, CAST(0.05000 AS Decimal(9, 5)), 25, 51, 74, 4)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (11, N'DelimitedSplit8K', 1000, 8, 1, 10, CAST(0.06000 AS Decimal(9, 5)), 25, 51, 74, 4)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (12, N'tvfDelimitedSplitXML1', 1000, 8, 1, 10, CAST(0.68600 AS Decimal(9, 5)), 25, 51, 74, 4)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (13, N'Split', 1000, 16, 1, 10, CAST(0.08300 AS Decimal(9, 5)), 65, 103, 140, 5)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (14, N'DelimitedSplit8K', 1000, 16, 1, 10, CAST(0.12000 AS Decimal(9, 5)), 65, 103, 140, 5)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (15, N'tvfDelimitedSplitXML1', 1000, 16, 1, 10, CAST(1.09000 AS Decimal(9, 5)), 65, 103, 140, 5)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (16, N'Split', 1000, 32, 1, 10, CAST(0.10300 AS Decimal(9, 5)), 161, 206, 260, 6)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (17, N'DelimitedSplit8K', 1000, 32, 1, 10, CAST(0.24300 AS Decimal(9, 5)), 161, 206, 260, 6)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (18, N'tvfDelimitedSplitXML1', 1000, 32, 1, 10, CAST(1.86000 AS Decimal(9, 5)), 161, 206, 260, 6)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (19, N'Split', 1000, 64, 1, 10, CAST(0.19000 AS Decimal(9, 5)), 348, 414, 483, 7)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (20, N'DelimitedSplit8K', 1000, 64, 1, 10, CAST(0.43300 AS Decimal(9, 5)), 348, 414, 483, 7)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (21, N'tvfDelimitedSplitXML1', 1000, 64, 1, 10, CAST(3.11000 AS Decimal(9, 5)), 348, 414, 483, 7)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (22, N'Split', 1000, 128, 1, 10, CAST(0.34000 AS Decimal(9, 5)), 730, 831, 932, 8)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (23, N'DelimitedSplit8K', 1000, 128, 1, 10, CAST(0.85300 AS Decimal(9, 5)), 730, 831, 932, 8)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (24, N'tvfDelimitedSplitXML1', 1000, 128, 1, 10, CAST(6.30000 AS Decimal(9, 5)), 730, 831, 932, 8)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (25, N'Split', 1000, 256, 1, 10, CAST(0.64600 AS Decimal(9, 5)), 1471, 1662, 1803, 9)

    INSERT [dbo].[TestResults] ([RowNum], [SplitterName], [NumberOfRows], [NumberOfElements], [MinElementLength], [MaxElementLength], [Duration], [MinLength], [AvgLength], [MaxLength], [Run]) VALUES (26, N'DelimitedSplit8K', 1000, 256, 1, 10, CAST(1.67600 AS Decimal(9, 5)), 1471, 1662, 1803, 9)

    SET IDENTITY_INSERT [dbo].[TestResults] OFF

    Now you will need to create this procedure on the db you want to run this on. (If you can access MASTER then you can create it on MASTER and use it globally.)

    USE MASTER

    GO

    EXEC sp_configure 'show advanced options', 1

    GO

    RECONFIGURE

    GO

    EXEC sp_configure 'xp_cmdshell', 1

    GO

    RECONFIGURE

    GO

    --this procedure doesn't have to be on master, just make sure to change the reference in the code

    CREATE PROCEDURE sp_execresultset

    @SQLToGetRecordSet nvarchar(max)

    ,@DatabaseToExecuteCodeIn sysname

    ,@debug bit = 0

    AS

    BEGIN

    DECLARE @SQL nvarchar(max)

    DECLARE @AlteredSQL nvarchar(max)

    SET @SQL='USE ' + @DatabaseToExecuteCodeIn + '

    DECLARE @DynamicSQL nvarchar(max)

    DECLARE @tbl TABLE(SQL nvarchar(max))

    SET @DynamicSQL = ''''

    INSERT INTO @tbl(SQL)

    ' + @SQLToGetRecordSet + '

    SELECT @DynamicSQL = @DynamicSQL + SQL FROM @tbl

    EXEC sp_ExecuteSQL @DynamicSQL'

    IF @debug=0

    EXEC sp_ExecuteSQL @SQL

    ELSE

    PRINT @SQL

    END

    GO

    Finally the code to output the rows each to it's own table.

    USE LocalTestDB

    DECLARE

    @strSELECT NVARCHAR(4000)

    ,@RowNum VARCHAR (50)

    ,@cmd NVARCHAR(4000)

    ,@minRow INT

    ,@maxRow INT

    SET @strSELECT = 'SELECT * FROM dbo.TestResults WHERE RowNum = '

    SELECT

    @minRow = MIN(RowNum)

    ,@maxRow = MAX(RowNum)

    FROM

    dbo.TestResults

    WHERE

    RowNum BETWEEN 10 AND 20

    WHILE @minRow <= @maxRow

    BEGIN

    SET @RowNum = @minRow

    SET @cmd = N'SELECT ''EXEC master..xp_cmdshell ''''BCP "'+@strSELECT+@RowNum+'" QUERYOUT "C:\'+@RowNum+'.csv" -w -t"," -T -S"''+@@servername+''"'''''''

    EXEC master..sp_execresultset @cmd, N'LocalTestDB',0

    SET @minRow = @minRow + 1

    END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply