Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2003
    Posts
    60

    Unanswered: Insert Statement Help

    I need to insert over 600 similar records in a table. The only way I know is to write insert into <table> command 600 times. Is there another way which is easier?

    Thanks in advance,
    Saurav

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    First create this interger table
    Code:
    CREATE TABLE Numbers(
    	Number INT NOT NULL,
    	CONSTRAINT PK_Numbers
     		PRIMARY KEY CLUSTERED (Number)
    		WITH FILLFACTOR = 100)
    INSERT INTO Numbers 
    SELECT
    	(a.Number * 256) + b.Number AS Number FROM
     	(SELECT number
    		FROM master..spt_values
    		WHERE 			type = 'P'
    			AND number <= 255)a (Number),
    	(SELECT number		
                              FROM master..spt_values
    		WHERE 			type = 'P'
    			AND number <= 255)b (Number)
    GO
    now run this
    Code:
    CREATE TABLE test(
    name VARCHAR(20),
    age INT)
    
    INSERT INTO test VALUES('joy',30)
    
    INSERT INTO test
    
    SELECT name,age FROM test 
    CROSS JOIN Numbers
    WHERE number<601
    Last edited by rudra; 09-22-06 at 07:32.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Aug 2003
    Posts
    60
    Thank you Rudra for the reply. However, I need to insert similar values (actually not the same values). Values in certain columns are same and in others different.

    Thanks,
    Saurav

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by sajmera
    Thank you Rudra for the reply. However, I need to insert similar values (actually not the same values). Values in certain columns are same and in others different.

    Thanks,
    Saurav
    Please give some more info,I mean examples of your table and data.Then it would be easy for us to help you.Please read the sticky at the top most post.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Where is this data now?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where's the data now?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    What is the location and format of the data at present?

    (just thought I'd change it up a bit )
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  8. #8
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    If the data is some sort of file you should create a DTS package and import the data. It would be a lot easier than creating some sort of BULK insert statement

  9. #9
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    When nothing has been done with the data,then it should there where it was earlier...so don't worry be happy
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  10. #10
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Yes, my fuzzy friend, but we were not able to glimpse the location of the data earlier.

    And what you say is not always true, glasshoppa...sometimes doing nothing can cause loss of data, which would mean it is not where it was...and further cause a great deal of debate over whether it ever was.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  11. #11
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Welcome back Paul,I missed you a lot ...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  12. #12
    Join Date
    Aug 2004
    Location
    Pune,India
    Posts
    94
    i think the real issue should not be locating this data. Rather it's the logic our friend requires to sort out his difficulty.
    In GOD we believe. Everything else we Test!

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by wash
    i think the real issue should not be locating this data. Rather it's the logic our friend requires to sort out his difficulty.
    So the logic is independent of whether the data is handwritten on some forms on his desk, contained in qualitative text in a word document or normalised and typed in an Oracle database?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Quote Originally Posted by wash
    i think the real issue should not be locating this data. Rather it's the logic our friend requires to sort out his difficulty.
    Yes, as Pootie has so well highlighted, perhaps the logic our friend requires to sort out his difficulty is rooted in the location of his data. In fact, one might argue that at least on the surface, the origin of data is one of the cornerstones of database analysis and design.

    In fact, I put forth for your consideration the assertation that without knowing the origin of one's data, the manipulation of said data is perhaps nearly impossible.

    Or, as Grandma used to say, one cannot hope to successfully build a relational database for the future without knowing intimately the data around which the database is to be built, and this knowledge is largely based upon knowing the past of one's data.

    She usually followed up this bit of sage advice with an often lengthy tirade against the use of cursors, and sometimes followed that with a treatise on the evils of using sweet apples in an apple pie...but that's a discussion for a different thread.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

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