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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.