Tuesday, November 24, 2009

Updating Columns in a Table from Data from Another

Let us imagine that you are attempting a migration of data from a schema in an existing third party application. Then you noticed that the tables are simply over-normalised and you have a need update a column in a target table from another table. What should you do ?

This is what I normally do :-

1) Identify the common field or fields  in both say GLCODE
2) Identify the target column to populate in your target table and the source column in the source table
say GLNAME

To achieve your objective, EXECUTE this SQL statement


UPDATE  TargetTableName SET GLNAME=(SELECT GLNAME  FROM SourceTableName WHERE GLCODE=SourceTable.GLCODE)

That's it !

Saturday, November 14, 2009

How to Shrink a SQLExpress 2008 Database

A SQLExpress 2008 database usually comprises of a database and a log file with the extension *.MDF and *.LDF respectively. As time goes by, the LDF can grow dramatically and I have seen the LDF file being more than triple the size of the MDF !

So, how does one shrink the size of MDF and LDF ? It is really simple to do so using the Management Studio for SQL Express 2008

1. Launch Management Studio
2. Select the target database
3. Right-click and select All Tasks

4. Select Shrink Database

5. Check Reorganise files... as below :-



and click OK, That's it !