I need to add entries to an existing table. The table has 2 columns (name,
number). I need to routinely add entries for multiple names and multiple
numbers for each name I'm just not sure the best way to do it. I know I can
just do a Insert..values query, but I don't want to specify each one
individually. I'm sure there is a better way.

Example. For sake of argument, I'll use 2 Names and 3 numbers

Name1, Name2
001,002,003

The table would end up containing

Name Number
Name1 001
Name1 002
Name1 003
Name2 001
Name2 002
Name2 003

TIA

Matt

You can find the function (and a lot more information) listed at:
http://www.sommarskog.se/arrays-in-sql.html
I created a stored procedure that almost gets you where you need to be. The
first step is understanding (or at least seeing) how this part works.

Then you can convert the stored procedure into an insert statement.




use tempdb
go
CREATE FUNCTION dbo.iter_charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS

BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)

SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen

SET @pos = charindex(@delimiter, @tmpstr)

WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END

SET @leftover = @tmpstr
END

INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))
RETURN
END
go


create proc dbo.BuildYourInsertList
@Names varchar(200),
@NameSplitter char(1),
@Numbers varchar(200),
@NumberSplitter char(1)
AS

--insert into YourTable (Cola, Colb, ....)
select A.str, B.str
from dbo.iter_charlist_to_table (@Names, @NameSplitter) A
CROSS JOIN dbo.iter_charlist_to_table (@Numbers, @NumberSplitter) B
ORDER BY A.str, B.str
GO


--call the stored procedure
exec dbo.BuildYourInsertList 'Name1, Name2', ',', '001,002,003',','



--
Keith Kratochvil


[quoted text, click to view]