Using SQL Server Merge to Employ Intelligent Data Gathering Solutions

merged In this article, we want to introduce one of the most powerful code snippets out there right now to gather competitive intelligence and generate datasets to run your business. The SQL Server merge statement allows you to process data by determining whether to add or update in a single statement. MySQL and Oracle also have the merge statement but I don’t know where you can host Oracle DBs for cheap and MySQL merge statement is not as robust as what we are about to demonstrate. So in this article, we are going to discuss how to do the Microsoft SQL Server Merge statement. I used the merge statement in the code for the GeoAirport last year and also used the same merge pattern in the SistaSphere sql code. I written the sql stored procedure code in a reusable format where me and the 3rd Strategic Institute can quickly write up our own code really fast for data gathering. The goal of this article is to get brothas and sistas familiar with advance data gathering techniques to build up their marketing and competitive intelligence skills. What Can the Merge Statement Do? The merge statement combines the data of one table into another table without creating duplicates. So if you have a database table of email addresses from one database, you can merge it into another database table of email addresses without adding duplicates rows. One good example is building a list of Black web sites. Let’s say I use the linkto:blackamericanweb.com in Google and screen scrape all of the URLs into a database table and then I have another service scrape linkto:bossip.com and my friends scrape URLs from The Root and the Grio or URLs or blogs that link to these web sites. We sql merge all of those data tables of URLs into one table and have a clean list of Black-orientated URLs we can use to contact in future marketing efforts and keep building that list up of Black-orientated blogs that are linked to them. So as you see, we are building a list of intelligence such as Black-orientated web sites we can target market in our future endeavors. Once we build that list, then we can do further stuff like narrow down the Black blogs worth doing business with and that gives us an edge to spread our marketing message now word of mouth among the Black bloggers for future projects we set up. The Merge Code The following code snippet is a stored procedure. In SQL Query Analyzer, you would copy and paste this code run execute to create the stored procedure. When you want to edit it, replace the word “CREATE PROCEDURE” with “ALTER PROCEDURE” to change it up to how you want to do it. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE procedure [dbo].[geo77_exhibit_process] @exhibit_id uniqueidentifier, @vendor_id uniqueidentifier, @exhibit_hall nvarchar(max), @exhibit_title nvarchar(max), @exhibit_description nvarchar(max) as --create temp table declare @tablerow table ( exhibit_id uniqueidentifier, vendor_id uniqueidentifier, exhibit_hall nvarchar(max), exhibit_title nvarchar(max), exhibit_description nvarchar(max) ) insert @tablerow values ( @exhibit_id, @vendor_id, @exhibit_hall, @exhibit_title, @exhibit_description ) --perform merge merge into geo77_exhibitinfo t1 using @tablerow t2 on t1.exhibit_id = t2.exhibit_id when not matched then insert values (t2.exhibit_id,t2.vendor_id,t2.exhibit_hall,t2.exhibit_title,t2.exhibit_description,getdate(),getdate(),1) when matched then update set exhibit_hall = t2.exhibit_hall, exhibit_title = t2.exhibit_title, exhibit_description = t2.exhibit_description, date_modified = getdate(); In the code snippet above, you will see that I create a table variable and then merge that temporary table into a real table of the value. This is the Sista Sphere code snippet to create a new exhibit hall and the geo77 is the code word for the upcoming GeoMoshi service me and my team is working on so we can reuse this code. If you are not familiar with the code above, let me explain how it works. You see at the top the @exhibit_id uniqueidentifier? This is what is passed in the stored procedure from the web site. If it was an existing exhibit, the unique id would be in the web site. If it was a new exhibit, a new id would have been generated on the ASP.NET side using id = Guid.NewGuid() in C# code and passed in. I create a temp table by creating the table columns and insert the variables that were passed in as the row. So this temp table will always have just one row which is the row that was passed in. Then what I do is merge that temp table with the main table and the code will decide whether to do an insert or a merge. This looks like a foreign language to some of you guys but I suggest you smarten up and learn how to use this piece of code and in your SQL Server database because this is probably the most powerful thing you will create in such a short line of code. In fact, this merge code will be the major driver of the majority of your global hustle operations. There are plenty of YouTube videos to get you going: Case Scenarios So how can you use the SQL Server merge code? Let’s give the examples how you can use this code on your $5/month GoDaddy site and show you how powerful you can be with data intelligence collecting. The first example is create a mailing list – one email per line. You can have people add their email address to a web form and you just display a response that says thank you. You don’t need to worry about if their email address already exist because the merge code will not let a duplicate appear. The second example is intelligence scraping. Many people asked me about scraping information about Black celebrities for gossip sites and selling the information. Here is a secret – create a Regular Expression that look for two words that are capitalized such as Barack Obama or Lance Gross or Jay Z and these are entered as one line in your data collection. Then you see new words like Steve Harvey, Oprah Winfrey, Nikki Minaj start showing up also, you are now data collecting a list of Black celebrities over time and will have tens of thousands of names you can monitor for news and update and report on these people as breaking news. Another example is profiling and capturing the name of all African-Americans like you are part of the NSA. If I was to spy on all Black people, I would create a database of 42 million ids and have a column for name, age, city and some attributes. Then when someone Black on Facebook or these other sites start running their mouth, their Black behind goes into my database as a line item and then we keep building up a dossier of Black people in every city and region and know their age and so on. Now, here is another trick – you can use serial numbers to sell your e-book or software using the same manner I just stated to track African-Americans. If you are selling an e-book or digital art, maybe you just want them to have only 5 chances to download. When they enter the serial number, you look up the serial number in your merge database and if it is there you do an 1 + 1 update to the downloads field to increase the count. When the download count is greater than 5, tell the user their e-book download limit has been exceeded and they need to buy a new serial key. Creating unique names for your social network or member service. If I choose eddiefresh as my username, the merge will insert my user name if it not there but if it is there, it will tell me the username is already taken. In the update statement, you can set a variable to true and false and return it to indicate if the username already exist. Overall, you should realize this piece of code is really about proliferation of unique data from your data gathering techniques. Like I said with the web crawling, I can do 3,000,000 crawls of URLs and then they go into a database table with a merge statement – I probably have a clean list of only 150,000 good URLs removing all the duplicates. Keep crawling 3,000,000 a day then I get more 30,000 clean URLs and that’s how digital firms are able to grow so fast. Think about it when LinkedIn or Facebook ask to scan your email account for contacts – they are throwing all those names and email in a list and then they going to hit them up to join their social media site expanding their population. That is how this code works. How to Get Started The first thing you should do is download SQL Server Express since it is free and get it off the Microsoft Site. Make sure you download the version with the Management Studio and install management studio on your local Windows machine. Then you create your database and your table. Then you create a merge statement similar to the code above as a stored procedure. Then you have web code or some code to pass data in and watch the data start piling up and you have unique data. You will see me use this code extensively for our marketing campaign as we take things serious now to reach the Black masses. For example, I can do a merge on all of the hoods in America by asking cats to enter a city and a hood name. So cats can enter their hood name and city name and I end up with a clean list of unique names without worrying about duplicate entries. I can ask cats to name some house music songs and they can enter title of songs and I get a clean list of house songs without a bunch of duplicates. Again, just want to show you guys this code because this will be the core marketing tool we will be using to get data together.

5 thoughts on “Using SQL Server Merge to Employ Intelligent Data Gathering Solutions

  1. Hey Ed thanks a million for your site and ALL the life long information that I will using in the future. I just registered today on Udemy.Com and took the free lectures for MySQL Database For Beginners. I am so ready to change my life at 38 years old. I have spent years not getting paid like I should and am ready to start my entrepreneurial destination from here on out. God Speed!

    1. Rodrick,

      Good to hear and to be honest, you are at the right age to start…don’t think for a minute you are not…

  2. Thanks Mr Dunn for the quality information I have have received from your blog I to have just started with sql server technology I’m learning a lot from this blog .

  3. Hi, I am happy to find your blog. I’m planning to dig deeper into SQL Server. Also, I’m a beginner to the t-sql language. Loving your information because it has inspired me to go back into the entrepreneurial mindset. I’m looking forward to your future posts.

    Much love.

Comments are closed.