Month: July 2019

Where there are lawyers, there are paper contracts — lots of them. A Seattle startup has come up with a way for in-house legal teams to get a handle on all those contracts with the help of artificial intelligence.

Lexion today announced a $4.2 million seed round led by Madrona Venture Group, with participation from Wilson Sonsini, Goodrich and Rosati, a legal advisor to technology and life sciences companies.

The investment from a major law firm is unusual, but speaks to the conviction that legal experts have in Lexion.

The company originated inside the Allen Institute for Artificial Intelligence (AI2). Its premise mirrors a lot of machine learning startups: find an inefficient manual process and use tools such as natural language processing to fix it.

For Lexion, that problem starts in the legal departments of mid-market companies.

“Most in-house counsel today at companies manage all their contracts using file folders, spreadsheets and email,” said Lexion co-founder Gaurav Oberoi, a Seattle startup vet who joined AI2 in February 2018.

Manual processes lead to a classic needle-in-a-haystack problem where high-paid lawyers waste time searching through contracts whenever somebody has a question about one of them.

Lexion created a natural language processing system that reads contracts, organizes them, and lets users search them with a simple interface. Users can also set up notifications for important reminders, such as expiration dates.

While Lexion’s initial focus on internal legal teams is niche, Oberoi sees a larger opportunity in broader contract management for companies at a lower cost, especially among mid-sized firms.

“Natural language processing over the last 2-to-3 years has really come to maturity,” he said. “And it’s come to the point where it’s ready for commercialization.”

Oberoi was the brains behind BillMonk, a bill-splitting app that predated Splitwise. He also created Precision Polling, an automated survey startup that was later purchased by SurveyMonkey, and then went on to build SurveyMonkey Audience.

After that streak of success, the young entrepreneur took some time off. He wanted to do another early-stage company and was interested in artificial intelligence. He joined the Allen Institute for Artificial Intelligence (AI2) as an entrepreneur-in-residence and got cracking.

Among the seeds of ideas that were left on the cutting room floor: satellites, ultrasound imaging, and even exploring deepfakes, which landed him in an episode of Buzzfeed’s Netflix documentary series Follow This.

In the end, it was Lexion co-founder Emad Elwany who discovered the contract problem. His wife knew the problem all too well from her work in the procurement division of a large company.

Oberoi and Elwany joined with James Baird to launch Lexion. Elwany previously worked as an engineer at Microsoft on artificial intelligence and Baird is an engineer who came from web development firm Pancake Labs.

There are plenty of companies that help clients manage contracts, including Bellevue, Wash.-based Icertis, which became a billion-dollar company this week. But most have focused on products for large enterprise companies.

“In talking to dozens of portfolio and other middle-market and growth companies, we heard this pain point and market opportunity reiterated over and over again,” Tim Porter, managing director at Madrona, wrote in a blog post. “[Oberoi] is a tremendous founder and well-known in the tech community both for his successes in and his enthusiasm for the Seattle startup scene.”

The Lexion investment is in line with Madrona’s ongoing interest in companies that build machine learning applications, such as Lattice Data, which was acquired by Apple in 2017. Madrona also invested in AI2 spinout Xnor, which raised $12 million last year.

AI2 expanded its incubator in 2017. Another spinout, Kitt.ai, was acquired by Chinese search giant Baidu later that year.

via GeekWire
Allen Institute spinout Lexion lands $4.2M to help companies manage legal contracts with AI

Contract management isn’t exactly an exciting subject, but it’s a real pain point for many companies. It also lends itself to automation, thanks to recent advances in machine learning and natural language processing. It’s no surprise then, that we see renewed interest in this space and that investors are putting more money into it. Earlier this week, Icertis raised a $115 million Series E round, for example, at a valuation of more than $1 billion. Icertis has been in this business for ten years, though. On the other end of the spectrum, contract management startup Lexion today announced that it has raised a $4.2 million seed round led by Madrona Venture Group and law firm Wilson Sonsini Goodrich & Rosati, which was also one of the first users of the product.

Lexion was incubated at the Allen Institute for Artificial Intelligence (AI2), one of the late Microsoft co-founders’ four scientific research institutes. The company’s co-founder and CEO, Gaurav Oberoi, is a bit of a serial entrepreneur, whose first startup, BillMonk, was first featured on TechCrunch back in 2006. His second go-around was Precision Polling, which SurveyMonkey then acquired shortly after it launched. Oberoi founded the company together with former Microsoft research software development engineering lead Emad Elwany, and engineering veteran James Baird.

“Gaurav, Emad, and James are just the kind of entrepreneurs we love to back: smart, customer obsessed and attacking a big market with cutting edge technology,” said Madrona Venture Group managing director Tim Porter. “AI2 is turning out some of the best applied machine learning solutions, and contract management is a perfect example – it’s a huge issue for companies at every size and the demand for visibility into contracts is only increasing as companies face growing regulatory and compliance pressures.”

Contract management is becoming a bit of a crowded space, though, something Oberoi acknowledge. But he argues that Lexion is tackling a different market from many of its competitors.

“We think there’s growing demand and a big opportunity in the mid-market,” he said. “I think similar to how back in the 2000s, Siebel or other companies offered very expensive CRM software and now you have Salesforce — and now Salesforce is the expensive version — and you have this long tail of products in the mid-market. I think the same is happening to contracts. […] We’re working with companies that are as small as post-seed or post-Series A to a publicly-traded company.”

Given that it handles plenty of highly confidential information, it’s no surprise that Lexion says that it takes security very seriously. “I think, something that all young startups that are selling into business or enterprise in 2019 need to address upfront,” Oberoi said. “We realized, even before we raised funding and got very serious about growing this business, that security has to be part of our DNA and culture from the get-go.” He also noted that every new feature and product iteration at Lexion goes through a security review.

Like most startups at this stage, Lexion plans to invest the new funding into building out its product — and especially its AI engine — and go-to-market and sales strategy.

via TechCrunch
Lexion raises $4.2M to bring AI to contract management

The University of Washington named longtime professor François Baneyx to lead its CoMotion innovation center.

Baneyx is currently the Charles W.H. Matthaei Professor of Chemical Engineering; director of the Center for the Science of Synthesis Across Scales; and an adjunct professor of bioengineering. He has held several leadership positions across campus since arriving in 1992, including:

• Site director of the National Nanotechnology Infrastructure Network (2004-2012)
• Director of the Center for Nanotechnology (2005-2013)
• Chair of the Department of Chemical Engineering (2014-2019)

In 2015, Baneyx co-founded Proteios, a UW life sciences spinout. He’s also an elected fellow of the American Association for the Advancement of Science and a member of the Washington State Academy of Sciences.

Baneyx replaces Vikram Jandhyala, the former CoMotion leader who died in February.

“I am honored to succeed Vikram at the helm of CoMotion and I look forward to helping our incredibly talented faculty and students innovate for the greater good,” Baneyx said in a statement.

CoMotion helps startups through education and access to experts and funding sources. Originally started as the Center for Commercialization (C4C) at the UW’s main Seattle campus, CoMotion evolved a few years ago from a department that mainly helped commercialize ideas born at the university to what it now describes as a “collaborative innovation hub dedicated to expanding the economic and societal impact of the UW community.”

The UW has ranked among the top 10 on Reuters’ list of the world’s most innovative universities for the past several years and cracked the top 10 of the Milken Institute national tech transfer rankings. CoMotion also helped open a makerspace on campus; created an Amazon Catalyst program; and launched the Mobility Innovation Center with Challenge Seattle.

“François is a respected researcher, teacher and innovator with connections throughout academia and industry, as well as firsthand experience with startups,” UW Provost Mark Richards said in a statement. “I am confident that with his interdisciplinary, collaborative approach to his work and leadership, François will build upon CoMotion’s success and further propel the University’s culture of innovation.”

via GeekWire
Univ. of Washington names longtime professor François Baneyx to lead CoMotion innovation center

When the City of Baltimore agreed to settle with a victim of police brutality, it inserted the usual clauses that come with every settlement. There was the standard non-admission of wrongdoing, along with a "non-disparagement" clause the city’s attorney told courts was used "in 95% of settlements" to prevent those being settled with from badmouthing the entity they sued.

Ashley Overbey received a $63,000 settlement from the city for allegations she was beaten, tased, verbally abused, and arrested after calling officers to her home to report a burglary. When a local newspaper published a story about the settlement, the City Solicitor chose to disparage Overbey by saying she was "hostile" when the police arrived at her home. As the comments filled up with invective against Overbey, she showed up in person to fire back at her detractors, claiming the police had been in the wrong and detailing some of the injuries she suffered.

The City — which had chosen to skew public perception against Overbey by commenting on the settlement — decided Overbey’s defense of herself violated the non-disparagement clause. So, it clawed back half of her settlement — $31,500 — for violating its STFU clause.

Overbey sued again, claiming this clause violated her First Amendment. Now, seven years after police showed up at her home and treated like the perpetrator — rather than a victim — of a crime, the Fourth Circuit Court of Appeals has ruled [PDF] these non-disparagement clauses are unconstitutional bullshit.

The City argued Overbey’s acceptance of the clause was actually an action of free expression. By opting for a payout, she was (and I am quoting the City here) "exercising her right not to speak in exchange for payment." Alternatively, it argued that even if it was an unconstitutional waiver of rights, the court has no reason to intercede and nullify the clause.

The court agrees that it’s a waiver of rights, but disagrees about what it’s allowed to do about it:

We hold that the non-disparagement clause in Overbey’s settlement agreement amounts to a waiver of her First Amendment rights and that strong public interests rooted in the First Amendment make it unenforceable and void.

It goes on to point out that the government has no business compelling speech — i.e., violating someone’s decision to not speak about something. But that’s not what’s going on here. The clause penalizes settlement recipients for choosing to speak, which is definitely not Constitutional.

Overbey’s promise not to speak about her case cannot be fairly characterized as an exercise of her right to refrain from speaking, because none of the interests protected by the right to refrain from speaking were ever at stake in this case. No one tried to compel Overbey to make speech she did not want to make; no one tried to punish Overbey for refusing to say something she did not want to say. Instead, Overbey agreed, on pain of contractual liability to the City, to curb her voluntary speech to meet the City’s specifications. In doing so, she waived the First Amendment protections that would have otherwise shielded her speech from government sanction.

While it is possible for people to voluntarily waive their rights in certain situations, the discussion of a settled lawsuit isn’t one of these situations. Especially not when it deals with issues of considerable public interest, like allegations of police misconduct and abuse. The clause inserted into settlement agreements also drives a wedge between public agencies and the public they serve, contributing to the omnipresent distrust. In a case like this, the only purpose the clause serves is to silence speech the government doesn’t like.

Standing shoulder to shoulder with the citizenry’s interest in uninhibited, robust debate on public issues is this nation’s cautious “mistrust of governmental power.” Citizens United, 558 U.S. at 340. This mistrust is one of the “premise[s]” of the First Amendment, id., and we think it well-warranted here, because the non-disparagement clause is a government-defined and government-enforced restriction on government-critical speech. Indeed, when the government (1) makes a police-misconduct claimant’s silence about her claims a condition of settlement; (2) obtains the claimant’s promise of silence; (3) retains for itself the unilateral ability to determine whether the claimant has broken her promise; and (4) enforces the claimant’s promise by, in essence, holding her civilly liable to itself, there can be no serious doubt that the government has used its power in an effort to curb speech that is not to its liking.

That unconstitutionality voids the clause.

Accordingly, we conclude that enforcement of the non-disparagement clause at issue here was contrary to the citizenry’s First Amendment interest in limiting the government’s ability to target and remove speech critical of the government from the public discourse.

The court has zero patience for the City’s exhausting arguments about how unfair this is to the City:

As the City would have it, Overbey “sold her [speech] rights, with an option to buy them back, which she exercised, and now she has [her rights] again.” Id. at 39. Essentially, the City argues that half of Overbey’s settlement sum was earmarked for her silence, and that it would be unfair for Overbey to collect that half of her money when she was not, in fact, silent. When the second half of Overbey’s settlement sum is viewed in this light, it is difficult to see what distinguishes it from hush money. Needless to say, this does not work in the City’s favor. We have never ratified the government’s purchase of a potential critic’s silence merely because it would be unfair to deprive the government of the full value of its hush money. We are not eager to get into that business now.

The City will now have to give back the other half of Overbey’s money. You can’t beat the hell out of the First Amendment and expect to cash out. Litigants in Baltimore — and elsewhere in the circuit — have been freed to discuss the details of their cases in public without fear of reprisal. Hopefully, this will reduce the percentage of settlements with gag orders from 95% to 0% very quickly.

Permalink | Comments | Email This Story

via Techdirt
Appeals Court Shoots Down The Unconstitutional ‘Non-Disparagement’ Clauses Baltimore Attaches To Lawsuit Settlements

Google and its flagship search portal opened the door to the possibilities of how to build a business empire on the back of organising and navigating the world’s information, as found on the internet. Now, a startup that’s built a search engine tailored to the needs of enterprises and their own quests for information has raised a round of funding to see if it can do the same for the B2B world.

AlphaSense, which provides a way for companies to quickly amass market intelligence around specific trends, industries and more to help them make business decisions, has closed a $50 million round of funding, a Series B that it’s planning to use to continue enhancing its product and expanding to more verticals.

Today, the company today counts some 1,000 clients on its books, with a heavy emphasis on investment banks and related financial services companies. That’s in part because of how the company got its start: Finnish co-founder and CEO Jaakko (Jack) Kokko he had been an analyst at Morgan Stanley in a past life and understood the labor and time pain points of doing market research, and decided to build a platform to help shorted a good part of the information gathering process.

“My experience as an analyst on Wall Street showed me just how fragmented information really was,” he said in an interview, citing as one example how complex sites like those of the FDA are not easy to navigate to look for new information an updates — the kind of thing that a computer would be much more adept at monitoring and flagging. “Even with the best tools and services, it still was really hard to manually get the work done, in part because of market volatility and the many factors that cause it. We can now do that with orders of magnitude more efficiency. Firms can now gather information in minutes that would have taken an hour. AlphaSense does the work of the best single analyst, or even a team of them.”

(Indeed, the “alpha” of AlphaSense appears to be a reference to finance: it’s a term that refers to the ability of a trader or portfolio manager to beat the typical market return.)

The lead investor in this round is very notable and says something about the company’s ambitions. It’s Innovation Endeavors, the VC firm backed by Eric Schmidt, who had been the CEO of none other than Google (the pace-setter and pioneer of the search-as-business model) for a decade, and then stayed on as chairman and ultimately board member of Google and then Alphabet (its later holding company) until just last June.

Schmidt presided over Google at what you could argue was its most important time, gaining speed and scale and transitioning from an academic idea into full-fledged, huge public business whose flagship product has now entered the lexicon as a verb and (through search and other services like Android and YouTube) is a mainstay of how the vast majority of the world uses the web today. As such he is good at spotting opportunities and gaps in the market, and while enterprise-based needs will never be as prominent as those of mass-market consumers, they can be just as lucrative.

“Information is the currency of business today, but data is overwhelming and fragmented, making it difficult for business professionals to find the right insights to drive key business decisions,” he said in a statement. “We were impressed by the way AlphaSense solves this with its AI and search technology, allowing businesses to proceed with the confidence that they have the right information driving their strategy.”

This brings the total raised by AlphaSense to $90 million, with other investors in this round including Soros Fund Management LLC and other unnamed existing investors. Previous backers had included Tom Glocer (the former Reuters CEO who himself is working on his own fintech startup, a security firm called BlueVoyant), the MassChallenge incubator, Tribeca Venture Partners and others. Kokko said AlphaSense is not disclosing its valuation at this point. (I’m guessing though that it’s definitely on the up.)

There have been others that have worked to try to tackle the idea of providing more targeted, and business focused search portals, from the likes of Wolfram Alpha (another alpha!) through to Lexis Nexis and others like Bloomberg’s terminals, FactSet, Business Quant and many more.

One interesting aspect of AlphaSense is how it’s both focused on pulling in requests as well as set up to push information to its users based on previous search parameters. Currently these are set up to only provide information, but over time, there is a clear opportunity to build services to let the engines take on some of the actions based on that information, such as adjusting asking prices for sales and other transactions.

“There are all kinds of things we could do,” said Kokko. “This is a massive untapped opportunity. But we’re not taking the human out of the loop, ever. Humans are the right ones to be making final decisions, and we’re just about helping them make those faster.”

via TechCrunch
AlphaSense, a search engine for analysis and business intel, raises $50M led by Innovation Endeavors

Icertis, welcome to the unicorn club.

The Bellevue, Wash. contract management startup announced a whopping $115 million investment round, the second biggest funding round for a Seattle-area startup so far this year, to push its valuation over $1 billion. The 10-year-old company has now raised $211 million to date as it aims to fend off an ever-growing list of competitors.

RELATED: Recent startup funding deals in Seattle and the Pacific Northwest

Icertis builds software that helps companies keep track of deals with suppliers and customers that require extensive contracts. Given how the legal system works, that’s a lot of contracts, and Icertis’ software also helps companies negotiate better contracts by highlighting ways to save money or move faster.

“The contract governs every dollar that comes in to the company and every dollar that you spend, and if you don’t have the contracts right … you can get into trouble over a long period,” Icertis CEO Samir Bodas said in an interview with GeekWire.

Contract lifecycle management will be a $20 billion market over the next five years, per a recent report from MGI Research, growing at more than 30 percent per year.

Icertis’ milestone represents another feather in the cap of Seattle’s tech scene and its status as an enterprise software hub. Just three months ago, fast-growing sales automation company Outreach also reached unicorn status after a huge funding round. Other recent notable investments for Seattle enterprise companies include a $103 million round for Auth0; a $60 million round for Highspot; a $50 million round for Zenoti; and a $43 million round for Flexe.

A number of Seattle-area enterprise software companies have also recently gone public, including Smartsheet and Avalara. Anchor tech giant Microsoft is at the heart of the region’s ascension as a software hub, spinning out leaders of roughly a quarter of the startups in the GeekWire 200 ranking. Bodas is a Microsoft vet, having spent seven years there in the 1990s, where he gave presentations directly to Bill Gates and Steve Ballmer.

Icertis’ wide range of competition includes products from tech giants including DocuSign, SAP and Oracle and smaller firms with more of a microscope on contracts such as Conga and ContractWorks. Contract management is one of the few enterprise software categories still up for grabs, Bodas said, without an incumbent giant in control. Bodas claims Icertis is the first $1 billion contract management company, putting it in position to lead the way.

Icertis stands out because its platform is a one-stop shop when it comes to contracts, Bodas said. It manages contracts for items companies both buy and sell, where most companies only cover one side of that equation.

Icertis also employs advanced technologies such as artificial intelligence and machine learning that can read into the contracts and actually understand what they mean for the companies’ balance sheets. It allows one hand of the company to know exactly what the other one is doing.

“It connects itself to all the systems around it,” Bodas said of the company’s contract management platform. “It connects itself to your procurement system, your purchase order system, your delivery system, and it becomes the nerve center of your commerce.”

In total, customers manage 5.7 million contracts on Icertis’ platform, valued at more than $1 trillion. The company has focused primarily on selling to the biggest companies, or those with more than $5 billion in annual revenue. But Icertis strives to be the “contract management platform of the world,” and that means gaining ground with smaller businesses as well.

Icertis has grown to 2 million users across the world, and it counts some of the most prominent companies in the world, including Microsoft, Google, 3M and Airbus, as customers. It has traction in a variety of industries, including retail, airlines and manufacturing.

The company is approaching $100 million in annual revenue, Bodas said. Roughly 40 percent of its revenue comes from the U.S., 40 percent is from Europe and the remaining 20 percent comes from Asia Pacific.

Icertis is not yet profitable as it continues to grow, but Bodas said the company is “cash efficient.” Bodas isn’t thinking about going public right now, as that opens up the company to host of macro-economic factors out of its control that could impact its growth and success.

The Series E funding round was led by Greycroft and Premji Invest with participation from additional investors including B Capital Group, Cross Creek Advisors, Eight Roads, Ignition Partners, Meritech Capital Partners and PSP Growth.

Icertis will use the cash infusion to build out more business applications, beef up its AI technology, grow sales and marketing initiatives to reach more companies in more areas and finance potential future acquisitions. With this round, Icertis will also double down on blockchain, the trendy but controversial technology that could make for an interesting development in contract management.

Bodas cited Icertis’ recent deal with automotive giant Daimler as an example of blockchain’s potential. With a sprawling supply chain, the Mercedes-Benz parent company has to work with a huge group of subcontractors. Through the blockchain, which is meant to create a more publicly viewable ledger of transactions, Daimler can ensure that deals down the chain with subcontractors meet its standards in areas like privacy, sustainability and labor treatment.

Icertis also recently partnered on a blockchain-related deal with Microsoft.

Icertis today has 900 people in 12 offices across the globe, including 150 employees at its Bellevue headquarters just down the street from Microsoft.

Bodas, whose company today sits at No. 11 on the GeekWire 200, still draws on his time at Microsoft, which ended in 2000, nine years before he started Icertis. He aims to apply the same level of detail and scrutiny that Gates, Ballmer and other executives gave his presentations and replicate their ability to think about how each new product or initiative would fit in with the larger organization.

“How they brought it all together was a tremendous learning for me,” Bodas said. “It really trained me well to think about the business holistically, end to end, in all its depth and breadth.”

via GeekWire
Contract management startup Icertis hits $1B valuation with massive $115M funding round

This video tells the tale of Ka-Bar knives, in documentary style complete with actors and professional-quality production.

The story really begins with a name any “knife guy” will recognize: Case.

More than 30 cutlery operations connected to the Cases branch out over the span of a few decades. This dynasty begins with Job Case…

One of Job’s grandsons, Wallace Brown, became a traveling knife salesman, learning the ropes of the business, then started The Union Razor Company, a mail-order cutlery business, in 1894. His brother Emerson joined the company as well, expanding into the warehousing business as well as selling razors made for them by local companies and sold under the company name Brown Brothers Razor. It was a flop.

Some years later, Wallace bought out a struggling knife company in Tidioute, PA. Before long, the company’s name was changed to The Union Razor Company, hearkening back to his earlier effort. Down the road, they began making more knives and changed the name again. In 1909 it was renamed The Union Cutlery Company.

When approached by the Chamber of Commerce of Olean, NY, who offered bribes such as tax breaks, land, and even a building if he would move to Olean from Tidioute, Wallace accepted. The company was successful and their hunting knives became popular. Then one fateful day, an interesting package was received by Wallace Brown.

In the package was the skin of a Kodiak bear and a letter written by an Alaskan hunter who wished to thank the Union Cutlery Company for “their outstanding knives.” This morphed into a legend that the original of the Ka-Bar was based on “kill a bear (or b’ar).”

The current company owners heard this legend when they purchased Ka-Bar, but never knew for sure until they found some old documents…

The truth was never really totally known until recently. Twenty years after we purchased the company we unearthed a document… a manual written by the grandson of one of the original owners of Union Cut[lery]. In there, it specifically references the letter…

This corroborated the story, although the manual excerpt leans more towards “Kodiak” as the source of the “K.”

At any rate, the Ka-Bar name became a registered trademark in 1924.

In the midst of the Great Depression, leadership of the company was left in the hands of 26-year-old Danforth Brown after his father and uncle both died young. Union Cutlery made it through, and was ready and able when World War 2 came around with its many manufacturing jobs.

The most famous combat knife to emerge at this time was the 1219C2, or USMC Fighting Utility Knife, which was designed by USMC Capt. (some sources say Maj.) Howard America, USMC Col. John Davis — and Danforth Brown.

At this time, Danforth made the fateful decision to mark products made for the war with the Ka-Bar name, rather than “Union Cutlery.”

Most 1219C2 knives were made by other companies, but most troops seemed to agree that a Ka-Bar branded 1219C2 was better than the rest. The name gained such popularity that Union eventually changed the company name to Ka-Bar.

When hard times came again, Danforth agreed to the same sort of “relocation bribery,” this time moving the company to Dawsonville, Georgia. It was a huge mistake, and two years later he was back in New York where an established factory and skilled workforce stood ready to make more Ka-Bars.

Danforth passed away in 1970, and the company was bought & sold numerous times, eventually going bankrupt. After being saved a time or two, Ka-Bar was purchased by a large conglomerate who used the brand on cheap imported knives. It was a sad time for anyone who really cared about the Ka-Bar name.

Cutco, then known as Alcas, bought Ka-Bar in the mid-1990s. Alcas had been making the old classic Ka-Bar-branded fighting knife for a couple of decades, so it was a natural fit as far as that goes. With the surge of warfare in the early 2000s, a new generation of soldiers began using Ka-Bar knives. Working with independent knife designers has also been lucrative for them in recent years.

In April 2018, the company celebrated “120 years in the Ka-Bar story.”

The post Watch: The KA-BAR Story appeared first on AllOutdoor.com.

via All Outdoor
Watch: The KA-BAR Story

SQL Merge Statement Tutorial With Example is today’s topic. SQL MERGE STATEMENT is the combination of INSERT, UPDATE, and DELETE statement. Merge Statement can perform all these operations in our main target table when the source table is provided. MERGE is very useful when it comes to loading the data warehouse tables, which can be very large and require the specific actions to be taken when the rows are or are not present.

SQL Merge Statement

The syntax is following.

MERGE <target_table> [AS TARGET]
USING <table_source> [AS SOURCE]
ON <search_condition>
[WHEN MATCHED 
THEN <merge_matched> ]
[WHEN NOT MATCHED [BY TARGET]
THEN <merge_not_matched> ]
[WHEN NOT MATCHED BY SOURCE
THEN <merge_matched> ];

#How to use SQL MERGE STATEMENT

1. Identify the target table which is going to be used in that logic.
2. Next step is to identify the source table which we can use in the logic.
3. Next step is to determine the appropriate search conditions in the ON clause to match the rows.
4. Implement logic when records are matched or not matched between the target and source.
5. For each of this comparison, conditions write the logic, and When matched, generally an update condition is used and When not matched, then insert or delete statement is used.

Let’s Clear this by seeing an example:

Consider Table Products: (This will be considered as Target Table).

ID	NAME	PRICE
101	Tea	5.00
201	Chips	10.00
301	Coffee	15.00

Updated_Products: (This will be Considered as SOURCE Table).

ID	NAME	PRICE
101	Tea	5.00
201	Biscuits	20.00
301	Coffee	25.00

 

#QUERY

MERGE PRODUCTS AS TARGET
USING UPDATED_PRODUCTS AS SOURCE
ON (TARGET.ID=SOURCE.ID)
THEN MATCHED AND TARGET.NAME SOUCE.NAME
OR TARGET.PRICE SOURCE.PRICE THEN
UPDATE SET TARGET.NAME=SOURCE.NAME,
TARGET.PRICE=SOURCE.PRICE
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, NAME, PRICE)
VALUES (SOURCE.ID, SOURCE.NAME, SOURCE.PRICE)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

#Output

So, after running the above query Products table will be replaced by the Updated_products table.

You can see the table below.

ID	NAME	PRICE
101	Tea	5.00
201	Biscuits	20.00
301	Coffee	25.00

 

So, in this way, we can perform all three operations together using MERGE clause.

NOTE:

We can use any name other than source and target we have used these names to give you a better explanation.

#Some basic Key Points

1. The MERGE SQL statement requires the semicolon (;) as a statement terminator Otherwise Error 10713 will be raised.
2. At least one of three MATCHED clauses must be specified when we are using the MERGE statement.
3. The user using the MERGE statement should have SELECT permission on the SOURCE table and INSERT, UPDATE and DELETE permissions on a TARGET table.
4. While inserting, deleting or updating using merge statement in SQL Server fires any corresponding AFTER triggers defined on that target table, but it does not guarantee which action to fire triggers first or last.

Finally, SQL Merge Tutorial With Example is over.

The post SQL Merge Statement Tutorial With Example | Merge In SQL appeared first on AppDividend.

via Planet MySQL
SQL Merge Statement Tutorial With Example | Merge In SQL

Features
* Using raw SQL queries
– you can use all the capabilities of your database to describe the structure and changes
– easy work with procedures and functions
– easy data migrations (INSERT/UPDATE)
– IDE native syntax support
* Running migrations within a transaction with automatic rollback in case of an error (if your database supports it, PostgreSQL for example)
* Dynamic output running the SQL-queries
* Automatic rollback after switching the branch (for reviewing, testing, demo, building at permanent/staging database)
* Auto-update the migration after editing
* Apply with rollback testing – UD-DOWN-UP
* Rollback or Reload any selected migration
via Laravel News Links
An intelligent alternative version of Laravel Database Migrations

A package that provides the essential elements for running a Laravel app on AWS Elastic Beanstalk with HTTPS.
via Laravel News Links
Running Laravel on AWS Elastic Beanstalk with HTTPS