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

Lance Roberts gravatar image

Lance Roberts
90 5 5 7

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

3 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.

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

      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

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

(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

Bob Hovious gravatar image

Bob Hovious
1.6k 5 6 9

Simple and easy to read and understand
Oct 19, 2009 at 11:50 PM Jack Corbett
Thanks, Jack. I think it would win on performance too.
Oct 22, 2009 at 10:35 AM Bob Hovious
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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: 3425 times

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