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
asked
How do I INSERT or UPDATE an array of values without iteration?
Can you clarify on what you mean a bit? I don't think the question is really answerable in it's current form...
Please also add some code and sample data to show how you are phrasing the question.
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.
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
END
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:
Column1
1
3
13
31
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)'
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?
