RE: finding and inserting

[Hardy Merrill]

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: 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____________________________________
  _____|_____|_____|_____|_____|_____|_____|_____|