Append query results to existing table

  • Thanks, I'm trying this out.

    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)

    SELECT DISTINCT

    (SELECT CAST(STUFF(STUFF(STUFF(BITOFDATA,13,0,':'),11,0,':'),9,0,' ') AS datetime)

    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

    INSERT INTO test.dbo.d0003 ([Date&TimeOfReading])

    SELECT so1.[Date&TimeofReading]

    FROM #SPLITOUTDATA

    LEFT OUTER JOIN TEST.DBO.d0003 so1

    on so1.[Date&TimeOfReading] = so1.[Date&TimeOfReading]

    WHERE so1.[Date&TimeOfReading] IS NULL

    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

    At the moment, I've added in what I think is right, I'm not getting errors, but when I look in the D0003 table, all fields are just NULL and there are over a thousand records. Also, the results are still showing so it's displaying query results rather than inserting. Although it is inserting the NULLs.

  • Your join is wrong. Try this:

    SELECT so1.[Date&TimeofReading]

    FROM #SPLITOUTDATA so1

    LEFT OUTER JOIN TEST.DBO.d0003 d

    on so1.[Date&TimeOfReading] = d.[Date&TimeOfReading]

    WHERE d.[Date&TimeOfReading] IS NULL

    John

  • Thanks for replying.

    It doesn't seem to like so1.[Date&TimeOfReading] now.

    Msg 207, Level 16, State 1, Line 148

    Invalid column name 'Date&TimeOfReading'.

    Msg 207, Level 16, State 1, Line 145

    Invalid column name 'Date&TimeOfReading'.

    And I can't seem to figure out why.

  • That's because there's no column of that name in your temp table. You need to break out your dates from the tale and join on those. That's why I said you need to put your SELECT DISTINCT statement into a common table expression or derived table.

    John

  • Oh okay, thanks for clarifying that for me. I have no idea how to do that but I'll research and try.

  • I have tried to create what I think is a derived table, but I'm still getting errors. If anyone can see what is wrong with it, that'd be great.

    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)

    SELECT [Date&TimeOfReading], [MPAN], [P1], [P2], [P3], [P4], [P5], [P6], [P7], [P8], [P9], [P10], [P11], [P12], [P13], [P14], [P15], [P16],

    [P17], [P18], [P19], [P20], [P21], [P22], [P23], [P24], [P25], [P26], [P27], [P28], [P29], [P30], [P31], [P32], [P33], [P34], [P35], [P36],

    [P37], [P38], [P39], [P40], [P41], [P42], [P43], [P44], [P45], [P46], [P47], [P48] FROM (

    SELECT DISTINCT

    (SELECT CAST(STUFF(STUFF(STUFF(BITOFDATA,13,0,':'),11,0,':'),9,0,' ') AS datetime)

    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]

    ) #SPLITOUTDATA so1

    INSERT INTO test.dbo.d0003 ([Date&TimeOfReading])

    SELECT so1.[Date&TimeOfReading]

    FROM #SPLITOUTDATA so1

    LEFT OUTER JOIN TEST.DBO.d0003 d

    on so1.[Date&TimeOfReading] = d.[Date&TimeOfReading]

    WHERE d.[Date&TimeOfReading] IS NULL

    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'm currently getting error message:

    Msg 102, Level 15, State 1, Line 142

    Incorrect syntax near 'so1'.

  • Since you are learning and are on SQL 2008 I would recommend acquainting yourself with the MERGE statement. INSERT...SELECT is fine, but MERGE is much more versatile and intuitive (in my opinion, because it is explicit) in terms of what will occur when your join condition is matched or not matched.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks for the advice, I have tried implementing a merge statement, but as John previously said I need my Select statements in a derived table, which I still cannot work out how to do as I'm getting the error I posted previously.

  • If anyone could help I'm now trying a CTE, but getting a similar error as when I tried the derived table, I assume it's something simple, I just don't have the knowledge to realise what it 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)

    ;with cte as (

    SELECT DISTINCT

    (SELECT CAST(STUFF(STUFF(STUFF(BITOFDATA,13,0,':'),11,0,':'),9,0,' ') AS datetime)

    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

    )

    MERGE TEST.DBO.D0003 D

    USING #SPLITOUTDATA so1

    ON D.[Date&TimeOfReading] = so1.[Date&TimeOfReading]

    WHEN MATCHED THEN

    UPDATE

    SET D.[Date&TimeOfReading] = D.[Date&TimeOfReading] + so1.[Date&TimeOfReading]

    ;

    SELECT * FROM test.dbo.D0003

    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

    Error message the same as before -

    Msg 207, Level 16, State 1, Line 148

    Invalid column name 'Date&TimeOfReading'.

  • In your attempt yesterday, you aliased your derived table with the name of table that already exists, which is confusing for me at least, if not for SQL Server. Try aliasing it as dt or something like that and using that in your join/MERGE statement.

    John

  • sqlrd22 (12/19/2012)


    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.

    I hear you there my friend. My company does not even use SQL Server. I use it only to analyze data I pull out of our DB2 mainframe, which is indexed to maximize the performance of some COBOL functions running in the terminal emulator, not for queries. One thing to keep in mind though, is that your company may grow, and what works okay for you now may start to tank as your data grows. You should still strive to maximize the efficiency of your code, even if it is just for your use in preparing reports. By doing so, you will make yourself a better programmer. And, its just fun.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

Viewing 11 posts - 16 through 25 (of 25 total)

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