SQL Server Performance Forum – Threads Archive

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(@[email protected][email protected],S1.ID,1)[email protected]
AND substring(@[email protected][email protected],S2.ID,1)[email protected]
AND S1.ID < S2.ID AND S1.ID < @len AND S2.ID <= @len+2
GROUP BY S1.ID

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

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

]]>