stored procedure that inserts data

desireemm · January 17th, 2006, 01:02 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.

jbenson001 · January 19th, 2006, 11:28 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

desireemm · January 20th, 2006, 02:09 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

Code:

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

jbenson001 · January 20th, 2006, 06:10 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

desireemm · January 22nd, 2006, 02:08 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

jbenson001 · January 22nd, 2006, 04:25 PM

Again..it would be helpfull if you showed some sample data..and the desired results