Query Mapping between MS-SQL and 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: Each Statement must end with “;“ Remove references to schema on query eg: References to [dbo]. Select * from [dbo].[table1] in SQL =... » read more

Create Table In SQL Based On .NET Datatable
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. [crayon-5dccfa48300e8150587983/]

SQL: Get Database Name From Physical Name
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... » read more

SQL: Save Result From RESTORE FILELISTONLY
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,... » read more

SQL Pivot with Dynamic Column Names
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... » read more

SQL: String Format Function
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... » read more

Are you ready to work as a SQL Developer?
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... » read more

Connecting to SQL Server using RODBC package in R
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... » read more

Uploading Unknown Format Excel File To A SQL Database
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... » read more

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... » read more