Archives for December 2013

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

December 24, 2013 by Carlos Ferreira 4 Comments

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.

T-SQL:

SELECT * 
INTO Sales.CustomerBackup 
FROM Sales.Customer;

SELECT *

INTO Sales.CustomerBackup

FROM Sales.Customer;

PL/SQL:

CREATE TABLE CustomerBackup 
AS 
SELECT * FROM Customer;

CREATE TABLE CustomerBackup

SELECT * FROM Customer;

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 )

T-SQL:

INSERT INTO Sales.CustomerBackup (PersonID, StoreID, ModifiedDate, AccountNumber, rowguid)  
SELECT PersonID, StoreID, ModifiedDate, AccountNumber, rowguid FROM Sales.Customer;

1 2	INSERT INTO Sales.CustomerBackup (PersonID, StoreID, ModifiedDate, AccountNumber, rowguid) SELECT PersonID, StoreID, ModifiedDate, AccountNumber, rowguid FROM Sales.Customer;

PL/SQL:

INSERT INTO CustomerBackup 
SELECT * FROM Customer;

1 2	INSERT INTO CustomerBackup SELECT * FROM Customer;

Filed Under: Oracle, SQL Server, Technology Tagged With: Copy, Data, Oracle, PL/SQL, SQL Server, T-SQL, Table

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

December 19, 2013 by Carlos Ferreira 4 Comments

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 Listing

USE AdventureWorks2012; 
GO 
SELECT C.Name, 
       SC.Name 
FROM   [Production].[ProductCategory] C 
       LEFT JOIN [Production].[ProductSubcategory] SC 
              ON C.ProductCategoryID = SC.ProductCategoryID;

USE AdventureWorks2012;

SELECT C.Name,

SC.Name

FROM [Production].[ProductCategory] C

LEFT JOIN [Production].[ProductSubcategory] SC

ON C.ProductCategoryID = SC.ProductCategoryID;

Into something that looks like this:

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.

FOR XML PATH Clause

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

SELECT Name from [Production].[ProductSubcategory] FOR XML PATH('');

1	SELECT Name from [Production].[ProductSubcategory] FOR XML PATH('');

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:

SELECT ',' + Name from [Production].[ProductSubcategory] FOR XML PATH('');

1	SELECT ',' + Name from [Production].[ProductSubcategory] FOR XML PATH('');

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:

STUFF ( character_expression , start , length , replaceWith_expression )

1	STUFF ( character_expression , start , length , replaceWith_expression )

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

Stuff( result of the query listed above, 1, 1, '')

1	Stuff( result of the query listed above, 1, 1, '')

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

What does the final solution look like?

USE AdventureWorks2012
GO
SELECT CAT.Name AS [Category],
	   SubCatName =
            STUFF((    SELECT ','+ SUB.Name AS [text()]
                        FROM Production.ProductSubcategory SUB
                        WHERE
                        SUB.ProductCategoryID = CAT.ProductCategoryID
                        FOR XML PATH('') 
                        ), 1, 1,'')
FROM  Production.ProductCategory CAT;

USE AdventureWorks2012

SELECT CAT.Name AS [Category],

SubCatName =

STUFF(( SELECT ','+ SUB.Name AS [text()]

FROM Production.ProductSubcategory SUB

WHERE

SUB.ProductCategoryID = CAT.ProductCategoryID

FOR XML PATH('')

), 1, 1,'')

FROM Production.ProductCategory CAT;

: SubCategories formatted data

References

Filed Under: Uncategorized

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

December 15, 2013 by Carlos Ferreira Leave a Comment

Problem:

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

Solution:

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

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

Filed Under: SQL Server, Technology, Uncategorized Tagged With: command line, Load, script, SQL Server, SQLCMD, T-SQL

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

December 12, 2013 by Carlos Ferreira Leave a Comment

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

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.

-- Create Table
CREATE TABLE PhoneticAlphabet (Alphabet char(1),  CodeWord varchar(20))

-- Load Sample Data in Table
INSERT INTO  PhoneticAlphabet VALUES ('A', 'Alfa')
INSERT INTO  PhoneticAlphabet VALUES ('B', 'Brave')
INSERT INTO  PhoneticAlphabet VALUES ('C', 'Charlie')
INSERT INTO  PhoneticAlphabet VALUES ('D', 'Delta')
INSERT INTO  PhoneticAlphabet VALUES ('E', 'Echo')
INSERT INTO  PhoneticAlphabet VALUES ('F', 'Foxtrot')
INSERT INTO  PhoneticAlphabet VALUES ('G', 'Gulf')
INSERT INTO  PhoneticAlphabet VALUES ('H', 'Hotel')
INSERT INTO  PhoneticAlphabet VALUES ('I', 'India')
INSERT INTO  PhoneticAlphabet VALUES ('J', 'Juliett')
INSERT INTO  PhoneticAlphabet VALUES ('A', 'Alfa')
INSERT INTO  PhoneticAlphabet VALUES ('B', 'Brave')
INSERT INTO  PhoneticAlphabet VALUES ('C', 'Charlie')
INSERT INTO  PhoneticAlphabet VALUES ('D', 'Delta')
INSERT INTO  PhoneticAlphabet VALUES ('E', 'Echo')
INSERT INTO  PhoneticAlphabet VALUES ('F', 'Foxtrot')
INSERT INTO  PhoneticAlphabet VALUES ('G', 'Gulf')
INSERT INTO  PhoneticAlphabet VALUES ('H', 'Hotel')
INSERT INTO  PhoneticAlphabet VALUES ('I', 'India')
INSERT INTO  PhoneticAlphabet VALUES ('J', 'Juliett')

--See table before deletion
SELECT * FROM PhoneticAlphabet ORDER BY Alphabet;

--Display what the table will look like when we sort it by Alphabet and CodeWord
--and use the row_number function over the dataset see http://technet.microsoft.com/en-us/library/ms186734.aspx
--for infromation of row_number function.
SELECT
  Alphabet
  , CodeWord
  , row_number() over (partition by Alphabet, CodeWord order by Alphabet) as rn
FROM PhoneticAlphabet;

--Create a Common Table Expression and delete all rows which value greater than 1
WITH PhoneticAlphabetWithRows AS (
SELECT
  Alphabet
  , CodeWord
  , row_number() over (partition by Alphabet, CodeWord order by Alphabet) as rn
FROM PhoneticAlphabet
)
DELETE PhoneticAlphabetWithRows where rn &gt; 1;

--See the table after deletion
select * from PhoneticAlphabet ORDER BY Alphabet;

-- Create Table

CREATE TABLE PhoneticAlphabet (Alphabet char(1), CodeWord varchar(20))

-- Load Sample Data in Table

INSERT INTO PhoneticAlphabet VALUES ('A', 'Alfa')

INSERT INTO PhoneticAlphabet VALUES ('B', 'Brave')

INSERT INTO PhoneticAlphabet VALUES ('C', 'Charlie')

INSERT INTO PhoneticAlphabet VALUES ('D', 'Delta')

INSERT INTO PhoneticAlphabet VALUES ('E', 'Echo')

INSERT INTO PhoneticAlphabet VALUES ('F', 'Foxtrot')

INSERT INTO PhoneticAlphabet VALUES ('G', 'Gulf')

INSERT INTO PhoneticAlphabet VALUES ('H', 'Hotel')

INSERT INTO PhoneticAlphabet VALUES ('I', 'India')

INSERT INTO PhoneticAlphabet VALUES ('J', 'Juliett')

INSERT INTO PhoneticAlphabet VALUES ('A', 'Alfa')

INSERT INTO PhoneticAlphabet VALUES ('B', 'Brave')

INSERT INTO PhoneticAlphabet VALUES ('C', 'Charlie')

INSERT INTO PhoneticAlphabet VALUES ('D', 'Delta')

INSERT INTO PhoneticAlphabet VALUES ('E', 'Echo')

INSERT INTO PhoneticAlphabet VALUES ('F', 'Foxtrot')

INSERT INTO PhoneticAlphabet VALUES ('G', 'Gulf')

INSERT INTO PhoneticAlphabet VALUES ('H', 'Hotel')

INSERT INTO PhoneticAlphabet VALUES ('I', 'India')

INSERT INTO PhoneticAlphabet VALUES ('J', 'Juliett')

--See table before deletion

SELECT * FROM PhoneticAlphabet ORDER BY Alphabet;

--Display what the table will look like when we sort it by Alphabet and CodeWord

--and use the row_number function over the dataset see http://technet.microsoft.com/en-us/library/ms186734.aspx

--for infromation of row_number function.

SELECT

Alphabet

, CodeWord

, row_number() over (partition by Alphabet, CodeWord order by Alphabet) as rn

FROM PhoneticAlphabet;

--Create a Common Table Expression and delete all rows which value greater than 1

WITH PhoneticAlphabetWithRows AS (

SELECT

Alphabet

, CodeWord

, row_number() over (partition by Alphabet, CodeWord order by Alphabet) as rn

FROM PhoneticAlphabet

)

DELETE PhoneticAlphabetWithRows where rn > 1;

--See the table after deletion

select * from PhoneticAlphabet ORDER BY Alphabet;

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

CREATE TABLE PhoneticAlphabet (Alphabet char(1),  CodeWord varchar(20))

-- Load Sample Data in Table
INSERT INTO  PhoneticAlphabet VALUES ('A', 'Alfa')
INSERT INTO  PhoneticAlphabet VALUES ('B', 'Brave')
INSERT INTO  PhoneticAlphabet VALUES ('C', 'Charlie')
INSERT INTO  PhoneticAlphabet VALUES ('D', 'Delta')
INSERT INTO  PhoneticAlphabet VALUES ('E', 'Echo')
INSERT INTO  PhoneticAlphabet VALUES ('F', 'Foxtrot')
INSERT INTO  PhoneticAlphabet VALUES ('G', 'Gulf')
INSERT INTO  PhoneticAlphabet VALUES ('H', 'Hotel')
INSERT INTO  PhoneticAlphabet VALUES ('I', 'India')
INSERT INTO  PhoneticAlphabet VALUES ('J', 'Juliett')
INSERT INTO  PhoneticAlphabet VALUES ('A', 'Alfa')
INSERT INTO  PhoneticAlphabet VALUES ('B', 'Brave')
INSERT INTO  PhoneticAlphabet VALUES ('C', 'Charlie')
INSERT INTO  PhoneticAlphabet VALUES ('D', 'Delta')
INSERT INTO  PhoneticAlphabet VALUES ('E', 'Echo')
INSERT INTO  PhoneticAlphabet VALUES ('F', 'Foxtrot')
INSERT INTO  PhoneticAlphabet VALUES ('G', 'Gulf')
INSERT INTO  PhoneticAlphabet VALUES ('H', 'Hotel')
INSERT INTO  PhoneticAlphabet VALUES ('I', 'India')
INSERT INTO  PhoneticAlphabet VALUES ('J', 'Juliett')

--Lets see the duplicated records
SELECT * FROM PhoneticAlphabet;

--Insert the distinct rows from the duplicate rows table to a new temporary table.
SELECT DISTINCT * INTO #tmp FROM [PhoneticAlphabet];

--Delete all the rows from the original table
DELETE FROM PhoneticAlphabet;

--Copy the distinct rows from the temporary table back into the original table (which was empty).

INSERT INTO PhoneticAlphabet
SELECT * FROM #tmp; 

DROP TABLE #tmp;

--Lets now see the table without duplicates
SELECT * FROM PhoneticAlphabet;

CREATE TABLE PhoneticAlphabet (Alphabet char(1), CodeWord varchar(20))

-- Load Sample Data in Table

INSERT INTO PhoneticAlphabet VALUES ('A', 'Alfa')

INSERT INTO PhoneticAlphabet VALUES ('B', 'Brave')

INSERT INTO PhoneticAlphabet VALUES ('C', 'Charlie')

INSERT INTO PhoneticAlphabet VALUES ('D', 'Delta')

INSERT INTO PhoneticAlphabet VALUES ('E', 'Echo')

INSERT INTO PhoneticAlphabet VALUES ('F', 'Foxtrot')

INSERT INTO PhoneticAlphabet VALUES ('G', 'Gulf')

INSERT INTO PhoneticAlphabet VALUES ('H', 'Hotel')

INSERT INTO PhoneticAlphabet VALUES ('I', 'India')

INSERT INTO PhoneticAlphabet VALUES ('J', 'Juliett')

INSERT INTO PhoneticAlphabet VALUES ('A', 'Alfa')

INSERT INTO PhoneticAlphabet VALUES ('B', 'Brave')

INSERT INTO PhoneticAlphabet VALUES ('C', 'Charlie')

INSERT INTO PhoneticAlphabet VALUES ('D', 'Delta')

INSERT INTO PhoneticAlphabet VALUES ('E', 'Echo')

INSERT INTO PhoneticAlphabet VALUES ('F', 'Foxtrot')

INSERT INTO PhoneticAlphabet VALUES ('G', 'Gulf')

INSERT INTO PhoneticAlphabet VALUES ('H', 'Hotel')

INSERT INTO PhoneticAlphabet VALUES ('I', 'India')

INSERT INTO PhoneticAlphabet VALUES ('J', 'Juliett')

--Lets see the duplicated records

SELECT * FROM PhoneticAlphabet;

--Insert the distinct rows from the duplicate rows table to a new temporary table.

SELECT DISTINCT * INTO #tmp FROM [PhoneticAlphabet];

--Delete all the rows from the original table

DELETE FROM PhoneticAlphabet;

--Copy the distinct rows from the temporary table back into the original table (which was empty).

INSERT INTO PhoneticAlphabet

SELECT * FROM #tmp;

DROP TABLE #tmp;

--Lets now see the table without duplicates

SELECT * FROM PhoneticAlphabet;

Filed Under: SQL Server, Technology, Uncategorized Tagged With: Common Table Expression, CTE, distinct, duplicates, remove, row_number, SQL Server, SQL Server 2000, SQL Server 2005

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

December 10, 2013 by Carlos Ferreira 6 Comments

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.

USE AdventureWorks2012;
GO
BEGIN TRANSACTION
SELECT * FROM Person.Person WITH (TABLOCKX, HOLDLOCK);
WAITFOR DELAY '00:01:00' ---Wait a minute!
ROLLBACK TRANSACTION
--Release the lock

USE AdventureWorks2012;

BEGIN TRANSACTION

SELECT * FROM Person.Person WITH (TABLOCKX, HOLDLOCK);

WAITFOR DELAY '00:01:00' ---Wait a minute!

ROLLBACK TRANSACTION

--Release the lock

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

USE AdventureWorks2012;
GO
SELECT * FROM Person.Person;

USE AdventureWorks2012;

SELECT * FROM Person.Person;

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:

USE [master]
GO
SELECT  session_id
 ,db_name(database_id) as 'Database_Name'
 ,sql_text.text as 'SQL_Blocked'
 ,blocking_session_id
 ,wait_time
 ,wait_type
 ,last_wait_type
 ,wait_resource
 ,transaction_isolation_level
 ,lock_timeout
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as sql_text
WHERE blocking_session_id != 0

USE [master]

SELECT session_id

,db_name(database_id) as 'Database_Name'

,sql_text.text as 'SQL_Blocked'

,blocking_session_id

,wait_time

,wait_type

,last_wait_type

,wait_resource

,transaction_isolation_level

,lock_timeout

FROM sys.dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text(sql_handle) as sql_text

WHERE blocking_session_id != 0

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.

Filed Under: SQL Server, Technology Tagged With: Block, Lock, SQL Server, T-SQL, Transaction, Waitfor

About Me

December 9, 2013 by Carlos Ferreira Leave a Comment

Filed Under: Carlos Tagged With: about, Carlos Ferreira, Project Manager, Techie

Archives for December 2013

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

SQL copy tables to new table

T-SQL:

PL/SQL:

SQL copy tables to existing table

T-SQL:

PL/SQL:

Like this:

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

The Problem:

The Solution:

FOR XML PATH Clause

STUFF() Function

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

What does the final solution look like?

References

Like this:

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

Like this:

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

Like this:

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

Like this:

About Me

Like this:

Carlos Ferreira

Advertisement

Articles

Archives

Archives for December 2013

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

SQL copy tables to new table

T-SQL:

PL/SQL:

SQL copy tables to existing table

T-SQL:

PL/SQL:

Share this:

Like this:

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

The Problem:

The Solution:

FOR XML PATH Clause

STUFF() Function

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

What does the final solution look like?

References

Share this:

Like this:

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

Share this:

Like this:

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

Share this:

Like this:

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

Share this:

Like this:

About Me

Share this:

Like this:

Carlos Ferreira

Advertisement

Articles

Archives