October 17, 2012 at 5:02 am
I have a complex query which I wish to run everyday (shown below)
USE GATEKEEPER
IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP TABLE #SPLITOUTDATA;
CREATE TABLE #SPLITOUTDATA (
DC_INDEX_FK INT,
ROWNUM INT,
BITOFDATA VARCHAR(max));
IF OBJECT_ID(N'dbo.Split_XML', N'TF') IS NOT NULL DROP FUNCTION dbo.Split_XML
GO
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.Split_XML
(
@Parameter VARCHAR(MAX)
,@Delimiter VARCHAR(1)
)
RETURNS @Result TABLE
(
ItemNumber INT
,ItemValue VARCHAR(MAX)
)
AS
BEGIN
DECLARE @XML XML ;
SET @Parameter = ( SELECT @Parameter
FOR XML PATH('')
) ;
SELECT @XML = '<r>' + REPLACE(@Parameter, @Delimiter, '</r><r>') + '</r>' ;
INSERT INTO @Result
(
ItemNumber
,ItemValue
)
SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL) ) AS ItemNumber
, Item.value('text()[1]', 'VARCHAR(MAX)') AS ItemValue
FROM @XML.nodes('//r') R ( Item ) ;
RETURN ;
END ;
GO
;WITH
REFORMATTEDDATA AS
(
SELECT
DC_INDEX_FK,
REPLACE(REPLACE(CAST(RAW_CLOB AS VARCHAR(MAX)),CHAR(13),''),CHAR(10),'')AS RAWCLOB2
FROM
DATAFLOW_CLOB
WHERE
CAST(RAW_CLOB AS VARCHAR(MAX)) LIKE 'AAA|S0141007|%201208__|%'
)
INSERT INTO #SPLITOUTDATA
SELECT
DC_INDEX_FK,
ROW_NUMBER()OVER(PARTITION BY DC_INDEX_FK ORDER BY split.ItemNumber) AS ROWNUM,
split.ItemValue AS BITOFDATA
FROM REFORMATTEDDATA
CROSS APPLY dbo.Split_XML(reformatteddata.RAWCLOB2,'|') SPLIT
CREATE CLUSTERED INDEX idx1 ON #SPLITOUTDATA (DC_INDEX_FK,ROWNUM)
SELECT
[Date],
RunType,
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],
[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48]
FROM
(
SELECT
DC_INDEX_FK,
(SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.DC_INDEX_FK = so1.DC_INDEX_FK and so2.ROWNUM = 12) AS [Date] ,
(SELECT BITOFDATA FROM #SPLITOUTDATA so3 WHERE so3.DC_INDEX_FK = so1.DC_INDEX_FK and so3.ROWNUM = 13) AS RunType ,
ROW_NUMBER()OVER(PARTITION BY DC_INDEX_FK ORDER BY ROWNUM) AS ROWNUM,
(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.DC_INDEX_FK = so1.DC_INDEX_FK AND so4.ROWNUM = so1.ROWNUM +14) AS BITOFDATA
FROM #SPLITOUTDATA so1
WHERE BITOFDATA = 'SPH'
AND
(SELECT BITOFDATA FROM #SPLITOUTDATA so5 WHERE so5.DC_INDEX_FK = so1.DC_INDEX_FK AND so5.ROWNUM = 10) NOT IN ('TR01')
) p
PIVOT
(MAX (BITOFDATA)
FOR ROWNUM IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48])
) AS PVT
IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP TABLE #SPLITOUTDATA;
IF OBJECT_ID(N'dbo.Split_XML', N'TF') IS NOT NULL DROP FUNCTION dbo.Split_XML
I wish to output the results to file automatically when it runs every day. I have tried using an SQL Agent job and using the Advanced tab in the job step properties to define an output file but using this method makes the file very large (37kb) compared to when I just right click the query results and select "Save Results as.." (3kb).
Because the file size is so large it normally takes a while to open and often I find that the query results do not even display correctly.
Can anyone offer any advice on how else I can output these results automatically? I don't have SSIS capabilities and I tried using BCP but I'm new to using BCP and it didn't seem to work for me.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply