SQL Server Performance

multiple rows insert in a table

Discussion in 'Analysis Services/Data Warehousing' started by abhi, Jul 3, 2003.

  1. abhi New Member

    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)
  2. abhi New Member

    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
  3. joechang New Member

    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

  4. bambola New Member

    <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 />
  5. joechang New Member

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

Share This Page