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