Results 1 to 12 of 12

Thread: Massive insert

  1. #1
    Join Date
    Jul 2003
    Posts
    23

    Unanswered: Massive insert

    I have a list of contactID's in one table and and need to insert all of them into another table with vbscript I could just do a loop, but how would I do it in sql?

    INSERT INTO HISTORY (ContactID, Subject, Body, DateStart, UserID) VALUES ('" & cid & "', '2003 5th Wheel Show N Sell Mailer', '2003 5th Wheel Show N Sell Mailer', '7/15/03', '4')
    "Everything is possible, somethings are just less likely then others"

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This is much easier in TSQL than VB, becuase this is what TSQL is designed for. No loops required!

    INSERT INTO HISTORY (ContactID, Subject, Body, DateStart, UserID)
    Select ContactID, Subject, Body, DateStart, UserID
    From [YourSourceTable]

    The VALUES method is only one method of inserting data, and is a poor second choice to using a select statement as the recordsource.

    blindman

  3. #3
    Join Date
    Jul 2003
    Posts
    23
    INSERT INTO HISTORY (ContactID, Subject, Body, DateStart, UserID)
    Select ContactID, Subject, Body, DateStart, UserID
    From [YourSourceTable]

    So what about the text I want to put into it

    INSERT INTO HISTORY (ContactID, Subject, Body, DateStart, UserID)
    Select ContactID, Subject, Body, DateStart, UserID
    From [YourSourceTable]

    where does
    '2003 5th Wheel Show N Sell Mailer', '2003 5th Wheel Show N Sell Mailer', '7/15/03', '4'
    fit into the picture?
    "Everything is possible, somethings are just less likely then others"

  4. #4
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    If you run this query

    Select ContactID, Subject, Body, DateStart, UserID
    From [YourSourceTable]

    like blindman suggested, what do you get? Becuase what you receive as returning values is what will be inserted into the table, if you have 50,000 rows in your table, all 50,000 rows will be inserted into the table you specify in your insert condition.

    Also, if you are doing a massive update watch your tran log, inserts, updates and deletes are logged operations and can bloat your T-log unless you are using a simple recovery mode. If in your desired insert you are placing the data in a new table, you might run a select into instead of an insert as it is minimally logged. Syntax would be:

    select ContactID, Subject, Body, DateStart, UserID into newtable
    From [YourSourceTable]

    a Select into has to be into a table that has not been created as the select into creates the table for you.

    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    <dime light going on over head> Ah, I think I have it...

    Code:
    INSERT INTO HISTORY (ContactID, Subject, Body, DateStart, UserID)
    Select ContactID,'2003 5th Wheel Show N Sell Mailer', '2003 5th Wheel Show N Sell Mailer', '7/15/03', '4'
    From [YourSourceTable]
    Originally posted by ghornet
    INSERT INTO HISTORY (ContactID, Subject, Body, DateStart, UserID)
    Select ContactID, Subject, Body, DateStart, UserID
    From [YourSourceTable]

    So what about the text I want to put into it

    INSERT INTO HISTORY (ContactID, Subject, Body, DateStart, UserID)
    Select ContactID, Subject, Body, DateStart, UserID
    From [YourSourceTable]

    where does
    '2003 5th Wheel Show N Sell Mailer', '2003 5th Wheel Show N Sell Mailer', '7/15/03', '4'
    fit into the picture?
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Jul 2003
    Posts
    23

    worked great

    Simple enough thank you very very much. I do have another question I have a bunch of address's some are abbreviated (st) and some are not n(state) I would like to write a stored procedure or a sql function that I can state a table and a column to run it on a and it will take all of the abbreviations and make them long. Also is there anyway to undo an action in SQL say I accidently made a bunch of inserts I was'nt suppose to.. (not that I did (; ) there is no easy undo last statement right. Thanks for the help

    D--
    "Everything is possible, somethings are just less likely then others"

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    restore last transaction log, if you were backing it up...if not, - last full/incremental back, same condition

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ghornet,

    What you are looking for now is called an UPDATE query.

    Take a look in Books On Line for syntax and usage of SELECT, INSERT, and UPDATE, and become familiar with them because they will be 90% of what you do with SQL.

    blindman

  9. #9
    Join Date
    Jul 2003
    Posts
    23

    I know update

    I know that I can use update ok for example I want to update all of the St is my address field I can use

    UPDATE contacts set Address = replace(Address, " St ", "Street")

    This works ok if St is in the middle of the address if it is at the end it won't pick it up so maybe something like this will work

    UPDATE contacts set Address = replace(Address, " St", "Street")

    There are about 15 or so abbrevations ln, dr, pl and I am consistly getting lists of abbreviated address's so I need some kind of stored procedure, function or even a DTS that would allow me to put the table and the column into a variable so I can run the script on any table I want. Is this the best way to do this, using the replace function and place it in a stored procedure.

    D--
    "Everything is possible, somethings are just less likely then others"

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You probably need to start looking into your WHERE clause more closely. Your REPLACE(Address, ' St', 'Street') may inadvertantly cause addresses that contain ' st' with words like 'steep', 'stone', etc. to be replaced with 'Street'. Is it a one-time or on-going process?

  11. #11
    Join Date
    Jul 2003
    Posts
    23

    hopefully won't be

    Hopefully won't be an ongoing process, but I am sure that it is going to be that is why I want to create a little script that will correct this problem.

    D--
    "Everything is possible, somethings are just less likely then others"

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The only safe way to do this is to write multiple lines of code that clean up different data issues. Like this for starters:

    UPDATE contacts set Address = replace(Address, ' St ', 'Street')
    UPDATE contacts set Address = replace(Address, ' St', 'Street') where right(Address, 2) = 'St'

    Store the code in a cleanup script as you create it, and backup your table before running it. That way, you can easily modify the code, restore the table, run the new code, and then lickety split you are back to where you were before your inadvertently changed '123 St Christopher Lane' to '123 Street Christopher Lane'

    blindman

Posting Permissions

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