Append new records to a DB table using T-SQL

Question

I am using SQL Server 2005

I populate a table with this query:

USE [MyDB]
INSERT INTO dbo.Summaries
   SELECT DISTINCT TOP (100) PERCENT 
      s.Kiosk_ID, k.kioskName, u.Owner_id, o.DisplayName, u.value,  
      COUNT(u.value) AS ValueCount, k.KioskGroup_ID, 
      CONVERT(varchar, u.DateTime, 112) as date, 
      'Marketing' AS DBName
   FROM         
      dbo.k_UsageLog AS u 
   INNER JOIN
      dbo.K_Owner AS o ON o.Owner_id = u.Owner_id 
   INNER JOIN
      dbo.k_Session AS s ON u.Session_id = s.Session_id 
   INNER JOIN
      dbo.Kiosk AS k ON k.Kiosk_ID = s.Kiosk_ID
   WHERE        
     (o.Owner_id > 12) 
   GROUP BY 
      s.Kiosk_ID, u.Owner_id, u.value, k.KioskGroup_ID, o.DisplayName,    
      CONVERT(varchar, u.DateTime, 112), k.kioskName
   ORDER BY 
      s.Kiosk_ID

I need to run this nightly and only add new rows.

I thought of simply using a timedate stamp and only adding yesterday's data, but some units that report to the DB may be offline for days at a time and will send several days worth of data when they finally connect.

I know I need something like MERGE (which thanks to marc_s I now know is not included in MS SQL Server 2005), but I cannot figure out how to make it work. Help would be greatly appreciated.

SQL Server 2005 doesn't have the MERGE statement yet - that's a new feature in SQL Server 2008 — marc_s
– marc_s, Commented Mar 13, 2014 at 16:17
Do you have a unique primary key? How would you identify what is a new row? — ScubaManDan
– ScubaManDan, Commented Mar 13, 2014 at 16:21
New issue. The "counts" may change and I need to update them daily. I used SELECT EXCEPT (as proposed below), and it works great, except it adds a new line if the COUNT on Value has changed when I really need it to replace/delete/update that line. Any ideas? — barna10
– barna10, Commented Mar 14, 2014 at 17:40

user3410266 · Accepted Answer · 2014-03-14 10:49:23Z

1

Maybe you can use SELECT EXCEPT

http://technet.microsoft.com/en-us/library/ms188055.aspx

into temp table and then INSERT the result to your destination table.

answered Mar 14, 2014 at 10:49

user3410266

Sign up to request clarification or add additional context in comments.

1 Comment

barna10 Over a year ago

This would work GREAT, but see above for my new issue

jean · Accepted Answer · 2014-03-14 10:48:37Z

1

If the problem is detecting if the row is a new row or not you can try to add a hash column to your table (or stag table, depended how you are doing that inserts).

We got CHECKSUM(*), BINARY_CHECKSUM(*) and CHECKSUM_AGG as hash functions. Maybe you can add a persisted computed column.

Also a better way can be just by creating a unique constraint covering all your columns. That way can be a lot more performatic but you ill need to handle the exception generated by the constraint violations

answered Mar 14, 2014 at 10:48

jean

4,3574 gold badges33 silver badges55 bronze badges

Collectives™ on Stack Overflow

Append new records to a DB table using T-SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related