Use parameter value in .csv output filename

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Use parameter value in .csv output filename

Use parameter value in .csv output filename

Rate Topic

Display Mode

Topic Options

Author

Message

s.r.harris

Posted Sunday, March 03, 2013 1:02 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, March 03, 2013 3:16 PM
Points: 2, Visits: 7

Table_1

ESTAB_ID	ESTAB_CODE	FORENAME		SURNAME
1		9009001		John			Jones
1		9009001		Mike			Smith
1		9009001		Mary			Yates
2		9009005		Ann			Ennis
2		9009005		Kelly			Homes
3		9009014		Harry			Brand
3		9009014		James			Casey
3		9009014		Chris			Balls

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_CODE	FORENAME	SURNAME
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.

Post #1425996

Vedran Kesegic

Posted Sunday, March 03, 2013 5:33 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:57 PM
Points: 343, Visits: 1,089

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

_____________________________________________________
XDetails Addin - for SQL Developers and DBA
blog.sqlxdetails.com - Transaction log myths - debunked!

Post #1426050

Steven Willis

Posted Sunday, March 03, 2013 10:31 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 298, Visits: 1,323

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

Post #1426085

« Prev Topic | Next Topic »

Permissions