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 |
|
TrashMan
Starting Member
4 Posts |
 Posted - 2011-10-03 : 04:37:19
|
| The company I recently joined mantains databases for many clients. We're now moving those databases to a new version. Which means we're updating their DB's to a new schema - backing them up, creating new DB's, and porting the data back into the new databases.Our DB expert does all of this trough TSQL statements..but has never documented anything and is rather difficult to get anything out of.I'm trying to see if some of that process can be automated. I am currently trying to create BIDS packages to do the data moving...But, I havn't doen anything in BIDS for over a year, and even before I've done very little.Now, since the new schema has more columns, I have to add those. For this I take the data from the old DB, use derived colums which I fill with NULL values, and put thet into the new databases. This works well enough for simpelr tables. But in some cases there are foreign keys and restriction..meaning that a very specific value has to be in some rows.Which is a problem for me in BIDS.The expressions I can put in the dervied colum seem very limited.What would be the best way to go about this?Just fill everything with nulls and run a lookup on that data before putting it in? An OLE DB command?Is there any Data Flow Transformation that would be better for this task?I have ideas, but I'm not sure how feasable any of them are or how good they are. So I'm asking - how would you do it? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-03 : 04:53:44
|
| if you need to put some specific value in all columns why not fill them at first with NULL values and then add a Execute sql task to do set based update later to get required values?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
TrashMan
Starting Member
4 Posts |
Posted - 2011-10-03 : 07:24:45
|
| I thought of that...but, I have to change data before putting them into the database. IIRC, a execute SQL would not apply to the dataset in the data flow, would it?After I add the new colums, that data is not yet written in the database (and it can't be due to dependancies and foreign key restrictions, as some of the new columns don't allow nulls). I can run SQL commands on databases, but on this, I'm confused.Alternatively, I could find what values are allowed, put one of those in, and then commit that change and change that later.I though of replacing the nulls with proper values by using a cursor and then looping trough all the rows, comparing to a refference table and changing values when needed.EDIT:A stupid question - I can put in some expressions in the derived column, but how do you simply put in a simple value? Like for a example a number.I use this as an expression to get NULL -> NULL(DT_STR,50,1250)If I wanted to put in 'XCD' for example... I still havne't found out how. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-03 : 07:55:18
|
quote:
Originally posted by TrashMan
I thought of that...but, I have to change data before putting them into the database. IIRC, a execute SQL would not apply to the dataset in the data flow, would it?After I add the new colums, that data is not yet written in the database (and it can't be due to dependancies and foreign key restrictions, as some of the new columns don't allow nulls). I can run SQL commands on databases, but on this, I'm confused.Alternatively, I could find what values are allowed, put one of those in, and then commit that change and change that later.I though of replacing the nulls with proper values by using a cursor and then looping trough all the rows, comparing to a refference table and changing values when needed.EDIT:A stupid question - I can put in some expressions in the derived column, but how do you simply put in a simple value? Like for a example a number.I use this as an expression to get NULL -> NULL(DT_STR,50,1250)If I wanted to put in 'XCD' for example... I still havne't found out how.
you can just put constant number in expression column and map it against reelevant column. in case its data type is different make sure you cast it to type of column------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-03 : 09:55:21
|
Probably no help if you want to use the GUI tools etc., but we do this sort of thing with SQL scripts (as your DBA does, probably). We mechanically generate the scripts, so its easy for us to generate a script to copy "TableA, TableB, ... FROM ServerA.DatabaseA to ServerB.DatabaseB"Then easy to manually tinker if we need a column to have a default value if not present in ServerA.DatabaseA, or if NULL, or some other criteria.Another way of doing it would be to set up VIEWs on the Source database with all Tables / COlumns as-per the Target. So any non-existent columns on Source would be presented in the VIEW with dummy values (either NULL, or the default value you required)Then you can just do:INSERT INTO TargetServer.TargetDatabase.dbo.TargetTableSELECT *FROM SourceServer.SourceDatabase.dbo.SourceTable_VIEW SourceTable_VIEW would be something like:CREATE dbo.SourceTable_VIEWASSELECT Col1 AS Col1, Col2 AS Col2NewName, ... NULL AS Col98NewName, 'ABC' AS Col99NewName ...FROM dbo.SourceTable |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-03 : 12:03:40
|
| please keep in mind that you need to set up a linked server connection to SourceServer before you use four part naming convention like INSERT INTO TargetServer.TargetDatabase.dbo.TargetTableSELECT *FROM SourceServer.SourceDatabase.dbo.SourceTable_VIEW------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-03 : 12:44:42
|
| Ah, good point. That may be more hassle than its worth if not already set up - or if the attempt to make a Linked Server turns out NOT to be trivial ... |
|
|
|
TrashMan
Starting Member
4 Posts |
Posted - 2011-10-05 : 09:22:29
|
| Thank you guys.Filling the tables wiht some temporary value and updating later seems the best way to go.And the reason why I had trouble with Derived colum was because it kept changing the data type when I chenged hte expression!! How easily can one waste time on sillly things like that!EDIT: Why the hell didn't Microsoft put alphabetical sorting in object explorer? |
|
|
|
|
|
|
|
|
Advice on moving database to new schema