SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation Copying records using Select Into

    Hi,
    I have a table [x] that has records with a Company Id field. When I add a new company (talbe [y]) I want to copy all the records in table x into table x with the new Company Id. This allows me to have different values for each company in table x.
    Does this make sense?
    I have seen the Select Into sql syntax, but this only creates a new table. I have used this to create a temp table where I can change all the Company Id's, but do I have to really Insert each line back into table [x]?
    Any ideas much appreciated...

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    please rephrase the question, it's not clear what you're trying to do (i think you got X and Y mixed up somewhere)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have 10 records in table [CompanyConfig] that should be set for each company I have in the [Company] table. I have set up some default config values in the [CompanyConfig] table and used a CompanyId as -1 to show this. When I create a new company I need to insert the 10 records again BUT with the CompanyId of the new company. Instead of running 10 insert statements I was wondering whether I can use a Select Into a temp table, then copying back into the [CompanyConfig] table with the new companyId.
    Does this make sense?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, i think i understand
    Code:
    insert
      into CompanyConfig
         ( CompanyId 
         , foo
         , bar )
    select 937
         , foo
         , bar
      from CompanyConfig
     where CompanyId = -1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937 but couldnt get this to work or more likely I am typing this incorrectly.

    Am I right in thinking that I can insert new records into a table using records that exist in the same table and using a where clause?

    The table is called [CompanyConfig] and there are three fields, including an ID field.

    Much appreciated

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you may need to show your actual query

    by the way, what database system is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, I am using MS SQL2000.

    I used the sql syntax you wrote in the previous post.

    Apologises if I am seeming slow here

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, you didn't use the query i posted, unless you actually named your columns foo and bar

    i wanted to see the query that you wrote in case "... or more likely I am typing this incorrectly"

    also, it would help if you could explain what "couldn't get this to work" means -- did you get an error message? if so, what was it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    I used the following sql:

    insert into libCategory ( CompanyId, CategoryName, CategoryLetter )
    select CompanyId, CategoryName, CategoryLetter from libCategory where CompanyId = -1

    This worked well for copying records into the same DB, but how would I in the process set the company Id?

    Thanks for your time.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    by replacing the column CompanyId right after the SELECT with an actual value, the value that you want that new company to have

    like the value 937 in my example

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist
    Join Date
    Jun 2001
    Location
    London
    Posts
    423
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Thank you so so much, your a star as this works a treat....



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
  •