Professional Web Applications Themes

Insert 100 rows in just one statement - Microsoft SQL / MS SQL Server

Your auxiliary table is probably the best way. Populate it with as many rows as you like rather than doing multiple inserts when you need more than 1000 rows. Alternatively, if your target table has at least as many rows as you need and you can avoid/don't mind any gaps in your numbering: INSERT INTO Sometable SELECT colx + (SELECT COALESCE(MAX(colx),0) FROM Sometable) FROM Sometable WHERE colx<=100 PS. Don't use ORDER BY with SELECT INTO or INSERT INTO. It achieves nothing and potentially just slows down the insert. -- David Portas ------------ Please reply only to the newsgroup -- "Alberto ...

  1. #1

    Default Re: Insert 100 rows in just one statement

    Your auxiliary table is probably the best way. Populate it with as many rows
    as you like rather than doing multiple inserts when you need more than 1000
    rows.

    Alternatively, if your target table has at least as many rows as you need
    and you can avoid/don't mind any gaps in your numbering:

    INSERT INTO Sometable
    SELECT colx + (SELECT COALESCE(MAX(colx),0) FROM Sometable)
    FROM Sometable
    WHERE colx<=100

    PS. Don't use ORDER BY with SELECT INTO or INSERT INTO. It achieves nothing
    and potentially just slows down the insert.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Alberto Richart" <a.richartpreference.es> wrote in message
    news:OHNSx6tPDHA.1608TK2MSFTNGP11.phx.gbl...
    > Hi,
    >
    > I usually face the problem of inserting a great deal of data in one
    table..
    > For instance, I would like to add 100 rows in a table which contains just
    > one int column (from 101 to 200).
    >
    > I would like to know if it's possible to solve the problem using just one
    > insert statement and without the help of any helper table.
    >
    > To workaround this problem, I have a helper table (Table1000) in my
    database
    > structure which contains just one column (Number int) and 1000 rows, from
    1
    > to 1000. The solution in this case is simple:
    >
    > select top 100 Value=Table1000.Number+100
    > into #myTable
    > FROM Table1000
    > ORDER BY Number
    >
    > select *
    > from #myTable
    >
    > drop table #myTable
    >
    > Obviously, I need a counter and execute the statement several times if the
    > quantity to insert is greater than 1000.
    >
    > Thanks in advance,
    >
    > Alberto.
    >
    >

    David Portas Guest

  2. #2

    Default Re: Insert 100 rows in just one statement

    > Obviously, I need a counter and execute the statement several times if the
    > quantity to insert is greater than 1000.
    Not really, try this:

    CREATE TABLE Table1000 (Number INT IDENTITY)

    WHILE COALESCE(SCOPE_IDENTITY(),0) < 1000
    INSERT INTO Table1000 DEFAULT VALUES

    DECLARE rows INT
    SET rows = 5000

    SET ROWCOUNT rows

    SELECT Value= (Table1000_1000.Number - 1) * 1000 + Table1000_1.Number +100
    into #myTable
    FROM Table1000 Table1000_1 CROSS JOIN Table1000 Table1000_1000
    SELECT * FROM #myTable
    DROP TABLE #myTable
    DROP TABLE Table1000


    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Alberto Richart" <a.richartpreference.es> wrote in message
    news:OHNSx6tPDHA.1608TK2MSFTNGP11.phx.gbl...
    > Hi,
    >
    > I usually face the problem of inserting a great deal of data in one
    table..
    > For instance, I would like to add 100 rows in a table which contains just
    > one int column (from 101 to 200).
    >
    > I would like to know if it's possible to solve the problem using just one
    > insert statement and without the help of any helper table.
    >
    > To workaround this problem, I have a helper table (Table1000) in my
    database
    > structure which contains just one column (Number int) and 1000 rows, from
    1
    > to 1000. The solution in this case is simple:
    >
    > select top 100 Value=Table1000.Number+100
    > into #myTable
    > FROM Table1000
    > ORDER BY Number
    >
    > select *
    > from #myTable
    >
    > drop table #myTable
    >
    > Obviously, I need a counter and execute the statement several times if the
    > quantity to insert is greater than 1000.
    >
    > Thanks in advance,
    >
    > Alberto.
    >
    >

    Jacco Schalkwijk Guest

  3. #3

    Default Re: Insert 100 rows in just one statement

    Thanks for your reply, David.

    I've used this technique as well (the Sometable example), but it doesn't
    always fit my needs.

    I think the solution I'm looking for is not possible (or I'm unable to find
    it). Imagine Sometable contains 0 rows and Table1000 doesn't exist.

    Alberto.



    "David Portas" <REMOVE_BEFORE_REPLYING_dportasacm.org> escribió en el
    mensaje news:uRSHYMuPDHA.1556TK2MSFTNGP10.phx.gbl...
    > Your auxiliary table is probably the best way. Populate it with as many
    rows
    > as you like rather than doing multiple inserts when you need more than
    1000
    > rows.
    >
    > Alternatively, if your target table has at least as many rows as you need
    > and you can avoid/don't mind any gaps in your numbering:
    >
    > INSERT INTO Sometable
    > SELECT colx + (SELECT COALESCE(MAX(colx),0) FROM Sometable)
    > FROM Sometable
    > WHERE colx<=100
    >
    > PS. Don't use ORDER BY with SELECT INTO or INSERT INTO. It achieves
    nothing
    > and potentially just slows down the insert.
    >
    > --
    > David Portas
    > ------------
    > Please reply only to the newsgroup
    > --
    >
    > "Alberto Richart" <a.richartpreference.es> wrote in message
    > news:OHNSx6tPDHA.1608TK2MSFTNGP11.phx.gbl...
    > > Hi,
    > >
    > > I usually face the problem of inserting a great deal of data in one
    > table..
    > > For instance, I would like to add 100 rows in a table which contains
    just
    > > one int column (from 101 to 200).
    > >
    > > I would like to know if it's possible to solve the problem using just
    one
    > > insert statement and without the help of any helper table.
    > >
    > > To workaround this problem, I have a helper table (Table1000) in my
    > database
    > > structure which contains just one column (Number int) and 1000 rows,
    from
    > 1
    > > to 1000. The solution in this case is simple:
    > >
    > > select top 100 Value=Table1000.Number+100
    > > into #myTable
    > > FROM Table1000
    > > ORDER BY Number
    > >
    > > select *
    > > from #myTable
    > >
    > > drop table #myTable
    > >
    > > Obviously, I need a counter and execute the statement several times if
    the
    > > quantity to insert is greater than 1000.
    > >
    > > Thanks in advance,
    > >
    > > Alberto.
    > >
    > >
    >
    >

    Alberto Richart Guest

  4. #4

    Default Re: Insert 100 rows in just one statement

    Useful hint, Jacco.

    I haven't unsed this technique, but I think it's a bit risky, because it's
    necessary to set the rowcount to 0 again. Otherwise, next sentences may not
    work properly.

    You have used a good way of inserting the 1000 records in Table1000. But my
    first question is how to insert X records in just one table in one insert
    statement, without the help of auxiliary tables. I mean, instead of using a
    loop (as it is your example), is it possible to insert 1000 rows in
    Table1000 in one insert statement?

    Thanks,

    Alberto.



    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> escribió en el mensaje
    news:eUO%23oeuPDHA.3088TK2MSFTNGP10.phx.gbl...
    > > Obviously, I need a counter and execute the statement several times if
    the
    > > quantity to insert is greater than 1000.
    >
    > Not really, try this:
    >
    > CREATE TABLE Table1000 (Number INT IDENTITY)
    >
    > WHILE COALESCE(SCOPE_IDENTITY(),0) < 1000
    > INSERT INTO Table1000 DEFAULT VALUES
    >
    > DECLARE rows INT
    > SET rows = 5000
    >
    > SET ROWCOUNT rows
    >
    > SELECT Value= (Table1000_1000.Number - 1) * 1000 + Table1000_1.Number +100
    > into #myTable
    > FROM Table1000 Table1000_1 CROSS JOIN Table1000 Table1000_1000
    > SELECT * FROM #myTable
    > DROP TABLE #myTable
    > DROP TABLE Table1000
    >
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Alberto Richart" <a.richartpreference.es> wrote in message
    > news:OHNSx6tPDHA.1608TK2MSFTNGP11.phx.gbl...
    > > Hi,
    > >
    > > I usually face the problem of inserting a great deal of data in one
    > table..
    > > For instance, I would like to add 100 rows in a table which contains
    just
    > > one int column (from 101 to 200).
    > >
    > > I would like to know if it's possible to solve the problem using just
    one
    > > insert statement and without the help of any helper table.
    > >
    > > To workaround this problem, I have a helper table (Table1000) in my
    > database
    > > structure which contains just one column (Number int) and 1000 rows,
    from
    > 1
    > > to 1000. The solution in this case is simple:
    > >
    > > select top 100 Value=Table1000.Number+100
    > > into #myTable
    > > FROM Table1000
    > > ORDER BY Number
    > >
    > > select *
    > > from #myTable
    > >
    > > drop table #myTable
    > >
    > > Obviously, I need a counter and execute the statement several times if
    the
    > > quantity to insert is greater than 1000.
    > >
    > > Thanks in advance,
    > >
    > > Alberto.
    > >
    > >
    >
    >

    Alberto Richart Guest

  5. #5

    Default Re: Insert 100 rows in just one statement

    Hi

    This would give you the numbers, but I wouldn't use it!

    select [Number]
    FROM (
    SELECT o.id, COUNT(*) as [Number]
    FROM SYSOBJECTS o JOIN SYSOBJECTS s ON o.id >= s.id
    GROUP BY o.id ) A
    Order by Id

    John

    "Alberto Richart" <a.richartpreference.es> wrote in message
    news:udg3aowPDHA.3020TK2MSFTNGP10.phx.gbl...
    > Useful hint, Jacco.
    >
    > I haven't unsed this technique, but I think it's a bit risky, because it's
    > necessary to set the rowcount to 0 again. Otherwise, next sentences may
    not
    > work properly.
    >
    > You have used a good way of inserting the 1000 records in Table1000. But
    my
    > first question is how to insert X records in just one table in one insert
    > statement, without the help of auxiliary tables. I mean, instead of using
    a
    > loop (as it is your example), is it possible to insert 1000 rows in
    > Table1000 in one insert statement?
    >
    > Thanks,
    >
    > Alberto.
    >
    >
    >
    > "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> escribió en el mensaje
    > news:eUO%23oeuPDHA.3088TK2MSFTNGP10.phx.gbl...
    > > > Obviously, I need a counter and execute the statement several times if
    > the
    > > > quantity to insert is greater than 1000.
    > >
    > > Not really, try this:
    > >
    > > CREATE TABLE Table1000 (Number INT IDENTITY)
    > >
    > > WHILE COALESCE(SCOPE_IDENTITY(),0) < 1000
    > > INSERT INTO Table1000 DEFAULT VALUES
    > >
    > > DECLARE rows INT
    > > SET rows = 5000
    > >
    > > SET ROWCOUNT rows
    > >
    > > SELECT Value= (Table1000_1000.Number - 1) * 1000 + Table1000_1.Number
    +100
    > > into #myTable
    > > FROM Table1000 Table1000_1 CROSS JOIN Table1000 Table1000_1000
    > > SELECT * FROM #myTable
    > > DROP TABLE #myTable
    > > DROP TABLE Table1000
    > >
    > >
    > > --
    > > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > > Database Administrator
    > > Eurostop Ltd.
    > >
    > >
    > > "Alberto Richart" <a.richartpreference.es> wrote in message
    > > news:OHNSx6tPDHA.1608TK2MSFTNGP11.phx.gbl...
    > > > Hi,
    > > >
    > > > I usually face the problem of inserting a great deal of data in one
    > > table..
    > > > For instance, I would like to add 100 rows in a table which contains
    > just
    > > > one int column (from 101 to 200).
    > > >
    > > > I would like to know if it's possible to solve the problem using just
    > one
    > > > insert statement and without the help of any helper table.
    > > >
    > > > To workaround this problem, I have a helper table (Table1000) in my
    > > database
    > > > structure which contains just one column (Number int) and 1000 rows,
    > from
    > > 1
    > > > to 1000. The solution in this case is simple:
    > > >
    > > > select top 100 Value=Table1000.Number+100
    > > > into #myTable
    > > > FROM Table1000
    > > > ORDER BY Number
    > > >
    > > > select *
    > > > from #myTable
    > > >
    > > > drop table #myTable
    > > >
    > > > Obviously, I need a counter and execute the statement several times if
    > the
    > > > quantity to insert is greater than 1000.
    > > >
    > > > Thanks in advance,
    > > >
    > > > Alberto.
    > > >
    > > >
    > >
    > >
    >
    >

    John Bell Guest

  6. #6

    Default Re: Insert 100 rows in just one statement


    Could I ask why you need to do it this way ?

    What is the reason why you cannot seed with one parameter with number of
    rows as a second parameter. A stored procedure could then insert,
    looping for the number of rows ?


    *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    Don't just participate in USENET...get rewarded for it!
    malcolm k Guest

  7. #7

    Default Re: Insert 100 rows in just one statement

    That's more or less the implementation of the problem: I've used a stored
    procedure to insert the rows. Inside this procedure I can loop or use
    Table1000 to speed up the insert. I just wanted to know if there's a better
    way of doing things.

    Thanks,

    Alberto.

    "malcolm k" <kraushaarwillis.dot.com> escribió en el mensaje
    news:ukko2HyPDHA.1908TK2MSFTNGP11.phx.gbl...
    >
    > Could I ask why you need to do it this way ?
    >
    > What is the reason why you cannot seed with one parameter with number of
    > rows as a second parameter. A stored procedure could then insert,
    > looping for the number of rows ?
    >
    >
    > *** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
    > Don't just participate in USENET...get rewarded for it!

    Alberto Richart Guest

Similar Threads

  1. Question Insert from ASP to SQL using recordset data as values in insert statement
    By JasonM in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 13th, 05:54 PM
  2. Help with INSERT INTO statement
    By s_jancich in forum Coldfusion - Getting Started
    Replies: 1
    Last Post: July 20th, 04:39 PM
  3. Replies: 3
    Last Post: September 30th, 09:24 PM
  4. Replies: 3
    Last Post: September 5th, 04:16 PM
  5. Limiting similar rows in SELECT statement...
    By Greg in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 4th, 02:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139