Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Unanswered: Save or Commit to a table.

    I'm rusty on the tsql side. How do you save to a table from a sql statement. I'm testing for sp's.

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by garrydawkins
    I'm rusty on the tsql side. How do you save to a table from a sql statement. I'm testing for sp's.
    LOL Good joke man,Hey guys check this post and ofcourse the man's profile who posted it...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Save and Commit

    Is this like a cultural thing? I don't tell jokes in this forum. That’s what the yak corral is for. General conversation. I guess that's why I turn webpro over there. IE (India's Microsoft) any way, If you can't help save the sarcasm.

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by garrydawkins
    Is this like a cultural thing? I don't tell jokes in this forum. That’s what the yak corral is for. General conversation. I guess that's why I turn webpro over there. IE (India's Microsoft) any way, If you can't help save the sarcasm.
    Please help me to understand your question...I didn't meant to be the YAk Corral way...anyway ,Do you want to insert data into table or update data in an existing table?That you can easily do by insert or update statements in procedures.You can use most Transact-SQL commands in a stored procedure; however, some commands (such as CREATE PROCEDURE, CREATE VIEW, SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL, and so forth) must be the first (or only) statement in a command batch, and therefore aren't allowed in stored procedures.
    Well,When I checked your profile I found you to be a DBA thats way....
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SWR_save_Work_Orde]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[SWR_save_Work_Orde]
    GO




    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE PROCEDURE dbo.SWR_save_Work_Orde
    -----------------------------------------------------------------------------------------
    -- AUTHOR: gdawkins
    -- PROCEDURE: SWR_Save_Work_Orde
    -- DATE CREATED: 2006-04-06
    -- DATE MODIFIED:
    -- DATE REMOVED:
    -- DESCRIPTION: saves associated with f_WorkOrders.
    -- DEPENDENCIES: SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    CREATE PROCEDURE dbo.SWR_save_Work_Orde
    -----------------------------------------------------------------------------------------
    -- AUTHOR: gdawkins
    -- PROCEDURE: SWR_save_Work_Orde
    -- DATE CREATED: 2006-04-07
    -- DATE MODIFIED:
    -- DATE REMOVED:
    -- DESCRIPTION: savetes fields associated with f_WorkOrders.
    -- DEPENDENCIES:
    -----------------------------------------------------------------------------------------

    (
    @WO_PK int, -- Work Order’s Primary Key.
    @WO_EST_START datetime, -- Estimated Start Date. Include time also in value.
    @WO_EST_COMP datetime, -- Estimated End Date. Include time also in value.
    @WO_BOOLEAN_1 smallint, -- Saturday work required. 0 = No; 1 = Yes.
    @WO_BOOLEAN_2 smallint, -- Sunday work required. 0 = No; 1 = Yes.
    @WO_MOD_DATE datetime, -- Date this record was last modified. May or may not include time.
    @RTN_Code int OUTPUT
    )

    AS

    BEGIN TRAN

    saveTE f_WorkOrder
    SET WO_EST_START = @WO_EST_START,
    WO_EST_COMP = @WO_EST_COMP,
    WO_BOOLEAN_1 = @WO_BOOLEAN_1,
    WO_BOOLEAN_2 = @WO_BOOLEAN_2,
    WO_MOD_DATE = @WO_MOD_DATE
    WHERE WO_PK = @WO_PK

    IF @@ERROR != 0 GOTO ERR_HANDLER

    COMMIT TRAN

    SET @RTN_Code = 0
    RETURN

    ERR_HANDLER:
    ROLLBACK TRAN

    SET @RTN_Code = -20001
    RETURN

    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO

  6. #6
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770
    Sorry, This is what I have so far

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by garrydawkins
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SWR_save_Work_Orde]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[SWR_save_Work_Orde]
    GO




    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO

    CREATE PROCEDURE dbo.SWR_save_Work_Orde
    -----------------------------------------------------------------------------------------
    -- AUTHOR: gdawkins
    -- PROCEDURE: SWR_Save_Work_Orde
    -- DATE CREATED: 2006-04-06
    -- DATE MODIFIED:
    -- DATE REMOVED:
    -- DESCRIPTION: saves associated with f_WorkOrders.
    -- DEPENDENCIES: SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    CREATE PROCEDURE dbo.SWR_save_Work_Orde
    -----------------------------------------------------------------------------------------
    -- AUTHOR: gdawkins
    -- PROCEDURE: SWR_save_Work_Orde
    -- DATE CREATED: 2006-04-07
    -- DATE MODIFIED:
    -- DATE REMOVED:
    -- DESCRIPTION: savetes fields associated with f_WorkOrders.
    -- DEPENDENCIES:
    -----------------------------------------------------------------------------------------

    (
    @WO_PK int, -- Work Order’s Primary Key.
    @WO_EST_START datetime, -- Estimated Start Date. Include time also in value.
    @WO_EST_COMP datetime, -- Estimated End Date. Include time also in value.
    @WO_BOOLEAN_1 smallint, -- Saturday work required. 0 = No; 1 = Yes.
    @WO_BOOLEAN_2 smallint, -- Sunday work required. 0 = No; 1 = Yes.
    @WO_MOD_DATE datetime, -- Date this record was last modified. May or may not include time.
    @RTN_Code int OUTPUT
    )

    AS

    BEGIN TRAN

    saveTE f_WorkOrder
    SET WO_EST_START = @WO_EST_START,
    WO_EST_COMP = @WO_EST_COMP,
    WO_BOOLEAN_1 = @WO_BOOLEAN_1,
    WO_BOOLEAN_2 = @WO_BOOLEAN_2,
    WO_MOD_DATE = @WO_MOD_DATE
    WHERE WO_PK = @WO_PK


    IF @@ERROR != 0 GOTO ERR_HANDLER

    COMMIT TRAN

    SET @RTN_Code = 0
    RETURN

    ERR_HANDLER:
    ROLLBACK TRAN

    SET @RTN_Code = -20001
    RETURN

    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    1. Do you want to insert data in a new table?
    2.or do you want to update data in a existing table?PLz specify that
    The red marked areas are the regions where you should do your rectifications...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  8. #8
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    if you want to update an existing data in an existing table try this..
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SWR_save_Work_Orde]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[SWR_save_Work_Orde]
    GO


    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_NULLS ON
    GO


    -----------------------------------------------------------------------------------------
    -- AUTHOR: gdawkins
    -- PROCEDURE: SWR_Save_Work_Orde
    -- DATE CREATED: 2006-04-06
    -- DATE MODIFIED:
    -- DATE REMOVED:
    -- DESCRIPTION: saves associated with f_WorkOrders.
    -- DEPENDENCIES: SET QUOTED_IDENTIFIER ON


    CREATE PROCEDURE dbo.SWR_save_Work_Orde
    -----------------------------------------------------------------------------------------
    -- AUTHOR: gdawkins
    -- PROCEDURE: SWR_save_Work_Orde
    -- DATE CREATED: 2006-04-07
    -- DATE MODIFIED:
    -- DATE REMOVED:
    -- DESCRIPTION: savetes fields associated with f_WorkOrders.
    -- DEPENDENCIES:
    -----------------------------------------------------------------------------------------

    (
    @WO_PK int, -- Work Order’s Primary Key.
    @WO_EST_START datetime, -- Estimated Start Date. Include time also in value.
    @WO_EST_COMP datetime, -- Estimated End Date. Include time also in value.
    @WO_BOOLEAN_1 smallint, -- Saturday work required. 0 = No; 1 = Yes.
    @WO_BOOLEAN_2 smallint, -- Sunday work required. 0 = No; 1 = Yes.
    @WO_MOD_DATE datetime, -- Date this record was last modified. May or may not include time.
    @RTN_Code int OUTPUT
    )

    AS

    BEGIN TRAN

    Update TE_f_WorkOrder
    SET WO_EST_START = @WO_EST_START,
    WO_EST_COMP = @WO_EST_COMP,
    WO_BOOLEAN_1 = @WO_BOOLEAN_1,
    WO_BOOLEAN_2 = @WO_BOOLEAN_2,
    WO_MOD_DATE = @WO_MOD_DATE
    WHERE WO_PK = @WO_PK

    IF @@ERROR != 0 GOTO ERR_HANDLER

    COMMIT TRAN

    SET @RTN_Code = 0
    RETURN

    ERR_HANDLER:
    ROLLBACK TRAN

    SET @RTN_Code = -20001
    RETURN

    GO

    SET QUOTED_IDENTIFIER OFF
    GO
    SET ANSI_NULLS ON
    GO
    Last edited by rudra; 04-07-06 at 13:18.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  9. #9
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Red face save or commit

    After revising the code I still get this error message.

    Server: Msg 170, Level 15, State 1, Procedure SWR_Save_Work_Orde, Line 10
    Line 10: Incorrect syntax near 'SWR_Save_Work_Orde'.
    [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
    Attached Files Attached Files

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    It looks like you may have to comment out the

    Code:
    Possible values = 1, 2, 3, 4, 5.
    1 = Routine, 
    2 = Low, 
    3 = Medium,
    4 = High, 
    5 = Emergency.
    As well as the descriptions of the other variables. I take it you inheirited this?

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Rudra: I hardly find Gary's interests at all questionable. And his statement that his T-SQL is "rusty" does imply that he is not joking. Perhaps his PL/SQL is considerably less rusty. It is good to see that you helped him out in the end, even if the welcome was...well...less than welcoming.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The whole term "DBA" is misleading, because it can refer to two distinct disciplines:
    DataBase Administrator: Proficient in installing, securing, and optimizing servers.
    DataBase Architect: Proficient in schematic design and SQL coding.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by garrydawkins
    After revising the code I still get this error message.

    Server: Msg 170, Level 15, State 1, Procedure SWR_Save_Work_Orde, Line 10
    Line 10: Incorrect syntax near 'SWR_Save_Work_Orde'.
    [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
    I didn't gave that code,it seems that you havn't gave a single look at my rectified code.Thats good...carry one
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  14. #14
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by MCrowley
    Rudra: It is good to see that you helped him out in the end, even if the welcome was...well...less than welcoming.
    I always try to be a gentleman,just like PatP....Batman...hmm Rudy.. you and the rest of great ones.I know my DBA expectation is quite high,I mean when is see somebody as DBA,I don't expect something from them that I already know. Garrydawkins is self sufficient , as you see he hasn't even used my code..
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    rudra, you are freaking people out.

    Please stop.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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