Category Archives: Uncategorized

Uncategorized

Best and Easiest Way to Make a Copy of a Table Including Existing Data

Leave a comment

I have a new developer learning the ropes and who has created his first application with a simple CRUD interface.  Before placing the app into production I wanted to make sure to retain the existing contents of the table that would be manipulated, because, you know, stuff happens.  I went searching for a solution and came across this Technet article that exposed a beautiful mechanism for accomplishing this that I was not aware existed:


SELECT * INTO schema.new_table FROM schema.existing_table

And that’s it.  This will create the brand new table (it cannot already be created) containing all of the information from the existing table.

Simple and elegant.  Doesn’t get much better or easier than that.

Happy coding!

Uncategorized

Checking for NULL values in a CASE Statement

Leave a comment

This is a pretty simple need, with an equally simple solution.

I was initially using the simple format of the CASE statement:

CASE input_expression
     WHEN NULL THEN result_expression
   [ ELSE else_result_expression ]
END

…which does not handle this situation.  The solution is to change it to the searched version:

Searched CASE expression:

CASE
     WHEN Boolean_expression IS NULL THEN result_expression
     [ ELSE else_result_expression ]
END
Uncategorized

Helpful Dev Tools

Leave a comment

Sometimes in my rush to fix an issue or upgrade a component I commit the big programming no-no of updating production systems directly and forget to ever apply the new code to my testing environment.  Later on it comes time to make another update and I remember that my code base is out of sync.  Lucky for me there is a fantastic piece of open source software available that solves this issue.  It is called WinMerge, and it can be used to compare single files or even whole directories and sub-directories.  It differences in files are found, they can be investigated, which is where this product really shines.  WinMerge will open each file in a side-by-side comparison and highlights with different colors the discrepancies in the files.  This is a fast and easy-to-use solution for findng the differences in your code.

Another challenge I’ve faced is sizing elements in a front-end interface.  Before I would use a thuggish approach of setting a value and refreshing my app to see the results.  Now with the help of an awesome little tool from Mioplanet called Pixel Ruler I can measure the space of elements directly on the screen and adjust their size accordingly.  This small easy tool has saved me a lot of time.  Mioplanet also has some other pretty cool (and FREE) tools to checkout.

Hope these tools can help you out.  Code On!

Uncategorized

Gathering Multiple Dataset Information in a Single Query

Leave a comment
I’ve come across situations before and again this morning where I had to compile information in one report from multiple datasets.   Usually this is no problem, however this morning I found myself needing to use the results from one dataset to filter out results from a separate dataset.  My first data set grabbed a distinct list of codes from an access database on a network share and I needed to feed that list as a filter to a second query to a SQL Server table.  My solution was to store that data from the Access query in a report parameter that I could then feed into the second query.  To do this I created a report parameter, set its internal attribute to true (checked), and defaulted its values to that of the query from the Access database.  In my second query I then used the report parameter name in the IN statement of the WHERE clause to provide the filtering, as such:
 
SELECT <column_name>
FROM <table_name>
WHERE <column_name_to_filter> IN (@ReportParameter)
 
This solution works beautifully, but is limited to simple result sets.  I’m still interested in finding solutions to handle more complex data from multiple sources. 
 
Hope this helps.  Happy Reporting!