Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    8

    Unanswered: Insert Query Sql 2005

    Im new to databases and sql, I am trying to edit one of my stored procedures

    Code:
    USE [AchieverFix]
    GO
    /****** Object:  StoredProcedure [dbo].[HKQ_ActAllocUnknown]    Script Date: 03/06/2009 15:17:27 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER procedure [dbo].[HKQ_ActAllocUnknown] AS
    
    INSERT INTO ErrorTable (COMPANYNAME,USERCODE,COMPLETEFLAG,NUMBER,INTEREST,REMARKS,TYPE,DATE,TIME)
    
    SELECT COMPANY.COMPANYNAME, ACTIVITYHEADER.USERCODE, ACTIVITY.COMPLETEFLAG, PRODUCTLINES.NUMBER, ACTIVITY.INTEREST, ACTIVITY.REMARKS, ACTIVITY.TYPE, ACTIVITY.DATE, ACTIVITY.TIME
    FROM Achiever.dbo.ACTIVITY ACTIVITY, Achiever.dbo.ACTIVITYHEADER ACTIVITYHEADER, Achiever.dbo.COMPANY COMPANY, Achiever.dbo.PRODUCTLINES PRODUCTLINES
    WHERE PRODUCTLINES.PRODLINENO = ACTIVITY.PRODLINENO AND ACTIVITYHEADER.ACTIVITYHEADERNO = ACTIVITY.ACTIVITYHEADERNO AND COMPANY.COMPANYNO = ACTIVITYHEADER.COMPANYNO AND ((ACTIVITY.COMPLETEFLAG=2) AND (ACTIVITY.SALESPERSON='Unknown') OR (ACTIVITY.COMPLETEFLAG=0) AND (ACTIVITY.SALESPERSON='Unknown'))
    ORDER BY PRODUCTLINES.NUMBER
    
    execute HKQ_ActDiffProdSettings
    I have a column in achieverfix database called Qname, I want this column to contain 'Activities Allocated to Unknown' when this query is run. I have tried to search around and use @Qname as a variable and give this a value but it does not seem to work

  2. #2
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    'Activities Allocated to Unknown' as QName

    Also, get away from that old join syntax. use INNER JOIN, LEFT JOIN etc

    from:
    select taba.name from taba, tabb
    where taba.id = tabb.id

    to:
    select a.name from from taba a INNER JOIN tabb b
    on a.id = b.id.

  3. #3
    Join Date
    Jan 2009
    Posts
    17
    ALTER procedure [dbo].[HKQ_ActAllocUnknown] AS

    INSERT INTO ErrorTable (COMPANYNAME,USERCODE,COMPLETEFLAG,NUMBER,INTEREST ,REMARKS,TYPE,DATE,TIME)

    SELECT COMPANY.COMPANYNAME, ACTIVITYHEADER.USERCODE,
    ACTIVITY.COMPLETEFLAG, PRODUCTLINES.NUMBER,
    ACTIVITY.INTEREST, ACTIVITY.REMARKS,
    ACTIVITY.TYPE, ACTIVITY.DATE, ACTIVITY.TIME
    FROM Achiever.dbo.ACTIVITY ACTIVITY
    INNER JOIN
    Achiever.dbo.ACTIVITYHEADER ACTIVITYHEADER ON ACTIVITYHEADER.ACTIVITYHEADERNO = ACTIVITY.ACTIVITYHEADERNO
    INNER JOIN
    Achiever.dbo.COMPANY COMPANY ON COMPANY.COMPANYNO = ACTIVITYHEADER.COMPANYNO
    INNER JOIN
    Achiever.dbo.PRODUCTLINES PRODUCTLINES ON PRODUCTLINES.PRODLINENO = ACTIVITY.PRODLINENO
    WHERE
    ((ACTIVITY.COMPLETEFLAG=2) AND (ACTIVITY.SALESPERSON='Unknown'))
    OR ((ACTIVITY.COMPLETEFLAG=0) AND (ACTIVITY.SALESPERSON='Unknown'))
    ORDER BY PRODUCTLINES.NUMBER

    execute HKQ_ActDiffProdSettings

    and specify like for QNAME
    'Activities Allocated to Unknown' as QName

  4. #4
    Join Date
    Mar 2009
    Posts
    8
    Thanks for the replies, can't believe how simple it was. Well when you know how of course

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •