Update, Insert, & Delete in One Shot

CRUD Throwback

1. Mix the Sprite and vodka in a shaker with some ice to chill it.
2. Strain into glass.
3. Using a spoon turned upside down angled into the vodka mix just to slightly break the surface, very slowly pour the blue curacao. It must be done with patience, or the two will mix and you won’t get the same effect. You’ll see the white layer begin to exist.
4. Next add in the grenadine. The easiest way to do this is to pour close to the ednge of the glass to create an almost waterfall effect inside the glass. Since the grenadine is so thick, it’ll drop to the bottom of the glass.

• 1/3 ounce Sprite *
• 1/3 ounce lemon vodka *
• 2/3 ounce blue curacao
• 2/3 ounce grenadine
• ice
• *Note: Can use Mike's Hard Lemonade or Smirnoff Ice in place of these

---

So the idea here is to have MSSQL look at the data from the newFood table and if it is truly new, then insert the name, ID, and Type into the CurrentFood table.

If the IDs match but the values don't match update to change the values in the target to match the source.

If it isn't in the updated food table, then delete it.

---

;WITH cte_UpdatedFoods AS
(
     SELECT FoodName, FoodId
     FROM tblUpdatedFood NF
)
MERGE tblCurrentFood CF
USING cte_UpdatedFoods UF ON UF.foodId = CT.foodId
WHEN NOT MATCHED BY TARGET THEN
     INSERT (FoodName, FoodId, Type) VALUES (UF.FoodName, UF.FoodId, UF.Type)
WHEN MATCHED AND TARGET.foodName <> SOURCE.foodName OR TARGET.type <> SOURCE.type THEN
    UPDATE SET TARGET.foodName = SOURCE.foodName
      , TARGET.type = SOURCE.type
WHEN NOT MATCHED BY SOURCE THEN
     DELETE;

Parse List Into Table

The Parse Quencher

• 3 oz gin
• 2 oz St. Germain elderflower liqueur
• Juice of 1 lime
• 3 sprigs basil, plus more to garnish
• 1 cucumber
• Tonic
• Ice

The idea here is that you want to parse a list, in this example a list of numbers, and put them into a table using a simple "while" loop.  Here is how you might do it.

-- The idea is to parse a passed delimited list into a table

-- This is the variable holding the list
declare @CategoriesList VARCHAR(MAX)
SET		@CategoriesList = '1,2,3,4'



-- Create tables to hold the data being passed
CREATE TABLE #ttbCategories (
	categoryId INT
)

-- Populate the categories Table
	SET @CategoriesList = LTRIM(RTRIM(@CategoriesList))+ ','
	SET @Pos = CHARINDEX(',', @CategoriesList, 1)

	IF REPLACE(@CategoriesList, ',', '') <> ''
	BEGIN
		WHILE @Pos > 0
		BEGIN
			SET @OrderID = LTRIM(RTRIM(LEFT(@CategoriesList, @Pos - 1)))
			IF @OrderID <> ''
			BEGIN
				INSERT INTO #ttbCategories (categoryId)
				VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion
			END
			SET @CategoriesList = RIGHT(@CategoriesList, LEN(@CategoriesList) - @Pos)
			SET @Pos = CHARINDEX(',', @CategoriesList, 1)

		END
	END

-- Now, take a look at what you created
SELECT * FROM #ttbCategories
DROP TABLE #ttbCategories

Table CRUDs

Under the Table

• 2 ounces Licor 43
• 3 ounces tequila
• 1 tablespoon fresh lemon juice
• 3 tablespoons fresh orange juice
• Dash bitters

Just a straight forward create table template.

CREATE TABLE sample.dbo.test_table
(col1 int NOT NULL,
col2 char(25),
col3 decimal(10,2),
col4 varchar(25),
col5 datetime,
PRIMARY KEY (col1),
UNIQUE (col2))

Update from one table to another

update t1
set col2 = t2.col2
from table1 t1
inner join table2 t2
on t1.col1=t1.col2
where Col3 >5

Delete using a join.

DELETE FROM MyTable1
WHERE EXISTS
(SELECT *
FROM MyTable2
WHERE MyTable2.keyColumn = MyTable1.keyColumn);

Copy from a table in one database to a new table.

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename

Lets say you want to replace "bob" with "Bob", you would do this.
"bob" would be what you want to search for and replace 'a'
and "Bob" is want you want to put in. 'b'

Here is how you would do it.

UPDATE YourTable
SET Column1 = REPLACE(Column1,'a','b')
WHERE Column1 LIKE '%a%'

Delete duplicate records.

UPDATE YourTable
SET Column1 = REPLACE(Column1,'a','b')
WHERE Column1 LIKE '%a%'

Or maybe you just want to find where there are duplicates, or filter out the unique records.

-- Find all records with only one occurence.
SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

-- Search for records with more than one occurance
SELECT email,
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Count Decimal Places

The Decimal Splash

• 1 1/2 oz. TY KU Liqueur
• 1 oz. apple juice
• Splash of sugar-free cranberry juice

I needed a way to see if a Latitude and Longitude value was long enough to be precise.  To be precise it needs to be at least 6 decimal places long so I needed to count the decimals.  

Here is what I found.

select latitude, longitude
from TableWithGeoLocations
where 

LEN(CAST(REVERSE(SUBSTRING(STR(latitude, 13, 11), CHARINDEX('.', STR(latitude, 13, 11)) + 1, 20)) AS decimal)) <= 6
or 
LEN(CAST(REVERSE(SUBSTRING(STR(longitude, 13, 11), CHARINDEX('.', STR(longitude, 13, 11)) + 1, 20)) AS decimal)) <= 6

This worked great to filter out the records that needed better geolocation data.

Transaction Try Catch Template

Try/Catch Margarita

2 oz Agave Nector
3 oz Lime Juice
Salt, course
2 1/2 oz Curacao Liqueur, blue
5 oz Silver Tequila

BEGIN TRANSACTION

BEGIN TRY
    -- Generate a constraint violation error.
    DECLARE @var INT
    SET @var = 'bob'

END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION
GO

---

SQL Break Points

Break Point Punch

3 ounces of pineapple juice
2 ounces of orange juice
1 ounce dark rum, plus 1/2 ounce to splash on top
1 ounce coconut rum
splash of grenadine
lime slice for garnish

So, maybe you have some code you want to run, and then code you want to skip over for later, and THEN code you want to run.  This can be easily chopped up using the below code.

-- Top of the test code
/**/set noexec off

-- (Stuff here will run )
-- This is the code that you want to run and check.

/**/set noexec on

-- (stuff here gets skipped )
-- This is the code that you have yet to get to


/**/set noexec off

-- (stuff here will run)
-- This is good for final temp table drops.

Sending email using SQL

Email on a bEach

3 oz vodka
1 oz peach schnapps
4 oz cranberry juice
4 oz orange juice

Simply put, you get the mail profile, and use a MSSQL procedure to send it.

/* Dynamically generates the profile name based on the server */
DECLARE @DBMailProfile VARCHAR (20)
SET @DBMailProfile = SUBSTRING(@@SERVERNAME,1,(CHARINDEX('\',@@SERVERNAME)-1)) + '_default'
SELECT @DBMailProfile


EXEC msdb.dbo.sp_send_dbmail
@profile_name = @DBMailProfile,
@recipients = 'bob@businessEmail.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message' ;

---

Here is an example where a query is passed.

---

-- if there is a return on the above select, insert the results and send an email showing the results.
    IF @@ROWCOUNT > 0
        BEGIN

            -- Email the results if a new record appears.
            EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'JobStatus',
            @recipients = 'DBAdmin@yourbusiness.com',
            @body = 'There are new records in the table.',
            @subject = 'New record that requires yoru attention.',
            @query = '
                SELECT * from tblWithData
                where isNew = 1
            '
        END

Re-Order on Order Field

The Orange Re-Order

• 1 c. tequila
• 1 c. freshly squeezed orange juice
• ½ c. Licor 43
• ¼ c. freshly squeezed lime juice
• splash of pure vanilla extract
• simple syrup or agave nectar, optional (to taste)

---

We will either put it on the end (append) which is indicated by an order of 0, or we will put it into an existing order, which will require incrementing everything after the given number.  And then reordering to get rid of holes.

SET @Order = 3

	-- If order number = 0, Generate actual order number to put it at the bottom
  IF @Order = 0
    BEGIN
      SELECT TOP 1 @Order = ATMPTC.ContentOrder + 1
      FROM cms.AscTblMountPointTblContent ATMPTC
      WHERE ATMPTC.MountPointId = @MountPointId
        -- some unique id that groups together items for ordering
      ORDER BY ATMPTC.ContentOrder DESC

      -- It is possible that this is the first record,
      -- in which case, the return would be 0.
      -- If thats the case, then set the number to 1

      IF @Order = 0
      BEGIN
        SET @order = 1
      END
  END


	-- If order number is not 0 make room for new ordernumber by incrementing everything after the order number
  ELSE
    BEGIN
      UPDATE cms.AscTblMountPointTblContent
      SET ContentOrder = ContentOrder + 1
      WHERE MountPointId = @MountPointId
      -- some unique id that groups together items for ordering
      AND contentOrder >= @order
    END

  -- Re-Order - Editting a ContentOrder number can cause a number gap so this cleans up a number gap.
SELECT ROW_NUMBER() OVER (order BY ATMPTC.ContentOrder ASC) AS NewRowId
, ATMPTC.ContentOrder
, ATMPTC.ContentId
FROM  CMS.AscTblMountPointTblContent ATMPTC
WHERE ATMPTC.MountPointId = @mountPointId
ORDER BY ATMPTC.ContentOrder ASC

Re-Order Method

The Re-Ordered Russian

3/4 ounce green Creme de Menthe
3/4 ounce Creme de Cacao
3 ounces milk
Swirl the martini glass with chocolate syrup
Shake over ice, strain into a martini glass
Garnish with chopped Andes mints or chocolate shavings

---

The goal is to re-order a list based on the new position and the old position.

-- incoming vars
DECLARE @InventoryPackageFeatureId varchar(36)
, @OrderNumber INT
, @InventoryPackageId VARCHAR(36)
, @oldOrderNumber INT

SET @InventoryPackageFeatureId = '4029D0E0-8188-4F2F-A9D5-1446BF0FDC3C'
SET @OrderNumber = 2


SELECT @oldOrderNumber = InventoryPackageFeatureOrder
FROM InventoryPackageFeatures
WHERE InventoryPackageFeatureId = @InventoryPackageFeatureId

-- Depending on the direction a record will go, the orderNumber may need to increment for the rest
-- of the processes to work right.
IF @oldOrderNumber < @OrderNumber
  BEGIN
    SET @OrderNumber = @OrderNumber + 1
  END


-- Get the InventoryPackageId
SELECT @InventoryPackageId = TIPF.InventoryPackageId
FROM InventoryPackageFeatures TIPF
WHERE TIPF.InventoryPackageFeatureId = @InventoryPackageFeatureId


-- Increase Order for all numbers greater than the one being changed.
UPDATE InventoryPackageFeatures
SET InventoryPackageFeatureOrder = InventoryPackageFeatureOrder + 1
WHERE InventoryPackageId = @InventoryPackageId
AND InventoryPackageFeatureOrder >= @orderNumber

  -- CHECK - At this point, all records above or equal to the target order number have been incremented
  SELECT * FROM InventoryPackageFeatures TIPF
  WHERE TIPF.InventoryPackageId = @InventoryPackageId
  ORDER BY TIPF.InventoryPackageFeatureOrder


-- Now that the order has made room for the new entry, set the new order number for the target record
UPDATE InventoryPackageFeatures
SET InventoryPackageFeatureOrder = @OrderNumber
WHERE InventoryPackageFeatureId = @InventoryPackageFeatureId

-- At this point, depending on how many are in the list, there may be a hole in the order.
-- particularly if your moving an existing the middle of order list


  -- CHECK - At this point, all records above or equal to the target order number have been incremented
  SELECT * FROM InventoryPackageFeatures TIPF
  WHERE TIPF.InventoryPackageId = @InventoryPackageId
  ORDER BY TIPF.InventoryPackageFeatureOrder

-- Use this new resulting order within a table update
UPDATE TIPF
    SET InventoryPackageFeatureOrder = TIPF2.newOrder
    FROM InventoryPackageFeatures TIPF
	  INNER JOIN (-- This creates an order number field
						SELECT ROW_NUMBER() OVER(ORDER BY TIPF1.InventoryPackageFeatureOrder) AS newOrder,
		        * FROM InventoryPackageFeatures TIPF1
            WHERE TIPF1.InventoryPackageId = @InventoryPackageId
						) TIPF2
		ON TIPF.InventoryPackageFeatureId = TIPF2.InventoryPackageFeatureId





  -- CHECK - At this point, all records above or equal to the target order number have been incremented
  SELECT * FROM InventoryPackageFeatures TIPF
  WHERE TIPF.InventoryPackageId = @InventoryPackageId
  ORDER BY TIPF.InventoryPackageFeatureOrder

Test Time Difference

Peppermint Spiral of Time

• 1 shot White Godiva liqueur
• 1 shot White Crème de Menthe
• 0.50 shot Green Crème de Menthe
• chocolate syrup

Sometimes we just need to know how long something takes.  This is what I use to test sections of code.

-- BEGIN TIMED TEST --
DECLARE @timeTest DATETIME
SET @timeTest = GETDATE()
-- BEGIN TIMED TEST --


-- Code to test for length of time


-- END TIMED TEST
SELECT DATEDIFF(millisecond, @timeTest, GETDATE()) AS [in Milliseconds]
, DATEDIFF(millisecond, @timeTest, GETDATE()) / 1000 AS [in Seconds]
-- END TIMED TEST

Try Catch

BEGIN TRY
	-- try / catch requires SQLServer 2005
	-- run your code here
END TRY
BEGIN CATCH
	PRINT 'Error Number: ' + str(error_number())
	PRINT 'Line Number: ' + str(error_line())
	PRINT error_message()
	-- handle error condition
END CATCH

Baileys Flaming Code

• 1 ½ oz Bailey’s Irish Cream
• 1 ½ oz Butterscotch Schnapps
• ¾ oz Goldschlager
• 1 tbsp 151 Rum
• 1 dash Cinnamon

DateDiff and its Parts

Because it helps to know the difference.

DATEDIFF ( datepart , startdate , enddate )

/*   datepart
year       yy, yyyy
quarter    qq, q
month      mm, m
dayofyear  dy, y
day        dd, d
week       wk, ww
hour       hh
minute     mi, n
second     ss, s
millisecond ms
microsecond mcs
nanosecond  ns
*/

SQL Pie Cocktail

• 1-1/2 oz. vodka or tequila
• 1 oz. Hiram Walker Butterscotch Schnapps
• 1-1/2 oz. apple cider
• 1 tsp. fresh lemon juice
• Apple slice for garnish

Date Formats

"It's A Date" Cider

• 6 oz. apple cider (heated)
• 2 oz. Tuaca liqueur
• whipped cream
• pinch of cinnamon and nutmeg
• 1 cinnamon stick

---

With credit to this blog for laying it out, I am picking out the parts that have been the most useful but by all means, check out this guys blog for more stuff.

http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

---

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)
                                        -- Oct  2 2008 11:01AM
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2008
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2008.10.02
SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) -- dd mon yyyy
SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy
SELECT convert(varchar, getdate(), 108) -- hh:mm:ss
SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
                                        -- Oct  2 2008 11:02:44:013AM
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) -- yyyymmdd
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm
                                        -- 02 Oct 2008 11:02:07:577
SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm
                                        -- 2008-10-02T10:52:47.513
-- SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd
SELECT convert(varchar(7), getdate(), 126)                 -- yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8)          -- mon yyyy

-- SQL format datetime
-- Default format: Oct 23 2006 10:40AM
SELECT [Default]=CONVERT(varchar,GETDATE(),100)

-- US-Style format: 10/23/2006
SELECT [US-Style]=CONVERT(char,GETDATE(),101)

-- ANSI format: 2006.10.23
SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

-- UK-Style format: 23/10/2006
SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

-- German format: 23.10.2006
SELECT [German]=CONVERT(varchar,GETDATE(),104)

-- ISO format: 20061023
SELECT ISO=CONVERT(varchar,GETDATE(),112)

-- ISO8601 format: 2008-10-23T19:20:16.003
SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)

-- Combining different style formats for date & time
-- Datetime formats
-- Datetime formats sql
DECLARE @Date DATETIME
SET @Date = '2015-12-22 03:51 PM'
SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)
-- Result: 12-22-2015  3:51PM

-- Microsoft SQL Server cast datetime to string
SELECT stringDateTime=CAST (getdate() as varchar)
-- Result: Dec 29 2012  3:47AM
----
-- SQL Server date and time functions overview
----
-- SQL Server CURRENT_TIMESTAMP function
-- SQL Server datetime functions
-- local NYC – EST – Eastern Standard Time zone
-- SQL DATEADD function – SQL DATEDIFF function
SELECT CURRENT_TIMESTAMP                        -- 2012-01-05 07:02:10.577
-- SQL Server DATEADD function
SELECT DATEADD(month,2,'2012-12-09')            -- 2013-02-09 00:00:00.000
-- SQL Server DATEDIFF function
SELECT DATEDIFF(day,'2012-12-09','2013-02-09')  -- 62
-- SQL Server DATENAME function
SELECT DATENAME(month,   '2012-12-09')          -- December
SELECT DATENAME(weekday, '2012-12-09')          -- Sunday
-- SQL Server DATEPART function
SELECT DATEPART(month, '2012-12-09')            -- 12
-- SQL Server DAY function
SELECT DAY('2012-12-09')                        -- 9
-- SQL Server GETDATE function
-- local NYC – EST – Eastern Standard Time zone
SELECT GETDATE()                                -- 2012-01-05 07:02:10.577
-- SQL Server GETUTCDATE function
-- London – Greenwich Mean Time
SELECT GETUTCDATE()                             -- 2012-01-05 12:02:10.577
-- SQL Server MONTH function
SELECT MONTH('2012-12-09')                      -- 12
-- SQL Server YEAR function
SELECT YEAR('2012-12-09')                       -- 2012