[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure to move records

Posted on 2005-04-10
4
Medium Priority
?
379 Views
Last Modified: 2012-06-27
I have a stored procedure that just moves contacts from one table to another. It looks like this.

CREATE PROCEDURE sp_MoveContacts
(
      @ID      int,
                @companyID int
)
AS

INSERT Contacts ( FirstName,LastName, ContactTypeID, companyID )
SELECT FirstName, LastName, ContactTypeID ,companyID
FROM
      v_CartContactInfo
WHERE
      ID=@ID
GO

Instead of it copying the ComanyID from one table to another. I would like to put the companyID that I pass to the stored procedure
Instead ove
0
Comment
Question by:jui2ce
  • 2
4 Comments
 
LVL 17

Expert Comment

by:mokule
ID: 13749126
BEGIN TRAN

INSERT Contacts ( FirstName,LastName, ContactTypeID, companyID )
SELECT FirstName, LastName, ContactTypeID ,companyID
FROM
     v_CartContactInfo
WHERE
     ID=@ID

DELETE
FROM
     v_CartContactInfo
WHERE
     ID=@ID

COMMIT TRAN
0
 

Author Comment

by:jui2ce
ID: 13749131
I don't see where this would insert into the new Table the CompanyId I pas instead of just coping the one in the current table.
0
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13749173
Try this one:

CREATE PROCEDURE sp_MoveContacts
(
     @ID     int,
                @companyID int
)
AS

INSERT Contacts ( FirstName,LastName, ContactTypeID, companyID )
SELECT FirstName, LastName, ContactTypeID , @companyID
FROM
     v_CartContactInfo
WHERE
     ID=@ID
GO

Simply replace the companyID with @companyID and this should work.  If you have a foreign key to your company table, make sure that the @companyID exists in the company table.

Hope this answers your question.
0
 

Author Comment

by:jui2ce
ID: 13749199
Ah sooo simple should have tried it. Thanks
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question