RE: finding and inserting

[mark.d.andrews]

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: dbi-users@perl.org
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____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|