SPROC To move data from a SQL View to a SQL table, with some constants

I have to move data coming in from a SQL view to another SQL table, adding some additional, constant information, to every record

I've never used a SPROC to do this before.

This is the input view:
SELECT        dbo.SQMT_Comment_Payments_Work.ID AS SQMTID, dbo.tblProperty.PropertyRecID, dbo.tblTaxAuthority.TaxAuthorityID, 
                         dbo.SQMT_Comment_Payments_Work.Comment_Or_PayDtls AS Comment, dbo.SQMT_Comment_Payments_Work.TaxType, 
                         dbo.SQMT_Comment_Payments_Work.PaidFlag, dbo.SQMT_Comment_Payments_Work.Paid_To_County, dbo.SQMT_Comment_Payments_Work.Initials, 
                         dbo.SQMT_Comment_Payments_Work.PayYearNum
FROM            dbo.SQMT_Comment_Payments_Work LEFT OUTER JOIN
                         dbo.tblProperty ON dbo.SQMT_Comment_Payments_Work.ControlNumber = dbo.tblProperty.OldControlNumber AND 
                         dbo.SQMT_Comment_Payments_Work.MuniCode = dbo.tblProperty.Muni LEFT OUTER JOIN
                         dbo.tblTaxAuthority ON dbo.SQMT_Comment_Payments_Work.TaxType = dbo.tblTaxAuthority.TaxTypeID AND 
                         dbo.SQMT_Comment_Payments_Work.MuniCode = dbo.tblTaxAuthority.MuniCode AND 
                         dbo.SQMT_Comment_Payments_Work.ControlNumber = dbo.tblTaxAuthority.OldControlNumber
WHERE        (dbo.SQMT_Comment_Payments_Work.PayYearNum IS NULL)

Open in new window


This is the receiving table:
USE [JTSConversion]
GO

/****** Object:  Table [dbo].[tblComments]    Script Date: 1/25/2018 3:01:22 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblComments](
	[PropCommentID] [int] IDENTITY(1,1) NOT NULL,
	[PropertyRecID] [int] NULL CONSTRAINT [DF__tblProper__Prope__3B4BBA2E]  DEFAULT ((0)),
	[TaxAuthorityID] [int] NULL,
	[TaxTypeID] [int] NULL,
	[CommentTypeID] [int] NULL CONSTRAINT [DF__tblProper__Comme__3C3FDE67]  DEFAULT ((0)),
	[Comment] [nvarchar](max) NULL,
	[DateAdded] [datetime] NULL,
	[UserAdded] [nvarchar](100) NULL,
	[DateRevised] [datetime] NULL,
	[UserRevised] [nvarchar](100) NULL,
	[SSMA_TimeStamp] [timestamp] NOT NULL,
 CONSTRAINT [tblProperty_Comment$PrimaryKey] PRIMARY KEY CLUSTERED 
(
	[PropCommentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window


This is the logic I want to replace, it is from an Access 2013 program.  It works but is taking way tooo long to process.
                     rsOut.AddNew
                     '
                     rsOut![PropertyRecID] = Nz(rsIn2![PropertyRecID])
                     rsOut![TaxAuthorityID] = Nz(rsIn2![TaxAuthorityID])
                     rsOut![TaxTypeID] = Nz(rsIn2![TaxType])
                     rsOut![CommentTypeID] = eCommentType.eGeneral
                     rsOut![Comment] = Nz(rsIn2![Comment])
                     '
                     rsOut![DateAdded] = wkDate
                     rsOut![UserAdded] = Nz(rsIn2![Initials]) & "-" & wkUser
                     '
                     rsOut.Update

Open in new window

How could this be done in a SPROC?
LVL 1
mlcktmguyAsked:
Who is Participating?
 
ste5anConnect With a Mentor Senior DeveloperCommented:
E.g.

CREATE PROCEDURE p_Insert
(
    @CommentTypeID INT,
    @WkDate DATE,
    @WkUser NVARCHAR(100) -- length must be 100-1-maxLength(initials)
)
AS
    SET NOCOUNT ON;

    INSERT INTO dbo.tblComments ( PropertyRecID, TaxAuthorityID, TaxTypeID, CommentTypeID, Comment, DateAdded, UserAdded )
        SELECT P.PropertyRecID,
               TA.TaxAuthorityID,
               W.TaxType,
               @CommentTypeID,
               W.Comment_Or_PayDtls,
               @WkDate,
               W.Initials + '-' + @WkUser
        FROM dbo.SQMT_Comment_Payments_Work W
            LEFT JOIN dbo.tblProperty P ON W.ControlNumber = P.OldControlNumber
                                            AND W.MuniCode = P.Muni
            LEFT OUTER JOIN dbo.tblTaxAuthority TA ON W.TaxType = TA.TaxTypeID
                                                        AND W.MuniCode = TA.MuniCode
                                                        AND W.ControlNumber = TA.OldControlNumber
        WHERE W.PayYearNum IS NULL;

Open in new window


btw, use table alias names to increase readbilty..

p.s. why not using default values for DateAdded and UserAdded?
0
 
mlcktmguyAuthor Commented:
Wow, an incredible time savings.  Thanks Ste5an.

You are correct, There is no need to pass parameters.  @CommentType always is equal to 1

and I decalred @wkUser as a variable and set it to 'Convert'

I still want to stamp every record with the data and time added but it can be the same date and time for all of these records.

I changed the SPROC as below to declare wkDate as a variable.  How do I set wkDate to the current date and time in the SPROC?

USE [JTSConversion]
GO
/****** Object:  StoredProcedure [dbo].[aConvertSPPropertyComments]    Script Date: 1/25/2018 8:35:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- 
-- =============================================
ALTER PROCEDURE [dbo].[aConvertSPPropertyComments]
   
AS
BEGIN
    SET NOCOUNT ON;

	declare     @WkDate DATE
	declare     @wkUser  nvarChar(10)
	set @wkUser='Convert'
--
-- Clear the COmment Table
--
  Execute dbo.spDeleteAllRecsFromSQLTable 'tblComments'
--
--  Now load it
--
    INSERT INTO dbo.tblComments ( PropertyRecID, TaxAuthorityID, TaxTypeID, CommentTypeID, Comment, DateAdded, UserAdded )
        SELECT P.PropertyRecID,
               TA.TaxAuthorityID,
               W.TaxType,
               1,
               W.Comment_Or_PayDtls,
               @WkDate,
               W.Initials + '-' + @WkUser
        FROM dbo.SQMT_Comment_Payments_Work W
            LEFT JOIN dbo.tblProperty P ON W.ControlNumber = P.OldControlNumber
                                            AND W.MuniCode = P.Muni
            LEFT OUTER JOIN dbo.tblTaxAuthority TA ON W.TaxType = TA.TaxTypeID
                                                        AND W.MuniCode = TA.MuniCode
                                                        AND W.ControlNumber = TA.OldControlNumber
        WHERE W.PayYearNum IS NULL;


END

Open in new window

0
 
mlcktmguyAuthor Commented:
Huge Improvement, thanks.
0
 
ste5anSenior DeveloperCommented:
Use GETDATE() for the date. And you may take a look at SUSER_SNAME(). E.g.

ALTER PROCEDURE [dbo].[aConvertSPPropertyComments]
AS
    SET NOCOUNT ON;

    EXECUTE dbo.spDeleteAllRecsFromSQLTable 'tblComments';

    INSERT INTO dbo.tblComments ( PropertyRecID ,
                                  TaxAuthorityID ,
                                  TaxTypeID ,
                                  CommentTypeID ,
                                  Comment ,
                                  DateAdded ,
                                  UserAdded )
                SELECT P.PropertyRecID ,
                       TA.TaxAuthorityID ,
                       W.TaxType ,
                       1 ,
                       W.Comment_Or_PayDtls ,
                       GETDATE() ,
                       W.Initials + '-' + 'Convert (' + SUSER_SNAME() + ')'
                FROM   dbo.SQMT_Comment_Payments_Work W
                       LEFT JOIN dbo.tblProperty P ON W.ControlNumber = P.OldControlNumber
                                                      AND W.MuniCode = P.Muni
                       LEFT OUTER JOIN dbo.tblTaxAuthority TA ON W.TaxType = TA.TaxTypeID
                                                                 AND W.MuniCode = TA.MuniCode
                                                                 AND W.ControlNumber = TA.OldControlNumber
                WHERE  W.PayYearNum IS NULL;

Open in new window


No need for variables, the functions are only evaluated once, cause they don't depend on the row by a parameter.
0
 
mlcktmguyAuthor Commented:
Thanks for the follow up, much appreciated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.