How do I INSERT or UPDATE an array of values without iteration?

How do I in bulk INSERT or UPDATE an array of values, without using simple iteration?

Something like:

INSERT INTO table1 (column-1) VALUES bigarray WHERE rowsthatmatter

more ▼

asked Oct 19, 2009 at 12:24 PM in Default

avatar image

Lance Roberts
90 6 5 10

Can you clarify on what you mean a bit? I don't think the question is really answerable in it's current form...

Oct 19, 2009 at 12:29 PM Matt Whitfield ♦♦

Please also add some code and sample data to show how you are phrasing the question.

Oct 19, 2009 at 12:40 PM Steve Jones - Editor ♦♦

I don't really have any sample in front of me, this is something that has come up in the past. I'll come up with something to help clarify. I just want to insert an array of data with one (or a few commands) instead of looping through every element inserting each one individually.

Oct 19, 2009 at 12:46 PM Lance Roberts
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

NOTE: Edited to fix issue with copying and pasting the stored procedure code. My inline tabs were being converted to spaces, so I replaced them with CHAR(9). Also, see note at bottom for a potential real-world example.

This one took me a little while, but I just knew I could figure it out if I kept trying. I wrote it as a stored procedure for easy reuse.

CREATE PROCEDURE InsertListIntoTable @List varchar(max), @Delimiter varchar(5), @TableName varchar(50), @ColumnName varchar(50), @ListFilter varchar(max) = '' AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

   DECLARE @SqlStmt nvarchar(max)
   DECLARE @CrLf char(2); SET @CrLf = CHAR(13) + CHAR(10);
   DECLARE @Tab char(1); SET @Tab = CHAR(9);
   SET @SqlStmt =
         N'INSERT INTO ' + @TableName + @CrLf +
         @Tab + N'(' + @ColumnName + ')' + @CrLf +
         @Tab + N'SELECT * FROM' + @CrLf +
         @Tab + N'(' + @CrLf +
         @Tab + @Tab + N'SELECT ' +
         REPLACE(@List, @Delimiter, @CrLf + @Tab + @Tab + 'UNION SELECT ') + @CrLf +
         @Tab + N') t' + @CrLf
   IF LEN(@ListFilter) > 0
         SET @SqlStmt = @SqlStmt +
               @Tab + N'WHERE ' + @ListFilter + @CrLf
   SET @SqlStmt = STUFF(@SqlStmt, CHARINDEX(CAST(@CrLf + @Tab + @Tab + 'UNION' AS nvarchar(50)), @SqlStmt, 1), 0, ' ' + @ColumnName);
   PRINT @SqlStmt
   --EXEC sp_executesql @SqlStmt


Sample usage:

DECLARE @BigArray varchar(max) SET @BigArray = '1,2,3,5,7,11,13,17,19,23,29,31'

EXEC InsertListIntoTable @BigArray, ',', 'MyTable', 'Column1', 'Column1 IN (1,3,13,31)'

Table Contents:


This is just a simple example. A real-world example might be something like this:

@BigArray = a list of part numbers to add to inventory

EXEC InsertListIntoTable @BigArray, ',', 'Inventory', 'PartNumber',
        'EXISTS (SELECT PartNumber FROM RegisteredParts rp WHERE rp.PartNumber=t.PartNumber)'
more ▼

answered Oct 19, 2009 at 11:02 PM

avatar image

Tom Staab ♦
14.5k 7 14 21

(comments are locked)
10|1200 characters needed characters left

I ran your code, but it produced NULL values for @SqlStmt.
Here's a quick tally table solution:

DECLARE @BigArray varchar(max) SET @BigArray = ',1,2,3,5,7,11,13,17,19,23,29,31,'

;with Array AS (select substring(@bigArray,N+1,charindex(',',@bigArray,N+1)-(N+1)) as element from dbo.tally where substring(@bigArray,N,1) = ',' and N < len(@bigArray) )

  • insert into Sometable select * from Array where element in (1,3,13,31)

My question is: If you know in advance that those four elements (1,3,13,31) are the only ones that matter, why not just insert them?

more ▼

answered Oct 19, 2009 at 11:43 PM

avatar image

Bob Hovious
1.6k 5 9 13

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 19, 2009 at 12:24 PM

Seen: 5383 times

Last Updated: Oct 20, 2009 at 04:44 AM

Copyright 2017 Redgate Software. Privacy Policy