question

natongm avatar image
natongm asked

Common Table Expression

I am using @Oleg's fnsplitstring function, and I am looking for a way to insert the resultset into a table on a different database
cte
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@natongm Actually, if you can elaborate on your requirements a bit, I can probably come up with a decent solution including a test of Jeff Moden's and my split functions. Jeff had shown that his function gets the cake in most cases when compared with the split using xml. The latter still makes sense with rather big string values to split, and I am still planning to at least attempt to improve it, so it is not dead yet.
1 Like 1 ·
@natongm Is it a database on the different server?
0 Likes 0 ·
Thank you @Oleg The table manageSubscriptions is on the database DMReports which is on the same server as the Reporting Server. So, I use the fnsplit funtion to split the data in LastStatus column in Reporting server on the subscription table I am looking for two solutions: 1. the first split includes "Mail sent to jeff.thomas@datasol.com" in the resultset. I want to remove the "mail sent to" part of it. The rest of the split columns are fine 2. My ultimate goal is to store all the split columns (basically email address)into a single column (LastStatus)on the ManageSubscriptions table ManageSubscriptions Table has a SubscriptionID, LastStatus, Name, ModifiedDate, and LastRunTime
0 Likes 0 ·
@natongm In this case, this might actually be a parsing of the email addresses from the LastStatus column of the Subscription table in report server database. Please let me know if I understand it incorrectly, but it looks like to need to examine the LastStatus column value in Subscription table, extract the email address out of it and store this address in the LastStatus column of the ManageSubscriptions table in DMReports database. If my understanding is correct then the question is: do all LastStatus values have email address as a part of the value, and if some records don't then what do you want to do with those?
0 Likes 0 ·
@Oleg What I tried doing was insert all the data from the LastStatus Column from the Subscription table into the LastStatus column on the ManageSubscription table, but it stores string values (email addresses)separated by semi-colons. And email addresses are not the only data in the LastStatus column. I don't want to include anything other than email addresses
0 Likes 0 ·
Show more comments

1 Answer

·
Oleg avatar image
Oleg answered
I hope that I understand requirements correctly. The values of LastStatus column of the Subscriptions table in report server DB may have some email addresses separated by semicolon if there are multiple addresses. It is also possible that there are no email addresses in the value at all. In this case, we are not interested in these subscription records. While it is possible to use PATINDEX to try to extract all email addresses from the given string, it is not a trivial task. Instead, I will use the following technique: each LastStatus value will have all semicolons replaced with single space and then all spaces are replaced with ` `. This way, by adding ` ` to the head and `<\r>` to the tail, you can get each LastStatus value to be split into nodes named **r** where each node will have a value equal to the value of a single "word" in it. Then it will be possible to use cross apply to first segregate the words into records and then pick only those which look somewhat like email address. I will not use the actual Subscriptions table of the report server, but will use the table variable instead. Form the mockup data, expected results are as follows: - bozo.theclown@wringley.com is extracted from the first record - a@b.com and z@x.co are extracted from the second (@Oleg is disregarded because the email address is not valid - nothing is extracted from the third record - justemail@somewhere.com is extracted from the last record (the whole thing)
-- mockup test data
declare @subscriptions table
(
    SubscriptionID uniqueidentifier not null, 
    LastStatus nvarchar(260) null
);

-- insert test records
insert into @subscriptions
select newid(), 'bozo.theclown@wringley.com looking for more info';

insert into @subscriptions
select newid(), 'send email to @Oleg;a@b.com;z@x.co; please';

insert into @subscriptions
select newid(), null;

insert into @subscriptions
select newid(), 'justemail@somewhere.com';

-- here is the final select (uncomment commented line and modify as needed)
with records (SubscriptionID, LastStatus) as
(
    select 
        SubscriptionID, 
        cast('
    
     ' +  replace(replace(LastStatus, ';', ' '), ' ', '
    
    
     ') + 
        '
    ' as xml) LastStatus
        from @subscriptions
)
    --insert into some_db..some_schema.some_table (some_column_list)
    select
        records.SubscriptionID, x.LastStatus
        from records cross apply
        (
            select 
                item.value('text()[1]', 'varchar(50)') LastStatus
                from records.LastStatus.nodes('//r') R(item)
        ) x
        where
            x.LastStatus like '_%@_%._%';
    
-- The above returns something like this for data above:
SubscriptionID                       LastStatus
------------------------------------ --------------------------
686F0498-2BD6-4032-84A9-53AFCC60DF8C bozo.theclown@wringley.com
673DD0B4-1F41-4417-9709-79863BB32089 a@b.com
673DD0B4-1F41-4417-9709-79863BB32089 z@x.co
2DE37EA7-5243-4F48-B351-BB424B40A99F justemail@somewhere.com
Oleg
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

What is the deal with the code formatting today? It went totally bisserk on me, so I had to revert to the manual typing of pre and code tags.
0 Likes 0 ·
Thanks @Oleg. I am looking to populate SubscriptionID, LastStatus, ReportName, ModifiedDate, and LastRunTime in TBL_ManageSubscriptions table. I ran the script, and I am getting error: 'records' has more columns than were specified in the column list. See what is wrong with what I am doing. The ReportName is from the Catalog table with records (SubscriptionID, LastStatus, ReportName, ModifiedDate, LastRunTime) as ( select SubscriptionID, LastStatus, cast(' ' + replace(replace(LastStatus, ';', ' '), ' ', ' ') + ' ' as xml) LastStatus from subscriptions ) insert into DMReport.dbo.TBL_ManageSubscriptions (SubscriptionID, LastStatus) select records.SubscriptionID, x.LastStatus from records cross apply ( select item.value('text()[1]', 'varchar(50)') LastStatus from records.LastStatus.nodes('//r') R(item) ) x where x.LastStatus like '%@%._%';
0 Likes 0 ·
@natongm The error you are getting is due to the fact that you spell out 5 columns in the column list of your cte, but actually select only 2. Additionally, you probably need to modify the column list of your insert statement. Something like this:
with records (SubscriptionID, LastStatus, 
    ReportName, ModifiedDate, LastRunTime) as
(
    select
        s.SubscriptionID, 
        cast('' + replace(replace(s.LastStatus, ';', ' '), 
        ' ', '') + '' as xml) LastStatus, cat.ReportName, 
        s.ModifiedDate, s.LastRunTime
        from dbo.Subscriptions s inner join dbo.Catalog cat
            on s.Report_OID = cat.ItemID
)
    insert into DMReport.dbo.TBL_ManageSubscriptions
    (
        SubscriptionID, LastStatus,
        ReportName, ModifiedDate, LastRunTime
    )
    select
        records.SubscriptionID, x.LastStatus, records.ReportName,
        records.ModifiedDate, records.LastRunTime
        from records cross apply
        (
            select 
                item.value('text()[1]', 'varchar(50)') LastStatus
                from records.LastStatus.nodes('//r') R(item)
        ) x
        where
            x.LastStatus like '_%@_%._%';
0 Likes 0 ·
Thank you very much @Oleg. It works superbly!!
0 Likes 0 ·
Can I use the above cte in a stored procedure to update the ManageSubscriptions table? Basically, if there is a new record that does not exist in the ManageSubscriptions table, then update.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.