Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old January 17th, 2006, 01:02 AM
Authorized User
 
Join Date: Aug 2004
Location: LAKESIDE, California, USA.
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default stored procedure that inserts data

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 With Quote
  #2 (permalink)  
Old January 19th, 2006, 11:28 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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 With Quote
  #3 (permalink)  
Old January 20th, 2006, 02:09 PM
Authorized User
 
Join Date: Aug 2004
Location: LAKESIDE, California, USA.
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old January 20th, 2006, 06:10 PM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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 With Quote
  #5 (permalink)  
Old January 22nd, 2006, 02:08 AM
Authorized User
 
Join Date: Aug 2004
Location: LAKESIDE, California, USA.
Posts: 38
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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 With Quote
  #6 (permalink)  
Old January 22nd, 2006, 04:25 PM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stored Procedure versus Data Access Logic Componen AspNetGuy BOOK: Beginning ASP.NET 2.0 and Databases 0 June 26th, 2008 05:25 PM
Stored Procedure muthu555 SQL Server 2000 1 March 5th, 2007 07:13 AM
Stored Procedure kk_kumar99 Beginning VB 6 1 October 30th, 2006 12:50 PM
please help me with this stored procedure bebeko SQL Server 2000 1 June 10th, 2005 12:09 PM
help wanted re: form that inserts data to sql db madcap ADO.NET 2 November 14th, 2003 10:26 AM



All times are GMT -4. The time now is 01:51 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.