March 3, 2013 at 1:02 pm
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.
March 3, 2013 at 5:33 pm
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
March 3, 2013 at 10:31 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy