This is a follow-up post to my previous blog post on Generating scripts for schema and data. While the method we discussed in the previous post was very much standard, in this post we will examine a few not-so-common tricks that may be helpful in some scenarios.

Everyone reading this may be familiar with different techniques/methods for moving data from one location/server/database to another. Each one of us may be using a particular method (or set of methods) that suites our requirement/environment best.

Frequently used methods in my environment for moving/copying data

Usually we use (in our environment) an SSIS package to move data from one location to another. For copying a table from one server in the network to another, the import/export wizard may be found to be very handy. I usually see it to be quite efficient when copying really large tables.

Occasional SELECT query to generate INSERT scripts

Sometimes, if the table is small and the number of columns to transfer are less, I quickly write a SELECT query that produces the INSERT statements. It is not ‘always’ but there are times when I really found this to be really handy.

Let us assume that we have a table as follows.

IF OBJECT_ID('Customers','U') IS NOT NULL BEGIN
	DROP TABLE Customers
END
CREATE TABLE Customers (
	CustomerID INT IDENTITY,
	FirstName VARCHAR(20),
	LastName VARCHAR(20),
	City VARCHAR(20) 
)

INSERT INTO Customers (FirstName, LastName, City)
SELECT 'Jacob', 'Sebatian','Ahmedabad' UNION ALL
SELECT 'Pinal', 'Dave', 'Bangalore'

Assuming that i need to copy the FirstName, LastName and city columns, a query like the following can quickly give us the script.

SELECT
	'INSERT INTO Customers (FirstName, LastName, City)
	 SELECT ''' + ISNULL(FirstName, '') + ''',''' 
				+ ISNULL(LastName, '') + ''','''
				+ ISNULL(City, '') + ''''
FROM Customers AS Query

/*
----------------------------------------------------
INSERT INTO Customers (FirstName, LastName, City)
	 SELECT 'Jacob','Sebatian','Ahmedabad'
INSERT INTO Customers (FirstName, LastName, City)
	 SELECT 'Pinal','Dave','Bangalore'
*/

A CTE version?

A slight variation of this type of query has helped me in a number of times. The scenario is more like the following (fictitious example): The support team may have identified a few hundred customers who reported problems with our IVR system. The DBA needs to quickly run a query in the server running IVR database to see what happened with the calls of those customers. The customer database is huge, so is the IVR log. Attempted a linked server query joining the two servers/databases and it is taking for ever to complete. The phone is ringing..it is the manager on the other side.

In such a scenario, it may be beneficial to generate the script for a CTE on the customer database, copy it to the other server and do a JOIN with the CTE. The following example demonstrate this approach.

-- Original Query	
SELECT columnlist
FROM IVRLog ivr WITH(NOLOCK)
INNER JOIN OtherServer.MainDB.dbo.Customers c
	ON c.CustomerID = ivr.SomeColumn
	AND c.ProblemFlag = 'something'

As we can see, this is a linked server query between two servers and between two huge tables and it is expected to take some time. In this example, we know the customers we are trying to track are just 200 and may be there is a way we can remove the need for a linked server query. Let us try to create a CTE with the customer data and run it on the IVR server.

The following query (with the required variations) produces the SELECT statements that can go inside the CTE

-- Query to produce the cte on the customer database
SELECT
	'SELECT ' 
		+ CAST(CustomerID AS VARCHAR) + ' AS CustomerID, '''
		+ ISNULL(FirstName, '') + ''' AS FirstName UNION ALL'
FROM Customers 

-- Produces the following
/*
SELECT 1 AS CustomerID, 'Jacob' AS FirstName UNION ALL
SELECT 2 AS CustomerID, 'Pinal' AS FirstName UNION ALL
*/

Now we can copy the output into the other server and put it into a cte as follows

-- Modified Query
;WITH cte AS (
	SELECT 1 AS CustomerID, 'Jacob' AS FirstName UNION ALL
	SELECT 2 AS CustomerID, 'Pinal' AS FirstName
) -- Remove UNION ALL from the last line
SELECT columnlist
FROM IVRLog ivr WITH(NOLOCK)
INNER JOIN cte c
	ON c.CustomerID = ivr.CustomerID

The intention of sharing this is not to claim that this is a fantastic method. This is something that I have used a number of times and benefitted from. However, it may or may not be useful to many of you depending upon what you are trying to do and several environmental factors. So use it wisely