= "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.