Inserting Next Row
-
Will the following code work if I want to take new data from an existing table, insert it into another, find a blank row and insert next row of data into table?
Data from Project (PR) table is entered into Associated Contracts listing WBS1 number then WBS2 number.
CREATE PROCEDURE dbo.insert_AssociatedContracts
@WBS1 varchar(30)
@WBS2 varchar(7)
@WBS3 varchar (7)
@Name varchar (40)
@ProjectMgr varchar (20)
@StartDate datetime
@EndDate datetime
@Firmcost decimal(19,4)
AS
SELECT PR.WBS1, PR.WBS2, PR.WBS3, PR.Name, PR.ProjMgr, PR.StartDate, PR.EndDate, PR.FirmCost, EM.FirstName+EM.LastName, EM.Employee
FROM PR INNER JOIN
EM ON PR.ProjMgr = EM.Employee
INSERT INTO [dbo].[Projects_AssociatedContracts]
(
[WBS1]
,[WBS2]
,[WBS3]
,[custProjectName]
,[custProjectManager]
,[custStartDate]
,[custEndDate]
,[custJobValue]
WHERE WBS1 = @WBS1
GO
EXECUTE dbo.insert_AssociatedContracts
GO
-
No, in fact this SP should not even run. If you create and run this SP, do you get errors? You should. This should be closer to what you want:
INSERT INTO [dbo].[Projects_AssociatedContracts] ([WBS1],[WBS2],[WBS3],[custProjectName],[custProjectManager],[custStartDate],[custEndDate],[custJobValue])SELECT PR.WBS1,PR.WBS2,PR.WBS3,PR.Name,PR.ProjMgr,PR.StartDate,PR.EndDate,PR.FirmCost,COALESCE(EM.FirstName,'') + COALESCE(EM.LastName,''),EM.EmployeeFROM PR INNER JOIN EMON PR.ProjMgr = EM.EmployeeWHERE WBS1 = @WBS1Note that I took your code and formatted it into a proper INSERT/SELECT statement. I also added in COALESCE around your name columns. Without handling NULLs, any NULL in the first or last name would result in the whole value being NULL since you are combining them.
One last thing, your SP has several input parameters that are not being used. Did you intend to do something with them?
-
Thank you for your quick response. I really appreciate it.
Yes, I was getting errors.
Now I get one error:
Msg 137, Level 15, State 2, Line 23
Must declare the scalar variable "@WBS1".
-
My post was not the complete stored procecure. You'll want to take that code and put it in your CREATE PROCEDURE statement in place of what you have between the AS and GO.
-
Thank you! Guess that's why i'm a newbie
-
No problem.
How about those extra input parameters on your stored procedure, are you planning on using them? If not, you can get rid of them as it looks like you are only using the @WBS1 parameter.
-
I'm actually using all input parameters. I'm trying to make sure that all jobs (WBS2) and associated data
[WBS3],
[custProjectName],
[custProjectManager],
[custStartDate],
[custEndDate],
[custJobValue]
are input into the Associated Contracts Table. This will insert an entire row for each job that has the same WBS1 number.
-
The input parameters are the values declared in your CREATE PROCEDURE statement beginning with @.
CREATE PROCEDURE dbo.insert_AssociatedContracts@WBS1 varchar(30)@WBS2 varchar(7)@WBS3 varchar (7)@Name varchar (40)@ProjectMgr varchar (20)@StartDate datetime@EndDate datetime@Firmcost decimal(19,4)So you've declared 8 parameters, but only the @WBS1 paramter is being referenced anywhere in the code.
Keep in mind there is a giant difference between the parameter values (beginning with @) and the Column Names that you show in your last post.
[WBS3],
[custProjectName],
[custProjectManager],
[custStartDate],
[custEndDate],
[custJobValue]
-
I think I understand. So I modified the code accordingly.
CREATE PROCEDURE dbo.insert_AssociatedContracts
@WBS1 varchar(30)
@WBS2 varchar(7)
@WBS3 varchar (7)
@Name varchar (40)
@ProjectMgr varchar (20)
@StartDate datetime
@EndDate datetime
@Firmcost decimal(19,4)
AS
INSERT INTO [dbo].[Projects_AssociatedContracts] (
[WBS1],
[WBS2],
[WBS3],
[custProjectName],
[custProjectManager],
[custStartDate],
[custEndDate],
[custJobValue]
)
SELECT PR.WBS1,
PR.WBS2,
PR.WBS3,
PR.Name,
PR.ProjMgr,
PR.StartDate,
PR.EndDate,
PR.FirmCost,
COALESCE(EM.FirstName,'') + COALESCE(EM.LastName,''),
EM.Employee
FROM PR INNER JOIN EM
ON PR.ProjMgr = EM.Employee
WHERE WBS1 = @WBS1, WBS2 = @WBS2, WBS3 = @WBS3, Name = @Name, COALESCE(EM.FirstName,'') + COALESCE(EM.LastName,'') = @ProjectMgr, StartDate = @StartDate, EndDate =@EndDate, FirmCost = @FirmCost
GO
EXECUTE dbo.insert_AssociatedContracts
GO
-
Just to clarify, I was not saying that you needed to use them, I was just raising the question as to whether they should be used or not. Using them or not will have an impact on how the procedure runs.
From a business perspective, how do you want to tell the SP 'which' row to insert into your Projects_AssociatedContracts table? Do you really want to have to specify each of those values or is there a unique identifier that you could go by?
-
The unique identifier will be WBS1. All specified data for each row is uniquely identified as belonging to the same WBS1. I'm pulling this specific set of data from the PR table (which has over 40 rows) and inserting this set into and Associated Project Table.
Thank you so much for digging further into this with me.
-
If the WBS1 value uniquely identifies which row you want to copy to your destination table, your SP would look more like this:
CREATE PROCEDURE dbo.insert_AssociatedContracts@WBS1 varchar(30)ASINSERT INTO [dbo].[Projects_AssociatedContracts] ([WBS1],[WBS2],[WBS3],[custProjectName],[custProjectManager],[custStartDate],[custEndDate],[custJobValue])SELECT PR.WBS1,PR.WBS2,PR.WBS3,PR.Name,PR.ProjMgr,PR.StartDate,PR.EndDate,PR.FirmCost,COALESCE(EM.FirstName,'') + COALESCE(EM.LastName,''),EM.EmployeeFROM PR INNER JOIN EMON PR.ProjMgr = EM.EmployeeWHERE WBS1 = @WBS1GOEXECUTE dbo.insert_AssociatedContractsGO -
I meant to say that the PR table has over 40 columns.
-
Right, but if you will be using the WBS1 column to tell the Sp which row to copy over to your Projects_AssociatedContracts table, you only need one input parameter and one value in your WHERE clause.
The only thing missing now is your SP call needs to contain the WBS1 value that you want to copy, like this:
EXECUTE dbo.insert_AssociatedContracts 'WBS1 value'Make sense?
-
Modified:
With the following errors:
Msg 102, Level 15, State 1, Procedure insert_AssociatedContracts, Line 3
Incorrect syntax near '@WBS2'.
Msg 137, Level 15, State 2, Procedure insert_AssociatedContracts, Line 34
Must declare the scalar variable "@WBS1".
I don't have a good understanding of scalar variables.
CREATE PROCEDURE dbo.insert_AssociatedContracts
@WBS1 varchar(30)
@WBS2 varchar(7)
@WBS3 varchar (7)
@Name varchar (40)
@ProjectMgr varchar (20)
@StartDate datetime
@EndDate datetime
@Firmcost decimal(19,4)
AS
INSERT INTO [dbo].[Projects_AssociatedContracts] (
[WBS1],
[WBS2],
[WBS3],
[custProjectName],
[custProjectManager],
[custStartDate],
[custEndDate],
[custJobValue]
)
SELECT PR.WBS1,
PR.WBS2,
PR.WBS3,
PR.Name,
PR.ProjMgr,
PR.StartDate,
PR.EndDate,
PR.FirmCost,
COALESCE(EM.FirstName,'') + COALESCE(EM.LastName,''),
EM.Employee
FROM PR INNER JOIN EM
ON PR.ProjMgr = EM.Employee
WHERE WBS1 = @WBS1
VALUES (WBS1, WBS2, WBS3, Name, ProjMgr, StartDate, EndDate, FirmCost)
GO
EXECUTE dbo.insert_AssociatedContracts
GO
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply