SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Nightly Updates Help Needed
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

defyant_2004
Starting Member

1 Posts

Posted - 03/20/2007 :  19:08:01  Show Profile  Reply with Quote
I need to setup incremental updates to one of my SQL Server 2000 tables. I will get an initial file with all the data, then a nightly file with data updates and edits during the day.

When I import this data, three things must happen:

1. New records identified by primary key get appended to table.

2. Exisiting records identified by primary key get overwritten with new/(updated) data.

3. All other existing records are left alone.

Does anyone know how to Import Records with the following the criteria above? It cannot insert duplicate primary keys by nature, so it must overwrite those records!


nosepicker
Constraint Violating Yak Guru

USA
366 Posts

Posted - 03/20/2007 :  21:08:10  Show Profile  Reply with Quote
Probably the easiest thing to do is import the data into a staging table. Then use a series of SQL statements (can put them into a stored procedure) to append the new records, overwrite the old ones, etc.
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 03/21/2007 :  03:09:50  Show Profile  Reply with Quote
We do this in one of two ways (using a staging table as nosepicker suggests):

1. Pre-delete any matching PKs, then INSERT the lot. For this we ONLY pull "newly dated" records into the Staging table (the source table has an Update Date column which is set whenever the record changes, and we remember what date we have already processed up to, and then just pull anything with a more recent date). This will NOT transfer any deletes

2. We get ALL the data into a staging table. Then we JOIN the staging table to the "Live" table for a set of statements:

a) Delete anything in Destination table that is absent from Source/Staging table

DELETE D
FROM	MyDatabaseName_DST.dbo.[MyTable] AS D
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	MyDatabaseName_SRC.dbo.[MyTable] AS S
	WHERE
		    D.[MyPK1] = S.[MyPK1]
		AND D.[MyPK2] = S.[MyPK2]
	)

the deletes are done in FK order - children first, then parents.

b) Update anything in Destination table that is different to Source/Staging table
We use a "large" WHERE clause covering every column; it is not possible to compare TEXT columns, so we just compare the lengths, and the first 8000 characters.

UPDATE D
SET
	[MyPK1] = S.[MyPK1],
	[MyPK2] = S.[MyPK2],
	[MyCol1] = S.[MyCol1],
	[MyStringCol2] = S.[MyStringCol2],
	[MyTextCol3] = S.[MyTextCol3],
	...
-- SELECT TOP 100 'D', D.*, CHAR(13)+CHAR(10)+'S   ', S.*, CHAR(13)+CHAR(10)+'----------'
FROM	MyDatabaseName_DST.dbo.[MyTable] AS D
	JOIN MyDatabaseName_SRC.dbo.[MyTable] AS S
		 ON D.[MyPK1] = S.[MyPK1]
		AND D.[MyPK2] = S.[MyPK2]
WHERE
	   (D.[MyPK1] <> S.[MyPK1] 
		OR (D.[MyPK1] IS NULL AND S.[MyPK1] IS NOT NULL) 
		OR (D.[MyPK1] IS NOT NULL AND S.[MyPK1] IS NULL))
	OR (D.[MyPK2] <> S.[MyPK2] 
		OR (D.[MyPK2] IS NULL AND S.[MyPK2] IS NOT NULL) 
		OR (D.[MyPK2] IS NOT NULL AND S.[MyPK2] IS NULL))
	OR (D.[MyCol1] <> S.[MyCol1] 
		OR (D.[MyCol1] IS NULL AND S.[MyCol1] IS NOT NULL) 
		OR (D.[MyCol1] IS NOT NULL AND S.[MyCol1] IS NULL))
	OR (D.[MyStringCol2] COLLATE Latin1_General_BIN <> S.[MyStringCol2] 
		OR (D.[MyStringCol2] IS NULL AND S.[MyStringCol2] IS NOT NULL) 
		OR (D.[MyStringCol2] IS NOT NULL AND S.[MyStringCol2] IS NULL))
	OR (DATALENGTH(D.[MyTextCol3]) <> DATALENGTH(S.[MyTextCol3]) 
		OR CONVERT(varchar(8000), D.[MyTextCol3]) COLLATE Latin1_General_BIN <> CONVERT(varchar(8000), S.[MyTextCol3]) 
		OR (D.[MyTextCol3] IS NULL AND S.[MyTextCol3] IS NOT NULL) 
		OR (D.[MyTextCol3] IS NOT NULL AND S.[MyTextCol3] IS NULL))
	...

note that we use COLLATE Latin1_General_BIN for comparison so that even an Upper/Lower case difference is considered "significant"

c) Then we insert any new records:

SET IDENTITY_INSERT MyDatabaseName_DST.dbo.[MyTable] ON
INSERT INTO MyDatabaseName_DST.dbo.[MyTable]
(
	[MyPK1],
	[MyPK2],
	[MyCol1],
	[MyStringCol2],
	[MyTextCol3],
	...
)
SELECT	S.*
FROM	MyDatabaseName_SRC.dbo.[MyTable] AS S
WHERE	NOT EXISTS
	(
	SELECT	*
	FROM	MyDatabaseName_DST.dbo.[MyTable] AS D
	WHERE
		    D.[MyPK1] = S.[MyPK1]
		AND D.[MyPK2] = S.[MyPK2]
	)
SET IDENTITY_INSERT MyDatabaseName_DST.dbo.[MyTable] OFF

We do the Updates and Inserts in opposite FK order to the deletes - i.e. Parents first, then Children.

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000