Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

My technology blog on SQL Server, TSQL, XML, FILESTREAM and other areas of SQL Server.
Browse by Tags · View All
XML 112
TSQL 69
XQuery 69
XQuery Functions 67
XQuery Training 65
XQuery in TSQL 64
XQuery Tutorial 63
SQL Server XQuery 63
XQuery-Labs 57
BRH 38

Archive · View All
September 2008 32
August 2008 30
July 2008 21
August 2009 19
June 2009 19
May 2010 18
January 2009 15
January 2010 14
October 2008 14
June 2008 13

Generating scripts to copy data from one location/server/database to another - Part 2

Jul 17 2012 12:03PM by Jacob Sebastian   

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 Smile

Tags: 


Jacob Sebastian
1 · 100% · 32235
11
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

6  Comments  

  • Nice post Jacob.

    1. Until SQL Server 2005 I have used Narayana Vyas Kondreddi's stored proc for generating Insert script for a given table. Its a pretty cool script which had come handy to me many times in the past :)
    2. Since SQL Server 2008 we could generate the INSERT scripts via SSMS using the built in Generate Script utility itself. [think that is what you had explained in your earlier post too]
    commented on Jul 17 2012 9:16PM
    Vadivel
    489 · 0% · 79
  • Does anyone use the SIF method. It's the acronym I use to remember the sequence SELECT... INTO... FROM... It comes in especially handy when FROM is LinkedServer.Database.Owner.TableName pointing to an Oracle database.

    commented on Jul 18 2012 2:59AM
    dishdy
    16 · 10% · 3269
  • This problem with this is you have to manually get rid of the last UNION ALL.

    Also, your cast to VARCHAR will cast to VARCHAR(30) by default. ALWAYS put in a size so you don't get silent truncation. Because this is generating a SQL script, which I'll often use with EXEC or sp_ExecuteSQL, use nvarchar(max)

    SELECT
        'SELECT ' 
        	+ CAST(CustomerID AS VARCHAR) + ' AS CustomerID, '''
        	+ ISNULL(FirstName, '') + ''' AS FirstName UNION ALL'
    FROM Customers
    

    Instead, try something like this:

    SELECT    REPLACE(REPLACE(REPLACE([SQLStmts].[SelectStmt], '</SelectStmt><SelectStmt>', ' UNION ALL' + CHAR(13) + CHAR(10), ''), '<SelectStmt>', ''), '</SelectStmt>', '')
    FROM (          
              SELECT     N'SELECT' + CAST(CustomerId as nvarchar(max)) + N' as CustomerID, ''' + ISNULL(FirstName, '') + N''' AS FirstName' as [SelectStmt]
              FROM       Customers
              FOR XML PATH('')
         ) [SQLStmts] ([SelectStmt])
    
    commented on Jul 18 2012 5:56AM
    Marc Jellinek
    96 · 2% · 586
  • @Dishdy,

    You are right. In a lot of scenarios, an SIF may be more desirable than generating the script. I just remember the reason why a SIF does not work with us in most cases. Most of the tables have XML columns and linked server queries to tables having XML columns fails (even if the XML column is not in the SELECT list).

    commented on Jul 18 2012 6:32AM
    Jacob Sebastian
    1 · 100% · 32235
  • @Marc

    Agreed with you

    commented on Jul 18 2012 6:33AM
    Jacob Sebastian
    1 · 100% · 32235
  • Hi Jacob,

    Wouldn't you be safer to use QUOTENAME to wrap the values, to avoid problems with single quotes ?

    SELECT
        'INSERT INTO Customers (FirstName, LastName, City)
         SELECT ' + quotename( ISNULL(FirstName, '') , '''') + ',' 
        			+ quotename( ISNULL(LastName, '') , '''')  + ','
        			+ quotename( ISNULL(City, '') , '''')  + ''
    FROM Customers AS Query
    
    commented on Dec 4 2012 7:27AM
    MisterMagoo
    48 · 4% · 1213

Your Comment


Sign Up or Login to post a comment.

"Generating scripts to copy data from one location/server/database to another - Part 2" rated 5 out of 5 by 11 readers
Generating scripts to copy data from one location/server/database to another - Part 2 , 5.0 out of 5 based on 11 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]