Skip to content
  • Categories
  • Recent
  • Tags
  • Popular
  • World
  • Users
  • Groups
Skins
  • Light
  • Cerulean
  • Cosmo
  • Flatly
  • Journal
  • Litera
  • Lumen
  • Lux
  • Materia
  • Minty
  • Morph
  • Pulse
  • Sandstone
  • Simplex
  • Sketchy
  • Spacelab
  • United
  • Yeti
  • Zephyr
  • Dark
  • Cyborg
  • Darkly
  • Quartz
  • Slate
  • Solar
  • Superhero
  • Vapor
  • Default (No Skin)
  • No Skin
Collapse
Code Project
CODE PROJECT For Those Who Code
  • Home
  • Articles
  • FAQ
Community
  1. Home
  2. Database & SysAdmin
  3. Database
  4. inserting multiple rows

inserting multiple rows

Scheduled Pinned Locked Moved Database
databasetutorial
14 Posts 4 Posters 0 Views 1 Watching
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • A arun_pk

    thanks for the response i wanted to add around 500000 rows of data in a table as atemplate for my project one field can be the primary key which will be auto incremented all others are repeated values... so is it possible for me to add multiple rows at one shot

    _ Offline
    _ Offline
    _Damian S_
    wrote on last edited by
    #5

    The easiest way is to use another table to create a cartesian product (ie: selecting from two or more tables with nothing linking them causes a cartesian product - one row for every possible combination). The following select statement will return over a million rows on my test SQL Server:

    select 'a'
    from dbo.sysobjects as so1, dbo.sysobjects as so2

    Extending this into an insert statement, you would do this:

    insert into TABLENAME(FIELD1, FIELD2)
    select 'a', 'b'
    from dbo.sysobjects as so1, dbo.sysobjects as so2

    which will basically insert the values a and b into your table a million or so times... If you have a table that autonumbers, then you will have a million consecutive numbers generated with which to test with. Hope this helps.

    I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

    A D 2 Replies Last reply
    0
    • D dan sh

      If you have all of that data in a table, you can use:

      Insert into DataBaseTable
      select * from SourceTable

      You can also use Bulk Insert[^] for doing this.

      50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

      A Offline
      A Offline
      arun_pk
      wrote on last edited by
      #6

      i dont have a alternate table ____ let me give u a clear picture lets assume my table is details with coloumns ID(primary key)(Auto incriment), Name so my insert statement is INSERT INTO DETAILS VALUES("abcd"); i want the same data 500000 times do i need to run it in a loop or is there any other method of adding multiple rows

      _ 1 Reply Last reply
      0
      • D dan sh

        If you have all of that data in a table, you can use:

        Insert into DataBaseTable
        select * from SourceTable

        You can also use Bulk Insert[^] for doing this.

        50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

        _ Offline
        _ Offline
        _Damian S_
        wrote on last edited by
        #7

        I figured he had no data, but wanted 500K records to test with...

        I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

        D 1 Reply Last reply
        0
        • A arun_pk

          i dont have a alternate table ____ let me give u a clear picture lets assume my table is details with coloumns ID(primary key)(Auto incriment), Name so my insert statement is INSERT INTO DETAILS VALUES("abcd"); i want the same data 500000 times do i need to run it in a loop or is there any other method of adding multiple rows

          _ Offline
          _ Offline
          _Damian S_
          wrote on last edited by
          #8

          See my cartesian product answer below.

          I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

          1 Reply Last reply
          0
          • _ _Damian S_

            The easiest way is to use another table to create a cartesian product (ie: selecting from two or more tables with nothing linking them causes a cartesian product - one row for every possible combination). The following select statement will return over a million rows on my test SQL Server:

            select 'a'
            from dbo.sysobjects as so1, dbo.sysobjects as so2

            Extending this into an insert statement, you would do this:

            insert into TABLENAME(FIELD1, FIELD2)
            select 'a', 'b'
            from dbo.sysobjects as so1, dbo.sysobjects as so2

            which will basically insert the values a and b into your table a million or so times... If you have a table that autonumbers, then you will have a million consecutive numbers generated with which to test with. Hope this helps.

            I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

            A Offline
            A Offline
            arun_pk
            wrote on last edited by
            #9

            thanks a lot bro its working for meee

            _ 1 Reply Last reply
            0
            • A arun_pk

              thanks a lot bro its working for meee

              _ Offline
              _ Offline
              _Damian S_
              wrote on last edited by
              #10

              No worries... glad I could help!

              I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

              1 Reply Last reply
              0
              • _ _Damian S_

                I figured he had no data, but wanted 500K records to test with...

                I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

                D Offline
                D Offline
                dan sh
                wrote on last edited by
                #11

                :doh: My bad. I thought he needs to insert data from somewhere to his DB.

                50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

                1 Reply Last reply
                0
                • _ _Damian S_

                  The easiest way is to use another table to create a cartesian product (ie: selecting from two or more tables with nothing linking them causes a cartesian product - one row for every possible combination). The following select statement will return over a million rows on my test SQL Server:

                  select 'a'
                  from dbo.sysobjects as so1, dbo.sysobjects as so2

                  Extending this into an insert statement, you would do this:

                  insert into TABLENAME(FIELD1, FIELD2)
                  select 'a', 'b'
                  from dbo.sysobjects as so1, dbo.sysobjects as so2

                  which will basically insert the values a and b into your table a million or so times... If you have a table that autonumbers, then you will have a million consecutive numbers generated with which to test with. Hope this helps.

                  I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! Booger Mobile (n) - A bright green 1964 Ford Falcon - our entry into the Camp Quality esCarpade!! Do something wonderful - make a donation to Camp Quality today!!

                  D Offline
                  D Offline
                  dan sh
                  wrote on last edited by
                  #12

                  Wow! this is a real good way. :thumbsup:

                  50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!

                  1 Reply Last reply
                  0
                  • A arun_pk

                    how to insert multiple rows in sql using a single sql command thanks in advance arun

                    N Offline
                    N Offline
                    Niladri_Biswas
                    wrote on last edited by
                    #13

                    If u are using

                    SQL SERVER 2008

                    , you can use

                    Table Valued Parameter

                    . Form your front end application(assuming .net), u need to send via a datatable to ur TVP. :)

                    Niladri Biswas

                    A 1 Reply Last reply
                    0
                    • N Niladri_Biswas

                      If u are using

                      SQL SERVER 2008

                      , you can use

                      Table Valued Parameter

                      . Form your front end application(assuming .net), u need to send via a datatable to ur TVP. :)

                      Niladri Biswas

                      A Offline
                      A Offline
                      arun_pk
                      wrote on last edited by
                      #14

                      pls explain i m sorry i didnt get wat u said .. pls

                      1 Reply Last reply
                      0
                      Reply
                      • Reply as topic
                      Log in to reply
                      • Oldest to Newest
                      • Newest to Oldest
                      • Most Votes

                      • Login
                      • Don't have an account? Register
                      • Login or register to search.
                      • First post
                        Last post
                      0
                      • Categories
                      • Recent
                      • Tags
                      • Popular
                      • World
                      • Users
                      • Groups