Copying records using Select Into

[addstravel]

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...

[r937]

please rephrase the question, it's not clear what you're trying to do (i think you got X and Y mixed up somewhere)

[addstravel]

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?

[r937]

okay, i think i understand

insert
  into CompanyConfig
     ( CompanyId 
     , foo
     , bar )
select 937
     , foo
     , bar
  from CompanyConfig
 where CompanyId = -1

[addstravel]

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

[r937]

you may need to show your actual query

by the way, what database system is this?

[addstravel]

Hi, I am using MS SQL2000.

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

Apologises if I am seeming slow here

[r937]

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?

[addstravel]

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.

[r937]

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

[addstravel]

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