Nightly Updates Help Needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Nightly Updates Help Needed

Author

Topic

defyant_2004
Starting Member

1 Post

Posted - 2007-03-20 : 19:08:01

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

366 Posts

Posted - 2007-03-20 : 21:08:10

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.

Kristen
Test

22859 Posts

Posted - 2007-03-21 : 03:09:50

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources