Replace TABLE with STAGING_TABLE. Approach?
-
You can use a query to see which table the view is currently pointing at:
SELECT referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID('dbo.Clients')
Also, I'd suggest creating a log table in which you record when a swap occurs. This would be a very small table. You can even keep it truncated to, say, no more than 2 weeks of activity. Then you would be able to see each time the table switched, and, just in case something does go wrong, you could see how long the table was empty/out-of-sync/whatever the problem was.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
-
Yes, all was fine.
At present changing the structure of the database and moving Access (with their relationships and constraints) tables into the SS database. The renaming of the staging- and client tables causes problems with the constraints. I am not confident about changing the constraints using procedures.
Your approach lets one set the constraints on the staging tables once, and "Bob's your uncle".
J.
-
Thanks Scott. Will look into this.
J.
- JJR333 wrote:
Thanks Scott. Will look into this.
J.
If you used a synonym instead of a view, it would be even easier because you could read what it was pointing at from the very definition you need to change.
And thanks for the feedback on repointing being better because of considerations for constraints instead of renaming.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Someone pointed out that this could be done with synonyms - which I think might be a better approach.
To identify which staging table - you can query the currently defined synonym:
Declare @baseObject sysname;
Select @baseObject = base_object_name From sys.synonyms s Where s.name = 'ClientStaging';
Drop Synonym If Exists dbo.Client;
Drop Synonym If Exists dbo.ClientStaging;
If right(@baseObject, 2) = '1]' --base object will be quoted with [ and ]
Begin
Create Synonym dbo.Client For dbo.Client_Staging_1;
Create Synonym dbo.ClientStaging For dbo.Client_Staging_2;
End
Else
Begin
Create Synonym dbo.Client For dbo.Client_Staging_2;
Create Synonym dbo.ClientStaging For dbo.Client_Staging_1;
EndUse the above after the load has completed - this will switch the client and clientstaging synonyms.
For example - when the load starts the synonym for Client points to Client_Staging_1 and the synonym for ClientStaging points to Client_Staging_2. All data is loaded into Client_Staging_2. After the load is completed - run something like the above and now Client points to Client_Staging_2 (the newly updated/refreshed data) and ClientStaging is switched to Client_Staging_1 (ready for the next load process).
You could also check the Client synonym - and load the data to 'other' table, then after the load completes update the Client synonym to the table you used for the load process.
If there are issues loading the data - then don't switch and the current data will still be available. You can disable non-clustered indexes on the staging table you are loading, rebuild indexes, update statistics, etc... and only switch if everything completes successfully.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Interesting!
Working on it.
Learning all the time.
Thank you Jeffrey, and Jeff.
-
Great, works like a charm!
Thank you Jeffrey and Jeff.
FWIW:
IF EXISTS keeps returning an error (SS 2008 R2),
DROP SYNONYM sBewoners, does not.
Swapping the view Clients (I prefer to distinguish names used in various parts of the database)
CREATE VIEW [dbo].[Clients]
AS
SELECT dbo.sBewoners.*
FROM dbo.sBewoners
GO/*
###SWAP THE BASE OF THE SYNONYM
*/
SELECT @baseObject = base_object_name From sys.synonyms s Where s.name = 'sBewoners';
--PRINT @baseObject
DROP SYNONYM sBewoners;
If right(@baseObject, 2) = '1]' --base object will be quoted with [ and ]
Begin
Create Synonym dbo.sBewoners For dbo.Bewoners_Staging_2;
End
Else
Begin
Create Synonym dbo.sBewoners For dbo.Bewoners_Staging_1;
End - JJR333 wrote:
Great, works like a charm!
Thank you Jeffrey and Jeff.
FWIW:
IF EXISTS keeps returning an error (SS 2008 R2),
DROP SYNONYM sBewoners, does not.
Swapping the view Clients (I prefer to distinguish names used in various parts of the database)
CREATE VIEW [dbo].[Clients]
AS
SELECT dbo.sBewoners.*
FROM dbo.sBewoners
GO/*
###SWAP THE BASE OF THE SYNONYM
*/
SELECT @baseObject = base_object_name From sys.synonyms s Where s.name = 'sBewoners';
--PRINT @baseObject
DROP SYNONYM sBewoners;
If right(@baseObject, 2) = '1]' --base object will be quoted with [ and ]
Begin
Create Synonym dbo.sBewoners For dbo.Bewoners_Staging_2;
End
Else
Begin
Create Synonym dbo.sBewoners For dbo.Bewoners_Staging_1;
EndBe real careful here... I wouldn't use the name of sBewoners for the synonym name because if you do later decide that the view is the better way to go for some reason or you go back to just using a single table, you're going to look a bit silly having either a view or a table that starts with a Hungarian Notation of "s".
There's another issue I'm thinking about but I'm not done thinking about it yet. I'll be back.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I have something working.
My next problem is "Cascade Delete" constraints on the tables related to Bewoners_Staging_1 and Bewoners_Staging_2
Records in tables get deleted. How would I go about this problem?
A MERGE procedure would be an option. Source is the BulkInsert table, Target Staging 1 or 2, delete when not matched.
MERGE myTableTarget AS Target
USING
(SELECT ETC) AS SOURCE
ON
TARGET.ID = SOURCE.ID etc
WHEN MATCHED THEN
UPDATE
SET
HRSWERK = SOURCE.HRSWERK ETC
WHEN NOT MATCHED BY TARGET THEN
INSERT (COLUMN NAMES)
VALUES (SOURCE.ID, ETC);
Optional:
WHEN NOT MATCHED BY SOURCE THEN -- Target contains rows not in the Source
DELETE -- Delete these rows in Target
END -
I guess we need to back this truck up a bit. I made the mistake of assuming that since you said you wanted to replace the table, that there wouldn't be any FKs pointing at the table and certainly no cascading deletes.
If you need all that, especially the cascading deletes, it would probably be better NOT to do a wholesale replacement of the table. It would be better to just load a staging table and use that to drive "upsert" code similar to what you wrote above.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply