Archives for December 2013

Insert data into new (or existing table) from another (T-SQL, PL/SQL, SQL Server, Oracle)

Often times we wish to copy data from one table into another.  The methods of doing this where the new table does not exist will be slightly different from the case in which it does exist.  Also I will show display the method of doing this in T-SQL and PL/SQL (just because I get confused all the time when I switch databases Smile ).

SQL copy tables to new table

When performing this command, a table will created using the same data types as the columns which as selected in the select statement.  This is usually very useful when making a copy of the existing table before making any alterations to the data.




SQL copy tables to existing table

These following when executed will insert data into already existing table.  It will match fields based on name if you choose to list the fields in the select clause.  This normally useful for when you need to reinsert data (don’t ask why… Smile)



How to combine values from multiple rows of a single column (T-SQL, Microsoft SQL Server, FOR XML PATH, CSV ).

The Problem:

I need a way to roll-up multiple rows into one row and one column.  In essence I needed to convert something that looked like this:

Original SubCategories Lisitng

Original SubCategories Listing

Into something that looks like this:

SubCategories formatted data

SubCategories formatted data

The Solution:

The solution I will show will make use of the FOR XML PATH clause and STUFF commands in T-SQL.


Will will make use of the XML functionality of SQL Server to amalgamate the series of rows into one row.  In order to create a XML string you must append the FOR XML command after any regular query.   There are four options to append to the FOR XML PATH command which will alter what the result will look like.  These options are either RAW, AUTO, EXPLICIT, or PATH.  For this solution we will use the PATH option (will explain these four option in more detail in a later post).

The following query shows a regular query which retrieves the subcategory name in an XML string

The results look like the following:


 You will notice that you get a formatted XML string without a root node.  To convert this list to a CSV string we just need to append a comma (“,”)  to the name field.

The following sql query demonstrates the XML string being converted to a CSV string:

CSV Result

CSV Result

This is getting a bit closer to the results we want, but we if you haven’t noticed the string begins with a comma which we should remove.  In order to do this we will use the t-sql STUFF function

STUFF() Function

The syntax of the t-sql STUFF function looks like the following:

To remove the first character of the string all we need to so is do something like the following:

This says, starting with the first 1 character place, replace 1 characters with nothing ''.

What does the final solution look like?

SubCategories formatted data
SubCategories formatted data



Run Transact-SQL Script Files Using sqlcmd–SQL Server T-SQL


I recently received a script which creates a database and loads data.  I was unfortunately too large for SQL Server Management Studio to process.


You can use the sqlcmd utility to run the script file. The example below shows the execution of a script file called script.sql which is located on the C drive and a connection is made to the SQL Server using integrated security.

To run the script file

  1. Open a windows command prompt window.
  2. In the command prompt window, type: sqlcmd –S SQLServerName-i C:\DatabaseLoad.sql
  3. Press ENTER.

How to delete duplicate records from your SQL Server tables – T-SQL

Below you will find two methods that can be used to remove duplicate records from you Microsoft SQL Server tables.

Method 1:

This method will only work in SQL Server 2005 and later; if you need to remove duplicates from an earlier version please upgrade you SQL Server version.  Just kidding!  Use the technique in method 2.  Actually you really should upgrade your version of SQL Server…

This method uses the Common Table Expression functionality and row_number function which was introduced in Microsoft SQL Server 2005.   The key strategy to this method is to generate a number next to the  records which denotes the row number.

So in essence we want to transform this:

Duplicated Records

Duplicated Records

To this:


Duplicated Results with Numbers

And remove all instances of those rows which the row number column do not correspond to 1.

Below is a script which can be used to demonstrate this.   Also you can use this link in SQL Fiddle.

Method 2:

This method should work on all versions for SQL Server.  It basically works by copying the unique rows out of the original table into a temporary table, and then deleting all records from the original table;  the unique rows from the temporary table are then copied into the original table.

Link to SQL Fiddle is here.

— Create Table

How to create a blocking transaction in SQL Server – T-SQL (exclusive lock)

Excuse the odd topic.   I often like to experiment with SQL Server and create odd situations which we normally should be avoiding.  One of those things is creating SQL Statement which get blocked and creating a timeout.  One of the simplest ways to run a query against a table which is locked in another in another session.  To do this do the following:

1) Create a lock on a table.  

The example below demonstrates a lock on the Person table in the AdventureWorks2012 sample table.    The key points which make this work is to create a transaction, and then issuing a query using the TABLOCKX and HOLDLOCK table lock hints.  This will force an exclusive lock on the table.  To keep the lock for significant amount of time we specify that the transaction stays alive for a minute by introducing the WAITFOR DELAY ’00:01:00′ command, which will prevent any further execution of statements until a minute has passed.

2)  Now open up another session in SQL Server (new query from Microsoft SQL Server Management Studio), and execute the following query:

You will notice that after this query is excuted, the query seems to run forever.  It will actually never  execute until the query in step 1 is finished.

3) Check for blocked transaction.  You can find this by opening up an new query in Microsoft SQL Server Management Studio and executing the following query:

I will explain in more detail how this blocking query works in a later post.

If you have any other tips on how to create a blocked transaction please share them in the comments below.

About Me

%d bloggers like this: