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

James Hackney 61 Reputation points
2021-04-06T13:23:59.797+00:00

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
Azure SQL Database
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2021-04-06T21:59:29.037+00:00

    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. EchoLiu-MSFT 14,571 Reputation points
    2021-04-07T06:28:13.417+00:00

    Hi @James Hackney ,

    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.

    0 comments No comments