daily refresh of data

Hi
I have around 7 tables that get new data or updates in the production envt
and I would like to insert/update these tables on a daily basis in the
testing environment. Can you suggest a script to do this.
Thanks
Bob=?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> I have around 7 tables that get new data or updates in the production
> envt and I would like to insert/update these tables on a daily basis
> in the testing environment. Can you suggest a script to do this.
To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
"old" SQL Server 2000 :)
--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Bob
--New data
INSERT INTO TableA (<column lists>) SELECT <column lists> FROM TableB WHERE
NOT EXISTS (SELECT * FROM TableA WHERE TableA.PK=TableB.PK)
--Updated data
UPDATE TableA SET col=(SELECT col FROM TableB WHERE TableA.PK=TableB.PK AND
TableA.col<>Table.B.col) WHERE EXISTS (SELECT * FROM TableB WHERE
TableA.PK=TableB.PK AND TableA.col<>Table.B.col)
"Bob" <Bob@.discussions.microsoft.com> wrote in message
news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com...
> Hi
> I have around 7 tables that get new data or updates in the production envt
> and I would like to insert/update these tables on a daily basis in the
> testing environment. Can you suggest a script to do this.
> Thanks
> Bob|||Thank you for the reply, but the problem with the inserts and updates is that
each attribute in my table has referential integrity constraints and there
are 7-8 such constraints on the table. What strategy should I use to solve
this problem.
"Ole Kristian Bangås" wrote:
> =?Utf-8?B?Qm9i?= <Bob@.discussions.microsoft.com> wrote in
> news:08545F94-6089-4A25-B925-51F321E0D4B2@.microsoft.com:
> > I have around 7 tables that get new data or updates in the production
> > envt and I would like to insert/update these tables on a daily basis
> > in the testing environment. Can you suggest a script to do this.
> To me, this looks like a task for SSIS, eh.. sorry, it's called DTS in the
> "old" SQL Server 2000 :)
> --
> Ole Kristian Bangås
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>