E.DIN Editor • September 20, 2017
XML CRUD SQL Bulk Operation
//NOTE: Input XML file, Resulting SQL Data Operation Insert, Modify, etc.
---------------
GO
/****** Object: StoredProcedure [DatabaseName].[recurring_account_transaction_add] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =====================================================================
-- Description:
Bulk operation to add recurring transaction records for
--
transitioning subscribers from System1 to System2
-- =====================================================================
CREATE PROCEDURE [DatabaseName].[recurring_account_transaction_add]
(@Rts xml)
AS
BEGIN
SET NOCOUNT ON;
-- declare table
create table #table(account_id int, merchant_category_code char(4), amount money, expiration_date datetime)
INSERT INTO #table(account_id, merchant_category_code, amount, expiration_date)
SELECT rt.value('@account_id','int') account_id
,rt.value('@merchant_category_code','char(4)') merchant_category_code
,rt.value('@amount','money') amount
,rt.value('@expiration_date','datetime') expiration_date
FROM @Rts.nodes('/rtList/rt') p(rt)
-- insert new recurring transaction records
insert into DatabaseName.recurring_account_transaction(account_id, merchant_category_code, amount, expiration_date)
select account_id, merchant_category_code, amount, expiration_date from #table
drop table #table
END
-------------------------
USE [DatabaseName]
GO
/****** Object: StoredProcedure [DatabaseName].[account_health_plan_sync_modify] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ====================================================
-- Description:
Bulk operation to add/update/delete subscribers health
--
plan information from 3 systems
-- ====================================================
CREATE procedure [DatabaseName].[account_health_plan_sync_modify]
(@Hps xml,
@EmployerID int)
AS
BEGIN
set nocount on;
create table #table(employer_id int, account_id int, member_id int, insurance_plan_name varchar(50), system2_plan_name varchar(50), vendor_plan_name varchar(50), [type] char(3), vendor_id int)
insert into #table(employer_id, account_id, member_id, insurance_plan_name, system2_plan_name, vendor_plan_name, [type], vendor_id)
select p.hp.value('@employer_id','int') employer_id
,p.hp.value('@account_id','int') account_id
,p.hp.value('@member_id','int') member_id
,p.hp.value('@insurance_plan_name','varchar(50)') insurance_plan_name
,p.hp.value('@system2_plan_name','varchar(50)') system2_plan_name
,p.hp.value('@vendor_plan_name','varchar(50)') vendor_plan_name
,p.hp.value('@type','char(3)') [type]
,p.hp.value('@vendor_id','int') vendor_id
from @hps.nodes('hpList/hp') p(hp)
merge DatabaseName.account_health_plan_sync as s
using #table as t
on (s.employer_id = t.employer_id
and s.member_id = t.member_id
and s.vendor_id = t.vendor_id
and s.[type] = t.[type])
when not matched by source and s.employer_id = @EmployerID then
delete
when
matched and (s.insurance_plan_name<>t.insurance_plan_name or s.system2_plan_name<>t.system2_plan_name or s.vendor_plan_name<>t.vendor_plan_name)
then update set s.insurance_plan_name=t.insurance_plan_name, s.system2_plan_name=t.system2_plan_name, s.vendor_plan_name=t.vendor_plan_name, modify_date=getdate()
when
not matched then
insert (employer_id, account_id, member_id, insurance_plan_name, system2_plan_name, vendor_plan_name, [type], vendor_id)
values (t.employer_id, t.account_id, t.member_id, t.insurance_plan_name, t.system2_plan_name, t.vendor_plan_name, t.[type], t.vendor_id );
END
----------------------------------------------
AccountHealthPlanSynchronizeList
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System2.Common;
using System.Xml.Linq;
using System.Data.SqlClient;
namespace System1
{
public class AccountHealthPlanSynchronizeList : BusinessListBase<AccountHealthPlanSynchronizeList, AccountHealthPlanSynchronizeObject>
{
#region constructors
public AccountHealthPlanSynchronizeList()
: base(DatabaseType.System1)
{
}
#endregion
#region xml
public void SaveWithXml(int employerId)
{
XElement hpList = new XElement("hpList",
from h in this
select new XElement("hp",
new XAttribute("employer_id", h.EmployerId),
new XAttribute("account_id", h.AccountId),
new XAttribute("member_id", h.MemberId),
new XAttribute("System1_plan_name", h.System1PlanName),
new XAttribute("System2_plan_name", h.System2PlanName),
new XAttribute("vendor_plan_name", h.VendorPlanName),
new XAttribute("type", h.Type),
new XAttribute("vendor_id", h.VendorId)
));
using (SqlConnection cn = new SqlConnection(Context.ConnectionString))
{
cn.OpenWithContext();
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = System2.Common.Database.CommandTimeout();
cmd.CommandText = "DatabaseName.account_health_plan_sync_modify";
cmd.Parameters.Add("@Hps", System.Data.SqlDbType.Xml);
cmd.Parameters["@Hps"].Value = hpList.ToString();
cmd.Parameters.AddWithValue("@EmployerId", employerId);
cmd.ExecuteNonQuery();
}
}
}
#endregion
}
}
Title: Base64 Encoding With Simple Salt (and decoding)
XPCMD_SHELL Move & Load Data from Directory
***** My Valid Data Monitor **** XML OUT *** HTML EMAIL DELIVERY *****IF OBJECT_ID('tempdb..#tCustomerID') IS NOT NULL DROP TABLE #tCustomerID select distinct mm.CustomerID Into #tCustomerID from myDatabase..myTable (nolock) mm left join myDatabase.dbo.myOtherTable (nolock) mo on mm.refID = mo.id left join myDatabase.dbo.myOtherTableDetail (nolock) mod on mo.id = mod.myOtherTableid and mm.SearchValue = mod.SearchValue where mm.refID is not null and mm.quantity is not null and mod.SearchValue is nullIF Exists (Select 1 From #tCustomerID)BEGIN DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)SET @xml = CAST(( SELECT [CustomerID] AS 'td' FROM #tCustomerID ORDER BY CustomerID FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))SET @body ='
'--SELECT @body exec msdb.dbo.sp_send_dbmail @profile_name = 'dbmailProfile' ,@recipients = 'usergroup@mydomain.com' ,@subject = 'My Valid Data Monitor' ,@body = @body ,@body_format = 'HTML'END
My Valid Data Monitor
| CustomerID |
|---|
The new season is a great reason to make and keep resolutions. Whether it’s eating right or cleaning out the garage, here are some tips for making and keeping resolutions.
There are so many good reasons to communicate with site visitors. Tell them about sales and new products or update them with tips and information.
Write about something you know. If you don’t know much about a specific topic that will interest your readers, invite an expert to write about it.
Turn Up! Not every microphone has the same base volume for transmitting your voice through your computer to whoever or whatever is on the other end of the exchange. Some microphones have a higher volume as compared to others, and some microphones have a volume that is so low that the person on the other […]
The post How to Turn Up Mic Volume in Windows 10 – Appuals.com appeared first on TekPreacher.
'+@UNCPathAndLogTable+'