Can we delay the execution?Or ReWrite this Query?

Question

Can we delay the execution?Or ReWrite this Query?

suryahanuma

Valued Member

Points: 51
More actions
January 29, 2016 at 1:19 pm

#315454

I have an insert statement like this -
Insert into TableA (Col1,Col2,Col3)
SELECT variable1,function(),variable3 FROM TABLEB
Is it in anyway possible to write this query so that the SELECT statement executes only after a record is inserted in the tableA.
In short - I need a very very very small delay in inserting the records because I want the function which is in the select statement to work on the updated tableA after each record is inserted.
Looking forward for somehelp

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

michael.welcome Right there with Babe Points: 795 More actions · Answer 1

As a general rule I don't like putting business logic in triggers if there is any other reasonable way to accomplish the result. If the logic in your function depends on values inserted into table a you could put the logic in an insert trigger.

If the table has a primary key identity column you could also use an insert / select query and update the table with the result of the select

suryahanuma Valued Member Points: 51 More actions · Answer 2

No. It is a part in the stored procedure. I cannot put triggers.

Basically I could have used identity but there isn't an identity column. So it boils down to either using a loop or inserting each record sequentially. Though I like to process them in bulk, the business rules require the function to be executed only after the previous record is inserted.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Do you mean inserting one row, then waiting a moment before inserting a new row, then insert the following row all in the same statement?

If so, then it's not possible. Either you put the logic depending on the SELECT or you need to code the insert as RBAR (Row-By-Agonizing-Row).

Sharing more details might help us to give you better ideas.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

suryahanuma (1/29/2016)
In short - I need a very very very small delay in inserting the records because I want the function which is in the select statement to work on the updated tableA after each record is inserted.

An "after-insert trigger" will do this for you but something in your wording is telling me to advise you that triggers work on all rows inserted into the table as a set, i.e. the trigger will only fire once per insert statement issued not once per row inserted by each statement.

For example, if I have a TableA with an after-insert trigger on it and I issue something like this:

INSERT INTO dbo.TableA (ColumnA, ColumnB)

SELECT Something, SomethingElse

FROM dbo.TableX;

and that means 20 rows will be inserted into dbo.TableA the after-insert trigger on dbo.TableA will only fire once and it will be up to you to code the trigger appropriately to handle all 20 inserted rows and get them into dbo.TableB as you described.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

suryahanuma Valued Member Points: 51 More actions · Answer 5

So this is the problem - When there is a change in the EdgNumber I need to increment the alpha identifier.. it works well when there is a record already in the db. But when I send multiple edgnumbers at once it is returning the same result

This is the procedure-

CREATE PROCEDURE [Interface].[uspGenerateOTISCaseNumbers]

(

@inputCaseNumbers [Interface].[udttOTISCaseNumber] READONLY,

@RunId varchar(100),

@ProcessDate DATETIME

)

AS

BEGIN

BEGIN TRY

DECLARE @userid VARCHAR(30) ='BATCH FRAMEWORK'

CREATE TABLE #tmpOtisCaseNumber

(

[CaseNumber] bigint,

EdgNumber biginT,

[SSN] bigint,

[OTISCaseNumber] varchar(20)

)

--INSERT INTO #tmpOtisCaseNumber([CaseNumber],EdgNumber,SSN)

--SELECT [CaseNumber],EdgNumber,SSNHeadOfHousehold from [Interface].[OTISCaseNumberMapping]

INSERT INTO #tmpOtisCaseNumber ([CaseNumber], [EdgNumber], [SSN])

SELECT

[CaseNumber]

,[EdgNumber]

,[SSN]

FROM @inputCaseNumbers

UPDATE B

SET B.[OTISCaseNumber] = A.OTISCaseNumber

FROM [Interface].[OTISCaseNumberMapping] A, #tmpOtisCaseNumber B

WHERE A.CaseNumber = B.CaseNumber

AND A.SSNHeadOfHousehold = B.SSN

AND A.EdgNumber = B.EdgNumber

IF EXISTS (SELECT

1

FROM #tmpOtisCaseNumber A

INNER JOIN [Interface].[OTISCaseNumberMapping] B

ON A.CaseNumber = B.CaseNumber

AND A.SSN = B.SSNHeadOfHousehold

AND A.OTISCaseNumber IS NULL) BEGIN

CREATE TABLE #tmpOtisCaseNumber2(Rownumber BIGINT,

Casenumber BIGINT,

[SSN] BIGINT,

EdgeNumber BIGINT,

Alpha VARCHAR(1))

INSERT INTO #tmpOtisCaseNumber2 (Rownumber, Casenumber, ssn, EdgeNumber)

SELECT

ROW_NUMBER() OVER (PARTITION BY X.CaseNumber ORDER BY X.CaseNumber) AS Row

,X.CaseNumber

,X.ssn

,X.EdgNumber

FROM (SELECT DISTINCT

A.CaseNumber

,A.EdgNumber

,SSN

FROM #tmpOtisCaseNumber A

INNER JOIN [Interface].[OTISCaseNumberMapping] B

ON A.CaseNumber = B.CaseNumber

AND A.SSN = B.SSNHeadOfHousehold

AND A.OTISCaseNumber IS NULL) X

ORDER BY X.CaseNumber

UPDATE #tmpOtisCaseNumber2

SET Alpha = [Interface].[udfGetLastUsedAlphaIdentifier](Casenumber, SSN, Rownumber)

INSERT INTO [Interface].[OTISCaseNumberMapping] (CaseNumber, SSNHeadOfHousehold, EdgNumber, AlphaIdentifier, OTISCaseNumber, RunId, CreateDate, UpdateDate, CreateUserId, UpdateUserId)

SELECT

Casenumber

,SSN

,EdgeNumber

,Alpha

,'0' + CAST(SSN AS VARCHAR) + Alpha

,@RunId

,@ProcessDate

,@USERID

FROM #tmpOtisCaseNumber2

UPDATE B

SET B.[OTISCaseNumber] = A.OTISCaseNumber

FROM [Interface].[OTISCaseNumberMapping] A, #tmpOtisCaseNumber B

WHERE A.CaseNumber = B.CaseNumber

AND A.SSNHeadOfHousehold = B.SSN

AND A.EdgNumber = B.EdgNumber

END

CREATE TABLE #tmpOtisCaseNumber3(Rownumber BIGINT,

Casenumber BIGINT,

[SSN] BIGINT,

EdgeNumber BIGINT,

Alpha VARCHAR(1))

INSERT INTO #tmpOtisCaseNumber3 (Rownumber, Casenumber, ssn, EdgeNumber)

SELECT

ROW_NUMBER() OVER (PARTITION BY X.CaseNumber ORDER BY X.CaseNumber) AS Row

,X.CaseNumber

,X.ssn

,X.EdgNumber

FROM (SELECT DISTINCT

A.CaseNumber

,A.EdgNumber

,SSN

FROM #tmpOtisCaseNumber A

LEFT JOIN [Interface].[OTISCaseNumberMapping] B

ON A.CaseNumber = B.CaseNumber

AND A.SSN = B.SSNHeadOfHousehold

WHERE B.CaseNumber IS NULL

AND B.SSNHeadOfHousehold IS NULL

AND A.OTISCaseNumber IS NULL) X

ORDER BY X.CaseNumber

--UPDATE #tmpOtisCaseNumber3

--SET Alpha = [Interface].[udfGetLastUsedAlphaIdentifier](Casenumber, SSN, Rownumber)

INSERT INTO [Interface].[OTISCaseNumberMapping] (CaseNumber, SSNHeadOfHousehold, EdgNumber, AlphaIdentifier, OTISCaseNumber, RunId, CreateDate, UpdateDate, CreateUserId, UpdateUserId)

SELECT

Casenumber

,SSN

,EdgeNumber

,[Interface].[udfGetLastUsedAlphaIdentifier](Casenumber, SSN, Rownumber)

,'0' + CAST(SSN AS VARCHAR) + [Interface].[udfGetLastUsedAlphaIdentifier](Casenumber, SSN, Rownumber)

,@RunId

,@ProcessDate

,@USERID

FROM #tmpOtisCaseNumber3

UPDATE B

SET B.[OTISCaseNumber] = A.OTISCaseNumber

FROM [Interface].[OTISCaseNumberMapping] A, #tmpOtisCaseNumber B

WHERE A.CaseNumber = B.CaseNumber

AND A.SSNHeadOfHousehold = B.SSN

AND A.EdgNumber = B.EdgNumber

--select * from [Interface].[OTISCaseNumberMapping] order by CaseNumber,AlphaIdentifier

SELECT

*

FROM #tmpOtisCaseNumber

ORDER BY CaseNumber, OTISCaseNumber

END TRY BEGIN CATCH

-- Error Handling Code

DECLARE @error_seq INT

DECLARE @ErrorNumber INT

DECLARE @ErrorSeverity INT

DECLARE @ErrorState INT

DECLARE @ErrorProcedure NVARCHAR(126)

DECLARE @ErrorLine INT

DECLARE @ErrorMessage NVARCHAR(4000)

SELECT

@ErrorNumber = ERROR_NUMBER()

,@ErrorSeverity = ERROR_SEVERITY()

,@ErrorState = ERROR_STATE()

,@ErrorProcedure = ERROR_PROCEDURE()

,@ErrorLine = ERROR_LINE()

,@ErrorMessage = ERROR_MESSAGE();

RAISERROR ('Error #: %d in %s . Message:%s', @ErrorSeverity, @ErrorState, @error_seq,

@ErrorProcedure, @ErrorMessage)

END CATCH

END

This is my function

ALTER FUNCTION [Interface].[udfGetLastUsedAlphaIdentifier]

(

@CaseNumber BigInt,

@SSN VARCHAR(20),

@Increment INT

)

RETURNS CHAR

AS

BEGIN

DECLARE @AlphaIdentifier CHAR(5)

SELECT TOP(1) @AlphaIdentifier = CHAR(ascii(MAX(AlphaIdentifier))+@Increment)

FROM [Interface].[OTISCaseNumberMapping]

WHERE CaseNumber = @CaseNumber AND SSNHeadOfHousehold = @SSN

RETURN ISNULL(@AlphaIdentifier,'A')

END

When I use this test data this is the result-

CaseNumberEdgNumberSSNOTISCaseNumber

100025730543909587546810958754681A

100025730543939587546810958754681A

100025730543999587546810958754681A

Test result--- I am expecting when the table [Interface].[OTISCaseNumberMapping] is empty

100025730543909587546810958754681A

100025730543939587546810958754681B

100025730543999587546810958754681C

test script---

BEGIN TRANSACTION

CREATE TABLE #tmpTbl1

(Casenumber int,

Edgnumber int,

ssn varchar(100))

INSERT INTO #tmpTbl1

VALUES (100025730, 54390, '958754681')

INSERT INTO #tmpTbl1

VALUES (100025730, 54393, '958754681');

INSERT INTO #tmpTbl1

VALUES (100025730, 54399, '958754681');

DECLARE @inputcasenumbers [Interface].[udttOTISCaseNumber]

INSERT INTO @INPUTCASENUMBERS (CaseNumber, SSN, EdgNumber)

SELECT

Casenumber

,SSN

,Edgnumber

FROM #tmpTbl1

EXEC [Interface].[uspGenerateOTISCaseNumbers]@inputcasenumbers

,'TESTRUNID'

,'01-30-2016'

ROLLBACK

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

CREATE TABLE #tmpTbl1

(Casenumber int,

Edgnumber int,

ssn varchar(100))

INSERT INTO #tmpTbl1

VALUES (100025730, 54390, '958754681')

INSERT INTO #tmpTbl1

VALUES (100025730, 54393, '958754681');

INSERT INTO #tmpTbl1

VALUES (100025730, 54399, '958754681');

DECLARE @inputcasenumbers [Interface].[udttOTISCaseNumber]

INSERT INTO @INPUTCASENUMBERS (CaseNumber, SSN, EdgNumber)

SELECT

Casenumber

,SSN

,Edgnumber

FROM #tmpTbl1

EXEC [Interface].[uspGenerateOTISCaseNumbers] @inputcasenumbers

,'TESTRUNID'

,'01-30-2016'

ROLLBACK

Based on the test script above, what should the the expected result look like?

suryahanuma Valued Member Points: 51 More actions · Answer 7

100025730543909587546810958754681A

100025730543939587546810958754681B

100025730543999587546810958754681C

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

suryahanuma (1/29/2016)
100025730543909587546810958754681A
100025730543939587546810958754681B
100025730543999587546810958754681C

And what happens when you get to Z and need more?

suryahanuma Valued Member Points: 51 More actions · Answer 9

suryahanuma

Valued Member

Points: 51

January 29, 2016 at 2:53 pm

#1855409

We don't get to Z. At the most we can reach E or F.

suryahanuma Valued Member Points: 51 More actions · Answer 10

One way I made it work is like this--

Right at the end just before I insert into the main db table, I added an identity column to the table #tmpOtisCaseNumber3 and use that identity in a while loop. This works fine for small number of records but if I get huge data,I know I am in trouble. I am really hoping to get a better solution than this.

CREATE TABLE #tmpOtisCaseNumber3(Rownumber BIGINT,

Casenumber BIGINT,

[SSN] BIGINT,

EdgeNumber BIGINT,

Alpha VARCHAR(1),id Int IDENTITY(1,1))

INSERT INTO #tmpOtisCaseNumber3 (Rownumber, Casenumber, ssn, EdgeNumber)

SELECT

ROW_NUMBER() OVER (PARTITION BY X.CaseNumber ORDER BY X.CaseNumber) AS Row

,X.CaseNumber

,X.ssn

,X.EdgNumber

FROM (SELECT DISTINCT

A.CaseNumber

,A.EdgNumber

,SSN

FROM #tmpOtisCaseNumber A

LEFT JOIN [Interface].[OTISCaseNumberMapping] B

ON A.CaseNumber = B.CaseNumber

AND A.SSN = B.SSNHeadOfHousehold

WHERE B.CaseNumber IS NULL

AND B.SSNHeadOfHousehold IS NULL

AND A.OTISCaseNumber IS NULL) X

ORDER BY X.CaseNumber

--UPDATE #tmpOtisCaseNumber3

--SET Alpha = [Interface].[udfGetLastUsedAlphaIdentifier](Casenumber, SSN, Rownumber)

DECLARE @cnt INT = 1

WHILE (@cnt <= (SELECT

COUNT(*)

FROM #tmpOtisCaseNumber3))

BEGIN

INSERT INTO [Interface].[OTISCaseNumberMapping] (CaseNumber, SSNHeadOfHousehold, EdgNumber, AlphaIdentifier, OTISCaseNumber, RunId, CreateDate, UpdateDate, CreateUserId, UpdateUserId)

SELECT

Casenumber

,SSN

,EdgeNumber

,[Interface].[udfGetLastUsedAlphaIdentifier](Casenumber, SSN, 1)

,'0' + CAST(SSN AS VARCHAR) + [Interface].[udfGetLastUsedAlphaIdentifier](Casenumber, SSN, 1)

,@RunId

,@ProcessDate

,@USERID

FROM #tmpOtisCaseNumber3 where id = @cnt

SELECT * FROM [Interface].[OTISCaseNumberMapping]

SET @CNT = @CNT+1

END

UPDATE B

SET B.[OTISCaseNumber] = A.OTISCaseNumber

FROM [Interface].[OTISCaseNumberMapping] A, #tmpOtisCaseNumber B

WHERE A.CaseNumber = B.CaseNumber

AND A.SSNHeadOfHousehold = B.SSN

AND A.EdgNumber = B.EdgNumber

--select * from [Interface].[OTISCaseNumberMapping] order by CaseNumber,AlphaIdentifier

SELECT

*

FROM #tmpOtisCaseNumber

ORDER BY CaseNumber, OTISCaseNumber

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

suryahanuma (1/29/2016)
We don't get to Z. At the most we can reach E or F.

There is a word about saying never, never say it. You should always consider limitations like this as one day it may happen and you code break.

suryahanuma Valued Member Points: 51 More actions · Answer 12

I am 100% sure it would not go beyond C usually,with 3 exemptions if all are utilized it will go F. SO we do not have to think until Z.

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 13

suryahanuma (1/29/2016)
I am 100% sure it would not go beyond C usually,with 3 exemptions if all are utilized it will go F. SO we do not have to think until Z.

The real problem is that the data is denormalized. The letter should be stored in a different column so that you'll be able to do other analysis you don't yet know about without having to write slow moving totally non-SARGable queries.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)