Load data to SQL server using python



    Loading of bulk data to SQL using pyodbc is very slow, To resolve this issue there is a new package introduced in python is called bcpy .

What is it?

This package is a wrapper for Microsoft's SQL Server bcp utility. Current database drivers available in Python are not fast enough for transferring millions of records .This is the fastest way of loading data from CSV to SQL server using python

1. Install the python package called bcpy by running below command.

pip install bcpy

2. Download and install the suitable ODBC driver from Microsoft site.

https://www.microsoft.com/en-us/download/details.aspx?id=56567

3. Download and install BCP Utility from Microsoft site.

https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017#T

The following example creates a DataFrame with 100 rows and 4 columns populated with random data and then it sends it to SQL Server.

import bcpy
import numpy as np
import pandas as pd

sql_Conn = {
'server': 'Server',
'database': 'Database',
'username': 'UserName',
'password': 'Password'
}

table_name = 'SampleTable'
df = pd.DataFrame(np.random.randint(-100, 100, size=(100, 4)),columns=list('ABCD'))
bdf = bcpy.DataFrame(df)
exec_sql = bcpy.SqlTable(sql_Conn, table=table_name)
bdf.to_sql(exec_sql)

Using Json In SQL Server 2016

      JSON (JavaScript Object Notation) is a lightweight data interchange format and it is set of data with key value pairs. This is awesome feature introduced in SQL 2016. Some built in function help to handle Json data in SQL server 2016. We can use Json data instead of table type in procedures to pass set of data.

Below are the list of built in function introduced in SQL 2016.

  • ISJSON
  • JSON_VALUE
  • JSON_QUERY
  • JSON_MODIFY
  • FOR JSON
  • OPENJSON

ISJSON – This will check inputted string is Json formatted or not,If it is Json return 1 else 0.

DECLARE @JSONData AS NVARCHAR(MAX)


SET @JSONData = N'{ "FirstName":"Michael", "LastName":"Jackson" }'

SELECT ISJSON(@JSONData) AS IsJson

JSON_VALUE – This will help to extract each column value from Json String, Or we can extract Json data like a table.

DECLARE @JSONData AS NVARCHAR(MAX)
SET @JSONData = N'{ 
    "CustInfo":{ 
        "FirstName":"
Michael",
        "LastName":"
Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ]
    }
}'

SELECT
JSON_VALUE(@JSONData,'$.CustInfo.FirstName') AS FirstName,
JSON_VALUE(@JSONData,'$.CustInfo.LastName') AS LastName,
JSON_VALUE(@JSONData,'$.CustInfo.CustId') AS CustomerId,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[0].AddressType') AS AddressType,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[0].City') AS City,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[0].State') AS State,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[1].AddressType') AS AddressType2,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[1].City') AS City2,
JSON_VALUE(@JSONData,'$.CustInfo.Addresses[1].State') AS State2







JSON_QUERY – This is used to extract array of Json data or array of Json string.

DECLARE @JSONData AS NVARCHAR(MAX)

SET @JSONData = N'{ 

    "CustInfo":{ 
       
"FirstName":"Michael",
        "LastName":"
Jackson", 
        "CustId":"10002569", 
        "Addresses":[ 
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ] 
    } 
}' 

SELECT JSON_QUERY(@JSONData,'$.CustInfo.Addresses')

UNION ALL

SELECT JSON_QUERY(@JSONData,'$.CustInfo.Addresses[1]')








JSON_MODIFY – This help to modify,insert,update or append value to Json string.

DECLARE @JSONData AS NVARCHAR(MAX)

SET @JSONData = N'{ 
    "CustInfo":{ 
       
"FirstName":"Michael",
        "LastName":"
Jackson", 
        "CustId":"10002569", 
        "Addresses":[ 
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ] 
    } 
}'


/*Inserting Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'$.CustInfo.Gender', 'Male')
PRINT @JSONData

/*Updating Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'$.CustInfo.Gender', '1')
PRINT @JSONData

/*Delete Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'$.CustInfo.Gender', NULL)
PRINT @JSONData

/*Multiple Field Update*/
SET @JSONData = JSON_MODIFY(JSON_MODIFY(@JSONData,'$.CustInfo.FirstName', 'Jack'),'$.CustInfo.LastName','Dani')
PRINT @JSONData

/*Append Json*/
SET @JSONData = JSON_MODIFY(@JSONData,'append $.CustInfo.ContactNo',JSON_QUERY('{"Mobile" :"989500000","LandLine" :"04950008"}'))

{

    "CustInfo":{
       "FirstName":"Michael",
        "LastName":"Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    ,"Gender":"Male"}  Inserted
}
{

    "CustInfo":{
       "FirstName":"Michael",
        "LastName":"Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    ,"Gender":"1"} Updated
}
{

    "CustInfo":{
       "FirstName":"Michael",
        "LastName":"Jackson",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
  Deleted    
  } 
}
{

    "CustInfo":{
       "FirstName":"Jack", Multi Update
        "LastName":"Dani",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    }
}
{

    "CustInfo":{
       "FirstName":"Jack",
        "LastName":"Dani",
        "CustId":"10002569",
        "Addresses":[
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"},
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"}
        ]
    ,"ContactNo":[{"Mobile" :"989500000","LandLine" :"04950008"}]} Appended
}
 

FOR JSON – Function FOR JSON help to export SQL table data to Json format.Its similar to FOR XML function.

DECLARE @TempData AS Table(Name VARCHAR(10),Age INT)
INSERT INTO @TempData
SELECT 'A',10
UNION ALL
SELECT 'B',25
UNION ALL
SELECT 'C',25

SELECT *
FROM @TempData
FOR JSON PATH







OPENJSON – Using open Json we can extract Json data in schema format. Below example extract the Json data and convert it to table schema as like SQL table.


DECLARE @JSONData AS NVARCHAR(MAX)
SET @JSONData = N'{ 
       
"FirstName":"Michael",
        "LastName":"
Jackson",
 
        "CustId":"10002569", 
        "Addresses":[ 
            { "AddressType":"1", "City":"Bangalore", "State":"Karnataka"}, 
            { "AddressType":"2", "City":"Cochin", "State":"Kerala"} 
        ] 
    }' 



SELECT
   CustId,FirstName, LastName, AddressType, City, State
FROM OPENJSON(@JSONData)
WITH
(
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  CustId INT,
  Addresses NVARCHAR(MAX) as json
) AS Cust
CROSS APPLY OPENJSON(Cust.Addresses)
WITH
(
  AddressType INT,
  City VARCHAR(50),
  State VARCHAR(50)
) AD













Query Store In SQL Server 2016


                 SQL Server Query Store is a relatively new feature introduced in SQL Server 2016.  This will helps you to  identifying performance problems in the SQL Server. Query Store basically captures and stores the history of query execution plans and its performance data.


Query store is a database level feature, which is disabled by default. To enable this follow below steps.
  • Run the below Query to Enable Query Store against database.
               ALTER DATABASE [DataBaseName]  
               SET QUERY_STORE = ON

  •  Run the below To Desable and clear Query Store 
               ALTER DATABASE [DatabaseName]  
               SET QUERY_STORE CLEAR;

  • The following script sets a new Max Size. 
              ALTER DATABASE [DatabaseName]
              SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024)

  • To set different value for Data Flush Interval.
             ALTER DATABASE [DatabaseName]
             SET QUERY_STORE (DATA_FLUSH_INTERVAL_SECONDS = 900)

  • To Set Statistical Collection Intervel.
             ALTER DATABASE [DataBaseName]
             SET QUERY_STORE (INTERVAL_LENGTH_MINUTES = 60)
  • To Set Time based cleanup policy.
            ALTER DATABASE [DatabaseName]
            SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 90))

  • To set size based auto cleanup.
           ALTER DATABASE [DatabaseName]
           SET QUERY_STORE (SIZE_BASED_CLEANUP_MODE = AUTO);

  
 Once you Enabled Query Store you can see Query Store Folder Under Database




     Open the Top Resource Consumed Query, You can see the SQL Server start itself to analyze Query performance with execution plan





Rename Table In SQL Server

How to Rename table In SQL Server 

 

       In SQL Server there no direct query to rename table. It can be possible using built in procedure in SQL server.

The following syntax help you to raname a table using procedure.

      EXEC sp_rename 'old_table_name', 'new_table_name' 


Rename table Using SSMS

     Another way to rename a table is Rght Click the table from  SQL Server Management Studio.