question

JamesHackney-9611 avatar image
0 Votes"
JamesHackney-9611 asked EchoLiu-msft commented

How to insert parsed and non-parsed data from one table into another table using AFTER INSERT trigger

I want to take the data from 3 columns of one table and input them into another table via an AFTER INSERT Trigger.

One of the columns is a VARCHAR datatype that consists of a large string of text (a csv file). I can parse this data if I take a record from the table and either put it in a variable or paste it directly in the string_split() function but have been unable to figure out how to take the last record and add it to the substring.

I also cannot figure out how to insert the two columns and the parsed data into the second table

Here is what I have so far. I know the way I presently trying to pull the data form the VARCHAR column is incorrect. It's the last thing I tried.

 Create TRIGGER [SDCS].trg_ParseAndInsertIntoBmaFormDataTable
 ON [SDCS].[SDCS_PRIME]
 AFTER INSERT
 AS
 BEGIN
        
                 SET NOCOUNT ON;                                                          
        
                 DECLARE @stringRecord VARCHAR(MAX) = [SDCS].SDCS_PRIME.PRIME_INFO;
        
                 WITH cte as (SELECT value, RowID = ROW_NUMBER() over(order by (select null))
                                                                  FROM STRING_SPLIT(@stringRecord, '^'))
        
                 SELECT  FEIN = max(case when RowID = 1 then value end)
                                                 ,BUSINESS_NAME = max(case when RowID = 2 then value end)
                                                 ,DOING_BUSINESS_AS= max(case when RowID = 3 then value end)
                                                 ,ADDRESS = max(case when RowID = 4 then value end)
                                                 ,ADDITIONAL_ADDRESS = max(case when RowID = 5 then value end)
                                                 ,CITY = max(case when RowID = 6 then value end)
                                                 ,STATE = max(case when RowID = 7 then value end)
                                                 ,ZIP_CODE  = max(case when RowID = 8 then value end)
                                                 ,BUSINESS_WEBSITE = max(case when RowID = 9 then value end)
                                                 ,EXECUTIVE_CONTACT_NAME = max(case when RowID = 10 then value end)
                                                 ,EXECUTIVE_CONTACT_TITLE = max(case when RowID = 11 then value end)
                                                 ,EXECUTIVE_CONTACT_PHONE = CAST(max(case when RowID = 12 then value end) AS INT)
                                                 ,EXECUTIVE_CONTACT_EMAIL = max(case when RowID = 13 then value end)
                                                 ,FIN_CONTACT_EXEC_CONTACT = max(case when RowID = 14 then value end)
                                                 ,FIN_CONTACT_EXEC_NAME = max(case when RowID = 15 then value end)
                                                 ,FIN_CONTACT_EXEC_TITLE = max(case when RowID = 16 then value end)
                                                 ,FIN_CONTACT_EXEC_PHONE = CAST(max(case when RowID = 17 then value end) AS INT)
                                                 ,FIN_CONTACT_EXEC_EMAIL = max(case when RowID = 18 then value end)
                                                 ,MAJOR_NON_PROFIT_ORG = max(case when RowID = 19 then value end)
                                                 ,BUSINESS_ENTITY_TYPE = max(case when RowID = 20 then value end)
                                                 ,BOARD_MEETING_LAST_YEAR = max(case when RowID = 21 then value end)
                                                 ,BOARD_MAINTAIN_MINUTES = max(case when RowID = 22 then value end)                        
                                                 ,AUDIT_TYPE = max(case when RowID = 23 then value end)          
                                                 ,TIMELY_ACCURATE_TRANSACTIONS = max(case when RowID = 24 then value end)           
                                                 ,TRACK_CFDA_TITLE_NUMBER = max(case when RowID = 25 then value end)       
                                                 ,TRACK_FAIN = max(case when RowID = 26 then value end)          
                                                 ,TRACK_ORIGINAL_FUNDING_AGENCY = max(case when RowID = 27 then value end)       
                                                 ,PASS_THROUGH_ENTITY = max(case when RowID = 28 then value end) 
                                                 ,PERSONNEL = max(case when RowID = 29 then value end)           
                                                 ,ACCOUNTING_TRANSACTIONS = max(case when RowID = 30 then value end)      
                                                 ,ALLOWABILITY_OF_COSTS = max(case when RowID = 31 then value end)              
                                                 ,PROPERTY_EQUIPMENT = max(case when RowID = 32 then value end)
                                                 ,RECORDS_RETENTION = max(case when RowID = 33 then value end)
                                                 ,PURCHASING_PROCUREMENT = max(case when RowID = 34 then value end)
                                                 ,FINANCIAL_SEGREGATION_DUTIES = max(case when RowID = 35 then value end)
                                                 ,CONFLICTS_OF_INTEREST = max(case when RowID = 36 then value end)
                                                 ,NO_SOLICIT_ACCEPT_GOODIES = max(case when RowID = 37 then value end)
                                                 ,DISCIPLINARY_ACTIONS = max(case when RowID = 38 then value end)
                                                 ,CHANGE_OF_MANAGEMENT = max(case when RowID = 39 then value end)
                                                 ,NEW_MANAGEMENT_SYSTEM = max(case when RowID = 40 then value end)                      
                                                 ,CONGRESSIONAL_DISTRICT = max(case when RowID = 41 then value end)
                                                 ,DUNS_NUMBER = max(case when RowID = 42 then value end)
                                                 ,PARENT_DUNS_NUMBER = max(case when RowID = 43 then value end)
                                                 ,RCV_25M_OR_MORE_FUNDING = max(case when RowID = 44 then value end)
                                                 ,RCV_80_PERCENT_GRANT_MONEY = max(case when RowID = 45 then value end)
                                                 ,PUBLIC_ACCESS_COMPENSATION = max(case when RowID = 46 then value end)
                                                 ,NAME_1 = max(case when RowID = 47 then value end)
                                                 ,NAME_1_AMOUNT = max(case when RowID = 48 then value end)
                                                 ,NAME_2 = max(case when RowID = 49 then value end)
                                                 ,NAME_2_AMOUNT = max(case when RowID = 50 then value end)
                                                 ,NAME_3 = max(case when RowID = 51 then value end)
                                                 ,NAME_3_AMOUNT = max(case when RowID = 52 then value end)
                                                 ,NAME_4 = max(case when RowID = 53 then value end)
                                                 ,NAME_4_AMOUNT = max(case when RowID = 54 then value end)
                                                 ,NAME_5 = max(case when RowID = 55 then value end)
                                                 ,NAME_5_AMOUNT = max(case when RowID = 56 then value end)
                                                 ,OTHER_CONTACT_NAME = max(case when RowID = 57 then value end)
                                                 ,OTHER_CONTACT_TITLE = max(case when RowID = 58 then value end)
                                                 ,OTHER_CONTACT_PHONE = max(case when RowID = 59 then value end)
                                                 ,OTHER_CONTACT_EMAIL = max(case when RowID = 60 then value end)                
                                                 ,SECONDARY_OTHER_CONTACT_NAME = max(case when RowID = 61 then value end)     
                                                 ,SECONDARY_OTHER_CONTACT_TITLE = max(case when RowID = 62 then value end)        
                                                 ,SECONDARY_OTHER_CONTACT_PHONE = CAST(max(case when RowID = 63 then value end) AS INT)
                                                 ,SECONDARY_OTHER_CONTACT_EMAIL = max(case when RowID = 64 then value end)     
                                                 ,SUBMITTER_NAME = max(case when RowID = 65 then value end)            
                                                 ,SUBMITTER_TITLE = max(case when RowID = 66 then value end)               
                                                 ,SUBMITTER_PHONE = CAST(max(case when RowID = 67 then value end) AS INT) 
                                                 ,SUBMITTER_EMAIL = max(case when RowID = 68 then value end)            
                                                 ,SDCS_PRIME_ENTRY_DATETIME = CAST(max(case when RowID = 69 then value end) AS datetime2)         
                                                 ,ENTRY_DATETIME = CAST(max(case when RowID = 70 then value end)   AS datetime2)
                                                 ,ENTRY_USERID = max(case when RowID = 71 then value end)     
                                                 ,LAST_CHANGED_DATETIME = CAST(max(case when RowID = 72 then value end) AS datetime2)         
                                                 ,LAST_CHANGED_USERID = max(case when RowID = 73 then value end)  
                 FROM cte
        
     END
sql-server-transact-sqlazure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have any update?

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

Didn't I tried to stop you the other day? :-)

There are several problems in that trigger.

First, to get the inserted data for an INSERT statement, you should query the virtual table inserted. This is also good for getting the update rows in an UPDATE statement.

Next, you need to consider that many rows can be inserted at the same time, so you cannot read things into variables.

string_split does not return any list position, and your workaround with row_number is not guaranteed to work. On my web site you find the article Arrays and Lists in SQL Server, where you find functions that return the list position, and you also find examples of how deal with list values in a table.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ErlandSommarskog,

Didn't I tried to stop you the other day? :-)

Yes you did and if I had a choice I would not be doing it believe me. Management wants no changes to the app code except what is needed to connect to the new databases. I have to replicate in Azure SQL Server what is presently being done in Oracle. As a temporary measure I wanted to create an Azure function to handle the parsing but they won't allow that either. This is the plan that management has come up with so unfortunately that's what I am stuck with :(

I will definitely check out your web page. Thank you.

0 Votes 0 ·

Hm, if you show management the trigger code you have now, they may change their mind when they see how ugly it gets...

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @JamesHackney-9611,

Your trigger looks complicated, please provide a minimal example for testing. In addition, if you want to get data from the original table and insert it into another table, you can use a temporary table.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.