Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Going From Many Schemas To One Schema Expand / Collapse
Author
Message
Posted Saturday, August 7, 2010 12:09 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
Comments posted to this topic are about the item Going From Many Schemas To One Schema

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #965573
Posted Monday, August 9, 2010 12:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 1:54 PM
Points: 6,130, Visits: 8,394
It's an interesting technique - but not one I would recommend!

The simple version has this caveat: it works "as long as they share the same primary key and the shared columns have the same names" (quote from the article). Verifying that is what takes time when preparing a data merge (be it a one-time operation or an ETL job that is to be run repeatedly). Building the actual statement is a matter of seconds, especially if you use SSMS' drag&drop techniques to copy the column list from the object explorer. The technique shown in this article doe indeed save you a couple of seconds and a few keystrokes, but at the price of having to use dynamic SQL (which means you have to run under a user account with elavated rights, and you incrue the risk of injection attacks). And worse - it invites laziness. There might be situations where the same name has been used for essentially different colum; I'll exclude those when working manuallly, but they'll be included by this procedure. Or there might be some subtle difference in how data is represented, making some conversion required.

The advanced version circuvenes some (not all!) of these problems. But I think that populating the table with column equivalences would take me more time than simply writing the INSERT, UPDATE, or MERGE statement.

Therefor, I consider this article in interesting mental exercise and good information on the information schema tables, but not a technique to be used in a production system.

On a final note: Any system with a case sensitive database collation will cause these queries to fail; the table and column names of the information schema tables are all uppercase.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #965742
Posted Monday, August 9, 2010 7:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:46 PM
Points: 106, Visits: 369
The use of the term "schema" in this article is confusing because the term has a specific meaning in SQL Server which differs from its use here. Perhaps using the term "table definition" would be clearer.
Post #965948
Posted Monday, August 9, 2010 10:51 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
Hugo Kornelis (8/9/2010)
It's an interesting technique - but not one I would recommend!

The simple version has this caveat: it works "as long as they share the same primary key and the shared columns have the same names" (quote from the article). Verifying that is what takes time when preparing a data merge (be it a one-time operation or an ETL job that is to be run repeatedly). Building the actual statement is a matter of seconds, especially if you use SSMS' drag&drop techniques to copy the column list from the object explorer. The technique shown in this article doe indeed save you a couple of seconds and a few keystrokes, but at the price of having to use dynamic SQL (which means you have to run under a user account with elavated rights, and you incrue the risk of injection attacks). And worse - it invites laziness. There might be situations where the same name has been used for essentially different colum; I'll exclude those when working manuallly, but they'll be included by this procedure. Or there might be some subtle difference in how data is represented, making some conversion required.


You make some valid criticisms, and when dealing with a relatively small number of tables, I agree with you. Given just a small handful of tables, it is much better to hand craft the statements and ensure they are correct and perform exactly what you want and to avoid the dynamic SQL this entails..

I first used this technique when dealing with moving over a dozen slight variations of a table into one consolidated analysis table, and moreover in that case for certain business reasons I expected many more slight variations of that table to be generated and need to be merged in as well. In that case, generating it dynamically was vastly easier to write and more maintainable than statically creating the insert code for every one of the variations.

The second time I used it was when I had around 30 tables all of which had one of five slight variations of a schema that all needed to be merged into an analysis table in a one-time ETL job.

As to whether you should use it in production, I would certainly approach that with caution, but I try to avoid any dynamic SQL in production where that is possible. As mentioned, I have used this in moving from production servers to analysis/reporting servers. I have also used it for ETL for data that was going into production, but that always had a staging location involved for final verification before moving it to production


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #966113
Posted Monday, August 9, 2010 10:52 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, November 24, 2014 4:29 PM
Points: 752, Visits: 920
fahey.jonathan (8/9/2010)
The use of the term "schema" in this article is confusing because the term has a specific meaning in SQL Server which differs from its use here. Perhaps using the term "table definition" would be clearer.


You have a good point. I used the word schema as it is used in database theory, not as it is used specifically in SQL Server. Next time I talk about it I will be more careful to differentiate. Thanks for the suggestion!


---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #966116
Posted Monday, August 9, 2010 11:31 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:20 PM
Points: 18,064, Visits: 16,099
Thanks Timothy for taking the time to write this up and share it with us.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #966363
Posted Tuesday, August 10, 2010 4:19 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 22, 2013 11:59 AM
Points: 477, Visits: 719
Good article sir. I still can't find the +1 button on this site though

Atlantis Interactive - SQL Server Tools
My blog
Why I wrote a sql query analyzer clone
Post #966502
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse