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