sqlite

Query Mapping between MS-SQL and SQLite

If for some reason you need to migrate your MS-SQL Server database to SQLite Database, among many other things, here are some things that you should pay attention to.

Things to consider:

  1. Each Statement must end with “;
  2. Remove references to schema on query
    1. eg: References to [dbo].
    2. Select * from [dbo].[table1] in SQL = select * from [table1] in SQLite
  3. Watch out for special functions
    1. Substring in SQL = Substr in SQLite
    2. Len in SQL = Length in SQLite
  4. Other things to watch out for:
    1. Setting Identity Column
      1. IDENTITY in SQL = AutoIncrement in SQLite
    2. Setting Primary Keys
      1. SQL has multiple ways to set Primary Keys, some ways do not work in SQLite
  5. Querying for Information Schema for tables/columns
    1. SQL has INFORMATION_SCHEMA.TABLES and SQLite has sqlite_master/sqlite_temp_master tables
  6. SQLite doesn’t support Truncate command
    1. Replace Truncate with Delete From
  7. Manually update DateTime value after inserting into SQLite to a string value
    1. DateTime in SQL and SQLite are different
    2. SQL: CAST(0x000090CE00000000 AS DateTime) shows valid date/time in SQL but shows different format in SQLite which is not readable by frameworks such as ADO.NET
    3. To be on safe side update with string value.
    4. Eg: update table1 set create_dt= ‘2018-03-30 00:36:10.520’;

 

Create Table In SQL Based On .NET Datatable

Here’s a quick code to generate a “Create Table” query based on your Datatable object. The code is written on C# and it generates a create query for SQL table.

I had found this code somewhere on the StackOverflow site and it has proven to be extremely helpful.

SQL: Get Database Name From Physical Name

Here’s a SQL Function to get name of the database from it’s physical full name.

This function can be used in conjunction with RESTORE FILELISTONLY which can generate physicalname,  the parameter of this function, from a database’s backup file.

You can read more about RESTORE FILELISTONLY here.


create function get_db_name_from_physical_name
(@physicalname varchar(500))
returns varchar
as
begin

declare @dbName as varchar(500)

select @dbName =  DB_NAME(database_id) from sys.master_files where name = @physicalname

return @dbName
end

SQL: Save Result From RESTORE FILELISTONLY

RESTORE FILELISTONLY

is a Transact Statement in MS-SQL that returns a result set containing a list of the database and log files contained in the backup set in SQL Server.

Basically it contains meta-data pertaining to the database’s:

  • LogicalName
  • PhysicalName
  • Type
  • Size
  • MaxSize
  • etc.

This information is quite useful when performing Restore operation. For example, the physical name can be used to restore an existing database to a new database using the backup of the existing database by identifying and changing it’s physical name.

Here’s a quick script to create a table and store result from RESTORE FILELISTONLY operation. This table can then be used to select desired columns only like PhysicalName and LogicalName.


IF OBJECT_ID('tempdb..#Restore') IS NOT NULL DROP TABLE #Restore

CREATE TABLE #Restore (
LogicalName NVARCHAR(128),
PhysicalName NVARCHAR(260),
[Type] CHAR(1),
FileGroupName NVARCHAR(128),
Size NUMERIC(20,0),
MaxSize NUMERIC(20,0),
FileID BIGINT,
CreateLSN NUMERIC(25,0),
DropLSN NUMERIC(25,0),
UniqueID UNIQUEIDENTIFIER,
ReadOnlyLSN NUMERIC(25,0),
ReadWriteLSN NUMERIC(25,0),
BackupSizeInBytes BIGINT,
SourceBlockSize INT,
FileGroupID INT,
LogGroupGUID UNIQUEIDENTIFIER,
DifferentialBaseLSN NUMERIC(25,0),
DifferentialBaseGUID UNIQUEIDENTIFIER,
IsReadOnly BIT,
IsPresent BIT,
TDEThumbprint VARBINARY(32)
)

INSERT INTO #Restore
RESTORE FILELISTONLY FROM DISK='' -- database backup path here

 

 

 

SQL Pivot with Dynamic Column Names

Found a great solution to writing a complex PIVOT function in SQL on the web!

The solution was lying on a thread at one of the ASP.NET forums where the question was:

how to display row values in columns in sql server 2008

and the solution was provided by M. Atif Nadeem from Pakistan.

He has even taken time to create a SQL Fiddle as well!

Basically his solution allows to write a dynamic column name using STUFF and FOR XML functions and later using this dynamic column names to create a Dynamic SQL Pivot Statement.

How sweet !?

SQL: String Format Function

Here’s a handy SQL function that allows to insert string(s) inside a larger string. This function is similar to String.Format() function in C#.

Typical Usage:

Have a string like below:

%s is a. %s and %s does %s

with as many ” %s ” as required to dynamically insert values.

Insert desired string objects using the function.

SQL Function


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION DBO.FN_SPRINTF
(
@STRING VARCHAR(MAX),
@PARAMS VARCHAR(MAX),
@PARAM_SEPARATOR CHAR(1) = ','
)
RETURNS VARCHAR(MAX)
AS
BEGIN

DECLARE @P VARCHAR(MAX)
DECLARE @PARAM_LEN INT

SET @PARAMS = @PARAMS + @PARAM_SEPARATOR
SET @PARAM_LEN = LEN(@PARAMS)
WHILE NOT @PARAMS = ''
BEGIN
SET @P = LEFT(@PARAMS+@PARAM_SEPARATOR, CHARINDEX(@PARAM_SEPARATOR, @PARAMS)-1)
SET @STRING = STUFF(@STRING, CHARINDEX('%S', @STRING), 2, @P)
SET @PARAMS = SUBSTRING(@PARAMS, LEN(@P)+2, @PARAM_LEN)
END
RETURN @STRING

END

The function has three required parameters:

  1. @STRING VARCHAR(MAX) : This is the main string to format
  2. @PARAMS VARCHAR(MAX) : List of objects to insert
  3. @PARAM_SEPARATOR CHAR(1)  : Identifier to parameter separator

Demo:


declare @test varchar(400)

select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1,0', ',') --param separator ','

print @test -- result: I am 1 and you are 0

select @test = [dbo].[FN_SPRINTF] ('I am %s and you are %s', '1#0', '#') --param separator '#'

print @test -- result: I am 1 and you are 0

Are you ready to work as a SQL Developer?

A SQL Developer is responsible for managing data in a relational database. Database Providers like Microsoft, Oracle and MySql have their own IDE for managing data. For example Microsoft has SQL Server Management Studio for this purpose and Oracle has Oracle Developer Studio. A SQL Developer makes use of these IDEs for managing data.

Today I would like to list down the most common tasks that every SQL Developer must be familiar with as these will often come up as a requirement in the course of their day to day to work. This list will also serve as a guideline for anyone who wishes to advance their career towards becoming a SQL Developer.

In no particular order, here’s the list of tasks:

  1. Connecting to different SQL Servers using Windows and SQL Server Authentication.
  2. Creating  new databases in many different ways.
  3. Backing up and restoring databases.
  4. Designing and Normalizing Tables as per need.
  5. Using Primary Keys, Identity Columns and Foreign Keys for table creation.
  6. Altering Tables and Columns.
  7. Properly using Data Types and Column Size Limits.
  8. Writing basic to advanced level queries for SELECT, UPDATE, INSERT and DELETE.
  9. Writing table JOIN statements.
  10. Filtering Rows using WHERE clauses and JOIN statements.
  11. String manipulation using functions like Replace, Stuff, Trim and Substring.
  12. Creating tables on the fly.
  13. Using Cursors for looping.
  14. Writing Stored Procedures.
  15. Writing Functions.
  16. Writing Triggers.
  17. Using Linked Servers.
  18. Adding Indexes on tables.
  19. Using aggregator functions like Sum, Count, Group By, Order By, Row_Number and Distinct.
  20. Accessing table and column information across databases using Information_Schema.
  21. Generating Scripts to re-create database objects like tables and functions.
  22. Importing Data from external sources (mainly csv files sand excel sheets).
  23. Creating and authorizing  Database Users.

Note: Many of these tasks can be completed in two different ways i.e. either using the GUI or using Structured Query Language.

Using the GUI

Writing Queries

While it may be easier to operate through a User Interface for a newbie as he doesn’t have to remember the necessary queries, but on the long run it is better and also important to be fluent in performing these tasks writing queries.

Once you get the hang of it, you will find that writing query is more easier and faster than operating through UI as you no longer have to worry about where a particular function is located to perform a task. You just have to write the query on the query editor window.

Conclusion

The intention for this post was to identify the common operations that a SQL Developer must be fluent with in order to work in any professional environment. While this is not an an inclusive list of tasks and I may have left out a few other essential tasks, but nevertheless, if you are familiar with the tasks listed above, surely you can call yourself a SQL Developer ;).

Connecting to SQL Server using RODBC package in R

RODBC is a useful package for making SQL queries to SQL Server database for R users.

To start using this package, first we need to make sure it is loaded into R studio.


is.installed <- function(mypkg) is.element(mypkg, installed.packages()[,1])

if(!is.installed('RODBC'))
{
install.packages("RODBC")
}

library(RODBC)

Now that the package is installed/loaded into our environment, let’s start making SQL queries.

First we will need to provide credentials for accessing our SQL Server Database for R. Mainly we will need to have correct info for:

  1. Server name
  2. Database name
  3. Login Id
  4. Password

Providing connection info:


conn <- odbcDriverConnect('driver={SQL Server};
server=SOVITHOME-PC\\RAY;
database=EmergencyRMDb;
uid=sa;
pwd=myPwd***')

Now the “conn” object holds connection for SQL Server. Using this object we can make different kinds of queries supported by RODBC. Let’s perform some common queries like:

  • SELECT statements
  • SELECT statements with WHERE Clause
  • UPDATE statements
  • SQL JOIN statements

# print row count of tables
sqlQuery(conn, "select count(*) from EQData", as.is=T)

# print name of columns
colnames(tblData)

# select all data from a table
tblData &lt;- sqlQuery(conn, "select * from EQData", as.is=T)
#preview data
head(tblData)

<img class="wp-image-542" src="http://sovitpoudel.com.np/wp-content/uploads/2017/03/result1-300x68.png" alt="" width="850" height="193"> Result from query


# Viewing columns and rows

# [x,y] -> x represents the row number and y represents the column number

tblData[,1:5] # view columns 1 through 5

tblData[2,] # view first two rows

tblData$Latitude[2] # view second value for specified column
# finding by matching row(s) value
tblData[tblData$Epicentre == "Kathmandu",]

# update tables
sqlQuery(conn, "update EQData set latitude = '27.75' where epicentre = 'kathmandu'")

SQL JOIN statements can also be easily performed using “merge” function.


# perform inner join
newT <- merge(tblA, tblB, by = "key_id")
dim(newT)

# perform outer join
newOuterT <- merge(tblA, tblB, by = "key_id", all = TRUE)
dim(newOuterT)

# perform left join
newLeftT <- merge(tblA, tblB, by = "key_id", all.x = TRUE)
dim(newLeftT)

# perform right join
newRightT <- merge(tblA, tblB, by = "key_id", all.y = TRUE)
dim(newRightT)

These are some common operations that we can easily perform using R and RODBC on SQL Server tables. As you can see, most of the common operations can be performed using built-in R functions for Data Frames on R. I am just making use of “sqlQuery” function provided by RODBC for making queries on the Server.

Please find the full list of RODBC supported queries at: https://cran.r-project.org/web/packages/RODBC/RODBC.pdf

Hope this was helpful. If you got stuck on any of the steps, above please do not hesitate to leave comments below.

Thanks!

Uploading Unknown Format Excel File To A SQL Database

Introduction

Usually it is expected from client side to upload only structured Excel sheets (sheet with defined Column Names and data types for Rows) to load to server. But sometimes you might also require to dump whatever is in the Excel directly to your database. While there is a tool that is provided by SQL Management Studio for importing data from Excel files, this tutorial covers how you can automate such task using C# and .NET framework.

Steps

Two important pieces for uploading excel file of unknown column format to SQL database are:

  1. Reading Data from Excel into a Data Table
  2. Creating Table in SQL Server based on the Data Table

Once these two steps are done, the final step is simply to bulk load the data table into the table in SQL server.

Reading Data from Excel into a Data Table

I am using Excel Data Reader nuget package for this purpose.


/// <summary>
/// Gets a data table based on provided Excel File
/// </summary>
/// <param name="pathToExcelFile"></param>
/// <returns></returns>
public static DataTable GetTableFromExcel(string pathToExcelFile)
{
FileStream stream = File.Open(pathToExcelFile, FileMode.Open, FileAccess.Read);

IExcelDataReader excelReader;
if (Path.GetExtension(pathToExcelFile) == ".xls")
{
//1. Reading from a binary Excel file ('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}

excelReader.IsFirstRowAsColumnNames = true;

//...
//3. DataSet - The result of each spreadsheet will be created in the result.Tables
DataSet result = excelReader.AsDataSet();
DataTable dt = null;
if (result != null && result.Tables.Count > 0)
{
dt = result.Tables[0];//get first sheet only
}
return dt;
}

Excel Data Reader package supports multiple sheets of Excel to be read into a Data Set at once and also allows other configurations such as: Supporting multiple file formats and Checking Column Names in First Row.

Creating Table in SQL Server based on the Data Table

Now, to generate a “Create Table” query, I found a very useful code on the Stack Overflow site for this purpose.


/// <summary>
/// Generates sql create table query for given data table
/// </summary>
/// <param name="tableName">name of the table to be created</param>
/// <param name="table">data table</param>
/// <returns></returns>
public static string GenerateCreateTableQuery(string tableName, DataTable table)
{
var sqlsc = "CREATE TABLE " + tableName + "(";
for (int i = 0; i < table.Columns.Count; i++)
{
sqlsc += "\n [" + table.Columns[i].ColumnName + "] ";
string columnType = table.Columns[i].DataType.ToString();
switch (columnType)
{
case "System.Int32":
sqlsc += " int ";
break;
case "System.Int64":
sqlsc += " bigint ";
break;
case "System.Int16":
sqlsc += " smallint";
break;
case "System.Byte":
sqlsc += " tinyint";
break;
case "System.Decimal":
sqlsc += " decimal ";
break;
case "System.DateTime":
sqlsc += " datetime ";
break;
case "System.String":
default:
sqlsc += string.Format(" nvarchar({0}) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
break;
}
if (table.Columns[i].AutoIncrement)
sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
if (!table.Columns[i].AllowDBNull)
sqlsc += " NOT NULL ";
sqlsc += ",";
}
return sqlsc.Substring(0, sqlsc.Length - 1) + "\n)";
}

Once we have the data and create table script, uploading to database is quite simple and can be achieved with following steps:

  1. Decide upon a table name
  2. Check if it exists or not in the server
  3. Create table if it doesn’t exist
  4. Bulk load data table into your newly created table.

 

7 Handy SQL Scripts for SQL Developers

Finally got my article published at Tuts+

I had written this article “7 Handy SQL Scripts for SQL Developers” referencing some of the common scripts that I most frequently use @ work. Some of these scripts were collected from different sites while some were developed by myself.

————————————————————————————

“A lot of things that we do depend upon the knowledge that we possess. If we are aware of what can be done, only then we can make smarter and more effective decisions. That is why it is always good to have quick tips and tricks handy in your pocket. This principle applies everywhere, including for MS-SQL developers.
Through this article I would like to share a few SQL scripts which have proven to be very useful for my daily job as a SQL developer. I’ll present a brief scenario about where each of these scripts can be used along with the scripts below. ……………………”

Read full article at:
http://code.tutsplus.com/tutorials/7-handy-sql-scripts-for-sql-developers–cms-25834—————————————————