December 18, 2012 at 8:17 am
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
December 18, 2012 at 8:26 am
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/
December 18, 2012 at 10:13 am
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) A socialist is someone who will give you the shirt off *someone else's* back.
December 19, 2012 at 3:11 am
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.
December 19, 2012 at 3:21 am
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
December 19, 2012 at 3:30 am
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.
December 19, 2012 at 3:34 am
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
December 19, 2012 at 3:37 am
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
December 19, 2012 at 4:02 am
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
December 19, 2012 at 4:22 am
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.
December 19, 2012 at 4:27 am
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
December 19, 2012 at 4:35 am
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.
December 19, 2012 at 4:40 am
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
December 19, 2012 at 4:44 am
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.
December 19, 2012 at 4:51 am
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