Inserting Next Row

Question

Inserting Next Row

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

It looks like you've added to it. You don't need to VALUES clause. You are INSERTing the result set produced by the SELECT statement.

Also, you have multiple input parameters declared which you don't need. The error you are getting is because you don't have a comma between parameter declarations. Copy/paste my example and test that version.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

technette SSC Rookie Points: 45 More actions · Answer 2

Hall of Fammer,

Yours works beautifully. I see also the message regarding the insert and select values. I want the first and last name listed in Employee table to go into the ProjMgr Column.

In the PR table, only the employee number is listed and linked to the EM table. That's where I'm pulling the first and last name.

Error Message:

Msg 121, Level 15, State 1, Procedure insert_AssociatedContracts, Line 5

The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns.

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Ah, I should have noticed that! The column list in the INSERT and SELECT should be matched up. The values will be inserted into the table and mapped in the order they appear in the INSERT and SELECT portions of the statement.

So you'll want to go through and align the columns so that the correct values end up in the correct columns. Your SELECT statement is returning 2 more columns then your INSERT is specifying.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

technette SSC Rookie Points: 45 More actions · Answer 4

Thank you for all of your help! 🙂

Here it is:

CREATE PROCEDURE dbo.insert_AssociatedContracts

@WBS1 varchar(30)

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(EM.FirstName + EM.LastName),

PR.StartDate,

PR.EndDate,

PR.FirmCost

FROM PR INNER JOIN EM

ON PR.ProjMgr = EM.Employee

WHERE WBS1 = @WBS1

GO

EXECUTE dbo.insert_AssociatedContracts

GO

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

I have to ask... what if someone needs a 4th WBS code?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

technette SSC Rookie Points: 45 More actions · Answer 6

This particular system only has three WBS levels:

Project WBS1

Phase WBS2

Task WBS3

When the system was implemented, the business development team decided that they only wanted to use WBS1 level, a very flat data structure. I have enabled the other levels in a test environment and will demonstrate many aspects of the systems that will enable more efficient and effective work processes for them.

This stored procedure will automatically populate a vital table, used in proposals, that was previously maintained manually.