RE: Append query results to existing table

sqlrd22

SSCrazy

Points: 2255

December 19, 2012 at 3:30 am

#1569954

OK.. But this is quite long winded.

USE [SVA_FLOWS]

GO

/****** Object: Table [dbo].[RUG_CLOB] Script Date: 19/12/2012 10:24:56 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[RUG_CLOB](

[INDEX1] [varchar](50) NULL,

[DATA] [varchar](max) NOT NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT INTO [dbo].[RUG_CLOB]

([INDEX1]

,[DATA])

VALUES

('NULL',

'ZHV|FF0000998|D0003001|C|SSSS|X|FFFF|20121203101202||||TR01|007|2300000000019|008|D0002|009|20121201090000|A|010|01|AE|011|20121201|

012|01|A|9.0|||||012|02|A|9.0|||||012|03|A|9.0|||||012|04|A|9.0|||||012|05|A|9.0|||||012|06|A|9.0|||||012|07|A|9.0|||||012|08|A|9.0|||||

012|09|A|9.0|||||012|10|A|9.0|||||012|11|A|9.0|||||012|12|A|9.0|||||012|13|A|9.0|||||012|14|A|9.0|||||012|15|A|9.0|||||012|16|A|9.0|||||012|17|A|9.0|||||

012|18|A|9.0|||||012|19|A|9.0|||||012|20|A|9.0|||||012|21|A|9.0|||||012|22|A|9.0|||||012|23|A|9.0|||||012|24|A|9.0|||||012|25|A|9.0|||||012|26|A|9.0|||||

012|27|A|9.0|||||012|28|A|9.0|||||012|29|A|9.0|||||012|30|A|9.0|||||012|31|A|9.0|||||012|32|A|9.0|||||012|33|A|9.0|||||012|34|A|9.0|||||012|35|A|9.0|||||

012|36|A|9.0|||||012|37|A|9.0|||||012|38|A|9.0|||||012|39|A|9.0|||||012|40|A|9.0|||||012|41|A|9.0|||||012|42|A|9.0|||||012|43|A|9.0|||||012|44|A|9.0|||||

012|45|A|9.0|||||012|46|A|9.0|||||012|47|A|9.0|||||012|48|A|9.0|||||013|01||ZPT|FF0000998|54||1|20121203101202|')

GO

My query currently is...

USE SVA_FLOWS

IF OBJECT_ID('tempdb..#splitoutdata','U') IS NOT NULL DROP TABLE #SPLITOUTDATA;

CREATE TABLE #SPLITOUTDATA (

INDEX1 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

ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS INDEX1,

REPLACE(REPLACE(CAST(DATA AS VARCHAR(MAX)),CHAR(13),''),CHAR(10),'')AS RAWCLOB2

FROM

RUG_CLOB

WHERE

CAST(DATA AS VARCHAR(MAX)) LIKE 'ZHV|%|D0003001%'

)

INSERT INTO #SPLITOUTDATA

SELECT

INDEX1,

ROW_NUMBER()OVER(PARTITION BY INDEX1 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 (INDEX1,ROWNUM)

INSERT INTO test.dbo.d0003

SELECT DISTINCT

(SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.INDEX1 = so1.INDEX1 and so2.ROWNUM = 18) AS [Date&TimeOfReading],

(SELECT BITOFDATA FROM #SPLITOUTDATA so2 WHERE so2.INDEX1 = so1.INDEX1 and so2.ROWNUM = 14) AS [MPAN],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 28) as [P1],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 36) as [P2],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 44) as [P3],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 52) as [P4],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 60) as [P5],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 68) as [P6],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 76) as [P7],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 84) as [P8],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 92) as [P9],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 100) as [P10],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 108) as [P11],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 116) as [P12],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 124) as [P13],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 132) as [P14],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 140) as [P15],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 148) as [P16],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 156) as [P17],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 164) as [P18],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 172) as [P19],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 180) as [P20],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 188) as [P21],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 196) as [P22],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 204) as [P23],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 212) as [P24],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 220) as [P25],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 228) as [P26],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 236) as [P27],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 244) as [P28],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 252) as [P29],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 260) as [P30],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 268) as [P31],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 276) as [P32],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 284) as [P33],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 292) as [P34],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 300) as [P35],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 308) as [P36],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 316) as [P37],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 324) as [P38],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 332) as [P39],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 340) as [P40],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 348) as [P41],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 356) as [P42],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 364) as [P43],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 372) as [P44],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 380) as [P45],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 388) as [P46],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 396) as [P47],

(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 404) as [P48]

--(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 412) as [P49],

--(SELECT BITOFDATA FROM #SPLITOUTDATA so4 WHERE so4.INDEX1 = so1.INDEX1 AND so4.ROWNUM = 420) as [P50]

FROM #SPLITOUTDATA so1

--WHERE CLAUSE?

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

People have pointed out that this code is not great, doesn't work well, but I work for a very small company and I'm just trying to do my best to get the data out that my bosses want for reports. I have quite little knowledge of SQL.