Append query results to existing table

  • Hi,

    I currently run a query daily and have to results going into a table using insert into.. select...

    The problem is, this puts all the results of the query into the table again and I only wish for it to enter the results that are new as it is repeating the data that is already in the table.

    Is there any type of append I can do that only adds records that are not currently in the table?

    Sorry if this seems simple, still learning.

    Thanks

  • sqlrd22 (12/18/2012)


    Hi,

    I currently run a query daily and have to results going into a table using insert into.. select...

    The problem is, this puts all the results of the query into the table again and I only wish for it to enter the results that are new as it is repeating the data that is already in the table.

    Is there any type of append I can do that only adds records that are not currently in the table?

    Sorry if this seems simple, still learning.

    Thanks

    Yes there is a way to do that. You have to add a where clause to your insert statement to filter out the data that is already there.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Yes.

    Typically a LEFT OUTER JOIN is used for that.

    Say you're inserting names and want to avoid inserting one that's already in the table:

    INSERT INTO dbo.names_table ( name )

    SELECT wt.name

    FROM dbo.work_table wt

    LEFT OUTER JOIN dbo.names_tables nt ON

    nt.name = wt.name

    WHERE

    nt.name IS NULL --true only if name doesn't already exist in dbo.names_table

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Thanks, I'm trying with WHERE but I'm just not getting the gist of it yet.

    I want it to look at the Date to be unique and only to take through to the new table records only with dates not currently in the new table.

    The only problem is we get this data sent into us and the date comes through into the database in a plain text format e.g. '20121219' as it just comes as part of a long string of info I split up.

    The column I then pass it into is Reading_Date.

  • Please will you post table DDL (a CREATE TABLE statement) and an example of a few rows that you are trying to import into the the database. What queries have you tried so far?

    John

  • 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.

  • Thanks. RUG_CLOB appears to be a staging table. Please can we see the DDL for the table with the Reading_Date column in?

    John

  • USE [test]

    GO

    /****** Object: Table [dbo].[d0003] Script Date: 19/12/2012 10:36:18 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[d0003](

    [Date&TimeOfReading] [varchar](max) NULL,

    [MPAN] [varchar](max) NULL,

    [P1] [varchar](max) NULL,

    [P2] [varchar](max) NULL,

    [P3] [varchar](max) NULL,

    [P4] [varchar](max) NULL,

    [P5] [varchar](max) NULL,

    [P6] [varchar](max) NULL,

    [P7] [varchar](max) NULL,

    [P8] [varchar](max) NULL,

    [P9] [varchar](max) NULL,

    [P10] [varchar](max) NULL,

    [P11] [varchar](max) NULL,

    [P12] [varchar](max) NULL,

    [P13] [varchar](max) NULL,

    [P14] [varchar](max) NULL,

    [P15] [varchar](max) NULL,

    [P16] [varchar](max) NULL,

    [P17] [varchar](max) NULL,

    [P18] [varchar](max) NULL,

    [P19] [varchar](max) NULL,

    [P20] [varchar](max) NULL,

    [P21] [varchar](max) NULL,

    [P22] [varchar](max) NULL,

    [P23] [varchar](max) NULL,

    [P24] [varchar](max) NULL,

    [P25] [varchar](max) NULL,

    [P26] [varchar](max) NULL,

    [P27] [varchar](max) NULL,

    [P28] [varchar](max) NULL,

    [P29] [varchar](max) NULL,

    [P30] [varchar](max) NULL,

    [P31] [varchar](max) NULL,

    [P32] [varchar](max) NULL,

    [P33] [varchar](max) NULL,

    [P34] [varchar](max) NULL,

    [P35] [varchar](max) NULL,

    [P36] [varchar](max) NULL,

    [P37] [varchar](max) NULL,

    [P38] [varchar](max) NULL,

    [P39] [varchar](max) NULL,

    [P40] [varchar](max) NULL,

    [P41] [varchar](max) NULL,

    [P42] [varchar](max) NULL,

    [P43] [varchar](max) NULL,

    [P44] [varchar](max) NULL,

    [P45] [varchar](max) NULL,

    [P46] [varchar](max) NULL,

    [P47] [varchar](max) NULL,

    [P48] [varchar](max) NULL

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    Sorry, it's actually called Date&TimeOfReading

  • Excellent. Before we start, a couple of things I recommend you change if it's within your power. First, don't create and delete the Split_XML function every time. Create it once and leave it. Second, change the data type of Date&TimeOfReading to datetime. This will save space in your table and prevent invalid dates being inserted.

    Now, assuming that the date will always appear in the same format, this will work for you:

    ...

    (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],

    ...

    John

  • Thanks.. I'm just trying this now, but struggling to find where this fits in with my code and which bits of mine to take out! I'll play around with it.

  • Take out the line of your current query that ends with "(AS [Date&TimeOfReading],", and replace it with my line. All I've done is STUFFed some colons into the date and CAST it as datetime.

    John

  • OK great that works, thanks.

    I'm now getting error below when I include the INSERT INTO statement

    Msg 213, Level 16, State 1, Line 28

    Column name or number of supplied values does not match table definition.

    This seems to be since I changed the data type of Date&TimeOfReading in D0003 table which I'm trying to insert into.

  • OK, how many columns are in the table and how many values are you trying to insert? You didn't accidentally remove a comma from the SELECT DISTINCT list, did you?

    John

  • Sorry, yes my fault, I've fixed it now!

    Thanks for the help with the Date/Time thing, very handy.

    If you/anyone else could help me with the WHERE on the INSERT INTO statement, described earlier in this thread, that'd be great.

  • No problem. What you need to do is LEFT JOIN the results of your SELECT DISTINCT statement to d0003 in the way that Scott showed you earlier. You'll need to put the results of the SELECT DISTINCT into a derived table or a common table expression, so read up on those terms if you haven't heard of them before.

    John

Viewing 15 posts - 1 through 15 (of 25 total)

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