SQL Server - Generate SQL INSERT from Excel

Generate SQL Server INSERT statements with Excel Concatenate.

A developer requested  access to a Production database. The details were to COPY and PASTE from a Excel spreadsheet into Access , which then updated SQL Server.  Upon asking him , why he used this method , he said “It’s the way I always insert this data to the lookup table”.

There are better ways . One way , is to use the “Concatenate” function in Excel.  It concatenates the ascii portions of the statement, adding single quotes where relevant.

In this example , there are four rows with the values in col A and B.  Create the formula for the first line, the copy to  the rest of the lines.

Copy and Paste the the INSERT statements directly into SQL Server Management Studio or create a text file.

See Also

Powershell , Excel charts and data presentation

Excel Connect to SQL Server

Data Loading Performance Guide

Bulk loading with large amounts of data  requires strategy.  The Data Loading Performance Guide for SQL Server 2008 and SQL Server 2005 is a very useful document.

 Highlights

Understanding Minimally Logged Operations
Trace Flag 610 (sql server 2008)
Summarizing Minimal Logging Conditions

Bulk Load Methods
Integration Services Data Destinations
BCP
BULK INSERT
INSERT ... SELECT
SELECT INTO

Choosing Between Bulk Load Methods
Data Compression and Bulk Load

Other Minimally Logged and Metadata-Only Operations
Solving Typical Scenarios with Bulk Loading
Optimizing Bulk Load

 View the the Data Load Performance Guide

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SSIS version in SQL Server

Integration Services is the management interface for  SSIS packages. SQL Server uses  the “MsDtsSrvr.exe”  for executing packages

To highlight the point, configuring the firewall to access Integration Services requires allowing access to port 135 for the “MsDtsSrvr.exe”. 

Access the MsDtsSrvr.exe file to view the version number

1. Find and open the following folder:

“%programfiles%\Microsoft SQL Server\90\DTS\Binn\”

1. Right-click the MsDtsSrvr.exe file |  click Properties.
2. Click the Version tab.
3. View the text after the File version label. The text is the SSIS version number.

The version number represents the SSIS service pack level or the SSIS hotfix level. For SQL SSIS and service pack upgrade

 
 

Excel Connect to SQL Server

Excel connect to SQL Server

Connecting to SQL Server from Excel is an easy process. 
Users want to distribute an Excel document , for example , within
a network. Particuarly as there are plenty of libraries the user can access from Excel
as a presentation layer.

To implement this solution you will need :

Microsoft Excel
Visual Basic Editor

Step 1: Create your Excel workbook
Step 2: In Visual Basic Editor create a new project
and within Tools | References , select "Microsoft ActiveX Data Objects Library"
Step 3: Insert a module into your new project , and create a subprocedure
Step 4: Within that sub procedure copy and paste the follwoing code:

 

 

 

####################Start Code##########################

' Create a connection object.
Dim cnMyDB As ADODB.Connection
Set cnMyDB = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the Pubs database on the local server.
strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=MyDB;"

'Use an integrated login.
strConn = strConn & " INTEGRATED SECURITY=sspi;"

'Now open the connection.
cnMyDB.Open strConn

' Create a recordset object.
Dim rsMyDB As ADODB.Recordset
Set rsMyDB = New ADODB.Recordset

With rsMyDB
' Assign the Connection object.
.ActiveConnection = cnMyDB
' Extract the required records.
.Open "SELECT * FROM MyTable"
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset rsMyDB

' Tidy up
.Close
End With

cnMyDB.Close
Set rsMyDB = Nothing
Set cnMyDB = Nothing

########################End Code################################### 

Step 5:  Run the code , and check your Excel worksheet
Step 6 : If you have problems , check connection and permissions

Author: Jack Vamvas (http://www.sqlserver-dba.com)

SQL Server Import Data from DB2 AS400 iSeries

A SQL Server Import Data from DB2 AS400 iSeries  is straightforward , once you’ve overcome a few problems.

 A common error message on a SSIS import is :

 Text was truncated or one or more characters had no match in the target code paged

 Associated with the following types of detailed error messages.

 - Executing (Error)

Messages

• · Error 0xc020901c: Data Flow Task: There was an error with output column "TITLE" (20) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".
   (SQL Server Import and Export Wizard)
   
• · Error 0xc020902a: Data Flow Task: The "output column "TITLE" (20)" failed because truncation occurred, and the truncation row disposition on "output column "TITLE" (20)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
   (SQL Server Import and Export Wizard)
   
• · Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Query" (1) returned error code 0xC020902A.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
   (SQL Server Import and Export Wizard)
   
• · Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
   (SQL Server Import and Export Wizard)
   
• · Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
   (SQL Server Import and Export Wizard)
   

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
 (SQL Server Import and Export Wizard)

 

The initial SQL Server target table definition used  the  data type set VARCHAR for some columns. VARCHAR is  for non-Unicode character data

 As the  data migration was  occurring from DB2 AS400 – I took a guess that some Unicode may be used.

 The first thing we should try is changing the VARCHAR columns to NVARCHAR, which support Unicode character data .

 This worked for all columns except 1 column.  To confirm, I took the column out of the SQL Server Import and the data imported successfully.

 I changed the Code Page types , but this had no effect.

 I’ve found 3  workarounds:

1)      Export the data from DB2 AS400 isSeries into a flat file with delimiters. Use sql Bulk Insert  to import into a SQL Server table. This method has never failed.

2)      Use DTS.

3)      Use a Linked Server. Create the Linked Server as:

                  General

                  Linked Server     AS400

                  Provider IBM AS400 OLE DB Provider

                  Product Name   <put_some_text>

                  Data Source      <server_name or ip_address>

     

                  Security

                  Map local account

                  Check the Allow Inprocess under IBMD400 Provider Options

 

A sample query:

                  SELECT * INTO import_table FROM AS400.ServerName.A.aTable

Author: Jack Vamvas (http://www.sqlserver-dba.com)