multiple rows insert in a table

I want to insert multiple insert in a table like MySQL
So that I can make a array of values and insert directly in a table.I have to insert minimum 20,000 rows at a time.
NOte: I dont want to use while loop or what's the limit of array
@array =(v1,v2),(v3,v4),(v5,v6).....(vn,vn+1)

like MySQL Example
insert into table (column1, column2) values (v1,v2),(v3,v4),(v5,v6).....(vn,vn+1)

Friends: I know I van do it like this way. But I need optimum and best solution

CREATE TABLE tmpPipeSepVals (name nVARCHAR(4000))

DECLARE @strPipeSepVals nvarchar(4000),
@ThisValue nvarchar(4000),
@EndPoint int

--SET @strPipeSepVals = (SELECT name FROM [testtable])
---- GETTING VALUES FROM CGI CODE
SET @strPipeSepVals = 'asahay.com,1,Y|asahay1.com,2,N|asahay2.com,2,N|asahay3.com,2,N|asahay.com,1,Y|asahay1.com,2,N|asahay2.com,2,N|asahay3.com,2,N|asahay3.com,2,N|asahay3.com,2,N'
--SET @strPipeSepVals = ltrim(rtrim(convert(nvarchar,@strPipeSepVals))) + '|'
select @strPipeSepVals
SET @EndPoint = CHARINDEX('|',@strPipeSepVals)

WHILE @EndPoint > 0

BEGIN
SET @ThisValue = SUBSTRING(@strPipeSepVals, 1, @EndPoint - 1)
SET @strPipeSepVals = SUBSTRING(@strPipeSepVals, @EndPoint + 1, 1000)
INSERT INTO [tmpPipeSepVals]([name]) VALUES(@ThisValue)
SET @EndPoint = CHARINDEX('|',@strPipeSepVals)
END

-- SELECTING TEMP TABLES VALUE
SELECT * FROM [tmpPipeSepVals]
-- PARSING , FROM TEMP TABLE AND INSERTING IN DOMAINS-SUBPRODUCT TABLE
select substring(name, 1,CharIndex(',' ,name ) -1 )as first ,
--right(name,(len(name) -len(substring(name, 1,CharIndex(',' ,name ) -1 )))-1 ),
substring(right(name,(len(name) -len(substring(name, 1,CharIndex(',' ,name ) -1 )))-1 ),1,charindex (',' ,right(name,(len(name) -len(substring(name, 1,CharIndex(',' ,name ) -1 )))-1 ) ) -1 ) as second,
right(name,len(name)-(len(substring(name, 1,CharIndex(',' ,name ) -1 ))+ len(substring(right(name,(len(name) -len(substring(name, 1,CharIndex(',' ,name ) -1 )))-1 ),1,charindex (',' ,right(name,(len(name) -len(substring(name, 1,CharIndex(',' ,name ) -1 )))-1 ) ) -1 )))-2) as third
from tmpPipeSepVals

DROP TABLE tmpPipeSepVals
--delete from testtable

this is my routine for extracting a list from a string

first is a table (RSequence) for holding a sequential list of integers.

CREATE TABLE RSequence (ID int NOT NULL,
CONSTRAINT [PK_RSequence] PRIMARY KEY CLUSTERED ( [ID] )
WITH FILLFACTOR = 100 ON [PRIMARY] )
GO
EXEC sp_indexoption 'dbo.RSequence', 'disallowrowlocks', TRUE
GO
EXEC sp_indexoption 'dbo.RSequence', 'disallowpagelocks', TRUE
GO

SET

SET NOCOUNT ON

DECLARE @I INT SELECT @I = 1
WHILE @I <= 8000 BEGIN
INSERT RSequence (ID) VALUES (@I)
SET @I = @I+1
END
GO

if your max string will never be more than 32K, you could use small int, but i would leave the type alone.
I am putting in 8000 values for a max string of 8000. This should be kept as low as necessary for your max string length

you can now build a procedure around the following, your code for pulling out the individual fields in each needs to be applied


DECLARE @list varchar(700), @len int, @delim char(1)
SELECT @delim = '|'
SELECT @list = 'asahay.com,1,Y|asahay1.com,2,N|asahay2.com,2,N|asahay3.com,2,N|asahay.com,1,Y|asahay1.com,2,N|asahay2.com,2,N|asahay3.com,2,N|asahay3.com,2,N|asahay3.com,2,N'
SELECT @len = LEN(@list)

SELECT ltrim(substring(@list, S1.ID, min(s2.ID)-S1.ID-1))
FROM RSequence AS S1, RSequence AS S2
WHERE substring(@delim+@list+@delim,S1.ID,1)=@delim
AND substring(@delim+@list+@delim,S2.ID,1)=@delim
AND S1.ID < S2.ID AND S1.ID < @len AND S2.ID <= @len+2
GROUP BY S1.ID

<b>Beautiful!</b><br />I usually do it with a table-value function that loops over a the string and inserts <br />values into the table, but your solution is much nicer and performs better!<br /><br />I was thinking about wrapping it within a function so I can use it in joins. <br />It depends on the RSequence table which I am not checking here (don't tell anyone <img src='/community/emoticons/emotion-5.gif' alt=';-)' /><br />but I was trying to stay with an inline function.<br /><pre><br />CREATE FUNCTION split_string(@list varchar(8000), @delim char(1), @len int)<br />RETURNS TABLE<br />AS <br />RETURN<br /> SELECT ltrim(substring(@list, S1.ID, min(s2.ID)-S1.ID-1)) ITEM<br /> FROM RSequence AS S1, RSequence AS S2 <br /> WHERE substring(@delim + @list + @delim, S1.ID, 1) = @delim<br /> AND substring(@delim + @list + @delim, S2.ID, 1) = @delim<br /> AND S1.ID &lt; S2.ID AND S1.ID &lt; @len AND S2.ID &lt;= @len+2<br /> GROUP BY S1.ID<br /></pre><br />Bambola.<br />

i re-post a summary of yours and mine in the Contribute your scripts forum