June 7, 2017 at 1:46 am
Hi Experts,
I noticed that in one of the sp they have user ALTER SCHEMA schemaname TRANSFER schemaname.tabe name.i googled it and i found it will transfer securable between schemas.
1) What is the use of it?
2) In which scenarios this will help?
Please advice. Thanks in advance.
Regards,
Vijay
June 7, 2017 at 2:26 am
Vijay
It will help if you want to move a table from one schema to another without having to export the data, drop the table, recreate the table in the new schema, and import all the data back in.
John
June 7, 2017 at 2:39 am
Hi John,
Thanks for the info. In my case there are tables with 2 schema's which already exists. For ex: cache.table name and dbo.Table name. first they are truncating data from cache.table and Loading data into Chache.Table then they are Transferring cache schema to dbo.Table name. Just wanted to know specific scenario when it is usefull.
Reards,
Vijay
June 7, 2017 at 2:58 am
First attempt:
What you're describing is a DML operation - moving data from one table to another (let's disregard the fact that TRUNCATE TABLE is technically a DDL operation). ALTER SCHEMA is a DDL operation. If your tables are already set up as you need them to be, then this command isn't going to be of any use to you.
Second attempt after re-reading your post:
What do you mean by "they are transferring cache schema to dbo.Table name"? Do you mean copying the data, or are you actually altering the structure of the table? If you could post some code, that would help us to understand what you're trying to do.
John
June 7, 2017 at 3:48 am
Hi John,
Please find the code below.
TRUNCATE TABLE cache.table
INSERT INTO cache.Table
SELECT * from [inkedserver].Database.dbo.Table
BEGIN TRANSACTION
ALTER SCHEMA cache1 TRANSFER dbo.Table;
ALTER SCHEMA dbo TRANSFER cache.Table;
ALTER SCHEMA cache TRANSFER cache1.Table;
COMMIT TRANSACTION
I dint understand what they are trying to do. Please help me in understanding this.
Regards,
Vijay
June 7, 2017 at 4:14 am
A strange way to do things. I prefer to use DML commands for what are essentially DML operations. If you use DDL commands, the user executing them needs higher permissions on the database.
I've annonated your code with comments explaining what I think it's doing. Is the person who wrote the code available to ask about this?
TRUNCATE TABLE cache.table -- clear the staging table
INSERT INTO cache.Table -- import from remote server to staging table
SELECT * from [inkedserver].Database.dbo.Table
BEGIN TRANSACTION
ALTER SCHEMA cache1 TRANSFER dbo.Table; -- move the existing live table to a temporary backup
ALTER SCHEMA dbo TRANSFER cache.Table; -- move into live table data just imported into staging table
ALTER SCHEMA cache TRANSFER cache1.Table; -- move backed up data into staging table. This will be lost next time the process runs
COMMIT TRANSACTION
John
June 7, 2017 at 4:48 am
Hi John,
Thanks you for explaining me the code and the person is not available to ask why he written this code.
Regards,
Viay
June 7, 2017 at 6:20 am
i have a process that does something exactly like this; the issue is the remote server has the "freshest" data, but we want the dbo table available without significant locking.
rather than running an ETL and a merge operation on the dbo table , which would create locks on the table during the merge of remote data to the local permanent table, they are simply grabbing the remote table, and hot-swapping it via the TRANSFER statements, to minimize the time the table is not available, and avoid costly rollbacks if there was a network problem going to the other server.
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply