SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
-
Feb 3, 2005, 04:59 #1
- Join Date
- Jun 2001
- Location
- London
- Posts
- 423
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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...
-
Feb 3, 2005, 06:20 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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)
-
Feb 3, 2005, 07:41 #3
- 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?
-
Feb 3, 2005, 07:52 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Feb 3, 2005, 08:06 #5
- 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
-
Feb 3, 2005, 08:14 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
you may need to show your actual query
by the way, what database system is this?
-
Feb 3, 2005, 09:07 #7
- 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
-
Feb 3, 2005, 09:10 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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?
-
Feb 3, 2005, 09:33 #9
- 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.
-
Feb 3, 2005, 09:49 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 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
-
Feb 3, 2005, 09:51 #11
- Join Date
- Jun 2001
- Location
- London
- Posts
- 423
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thank you so so much, your a star as this works a treat....
Bookmarks