Solved

MSSQL Stored Procecedure to Insert data

Posted on 2014-12-20
11
83 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 33

Expert Comment

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

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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 34

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 34

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 34

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 34

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tempdb Contention - SQL SERVER 10 41
SQL syntax for max(date) 3 39
SQL - Using an 'array' kind of variable 5 18
Query Task 8 22
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.
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 ?
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 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