Maybe id should have added a bit more detail. For simplicity sake I will assume that the table only has three columns: employee_id, district_number, and everything_else
Assume the table has these values: 1 | 'District 1' | 'any other data' 2 | 'District 1' | 'any other data' 5 | 'District 1' | 'any other data' 2 | 'District 2' | 'any other data' 3 | 'District 2' | 'any other data' 5 | 'District 2' | 'any other data' 1 | 'District 3' | 'any other data' 5 | 'District 3' | 'any other data' Now suppose we want to create a new "District 4" with all the stuff that is currently in district 2. Here is the code that will do that: INSERT into district SELECT employee_id, 'District 4', everyting_else WHERE district = 'District 2' This will create a table that looks like this: 1 | 'District 1' | 'any other data' 2 | 'District 1' | 'any other data' 5 | 'District 1' | 'any other data' 2 | 'District 2' | 'any other data' 3 | 'District 2' | 'any other data' 5 | 'District 2' | 'any other data' 1 | 'District 3' | 'any other data' 5 | 'District 3' | 'any other data' 2 | 'District 4' | 'any other data' 3 | 'District 4' | 'any other data' 5 | 'District 4' | 'any other data' Which is what I understand is as expected. I am assuming that employee_id is unique within a district, i.e. employee_id and district are the primary key of the table. On the other hand, if the table looks like this: 1 | 'District 1' | 'any other data' 2 | 'District 1' | 'any other data' 2 | 'District 1' | 'some other data' 5 | 'District 1' | 'any other data' 2 | 'District 2' | 'any other data' 3 | 'District 2' | 'any other data' 5 | 'District 2' | 'any other data' 1 | 'District 3' | 'any other data' 5 | 'District 3' | 'any other data' And you only want a single occurrence of employee_id = 2 to be created within the new District 4 then you will have problems. If the table looks like the second version and you want to create a new District 4 from the existing District 1 with only one entry per employee_id then you will need a two step process: first get a list of unique employee_id values: SELECT distinct employee_id from district where district = 'District 1' Then get the records for each one: SELECT * from district where employee_id = <retrieved value> and district = 'District 1' And then decide which record is the correct one and ignore the rest. Hope this is a more explicit example of what I briefly touched on in my first response. Mark -----Original Message----- From: Hardy Merrill [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 10:06 AM To: [email protected]; Andrews, Mark (TS USA) Subject: RE: finding and inserting I'm no database expert (I'm probably way off here), but doesn't that need to be more like this: INSERT INTO district2 SELECT employee_id, ... FROM district1 And, that still doesn't address the issue of duplicate employee id's. I think to address that problem you'd have to define a unqiue index in the district2 table that includes just the employee_id column. Assuming that has been done, I don't know how that will affect the INSERT statement above, since there are presumably many duplicate employee id's - will the first dup cause the above INSERT to die? >>> <[EMAIL PROTECTED]> 02/11/05 9:16 AM >>> This would definitely be the better solution but assuming you are constrained by an exiting schema you can accomplish your goal with a simple insert statement. The following almost complete insert statement will do what I think you want to do: INSERT INTO district_table SELECT 'District 2', employee_id, ... FROM district_table WHERE district = 'District 1' Hope this help Mark -----Original Message----- From: amonotod [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 9:00 AM To: Robert Cc: [email protected] Subject: Re: finding and inserting > From: "Robert" <[EMAIL PROTECTED]> > Date: 2005/02/11 Fri AM 07:10:25 CST > > I have about 200 users in "district 1". I need to add each of those > users into "district 2" using the same information that they have in > "district 1". I also need to make sure they are only inserted 1 time. > They have a unique employeeID, so I know I can use that to make sure > that they are inserted 1 time. This is simply an opinion, so take it at face value, okay? I think that you need to redesign your database, so that users and disctricts are not directly related, and then use a lookup table to correlate them together. That way, no matter which district a user is allowed to be "in", that user will have only one ID and one password. Below is code for table creation in MSSQL, for one each simple employee table, district table, and employee<->district lookup table... CREATE TABLE [dbo].[Districts] ( [DistID] [int] NOT NULL , [DistName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DistDesc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[EmpDistLkp] ( [EmpID] [int] NOT NULL , [DistID] [int] NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Employees] ( [EmpID] [int] NOT NULL , [EmpFName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [EmpLName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [EmpPwd] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Districts] ADD CONSTRAINT [PK_Districts] PRIMARY KEY CLUSTERED ( [DistID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Employees] ADD CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [EmpID] ) ON [PRIMARY] GO ALTER TABLE [dbo].[EmpDistLkp] ADD CONSTRAINT [FK_EmpDistLkp_Districts] FOREIGN KEY ( [DistID] ) REFERENCES [dbo].[Districts] ( [DistID] ), CONSTRAINT [FK_EmpDistLkp_Employees] FOREIGN KEY ( [EmpID] ) REFERENCES [dbo].[Employees] ( [EmpID] ) GO v/r, amonotod -- `\|||/ amonotod@ | sun|perl|windows (@@) charter.net | sysadmin|dba ooO_(_)_Ooo____________________________________ _____|_____|_____|_____|_____|_____|_____|_____|
