[dba-SQLServer] SPROC Help SELECT and INSERT.

Thanks Asger,

I have this working great now.  For the archives here is the whole thing.  Lesson learnt was that the INSERT statement needs () and the SELECT statement does not.

'==================================================

if exists (select * from dbo.sysobjects where id = object_id(N'dbo.stprSubmitForecast') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.stprSubmitForecast
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/*
        Name : stprSubmitForecast
        Description : Update/Submit the forecast data from the Live table to the Archive Table
        Revision History : 2008-10-17 Darryl Collins - Created

*/

        CREATE PROCEDURE dbo.stprSubmitForecast
        @UCFYPID Int,
        @APLID Int

        AS

        SET NOCOUNT ON

        DELETE FROM dbo.ITFCT_tblForecastSubmitted
        WHERE FYPID_Submitted = @UCFYPID AND APLID = @APLID

        INSERT INTO dbo.ITFCT_tblForecastSubmitted
        (FYPID_Submitted,
        ForecastID,
        ForecastLinkID,
        IsCurrent,
        ProductCode,
        ProjectCode,
        SkillGroup,
        Component,
        ResourceOrItem,
        Organisation,
        OrgAlias,
        StandardTaskID,
        StandardTaskAdd,
        MECID,
        SpendTypeID,
        AccTreatmentID,
        AccountingSplit,
        EffortDays,
        EffortRate,
        [ValueAUD],
        ValueOpex,
        ValueCapex,
        FYPID,
        [Comments],
        ForecastStatusID,
        APLID)

        SELECT
        @UCFYPID,
        ForecastID,
        ForecastLinkID,
        IsCurrent,
        ProductCode,
        ProjectCode,
        SkillGroup,
        Component,
        ResourceOrItem,
        Organisation,
        OrgAlias,
        StandardTaskID,
        StandardTaskAdd,
        MECID,
        SpendTypeID,
        AccTreatmentID,
        AccountingSplit,
        EffortDays,
        EffortRate,
        [ValueAUD],
        ValueOpex,
        ValueCapex,
        FYPID,
        [Comments],
        ForecastStatusID,
        APLID

        FROM dbo.ITFCT_tblForecastLive
        WHERE FYPID = (@UCFYPID+1) AND APLID = @APLID AND IsCurrent <> 0

        SELECT 0 AS Result

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

'============================================================

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Asger
Blond
Sent: Monday, 29 September 2008 6:00 AM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] SPROC Help SELECT and INSERT.


Darryl,

This may be what you need:

INSERT INTO tblTarget (FYP_Imported, SourceKeyID, Alpha, Beta, Gamma, FYP,
Prog)
SELECT @FYP_imported, SourceKeyID, Alpha, Beta, Gamma, FYP, Prog
FROM tblSOURCE
WHERE FYP = @FYP AND Prog = @Prog

Asger

-----Oprindelig meddelelse-----
Fra: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] På vegne af Darryl
Collins
Sendt: 28. september 2008 02:29
Til: 'Discussion concerning MS SQL Server'
Emne: Re: [dba-SQLServer] SPROC Help SELECT and INSERT.


Hi guys and gals,

A bit of advice if you don't mind. Trying to write a SPROC and know this
should be easy and I think I am making it way too complicated.

I have data in one table and want to copy and insert into another table
(identical fields pretty much).  So for each example

tblSource
'-------------------
SourceKeyID (PK)
Alpha
Beta
Gamma
FYP
Prog

so: SQL would be

SELECT * FROM tblSource
WHERE FYP = @FYP AND Prog = @Prog

or maybe

SELECT
SourceKeyID,
Alpha,
Beta,
Gamma,
FYP,
Prog
FROM tblSource
WHERE FYP = @FYP AND Prog = @Prog

Then I want to insert it in to the tblTarget table.

tblTarget
'-------------------
TargetKeyID ' Auto generated
FYP_Imported ' Needs to be written as a variable (eg 75)
SourceKeyID (FK) ' The following all come from the source table
Alpha
Beta
Gamma
FYP
Prog

Something like

INSERT INTO tblTarget
FYP_Imported = @FYP_imported
SourceKeyID,
Alpha,
Beta,
Gamma,
FYP,
Prog

.

Anyway, I can't seem to get it to work so I am clearly fouling something up
*sigh* any advice?

cheers
Darryl.



_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


This email and any attachments may contain privileged and confidential information
and are intended for the named addressee only. If you have received this e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this e-mail
has been sent to you in error. It is your responsibility to check this e-mail and
any attachments for viruses.  No warranty is made that this material is free from
computer virus or any other defect or error.  Any loss/damage incurred by using this
material is not the sender's responsibility.  The sender's entire liability will be
limited to resupplying the material.