|
Subject:
|
stored procedure that inserts data
|
|
Posted By:
|
desireemm
|
Post Date:
|
1/17/2006 12:02:34 AM
|
I need to create a stored procedure that will insert data from my Active table to my Terminated table, the thing is I only need one field from the active table the rest of the information I already have. Its an employee database when employees get terminated their status changes to terminted then I execute a stored procedure I have that inserts the data from the active table to the terminated table. The thing is I need to add another field to the term table and the information i need for that field is in the active table. How can I get it??? Does that make sense, every time I try I get a primary key violation.
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
1/19/2006 10:28:25 AM
|
You need to clarify. Show some sample data and the table structures, and what exactly what you are trying to do. Show data before and after to show what you are trying to accomplish
|
|
Reply By:
|
desireemm
|
Reply Date:
|
1/20/2006 1:09:06 PM
|
ok sorry I'll explain further, I have a database that has a table for terminated employees and I need to get data from one database to another.
the TM# is the primary key in the Termination table [GamingCommissiondb] and the Employee is the primary key [Test]in the DesireeTerm3 table Data type on both is INT
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE InsertTermsReasons
AS
INSERT INTO [Test].[dbo].[TERMINATION] (
[TM #], [Reasonfortermination]
) SELECT
a.[Employee], a.[DESCRIPTION]
FROM DesireeTerm3 AS a
WHERE a.ACTION_CODE = 'TERM'
AND NOT EXISTS (SELECT *
FROM [Test].[dbo].[TERMINATION] AS z
WHERE z.[TM #] = a.[Employee])
RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
1/20/2006 5:10:22 PM
|
Still unclear. Show some sample data, fist the data your have, and next, what it should look like after the insert... your expected results
|
|
Reply By:
|
desireemm
|
Reply Date:
|
1/22/2006 1:08:10 AM
|
ok just found out its not an insert I need to do its an update, an insert will try to insert an entire row and I am only trying to update old data with new data. For instance if I move all the 99 terms from the active table to the term table and lets say for example their [hiredate], [ID], [firstname], but after the update is done I realize I forgot to include the [lastname] field, see what I mean??? Or I just wanted to UPDATE old data with new data?? Would this stored procedure work
CREATE PROCEDURE [InsertTerms]
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION]
(ReasonTerminated)
SELECT a.DESCRIPTION
FROM DesireeTerm3
WHERE TERIMINATION.TM#= DesireeTerm3.Employee
RETURN
GO
|
|
Reply By:
|
jbenson001
|
Reply Date:
|
1/22/2006 3:25:21 PM
|
Again..it would be helpfull if you showed some sample data..and the desired results
|
