MS Office: Excel for SQL Developers II (Insert Statements)

    From time to time is required to load a bulk of data into a table, and even if SQL Server and Oracle provides tools for this purpose, there are also occasions when this needs to be done with statements, an insert statement needing to be prepared for each record from the dataset. It sounds like a task that could be easily done in Excel, isn’t it? For this purpose in order to concatenate the values on a row I will use the GetRangeAsList macro defined in the previous post on the same topic. Considering the dataset from the below screenshot and that the headers match attributes’ name from the destination table (see table definition below) the formula that will allow me to create the multiple insert statements would look like:

 

= "INSERT INTO Production.ProductsTest (" & SUBSTITUTE(GetRangeAsList($A$1:$F$1), "'", "") & ")" & CHAR(13) & "VALUES (" & GetRangeAsList(A2:F2) & ")" 

After stripping off the output of the double quotes the output would like below: 

INSERT INTO Production.ProductsTest ( Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel) 
VALUES( 'Road-150 Red, 62', 'BK-R93R-62', '1', '1', 'Red', '100') 
INSERT INTO Production.ProductsTest ( Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel) 
VALUES( 'Road-150 Red, 44', 'BK-R93R-44', '1', '1', 'Red', '100') 
INSERT INTO Production.ProductsTest ( Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel) 
VALUES( 'Road-150 Red, 48', 'BK-R93R-48', '1', '1', 'Red', '100') 
INSERT INTO Production.ProductsTest ( Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel) 
VALUES( 'Road-150 Red, 52', 'BK-R93R-52', '1', '1', 'Red', '100') 
INSERT INTO Production.ProductsTest ( Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel) 
VALUES( 'Road-150 Red, 56', 'BK-R93R-56', '1', '1', 'Red', '100') 
INSERT INTO Production.ProductsTest ( Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel) 
VALUES( 'Road-450 Red, 58', 'BK-R68R-58', '1', '1', 'Red', '100') 

    A much simpler approach is based on the use of a UNION ALL to join the records, for this purpose could be used the = " SELECT " & GetRangeAsList(A2:F2) & " UNION ALL" formula, following to strip off the last UNION ALL and eventually add the INSERT INTO statement manually: 

 

INSERT INTO Production.ProductsTest ( Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel) 
SELECT 'Road-150 Red, 62', 'BK-R93R-62', '1', '1', 'Red', '100' UNION ALL 
SELECT 'Road-150 Red, 44', 'BK-R93R-44', '1', '1', 'Red', '100' UNION ALL 
SELECT 'Road-150 Red, 48', 'BK-R93R-48', '1', '1', 'Red', '100' UNION ALL 
SELECT 'Road-150 Red, 52', 'BK-R93R-52', '1', '1', 'Red', '100' UNION ALL 
SELECT 'Road-150 Red, 56', 'BK-R93R-56', '1', '1', 'Red', '100' UNION ALL 
SELECT 'Road-450 Red, 58', 'BK-R68R-58', '1', '1', 'Red', '100' 

    SQL Server 2008 allows to insert multiple records within a single statements, for this purpose the last formula could be changed to = " (" & GetRangeAsList(A2:F2) & " ) ,", following to just add the INSERT INTO statement manually: 

 

INSERT INTO Production.ProductsTest ( Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel) VALUES 
( 'Road-150 Red, 62', 'BK-R93R-62', '1', '1', 'Red', '100' ) , 
( 'Road-150 Red, 44', 'BK-R93R-44', '1', '1', 'Red', '100' ) , 
( 'Road-150 Red, 48', 'BK-R93R-48', '1', '1', 'Red', '100' ) , 
( 'Road-150 Red, 52', 'BK-R93R-52', '1', '1', 'Red', '100' ) , 
( 'Road-150 Red, 56', 'BK-R93R-56', '1', '1', 'Red', '100' ) , 
( 'Road-450 Red, 58', 'BK-R68R-58', '1', '1', 'Red', '100' ) 

   Just in case you want to test the statements here is Production.ProductsTest table’s definition:

CREATE TABLE [Production].[ProductsTest]( 
[ProductID] [int]IDENTITY(1,1) NOT NULL, 
[Name] [nvarchar](50) NOT NULL, 
[ProductNumber] [nvarchar](25) NOT NULL, 
[MakeFlag] [bit]NOT NULL, 
[FinishedGoodsFlag] [bit]NOT NULL, 
[Color] [nvarchar](15) NULL, 
[SafetyStockLevel] [smallint] NOT NULL) 

Note:
    Of course, also for the above 3 cases could be written macros that produces the same output taking a range as parameter, however the formulas are fairly simple to use, the only thing that needs to be changed are the ranges and, in the first example, table’s name.