New In-Person Training Courses for Phoenix, Metropolitan Dallas and San Francisco!
With our in-person training partner SolidQ, SSWUG is proud to announce three new classes that will take place in February!
If you’re interested in learning more about the Microsoft Business Intelligence stack, we have hands-on labs in San Francisco, Phoenix and Irving, TX.
Click here to review the upcoming list of classes and sign up today.
Database Data Scripting Guidance
Today I received two helpful comments regarding moving data between SQL Server databases. Michael demonstrates some limits and performance options when using Insert Statements. Max has a nice demonstration of using PowerShell to script the export/import of data.
Michael:
I am writing regarding the Insert Into Values statement for the copying large database. People should know that the maximum amount of rows per a single insert into values statement that can be inserted is 1000. This is per the below article:
http://technet.microsoft.com/en-us/library/dd776382.aspx
Also, about 2 years ago I evaluated all of the new 2008 Insert formats. I no longer have the email with the exact results but the Insert Into with multiple values was somewhat sluggish and was similar to inserting multiple rows through passing in an XML file. Bulk copy and passing in .Net DataTables performed the best and had similar times. We went with saving .Net DataTables even though there was additional overhead because it was easier to manage from an application than moving a file and executing the bulk copy command.
Max:
Just a quick FYI to let you know I got a PowerShell Module specifically for scripting SQL tables in CodePlex call "SQLDevTools":
http://pssqldevtools.codeplex.com/
One of my favorite functions is the Write-TSQLScriptInsertSelect , which will create the "Insert into… Select .." TSQL Script. This all done in PowerShell with embeded T-SQL scripting.
Here’s an example of my Write-TSQLScriptInsertSelect function:
#Prepare you parameters values for the function:
$SourceSQLInstanceName = "ISO-DESKTOP-65";
$DestinationSQLInstanceName = "ISO-DESKTOP-65MSQLDENALICTP3";
$SourcedbName = "AdventureWorks";
$DestinationdbName = "Developer";
$TableToCopy = "HumanResources.Employee",`
"Purchasing.PurchaseOrderDetail",`
"Sales.SalesPerson";
#Example to create a T-SQL InsertSelect statements:
[Array] $InsertSelect = Write-TSQLScriptInsertSelect `
-SourceSQLInstanceName $SourceSQLInstanceName `
-DestinationSQLInstanceName $DestinationSQLInstanceName `
-SourcedbName $SourcedbName `
-DestinationdbName $DestinationdbName `
-TableToCopy $TableToCopy `
-RowLimit 10;
At the end, you’ll get three Insert…Select T-SQLScript to assist you in copy some data to another location. Also, by saving the result into an you can use PowerShell to export the result to an Out-File command to create you *.sql file.
Here’s an example of one of the output T-SQL script from the variable $InsertSelect:
USE [DEVELOPER]
GO
SET IDENTITY_INSERT [DEVELOPER].[PURCHASING].[PURCHASEORDERDETAIL] ON
INSERT INTO [DEVELOPER].[PURCHASING].[PURCHASEORDERDETAIL](
PurchaseOrderID
,PurchaseOrderDetailID
,DueDate
,OrderQty
,ProductID
,UnitPrice
,ReceivedQty
,RejectedQty
,ModifiedDate
)
SELECT TOP 10
PurchaseOrderID
,PurchaseOrderDetailID
,DueDate
,OrderQty
,ProductID
,UnitPrice
,ReceivedQty
,RejectedQty
,ModifiedDate
FROM [ISO-DESKTOP-65].[ADVENTUREWORKS].[PURCHASING].[PURCHASEORDERDETAIL]
GO
SET IDENTITY_INSERT [DEVELOPER].[PURCHASING].[PURCHASEORDERDETAIL] OFF
This is one of the strong benefit of using PowerShell as a tool for the DBA, and there’s a few other SQL-Oriented Modules. By the way, Did you notice my PowerShell function does know to identify an "Identity" column? Sweet ha!! I have tested my module against AdventureWorks making sure that everything works.
While the function demonstrated by Max does not actually generate a script, instead it inserts the data directly requiring a database on the same instance (as demonstrated), it could readily be adapted to generate insert scripts. In fact, this technique could easily be modified to work with other data engines (which Insert Statements should be rather generic to any SQL Engine) allowing for different syntax.
One of the other options we haven’t considered to this point is the use of BulkCopy. More on that tomorrow.
Drop me an Email if you have other suggestions for moving data from one database to another where there is no direct connectivity or database verions prohibit direct transfer techniques such as backups. Reach me at btaylor@sswug.org.
Cheers,
Ben
SSWUG TV
With Stephen Wynkoop and Sam Brace
Check out the Kinect for Windows rollout and more in this edition of SSWUG.TV.
[Watch the Show]
$$SWYNK$$
Featured Article(s)
Overview of SQL Server Indexes
A detailed description of what indexes in SQL Server are and how they work.
Featured White Paper(s)
Office 365 SharePoint Online – what does it mean to my organization as a CIO?
Written by AvePoint
The goal of this white paper is to clarify the disjoint… (read more)
Featured Script
dba3_Implementing_and_maintaining_the_Calendar_design_Article
Modeling Date Logic III: Implementing Business Calendars (by example in MS Sql Server 2000)http://bitonthewire.wpengine.com/see/17379 A d… (read more)