Solved

MSSQL Stored Procecedure to Insert data

Posted on 2014-12-20
11
71 Views
Last Modified: 2015-01-24
Hi,
I need to create an Store Procedure that I can assign a Job to insert records from 1 table to a second one.

From table tblOCPR, columns: Name, Dni, Company, Phone
To tblContacts, columns: UsrName, UsrDni, UsrCompany, UsrPhone

I would like to use this sp template:
CREATE PROCEDURE sp_InsertContacts
AS
BEGIN
 BEGIN TRANSACTION 
 BEGIN TRY 
Insert into Employee() Values()
 set @flag=1;
IF @@TRANCOUNT > 0
 BEGIN commit TRANSACTION;
 END
 END TRY 
BEGIN CATCH
IF @@TRANCOUNT > 0
 BEGIN rollback TRANSACTION;
 END
 set @flag=0;
 END CATCH
END

Open in new window

0
Comment
Question by:José Perez
  • 5
  • 5
11 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 40511340
Looks like a homework problem to me - just sayin'.
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40511344
The purpose of a proc is to be used repeatedly. The way you are using (hard coding) your proc defeats this purpose.

You need to add some parameters to your proc to function as intended. This will be like:

CREATE PROCEDURE spInsertContacts    -- also sp_  generally is used by then system procs, use spInsertContacts
@OCPRID int
AS
BEGIN

inside the proc then you need

Insert into  tblContacts(UsrName, UsrDni, UsrCompany, UsrPhone)
Select Name, Dni, Company, Phone From tblOCPR
Where OCPRID = @OCPRID


to test:
Exec spInsertContacts 2    -- for example, if 2 is a primary key in tblOCPR

you need to complete this without transaction and make sure it works the continue with Transaction.

Do you need to copy from tblOCPR  to tblContacts or move it?

Mike
0
 
LVL 2

Author Comment

by:José Perez
ID: 40511397
Copy.

It displays an error in "Where OCPRID = @OCPRID"
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40511546
post your code and exact error message. Also OCPRID I assumed exists in your table. I referred to it as primary key. What ID (pk) you have in that table?

You were supposed to replace OCPRID with pk field that exists in your table. Now that you are at point you are designing stored procedures, you are expected to know about pk field very well.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40517131
Tomorrow I'll try, please gime time.
Thanks.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40518067
ok, I updated the SP but now it says "invalid object name [dbo].[spInsertContacts]" (I checked the name and it is correct)

USE [dbDevelopment]
GO
/****** Object:  StoredProcedure [dbo].[spInsertContacts]    Script Date: 01-10-2014 23:29:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spInsertContacts]
@id int
AS
BEGIN

Insert into tblContacts(usrName, usrDNI, usrCompany, usrPhone)
Select name, dni, company, phone from tblOCPR
Where id=@id
End

Open in new window

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40518071
If spInsertContacts exists and you are just altering it, you will use:

ALTER PROCEDURE [dbo].[spInsertContacts]

Otherwise, as you know, you will be using:

 CREATEPROCEDURE [dbo].[spInsertContacts]

Is this where the problem was?
0
 
LVL 2

Author Comment

by:José Perez
ID: 40518159
It is now running but nothing is inserted into tblContacts.
I have 5 rows in tblOCPR

I added an id int as pk to both tables but still nothing happens.
Insert into tblContacts(id, usrName, usrDNI, usrCompany, usrPhone)
Select id, name, dni, company, phone from tblOCPR

Open in new window

0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40518187
Are  you testing this in SSMS? Or, via ADO.NET?

Oh BTW, you cannot include id field, try:

Insert into tblContacts(usrName, usrDNI, usrCompany, usrPhone)
Select name, dni, company, phone from tblOCPR Where id = @Id

Id fields are auto generated.
0
 
LVL 2

Author Comment

by:José Perez
ID: 40519171
Same error :(
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40519188
Are  you testing this in SSMS? Or, via ADO.NET?

If in SSMS, what is exact syntax of running the stored procedure.

Does this error occur when you are creating the stored procedure or executing the stored procedure?
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

810 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