January 29, 2016 at 1:19 pm
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
January 29, 2016 at 1:32 pm
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
January 29, 2016 at 1:38 pm
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.
January 29, 2016 at 1:38 pm
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.
January 29, 2016 at 1:38 pm
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
January 29, 2016 at 1:51 pm
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
,@ProcessDate
,@USERID
,@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
,@ProcessDate
,@USERID
,@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
January 29, 2016 at 2:04 pm
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?
January 29, 2016 at 2:14 pm
100025730543909587546810958754681A
100025730543939587546810958754681B
100025730543999587546810958754681C
January 29, 2016 at 2:45 pm
suryahanuma (1/29/2016)
100025730543909587546810958754681A100025730543939587546810958754681B
100025730543999587546810958754681C
And what happens when you get to Z and need more?
January 29, 2016 at 2:53 pm
We don't get to Z. At the most we can reach E or F.
January 29, 2016 at 2:58 pm
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
,@ProcessDate
,@USERID
,@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
January 29, 2016 at 3:17 pm
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.
January 29, 2016 at 3:23 pm
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.
January 29, 2016 at 7:12 pm
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
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply