T-SQL job title generator

While writing a sample random function in using T-SQL Server, I have remembered, why not write a job title generator for T-SQL domain only. You might have seen so called bulls**t job title generator and similar, but this one is T-SQL SQL server specific.

So, why not come up with random, yet funny T-SQL job titles. And making it, I have to tell you, it was fun. And I was simply hitting that F5 button in SSMS, to get new job title generated and laugh out loud.

Following this laughter, I have created the following website:

http://tsqljobtitlegenerator.azurewebsites.net/index.php

that encapsulated the T-SQL code explained in this blog. You can either copy/paste the T-SQL code and run it in your SSMS or visit the T-sql job title generator web site.

The code

Staging some data, I have created just some random words, consisting of three parts. One is just IT slang/jargon set of words, the second is a list of interesting SQL Server technologies, functions, sets, and other words and last one is the more sophisticated – new age – words. So here is the data set:

DROP TABLE IF EXISTS roles;
GO

CREATE TABLE roles
(id int identity(1,1) NOT NULL
,title VARCHAR(100)
);


INSERT into roles(title)

SELECT 'Analyst'
UNION ALL SELECT 'Project Manager'
UNION ALL SELECT 'Expert'
UNION ALL SELECT 'Manager'
UNION ALL SELECT 'Person'
UNION ALL SELECT 'Artist'
UNION ALL SELECT 'Tamer'
UNION ALL SELECT 'Developer'
UNION ALL SELECT 'Administrator'
UNION ALL SELECT 'Technologist'
UNION ALL SELECT 'Uploader'
UNION ALL SELECT 'Downloader'
UNION ALL SELECT 'Sherpa'
UNION ALL SELECT 'Philosopher'
UNION ALL SELECT 'Designer'
UNION ALL SELECT 'Legend'
UNION ALL SELECT 'Evangelist'
UNION ALL SELECT 'Hero'
UNION ALL SELECT 'Guru'
UNION ALL SELECT 'Director'
UNION ALL SELECT 'Slayer'
UNION ALL SELECT 'Composer'
UNION ALL SELECT 'Reader'
UNION ALL SELECT 'Outliner'
UNION ALL SELECT 'Proof-reader'
UNION ALL SELECT 'Assistant'
UNION ALL SELECT 'Operator'
UNION ALL SELECT 'Coffee Maker'
UNION ALL SELECT 'Pizza re-heater'
UNION ALL SELECT 'Banana Cutter'
UNION ALL SELECT 'Tester'
UNION ALL SELECT 'Deep tester'
UNION ALL SELECT 'Backward tester'
UNION ALL SELECT 'Office hater'
UNION ALL SELECT 'Hater'
UNION ALL SELECT 'Warrior'
UNION ALL SELECT 'Junkie'
UNION ALL SELECT 'Wizard'
UNION ALL SELECT 'Leader'
UNION ALL SELECT 'King'
UNION ALL SELECT 'Approver'
UNION ALL SELECT 'Engineer'
UNION ALL SELECT 'Architect'
UNION ALL SELECT 'Rockstar'
UNION ALL SELECT 'Ninja'
UNION ALL SELECT 'Python Coder'
UNION ALL SELECT 'R and Python Hater'
UNION ALL SELECT 'C# Lover'
UNION ALL SELECT 'Java evangelist'
UNION ALL SELECT 'Ninja'
UNION ALL SELECT 'Captain'
UNION ALL SELECT 'Strategist'
UNION ALL SELECT 'Consultant'
UNION ALL SELECT 'Organizer'
UNION ALL SELECT 'Coffee spiller'
UNION ALL SELECT 'Endorser'
UNION ALL SELECT 'Cow'
UNION ALL SELECT 'Dog'
UNION ALL SELECT 'Cheever'
UNION ALL SELECT 'Lazy'
UNION ALL SELECT 'Fanboy'
UNION ALL SELECT 'Copy/Paster'
UNION ALL SELECT 'Researcher'
UNION ALL SELECT 'Cloner sheep'
UNION ALL SELECT 'Copy cat'
UNION ALL SELECT 'Shadower'
UNION ALL SELECT 'Guerilla'
UNION ALL SELECT 'Bullshiter'
UNION ALL SELECT 'Updater'
UNION ALL SELECT 'F5key presser'
UNION ALL SELECT 'Helper'
UNION ALL SELECT 'Knows everything'
UNION ALL SELECT 'Coffee Addict'
UNION ALL SELECT 'ASAP Doer'
UNION ALL SELECT 'Complicator'
UNION ALL SELECT 'Helpdesk dispatcher'
UNION ALL SELECT 'His Awesomeness'
UNION ALL SELECT 'Hers Awesomeness'
UNION ALL SELECT 'Advanced Copy/paster'
UNION ALL SELECT 'Stackover subscriber'
UNION ALL SELECT 'Over-engineering'


DROP TABLE IF EXISTS sqlstuff;

CREATE table sqlstuff
(ID INT IDENTITY(1,1) NOT NULL
,title VARCHAR(100)
)

INSERT INTO sqlstuff (title)
SELECT 'Cardinality Estimator'
UNION ALL SELECT 'Stored Procedure'
UNION ALL SELECT 'Data Masking'
UNION ALL SELECT 'High Availability'
UNION ALL SELECT 'Database Durability'
UNION ALL SELECT 'Memory Optimized table'
UNION ALL SELECT 'User Defined Function'
UNION ALL SELECT 'Stale Statistics'
UNION ALL SELECT 'Azure'
UNION ALL SELECT 'Power BI'
UNION ALL SELECT 'Machine Learning service'
UNION ALL SELECT 'Reporting Service'
UNION ALL SELECT 'Notification Service'
UNION ALL SELECT 'Analysis Service'
UNION ALL SELECT 'Clustered Index'
UNION ALL SELECT 'Database Snapshot'
UNION ALL SELECT 'Query Store'
UNION ALL SELECT 'DBCC Check'
UNION ALL SELECT 'B-Tree'
UNION ALL SELECT 'Query Optimizer'
UNION ALL SELECT 'Linked Server'
UNION ALL SELECT 'Trigger'
UNION ALL SELECT 'Replication'
UNION ALL SELECT 'Resource Governor'
UNION ALL SELECT 'Maintenance Plan'
UNION ALL SELECT 'Server Log'
UNION ALL SELECT 'SQL Server Agent'
UNION ALL SELECT 'Extended Event'
UNION ALL SELECT 'Profiler'
UNION ALL SELECT 'Server Role'
UNION ALL SELECT 'Auditing'
UNION ALL SELECT 'Credentials'
UNION ALL SELECT 'Database Backup'
UNION ALL SELECT 'Extended Properties'
UNION ALL SELECT 'Log Shipping'
UNION ALL SELECT 'Database Mirroring'
UNION ALL SELECT 'Availability Group'
UNION ALL SELECT 'PowerShell'
UNION ALL SELECT 'Parameter Sniffing'
UNION ALL SELECT 'ANSI Default'
UNION ALL SELECT 'Service Broker'
UNION ALL SELECT 'Compatibility Level'
UNION ALL SELECT 'Containment Type'
UNION ALL SELECT 'Recovery Model'
UNION ALL SELECT 'Collation'
UNION ALL SELECT 'Primary Filegroup'
UNION ALL SELECT 'Database Log Backup'
UNION ALL SELECT 'Bulk Insert'
UNION ALL SELECT 'Left Join'
UNION ALL SELECT 'U-SQL'
UNION ALL SELECT 'Azure SQL Server'
UNION ALL SELECT 'MicroContainer'
UNION ALL SELECT 'Pandas Data-frame'
UNION ALL SELECT 'Numpy Array'
UNION ALL SELECT 'Parametrization'
UNION ALL SELECT 'Slow Query'
UNION ALL SELECT 'Long running query'
UNION ALL SELECT 'Nested Query'
UNION ALL SELECT 'R ggplot library'
UNION ALL SELECT 'SARGable Query'
UNION ALL SELECT 'WHERE clause'
UNION ALL SELECT 'WHILE loop'
UNION ALL SELECT 'DELETE statement'
UNION ALL SELECT 'CI/CD'
UNION ALL SELECT 'SQL Server 6.0'
UNION ALL SELECT 'Execution Plan'
UNION ALL SELECT 'String Aggregation'
UNION ALL SELECT 'Dynamic View Management'
UNION ALL SELECT 'User Defined Table'
UNION ALL SELECT 'Fortran OLEDB'
UNION ALL SELECT 'SQL Server 2017'
UNION ALL SELECT 'Cumulative Updates'
UNION ALL SELECT 'Monitoring resources'
UNION ALL SELECT 'Activity Monitor'


DROP TABLE IF EXISTS Fancystuff

CREATE table Fancystuff
(ID INT IDENTITY(1,1) NOT NULL
,title VARCHAR(100)
)


INSERT INTO Fancystuff

SELECT 'Regional'
UNION ALL SELECT 'Group'
UNION ALL SELECT 'Only the best'
UNION ALL SELECT 'Insane'
UNION ALL SELECT 'Qualitative'
UNION ALL SELECT 'Virtuous'
UNION ALL SELECT 'Senior'
UNION ALL SELECT 'Junior'
UNION ALL SELECT 'In-House'
UNION ALL SELECT 'Outsourced'
UNION ALL SELECT 'Magnificent'
UNION ALL SELECT 'Evolutionary'
UNION ALL SELECT 'Customer'
UNION ALL SELECT 'Product'
UNION ALL SELECT 'Forward'
UNION ALL SELECT 'Future'
UNION ALL SELECT 'Dynamic'
UNION ALL SELECT 'Corporate'
UNION ALL SELECT 'Legacy'
UNION ALL SELECT 'Investor'
UNION ALL SELECT 'Direct'
UNION ALL SELECT 'International'
UNION ALL SELECT 'Over-seas'
UNION ALL SELECT 'Internal'
UNION ALL SELECT 'Human'
UNION ALL SELECT 'Creative'
UNION ALL SELECT 'Volunteer'
UNION ALL SELECT 'Lead'
UNION ALL SELECT '4 Stages of'
UNION ALL SELECT 'Complete'
UNION ALL SELECT 'Most Advanced'
UNION ALL SELECT 'State of the art'
UNION ALL SELECT 'Super high'
UNION ALL SELECT 'First Class'
UNION ALL SELECT 'Powerful'
UNION ALL SELECT 'Data'
UNION ALL SELECT 'Head of'
UNION ALL SELECT 'Master of'
UNION ALL SELECT 'Chief of'
UNION ALL SELECT 'Officer'
UNION ALL SELECT 'Lead'
UNION ALL SELECT 'Specialist'

Once I have this, I can have endless possibilities to merge all the datasets together.

With the following CTE, I will generate the random title:

----------------------------------
----- Generating T-SQL job title
-----------------------------------

;WITH Fancy
AS
(
SELECT TOP 1
title
FROM
Fancystuff
ORDER BY NEWID()
),
SQLy AS
(
SELECT TOP 1
title
FROM
sqlstuff
ORDER BY NEWID()
),
Roley AS
(SELECT TOP 1
title
FROM roles
ORDER BY NEWID()
)

SELECT 
CONCAT(f.title, ' ', s.title, ' ', r.title) AS TSQLJobGenerator

FROM fancy AS f
CROSS JOIN SQLy AS s
CROSS JOIN Roley AS r

 

I can’t help myself not laughing, as every time I hit run query, I get a funny combination.

Couple of my random titles – and some go beyond my imagination.

2018-09-15 15_30_29-SQLServer_Job_title_generator.sql - TOMAZK_MSSQLSERVER2017.JTG (TOMAZK_Tomaz (70

Or

2018-09-15 16_41_19-SQLServer_Job_title_generator.sql - TOMAZK_MSSQLSERVER2017.JTG (TOMAZK_Tomaz (51

Or

2018-09-15 16_43_03-SQLServer_Job_title_generator.sql - TOMAZK_MSSQLSERVER2017.JTG (TOMAZK_Tomaz (51

What is your favorite T-SQL job title that you get with this title generator?

 

And this one is taken from the website

2018-09-15 22_14_40-T-SQL Job Title Generator

 

As always, code is also available on GitHub.

Enjoy T-SQLing!

Advertisements
Tagged with: , , , , , ,
Posted in Uncategorized

T-SQL date and time manipulation

Manipulating date and time in T-SQL is a daily and very common task that every DBA, SQL Developer, BI Developer and data scientist will come across. And over the years, I have accumulated many of the simple date or/and time manipulation combinations of different functions, that it is time, to put them together.

Don’t expect to find here anything you haven’t used or seen – especially, if you are a long time T-SQL developer. The point is to have a post, that will have a lot of examples on date and time manipulation on one place. And by no means, this is not the definite list, but should be quite substantial and the code on Github repository will be update.

The list will be updated on my Github, and therefore this blogpost might not include all. In all of the following examples I will be using function GETDATE() to get the current datetime, unless the examples will have stored dates. Therefore, some of the examples or screen-prints will be different from yours.

2018-09-04 23_29_56-Window

I will be primarily using following T-SQL functions:

  • dateadd
  • datediff
  • year
  • month
  • day
  • format
  • cast
  • convert
  • parsename
  • and some string manipulation functions like: substring, replicate, left, right.

Starting with simple date

Most common functions are for sure extracting Year, Month and Day from your date.

SELECT
   GETDATE() AS RightNow
 ,YEAR(GETDATE()) AS Year_RightNow
 ,MONTH(GETDATE()) AS Month_RightNow
 ,DAY(GETDATE()) AS Day_RightNow

2018-09-04 23_22_39-Window

Deciding if the Year is a leap or non-leap year, following examples will help you get along:

-- Is Leap Year (we check for presence/existence of 29.Feb)
SELECT
    ISDATE('2019/02/29') AS CheckLeapYear -- if 1, date exists and this year is a leap year; if 0 date does not exists and is not leap year
   ,CASE WHEN (YEAR(GETDATE()) % 4 = 0 AND YEAR(GETDATE()) % 100 <> 0) 
  OR YEAR(GETDATE()) % 400 = 0 THEN 'Leap Year' ELSE 'Non Leap Year' END AS CheckLeapYear

with results as following:

2018-09-04 23_26_19-Window

So the first example will simply check if the 29th of February is valid for given year or not. But for a given SQL Statement the second variant with modulo for years is much easier for execution.

The following block of examples will simply give you the desired dates for a given time period. Mainly using functions DATENAME, DATEPART, DATEDIFF, CAST, CONVERT, FORMAT and DAY, MONTH, WEEK, YEAR, EOMONTH.

Simply run and examine the examples to get the hang of the examples.

-- Name of Days, Months
SELECT 
DATENAME(WEEKDAY, GETDATE()) AS [DayName]
,DATENAME(MONTH, GETDATE()) AS [MonthName]
,DATEPART(WEEK, GETDATE()) AS [WeekNumber]
,DATEPART(ISO_WEEK, GETDATE()) AS [ISO_WeekNumber]

-- Using Date format or FORMAT or CAST / CONVERT
SELECT
CAST(GETDATE() AS DATE) AS Date_RightNow
,FORMAT(GETDATE(), 'dd/MM/yyyy') AS DATE_dd_MM_yyyy
,FORMAT(GETDATE(), 'yyyy-MM-dd') AS DATE_yyyy_MM_dd
,FORMAT(GETDATE(), 'MM-dd') AS DATE_MM_dd
,FORMAT(GETDATE(), 'dd/MM/yyyy', 'en-US' ) AS DATE_US 
,FORMAT(GETDATE(), 'dd/MM/yyyy', 'sl-SI' ) AS DATE_SLO

-- Days
SELECT
DATEADD(DAY,DATEDIFF(DAY,1,GETDATE()),0) AS Yesterday
,DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0) AS Today
,DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),1) AS Tomorrow

-- Weeks
SELECT
DATEADD(WEEK,DATEDIFF(WEEK,7,GETDATE()),0) AS LastWeek_startOf
,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0) AS ThisWeek_startOf
,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),7) AS NextWeek_startOf

-- Months (works for all months; with 30 or 31 days, or with February)
SELECT
DATEADD(MONTH,DATEDIFF(MONTH,31,GETDATE()),0) AS LastMonth_startOf
,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AS ThisMonth_startOf
,DATEADD(MONTH,DATEDIFF(MONTH,-1,GETDATE()),0) AS NextMonth_startOf

SELECT 
EOMONTH(GETDATE()) AS CurrentMonthEnd
,EOMONTH(GETDATE(), -1) AS PreviousMonthEnd
,EOMONTH(GETDATE(), 1) AS NextMonthEnd

-- Years (works with leap years)
SELECT
DATEADD(year, DATEDIFF(year, 365, (GETDATE())), 0) AS LastYear_startOf
,DATEADD(year, DATEDIFF(year, 0, (GETDATE())), 0) AS ThisYear_startOf
,DATEADD(year, DATEDIFF(year, -1, (GETDATE())), 0) AS NextYear_startOf

And the results are as following:

2018-09-05 16_25_18-Window

I have added in all of the examples the extraction of the current, previous and next part of the date (week, day, month, year).

Getting dates in particular point-in-time

Sometimes one needs to set a particular time in the future / past and setting a particular point-in-time. Therefore, I Have added just a simple example for finding exact time from now.

SELECT
DATEADD(MONTH, DATEDIFF(MONTH, -1, (GETDATE())), DAY(GETDATE()-1)) AS OneMonthFromNow
,CAST(DATEADD(MONTH, 1, CAST(GETDATE() AS DATE)) AS DATETIME) AS OneMonthFromNow


SELECT
CAST(DATEADD(YEAR, 1, CAST(GETDATE() AS DATE)) AS DATETIME) AS OneYearFromNow
,DATEADD(DAY, DATEDIFF(DAY, 0, (GETDATE())), 365) AS OneYearFromNow

Finding point-in-time can also be used with for example also using BETWEEN function.

The result of the query is:

2018-09-05 16_30_02-Window

 

Differences in dates

When you are solving and calculating the differences in dates, I have added a simple “countdown” with the remaining number of days until end of month or year.

-- Number of days until ...
SELECT
	 (DATEDIFF(DAY, GETDATE(), DATEADD(MONTH, DATEDIFF(MONTH, -1, (GETDATE())), 0)))-1 AS NumberOfDAysUntilEndOfMonth
	,(DATEDIFF(DAY, GETDATE(), DATEADD(YEAR, DATEDIFF(YEAR, -1, (GETDATE())), 0)))-1 AS NumberOfDAysUntilEndOfYear

The results can be replaced with calculation of hours, quarters, years.

2018-09-05 16_32_46-Window

But more interesting and sometimes not that straightforward business case would be the calculation of working days – so finding the difference between two dates and calculating the number or working day.

The following example does this with simple query:

-- Number of business / working days between two dates
SET DATEFIRST 1;
DECLARE @dayFrom SMALLDATETIME = '20180901'
DECLARE @dayTO SMALLDATETIME = '20180905'

SELECT
(DATEDIFF(DAY, @dayFrom, @dayTO) + 1)-(DATEDIFF(WEEK, @dayFrom, @dayTO) * 2)-(CASE WHEN DATEPART(WEEKDAY, @dayFrom) = 7 THEN 1 ELSE 0 END)-(CASE WHEN DATEPART(WEEKDAY, @dayTO) = 6 THEN 1 ELSE 0 END) AS NumberOfWorkingDays

And since I have added that the week starts on Monday (SET DATEFIRST 1), and that 1st of September 2018 was Saturday, one can easily calculate this. But dateframe can be extended and problem is not that trivial anymore.

2018-09-05 16_36_59-Window

But making yet such trivial problem little bit more complicated, another typical business case is to calculate (sum/count) working hours between two dates. Now, the trickery can now go, from simple to over-complicated. Following example uses CTE (Common Table Expression) with recursions.

So the calculation is fairly simple:

-- Number of working hours between two dates
SET DATEFIRST 1;
DECLARE @dayFromDateTime SMALLDATETIME = '2018-09-01 12:33:11.245'
DECLARE @dayTODateTime SMALLDATETIME = '2018-09-05 09:33:32.256'
DECLARE @hourFrom INT = 8
DECLARE @hourTo INT = 16

;WITH cte
AS
(SELECT
DATEADD(MINUTE, -1, @dayFromDateTime) AS StartDate
,0 AS WorkDayFlag
,0 AS WorkHourFlag

UNION ALL

SELECT
DATEADD(MINUTE, 1, StartDate) AS StartDate
,CASE WHEN DATEPART(WEEKDAY, DATEADD(MINUTE, 1, StartDate)) IN (1,2,3,4,5) THEN 1 ELSE 0 END AS WorkDayFlag
,CASE WHEN DATEPART(HOUR, DATEADD(MINUTE, 1, StartDAte)) BETWEEN @hourFrom AND @hourTo-1 THEN 1 ELSE 0 END AS WorkHourFlag
FROM cte
WHERE
StartDate <= @dayTODateTime
)
SELECT
SUM(CASE WHEN WorkDayFlag = 1 AND WorkHourFlag = 1 THEN 1 ELSE 0 END) AS nofWorkingMinutes
,SUM(CASE WHEN WorkDayFlag = 1 AND WorkHourFlag = 1 THEN 1 ELSE 0 END)*1.0/60 AS nofWorkingHours
FROM cte 
OPTION (MAXRECURSION 10000)

 

I have even added the time – when business hours begin and end within a given time frame. Please note that I do not support the usage of MAXRECURSION OPTIONS but for small examples the query option is very effective.

The result of the query is:

2018-09-05 16_41_34-Window

If you will be using and running such calculations extensively, I strongly suggest to change the solution to UDF and adding some additional rules table with list of holidays, working days / weekends, setting up the start of the week, getting correct times (timezone, UTC) and any other business rules that should be applied.

Date intervals

Finding correct intervals is always part of  solving the business need. By querying the database, one will for sure come across the intervals as start and end date/time format.

Here it the example collection of date intervals between current week, month, quarter and script can be extended to different or broader (narrower) interval.

SELECT 
DATEADD(WEEK, DATEDIFF(WEEK, '19000101', GETDATE()), '18991231') as FromCurrentWeek
,DATEADD(WEEK, DATEDIFF(WEEK, '18991231', GETDATE()), '19000106') as ToCurrentWeek

SELECT 
CAST(DATEADD(DAY, 1, EOMONTH(GETDATE(),-1)) AS DATETIME) AS FromCurrentMonth
,CAST(EOMONTH(GETDATE()) AS DATETIME) AS ToCurrentMonth

SELECT 
DATEADD(QUARTER, DATEDIFF(QUARTER, 0, (GETDATE())), 0) as FromStartCurrentQuarter
,DATEADD(DAY,-1,DATEADD(QUARTER, DATEDIFF(QUARTER, -1, (GETDATE())), 0)) as ToEndCurrentQuarter

--Last days
SELECT 
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS LastDayOfPreviousMonth
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) AS LastDayOfCurrentMonth
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) AS LastDayOfNextMonth

And the results of the query based on the GETDATE() function.

2018-09-05 16_46_57-Window

werwerwe

Support Date functions

With following couple of examples, you can set your current session settings, e.g.: language, start of the week (DATEFIRST), dateformat (DATEFORMAT) and based on these settings, the behaviour of inserts or selects against any datetime data types can be altered, controlled or changed.

-- Set language
SET LANGUAGE us_english;
SELECT DATENAME(WEEKDAY, '20190904') [US_English];
 
SET LANGUAGE Slovenian;
SELECT DATENAME(WEEKDAY, '20180904') [Slovenian];

-- Starting week with day
SET LANGUAGE Slovenian;  
SELECT @@DATEFIRST AS weekStart;  

SET LANGUAGE us_english;  
SELECT @@DATEFIRST AS WeekStart;  

-- datefirst or @@datefirst
SET DATEFIRST 1; -- week starts with monday

-- Example with  September 5th, 2018
-- Possible formats:  mdy, dmy, ymd, ydm, myd, and dym. 
DROP TABLE IF EXISTS #dateformats
CREATE TABLE #dateformats (dd SMALLDATETIME ,ddFormat VARCHAR(10))

SET DATEFORMAT mdy;
INSERT INTO #dateformats  VALUES ('09/06/2018', 'mdy');
SET DATEFORMAT ymd;
INSERT INTO #dateformats  VALUES ('2018/09/07', 'ymd');
SET DATEFORMAT dmy;
INSERT INTO #dateformats  VALUES ('08/09/2018', 'ymd');

SELECT 
	 dd AS InsertedDate
	,ddFormat AS InsertedDateFormat
FROM #dateformats

With SET DATEFORMAT, example shows, that if datetime format data types come in different format year-month-day or day-month-year, by simple controlling of SET DATEFORMAT, date formats can be handled easier, without any futher CASTing or CONVERTing.

2018-09-06 14_53_40-Date_Time_manipulations.sql - geningsql07.ETL (CORP_tomazk (115)) - Microsoft SQ

Starting Simple Time

Manipulating time, as part of datetime data type or a standalone time, following examples will help you understand working with time. Simple functions as HOUR, MINUTE, SECOND or MILLISECOND will extract part of the time for any further use.

Also CONVERT and CAST are used frequently for converting time to desired format.

SELECT
  GETDATE() AS RightNow
 ,DATEPART(HOUR,GETDATE()) AS Hour_RightNow
 ,DATEPART(MINUTE, GETDATE()) AS Minute_RightNow
 ,DATEPART(SECOND, GETDATE()) AS Second_RightNow
 ,DATEPART(MILLISECOND, GETDATE()) AS MilliSecond_RightNow

-- Using Date format or FORMAT or CAST / CONVERT To get only time
SELECT
  CAST(GETDATE() AS TIME) AS TIME_RightNow
 ,CONVERT(CHAR,GETDATE(),14) AS TIME_withConvert14
 ,CONVERT(TIME, GETDATE()) AS Time_ConvertONly
 ,CAST(CONVERT(CHAR(8),GETDATE(),114) AS DATETIME) AS Time_WithConvert114_AandDate
 ,CONVERT(VARCHAR(12),GETDATE(),114) AS Time_standardFormat
 ,GETUTCDATE() AS TimeUTC_RightNow
 ,SYSDATETIME() AS SystemDateTime
 ,CONVERT(VARCHAR(10), CAST(GETDATE() AS TIME(0)), 100) AS SimpleAM_PM

SELECT
  FORMAT(cast(GETDATE() AS TIME), N'hh\.mm') AS timeFormatDot
 ,FORMAT(cast(GETDATE() AS TIME), N'hh\:mm') AS timeFormatColon
 ,FORMAT(CAST(GETDATE() AS TIME), 'hh\:mm\:ss') AS standardTimeFormat

 

Query outputs the following results and using function FORMAT might be fast and easy handling time formats for desired outputs.

2018-09-05 16_52_51-Window

Converting to time formats

Time can be stored in different type formats, which might present a problem, but firstly, let’s take a look into integer.

Following example shows how to manipulate the time formats, when time parts are stored in integers.

-- seconds/milliseconds to time format
DECLARE @MilliSec INT = 55433
DECLARE @Sec INT = 532

SELECT 
CONVERT(VARCHAR(10),DATEADD(ms,@MilliSec,0),114) AS MilliSecToTime --format hh:mm:ss:nnn
,CONVERT(VARCHAR(10),DATEADD(s,@Sec,0),114) AS SecToTime --format hh:mm:ss:nnn

-- Converting seconds to time readable format
DECLARE @seconds INT = 10000

SELECT
@seconds AS NumberOfSeconds
,@seconds/86400 AS NumberOfDays
,@seconds/3600 As NumberOfHours
,@seconds/60 AS NumberMinutes
,CONVERT(VARCHAR, DATEADD(ms, @seconds * 1000, 0), 114) AS FormatedTime

Both examples are converting integers into time formats for readable and usable form.

2018-09-05 16_53_37-Window

VARCHAR / DECIMAL/FLOAT to time formats

Oh. Yes. This happens more often than you think. Having datetime information stored in DECIMAL, FLOAT is not that infrequent case. Let us not go into the bad designs, but rather propose the idea, on how to tackle such problems.

With decimal data type:

-- Using Decimal data type
DECLARE @test_DTY TABLE
(id int
,KA2_DATE decimal (8,0)
,KA2_TIME decimal (6,0)
)

INSERT INTO @test_DTY
SELECT 1, 20180905, 110951
UNION ALL SELECT 2, 20180905, 113407
UNION ALL SELECT 3, 20180905, 063407

SELECT
id
,KA2_DATE AS OriginalDate
,KA2_TIME AS OriginalDate --Note leading zeros will not be presented as this is decimal data type

,RIGHT(CAST(KA2_DATE AS VARCHAR(8)),2) + '/' + SUBSTRING(CAST(KA2_DATE AS VARCHAR(8)),5,2) + '/' + LEFT(CAST(KA2_DATE AS VARCHAR(8)),4) AS DateFormated_dd_MM_yyyy
,LEFT(CAST(REPLICATE('0', 6 - LEN(CAST(KA2_TIME AS VARCHAR(6)))) AS VARCHAR(1)) + CAST(CAST(KA2_TIME AS VARCHAR(6)) AS VARCHAR(6)),2) + ':' 
+ SUBSTRING(CAST(REPLICATE('0', 6 - LEN(CAST(KA2_TIME AS VARCHAR(6)))) AS VARCHAR(1)) + CAST(CAST(KA2_TIME AS VARCHAR(6)) AS VARCHAR(6)),3,2) + ':' 
+ RIGHT(CAST(REPLICATE('0', 6 - LEN(CAST(KA2_TIME AS VARCHAR(6)))) AS VARCHAR(1)) + CAST(CAST(KA2_TIME AS VARCHAR(6)) AS VARCHAR(6)),2) AS Time_formatted_hh_mm_ss
FROM @test_DTY

you can see the results are presented in more readable and easy to handle formats for any further use with your T-SQL query.

2018-09-05 22_23_06-Window

Another frequent, yet uncomfortable data type is VARCHAR format. With use of PARSENAME and other string manipulation functions (SUBSTRING, CHARINDEX, PATINDEX, …), converting to datetime formats can be done. Even though, it is not performance efficient, sometimes you just can not change the table(s) design.

-- Using String data Type (Varchar)
DECLARE @temp TABLE (Ddate VARCHAR(20))

INSERT INTO @temp (Ddate)
SELECT '3.11.2017 14:55:53' 
UNION ALL SELECT '12.11.2018 22:39:49' 
UNION ALL SELECT '12.8.2018 22:39:49' 
UNION ALL SELECT '2.3.2018 22:39:49' 
UNION ALL SELECT '12.8.2018 7:39:49' 
UNION ALL SELECT '12.8.2018 7:09:49'

SELECT 
Ddate AS OriginalDateFormatInVarchar
,PARSENAME(Ddate,3) AS DayFromVarchar
,PARSENAME(Ddate,2) AS MonthFromVarchar
,LEFT(PARSENAME(Ddate,1),4) AS YearFromVarchar
,REPLACE(SUBSTRING(Ddate, CHARINDEX(' ', Ddate), CHARINDEX(':', Ddate)),':','') AS TimeFromVarchar

,CAST(CAST(LEFT(PARSENAME(Ddate,1),4) AS CHAR(4)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,2) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,2) AS VARCHAR(2)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,3) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,3) AS VARCHAR(2)) AS INT) AS DateFormattedFromVarchar --Leading zeros corrected!

,CAST(CAST(LEFT(PARSENAME(Ddate,1),4) AS CHAR(4)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,2) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,2) AS VARCHAR(2)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,3) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,3) AS VARCHAR(2)) AS SMALLDATETIME) AS DateFormattedFromVarcharToSmallDateTime

,CAST(CAST(left(PARSENAME(Ddate,1),4) AS CHAR(4)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,2) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,2) AS VARCHAR(2)) + REPLICATE ('0', 2 - LEN(CAST(PARSENAME(Ddate,3) AS VARCHAR(2)))) + 
CAST(PARSENAME(Ddate,3) AS VARCHAR(2)) + REPLICATE('0', 6 - LEN(
CAST(LTRIM(RTRIM(REPLACE(SUBSTRING(Ddate, CHARINDEX(' ', Ddate), CHARINDEX(':', Ddate)),':',''))) AS VARCHAR(6)))) +
CAST(LTRIM(RTRIM(REPLACE(SUBSTRING(Ddate, CHARINDEX(' ', Ddate), CHARINDEX(':', Ddate)),':',''))) AS VARCHAR(6))
AS VARCHAR(14)) AS DateAndTimeFormatFromVarchar

FROM @temp
ORDER BY 8 ASC

Using the query, the end result is time (and date) represented in different formats for any further use with T-SQL query or functions for datetime formats, like between and logical operators.

2018-09-05 22_23_17-Window

Fiscal Years (#Update 1; Sept. 6th,2018)

For any given time (using GETDATE()), getting the start and end time of the fiscal year is another important boundary for and PnL or financial reports.

-- a) it start on April 1 and runs for 364,25 days until March 31 of next year 
-- b) it starts on October 1 and runs for 364,25 days until September 30 of next year

SELECT
-- Fiscal Year Apr-Mar
CASE WHEN MONTH(GETDATE()) >= 4 THEN YEAR(GETDATE()) ELSE DATEPART(year,DATEADD(Year,-1,GETDATE())) END AS StartOfFiscalYearYear
,CASE WHEN MONTH(GETDATE()) >= 4 THEN YEAR(GETDATE())+1 ELSE YEAR(GETDATE()) END AS EndOfFiscalYearYear
,CASE WHEN MONTH(GETDATE()) >= 4 THEN CAST(CONCAT(CAST(YEAR(GETDATE()) AS VARCHAR),'/04/01') AS DATE) ELSE CAST(CONCAT(CAST(DATEPART(year,DATEADD(Year,-1,GETDATE())) AS VARCHAR),'/04/01') AS DATE) END AS StartOfFiscalYearDateTime
,CASE WHEN MONTH(GETDATE()) >= 4 THEN CAST(CONCAT(CAST(YEAR(dateadd(year,1,getdate())) AS VARCHAR),'/03/31') AS DATE) ELSE CAST(CONCAT(CAST(YEAR(GETDATE()) AS VARCHAR),'/03/31') AS DATE) END AS EndOfFiscalYearDateTime
-- Fiscal Year Oct-Sep
,CASE WHEN MONTH(GETDATE()) >= 10 THEN YEAR(GETDATE()) ELSE DATEPART(year,DATEADD(Year,-1,GETDATE())) END AS StartOfFiscalYearYear
,CASE WHEN MONTH(GETDATE()) >= 10 THEN YEAR(GETDATE())+1 ELSE YEAR(GETDATE()) END AS EndOfFiscalYearYear
,CASE WHEN MONTH(GETDATE()) >= 10 THEN CAST(CONCAT(CAST(YEAR(GETDATE()) AS VARCHAR),'/10/01') AS DATE) ELSE CAST(CONCAT(CAST(DATEPART(year,DATEADD(Year,-1,GETDATE())) AS VARCHAR),'/10/01') AS DATE) END AS StartOfFiscalYearDateTime
,CASE WHEN MONTH(GETDATE()) >= 10 THEN CAST(CONCAT(CAST(YEAR(dateadd(year,1,getdate())) AS VARCHAR),'/09/30') AS DATE) ELSE CAST(CONCAT(CAST(YEAR(GETDATE()) AS VARCHAR),'/09/30') AS DATE) END AS EndOfFiscalYearDateTime

Using two different boundaries for fiscal year and given the fact that today is September 6th, 2018, the result is as following:

2018-09-07 21_19_46-Window

Thank you for the comments and suggestions.

 

As always, all the code is available at the Github.

Happy T-SQLing.

Tagged with: , , , , , , ,
Posted in Uncategorized

Top SQL Server bloggers of 2018

SQLShack community web-site  by company ApexSQL have announced on 1st of September 2018 the list of SQL Server bloggers.

ApexSQL_Logo

The list of top SQL Server bloggers is absolutely worth checking for entry-level learning, for troubleshooting and for advanced topics. There are also many other top list of bloggers – PowerShell and others.

2018-09-04 15_25_32-Top SQL Server bloggers of 2017

 

Based on ALEXA ranking the list for sorted by relevance. My blog also managed to get on the list, humbled and honoured to be recognised for my community work.  Congrats to all SQL fellows and SQL family and all the SQL lovers. Thank you also to SQLShack community for doing this.

Top-30-2018

Happy Reading!

Tagged with: , , ,
Posted in Uncategorized

SQL Server 2017 Machine Learning services with R book

Yes, I am finally blogging this. 🙂

This blog post is slighty different, since it brings you the tittle of the book, that my dear friend Julie Koesmarno (blog | twitter) and I have written in and it was published in March 2018 at Packt Publishing.

B06407_cover_0

Book covers the aspect of the R Machine Learning services available in Microsoft SQL Server 2017 (and 2016), how to start, handle and operationalise R code, deploy and manage your predictive models  and how to bring the complete solution to your enterprise environment. Exploring the CD/CI,  diving into examples supporting RevoScaleR algorithms, bringing closer the data science to database administrators and data analysts.

More specifically, content of the book is following (as noted in table of content):

1: Introduction to R and SQL Server
2: Overview of Microsoft Machine Learning Server and SQL Server
3: Managing Machine Learning Services for SQL Server 2017 and R
4: Data Exploration and Data Visualization
5: RevoScaleR Package
6: Predictive Modeling
7: Operationalizing R Code
8: Deploying, Managing, and Monitoring Database Solutions containing R Code
9: Machine Learning Services with R for DBAs
10: R and SQL Server 2016/2017 Features Extended

My dear friend, co-author and long time SQL Server community dedicated tech and data science lover, Julie and myself, we had great time working on this book, sharing the code, the ideas and collaborating on what was the great end product. Thank you, Julie.

I would also like to thank all the people involved, with their help, expertise, inspirations, people at the Packt Publishing, to Hamish Watson and also a special thanks, to you, Marlon Ribunal (blog | twitter), for your reviews and comments in the time of the writing and your review  and to you, dear David Wentzel (website| linkedin ) for your chapter comments and your review.

Finally, thank you Microsoft SQL Server community, SQL friends and SQL family, R community and R Consortium, and the Revolution Analytics community, gather and led by David Smith (twitter). Not only did this concept of R in Microsoft SQL Server, but also the intersection of technologies brought together so many beautiful people, minds and ideas, that will in future time help so many business and industries world-wide.

Much appreciated!

Book is available on Amazon  or you can get your copy at the Packt.

Happy reading and coding!

Tagged with: , , , , , , , , ,
Posted in Uncategorized

Filling [propagading] empty values with last nonNull value using T-SQL

So you have NULL values in your SQL Server table and you want to populate those NULL values with the last non-NULL value, based on a particular order. Once you have only one NULL values encapsulated between two populated values, there are quick and fast solutions. But what if you find a larger gap of NULL values and you want to populate these values as well?

Let’s make a small example to visualize the problem. Connect to your favorite SQL Server database and fun this:

DROP TABLE IF EXISTS testNonNull;
GO

CREATE TABLE testNonNull
(id INT IDENTITY(1,1) NOT NULL
,valFluct TINYINT
,valIncrem TINYINT
,dateD SMALLDATETIME);
GO

INSERT INTO testNonNull (valFluct,valIncrem, dateD)
SELECT 10,10, '2018/08/01'
UNION ALL SELECT 14,14, '2018/08/02'
UNION ALL SELECT NULL,NULL, '2018/08/03'
UNION ALL SELECT 9,16, '2018/08/04'
UNION ALL SELECT NULL,NULL, '2018/08/05'
UNION ALL SELECT NULL,NULL, '2018/08/06'
UNION ALL SELECT NULL,NULL, '2018/08/07'
UNION ALL SELECT 32,18, '2018/08/08'
UNION ALL SELECT 45,22, '2018/08/09'
UNION ALL SELECT NULL,NULL, '2018/08/10'
UNION ALL SELECT NULL,NULL, '2018/08/11'
UNION ALL SELECT 14,25, '2018/08/12'
UNION ALL SELECT NULL,NULL, '2018/08/13';
GO

And once you run a simple SELECT statement against this testNonNULL table, you will see the gaps:

2018-08-05 19_22_19-SQLQuery2.sql - TOMAZK_MSSQLSERVER2017.test (TOMAZK_Tomaz (60))_ - Microsoft SQL

A simple solution using OVER() clause for the ROWS UNBOUNDED PRECEDING would be a easy solution, but once applied, you quickly realize, that it is working correctly only for incremental values, eg.:

SELECT
dateD 
,valFluct AS FluctuatingValue
,MAX(valFluct) OVER 
  (ORDER BY dateD ASC ROWS UNBOUNDED PRECEDING) AS nonNULL_FluctuatingValues
,valIncrem
,MAX(valIncrem) OVER 
   (ORDER BY dateD ASC ROWS UNBOUNDED PRECEDING) AS nonNULL_IncrementalValues
FROM testNonNull
ORDER BY dateD

All works fine for the Incremental values, where as the Fluctuating values are not populated correctly:

2018-08-05 19_27_50-SQLQuery2.sql - TOMAZK_MSSQLSERVER2017.test (TOMAZK_Tomaz (60))_ - Microsoft SQL

So the query returns wrong values for the FluctuatingValue column two times, every time the lower number of currently maximum running number appears – and this is absolutely not correct. On the other hand, Incremental values (since they increment with every new value), are populated correctly.

In order to keep the simplicity of the window function and not overdo with complicated T-SQL, the following solution is genius, yet intriguing. Converting the value and column – upon which the sort is being done – to binary to extract the max value for the current window, will satisfy both the incremental and fluctuating type of values. The original post was introduced by Itzik Ben-Gan in SQL Magazine.

Simple substitution will do the trick:

SELECT
dateD 
,valFluct AS FluctuatingValue
,CAST(SUBSTRING(MAX(CAST(dateD AS BINARY(32)) + 
     CAST(valFluct AS BINARY(12))) 
     OVER( ORDER BY dateD ASC ROWS UNBOUNDED PRECEDING ),33,12) AS INT) 
         AS nonNULL_FluctuatingValues
,valIncrem
,MAX(valIncrem) OVER 
 (ORDER BY dateD ASC ROWS UNBOUNDED PRECEDING) AS nonNULL_IncrementalValues
FROM testNonNull
ORDER BY dateD

And the return results are what you would want for the values to be filled in with.

2018-08-05 19_41_59-SQLQuery2.sql - TOMAZK_MSSQLSERVER2017.test (TOMAZK_Tomaz (60))_ - Microsoft SQL

Enjoy!

 

Tagged with: , , , , ,
Posted in Uncategorized

Real-time data visualization using R and data extracting from SQL Server

In the previous post, I have showed how to visualize near real-time data using Python and Dash module.  And it is time to see one of the many ways, how to do it in R. This time, I will not use any additional frames for visualization, like shiny, plotly or any others others, but will simply use base R functions and RODBC package to extract data from SQL Server.

Extracting data from SQL Server will and simulating inserts in SQL Server table will primarily simulate the near real-time data. If you have followed the previous post, you will notice that I am using same T-SQL table and query to extract real-time data.

First, we will create a sample table in SQL Server and populate it with some sample data:

CREATE DATABASE Test;
GO

USE Test;
GO

CREATE TABLE dbo.LiveStatsFromSQLServer
(ID INT IDENTITY(1,1)
,Num tinyint NOT NULL)

And populate it with some sample data:

-- Insert some test data
INSERT INTO dbo.LiveStatsFromSQLServer(num)
SELECT ABS(CHECKSUM(NewId())) % 14
GO 10

Now, that we have SQL foundations set up, let’s focus on R code.

First we set the environment variable and the RODBC library:

library(RODBC)
# create env for storing the variables/data frames between the functions
assign("getREnvironment", new.env(), envir = .GlobalEnv)

We will generate a function for extracting data from SQL Server and storing it in environment data.frame variable:

# Function to read data from SQL Server
getSQLServerData <- function()
{
#extract environment settings for storing data
getREnvironment <- get("getREnvironment", envir = .GlobalEnv, mode = "environment")
#get the SQL Server data
con <- odbcDriverConnect('driver={SQL Server};
                         server=TOMAZK\\MSSQLSERVER2017;
                         database=test;trusted_connection=true')
db_df <- sqlQuery(con, 'SELECT 
                         TOP 20 id
                           ,num 
                        FROM LiveStatsFromSQLServer ORDER BY id DESC')
close(con)
#overwrite existing data with new data
df_overwrite <- db_df
getREnvironment$db_df <- data.frame(df_overwrite)
try(assign("getREnvironment", getREnvironment, envir = .GlobalEnv))
invisible() #do not print the results
}

 

Once we have this function registered, we can now create a small for loop that will update the plot with newly fetched data from SQL Server:

# Plot graph 
n=1000 #nof iterations
windowQuery=20 # syncronised with TOP clause in SELECT statement
for (i in 1:(n-windowQuery)) {
  flush.console()
  getSQLServerData()
  getREnvironment <- get("getREnvironment", envir = .GlobalEnv, mode = "environment")
  data <- getREnvironment$db_df
  plot( data$id, data$num, type='l',main='Realtime data from SQL Server')
  Sys.sleep(0.5)
}

 

Once we run the complete R code, we need to trigger and run also the new inserts in SQL Server Management studio:

-- Do some inserts to mimic the data stream
INSERT INTO dbo.LiveStatsFromSQLServer(num)
SELECT ABS(CHECKSUM(NewId())) % 14
WAITFOR DELAY '00:00:00.500'
GO 100

Once we do this, we can observe the realtime data from SQL Server being plotted in R environment (R Studio).

2018-07-22 20_51_00-Window

As always, complete code is available at Github.

Happy R-coding! 🙂

Tagged with: , , , , , ,
Posted in Uncategorized

Using Python Pandas dataframe to read and insert data to Microsoft SQL Server

In the SQL Server Management Studio (SSMS), the ease of using external procedure sp_execute_external_script has been (and still will be) discussed many times. But the reason for this short blog post is the fact that, changing Python environments using Conda package/module management within Microsoft SQL Server (Services), is literally impossible. Scenarios, where you want to build  a larger set of modules (packages) but are impossible to be compatible with your SQL Server or Conda, then you would need to set up a new virtual environment and start using Python from there.

Communicating with database to load the data into different python environment should not be a problem. Python Pandas module is an easy way to store dataset in a table-like format, called dataframe. Pandas is very powerful python package for handling data structures and doing data analysis.

pandas_logo

 

Loading data from SQL Server to Python pandas dataframe

This underlying task is something that every data analyst, data engineer, statistician and data scientist will be using in everyday work. Extracting data from Microsoft SQL Server database using SQL query and storing it in pandas (or numpy) objects.

With following code:

## From SQL to DataFrame Pandas
import pandas as pd
import pyodbc

sql_conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};
                            SERVER=SQLSERVER2017;
                            DATABASE=Adventureworks;
                            Trusted_Connection=yes') 
query = "SELECT [BusinessEntityID],[FirstName],[LastName],
                 [PostalCode],[City] FROM [Sales].[vSalesPerson]"
df = pd.read_sql(query, sql_conn)

df.head(3)

 

you will get the first three rows of the result:

2018-07-15 09_23_00-Spyder (Python 3.6)

Make sure that you configure the SERVER and DATABASE as well as the credentials to your needs.  If you are running older version of SQL Server, you will need to change the driver configuration as well.

Inserting data from Python pandas dataframe to SQL Server

Once you have the results in Python calculated, there would be case where the results would be needed to inserted back to SQL Server database. In this case, I will use already stored data in Pandas dataframe and just inserted the data back to SQL Server.

First, create a table in SQL Server for data to be stored:

USE AdventureWorks;
GO
DROP TABLE IF EXISTS vSalesPerson_test;
GO
CREATE TABLE vSalesPerson_test(
[BusinessEntityID] INT
,[FirstName] VARCHAR(50)
,[LastName] VARCHAR(100))

After that, just simply run the following Python code:

connStr = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};
                            SERVER=SQLSERVER2017;
                            DATABASE=Adventureworks;
                            Trusted_Connection=yes')
cursor = connStr.cursor()

for index,row in df.iterrows():
.. cursor.execute("INSERT INTO dbo.vSalesPerson_test([BusinessEntityID],
                        [FirstName],[LastName]) 
                         values (?, ?,?)", row['BusinessEntityID'], 
                                           row['FirstName'], 
                                           row['LastName']) 
.. connStr.commit()
cursor.close()
connStr.close()
*Python indentation might be broken;  use github file.

And the data will be inserted in SQL Server table:

2018-07-15 09_34_57-Window

As always, sample code is available at Github.

Happy coding! 🙂

Tagged with: , , , , , ,
Posted in Uncategorized
Categories
Follow TomazTsql on WordPress.com
Programs I Use
Programs I Use
Programs I Use
Rdeči Noski – Charity

Rdeči noski

100% of donations made here go to charity, no deductions, no fees. For CLOWNDOCTORS - encouraging more joy and happiness to children staying in hospitals (http://www.rednoses.eu/red-noses-organisations/slovenia/)

2 EUR

Top SQL Server Bloggers 2018
Discover

A daily selection of the best content published on WordPress, collected for you by humans who love to read.

Revolutions

Tomaz doing BI and DEV with SQL Server and R

tenbulls.co.uk

attaining enlightenment with sql server, .net, biztalk, windows and linux

SQL DBA with A Beard

He's a SQL DBA and he has a beard

DB NewsFeed

Matan Yungman's SQL Server blog

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Clocksmith Games

We make games we love to play

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R

Search Msdn

Tomaz doing BI and DEV with SQL Server and R

R-bloggers

Tomaz doing BI and DEV with SQL Server and R

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World

R-bloggers

R news and tutorials contributed by (750) R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

SQL Server, Azure and .net in a nutshell :D

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.

William Durkin

William Durkin a blog on SQL Server, Replication, Performance Tuning and whatever else.