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.
| Author |
Topic |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
 Posted - 2010-07-15 : 14:15:15
|
| GreetingsI want to create a UI for some BULK data import. The source database will be a combination of tables and views. I have totally no control over the source databases. Therefore today a field that is varchar(512) could change to varchar(1024). I would like to create a process that will create a staging table on the fly, dynamically during the import process if and only if some schema change has happened on the source database. My vision is I would to create metadata table that I can use for mapping during this process. So every time the process runs it checks schema of source against metadata of schema (which is populated automatically) table.So my question is there a way of grabbing schema metadata of sql objects (tables + views) Or is my vision just a pipe dream, nightmare or hallucinations?ThanksIf you don't have the passion to help people, you have no passion |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2010-07-16 : 20:25:05
|
| you can create a table in your staging from your source with a SELECT * INTO StagingTable FROM SourceTable WHERE 0=1.. so you just get the schema.. and either use a temp table for the Staging table which will automatically get dropped or you can create a normal table and make sure you manually drop it after your ETL process. Just a word of caution though to keep in mind if you have a lot of users running kicking off this process you could have a large number of objects being created/dropped leading to some side effects such as query plan recompiles (on the tables being used in queries/procs) because SQL Server thinks the schema may have changed..Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
|
|
|
dynamic staging table creation