T-SQL: String Search

Please see my other Database Development articles. Returning selection portions of values in a resultset. When searching for specific values within a field, returning portions surrounding the search criteria may be very helpful for providing the user a context in which the value in being used. In this example, I will illustrate searching within a…

T-SQL: Stored Procedure Default Values

Please see my other Database Development articles. Building flexibility into data INSERT operations. Often when designing an application that receives user input, it’s difficult to determine beforehand exactly which fields will be supplied where a user completes a form. SQL Server provides the ability to supply default values for parameters of stored procedures to allow…

T-SQL: DELETE FROM

Please see my other Database Development articles. Performing DELETE using a JOIN One of the most common actions necessary when deleting records is to delete some using relationships. One example of deleting via a relationship is utilizing a JOIN to isolate a recordset targeted for deletion. In this example, I’ll first create two temporary tables…

T-SQL: Cross-Database Logic

Please see my other Database Development articles. Executing T-SQL against different databases Often when accessing and manipulating data, requirements demand logic that crosses databases. Performing such actions do not require drastically different T-SQL, only an understanding of the syntax. In this example, I begin with two databases with identical tables and nearly identical records. For…

xSQL Data Compare

Please see my other Database Development articles. Managing Database Change While the xSQL package also comes with xSQL Object Compare, an impressive tools for managing changes between database instances, this article will focus solely on the product used for managing data changes, xSQL Data Compare. Produced by xSQL, xSQL Data Compare represents a robust application…

T-SQL: User Defined Functions

Please see my other Database Development articles. Creating reusable logic As with Object-Oriented Programming (OOP) where it’s important to separate code used frequently into reusable modules, in T-SQL the same need exists. In this example I demonstrate a user-defined function (UDF) that receive a string from which all pre-defined matching blocks of HTML are found…

T-SQL: Subquery – SELECT

Please see my other Database Development articles. Performing multiple, concurrent queries Often when needing a resultset, there’s a need to perform multiple queries to derive the desired data. In this example, we have multiple records of people, from which we need an aggregated report. In order to accomplish this goal, I must perform two separate…

SSIS: Using Views

Using Views to generate source data within a package. Following the same steps to create a package in the Table to text file article, I begin with an SSIS Project and a starting package. Instead of using the default package, I’ll delete it and select to create a new package using the Import/Export Wizard. After…

SSIS: Text file sources

Reading data from text files. I’ll begin with two separate text files, each representing sales from two states: California and Oregon. First, I’ll add a DataFlow task and add add to it two Flat File sources each configured to pull in data from the text files. When configuring the sources, it’s imperative to ensure the…

SSIS: Table to Text file

Creating an SSIS Package to consume a table and write the recordset into a text file. Rename package to match project Next, Create data source allowing access to sql table. create connection manager. Next, select the source database. Create a data flow task and give it a name indicating its purpose. Double-click the task to edit…