Need direction on Loading monthly data

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 2008 Forums

SSIS and Import/Export (2008)

Need direction on Loading monthly data

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-12-03 : 14:37:41

I have a table in sql server 2005 database with 500,000 records which will get truncated and loaded every month using ssis.This table is used by users of our website to search information and submit requests using the id value that is stored in the table.

I need suggestions on how to proceed if

I want to load data to the same table every month but need to keep data of the id's that the users of the website used in the last month to submit requests

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-03 : 15:11:44

are you writing those ids to a table so that you know which they are?

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2010-12-07 : 16:25:24

yes those Ids which will be used will be stored in a differant table

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-12-07 : 21:18:42

Depending on the # of records, I might save off the records you want to keep

SELECT	t.*
INTO	holdingTable
FROM	mainTable t
JOIN	idsToSaveTable i
On	i.id = t.id;
GO

TRUNCATE TABLE mainTable;
GO

INSERT 	mainTable
SELECT * FROM aHoldingTable;
GO

DROP TABLE holdingTable;
GO

Then import the data to a staging table, and insert the records not in the ids you want to save


INSERT	mainTable
SELECT	s.*
FROM	stagingTable s
LEFT JOIN
	idsToSaveTable i
On	i.id = s.id
WHERE	i.id IS NULL;
GO

That's just one way. There are others, but would need to know more about your data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources