SQL Server Bulk Insert – Part 1

SQL Server Bulk Insert – Part 1
4.1 (82.86%) 7 votes

According to Wikipedia, ”A Bulk insert is a process or method provided by a database management system to load multiple rows of data into a database table.”  If we adjust this explanation in accordance with the BULK INSERT statement, bulk insert allows importing external data files into SQL Server. Read More

SQL Server CRUD Operations – Part 1

SQL Server CRUD Operations – Part 1
4.7 (93.33%) 3 votes

In the database programming, there are four basic and essential operations: create, read, update, and delete. These operations can be defined as the first step in database programming and are called CRUD operations. CRUD is an acronym of the CREATE, READ, UPDATE and DELETE words.  The CRUD term was once mentioned by James Martin in his book ‘Managing the Database Environment’ and since then this term has become popular. In this article, we will explore the CRUD operation in terms of SQL Server because the operation syntax can differ from other relational and NoSQL databases. Read More

Dealing with NULLs in SQL Server

Dealing with NULLs in SQL Server
3.3 (66.67%) 3 votes

What is NULL? NULL or the NULL marker is the way we represent an unknown value in SQL, by SQL I am referring to the standard Structured Query Language, not MS SQL Server. The last sentence takes us a little back to 1969 when the standard was first defined by Dr. E.F. Codd. NULL becomes necessary because we need to think in terms of what is called three-valued predicate logic. A predicate is the property of an expression that either holds or does not hold. At face value, one would think of two possibilities: TRUE or FALSE. However, there is a third possibility: UNKNOWN. Read More

Basics of Running T-SQL Statements from Command Line using SQLCMD

Basics of Running T-SQL Statements from Command Line using SQLCMD
3.6 (72%) 10 votes

This article is about developing a basic understanding of sqlcmd utility to run T-SQL commands directly from the command prompt without the need of SSMS (SQL Server Management Studio).

The article also highlights the importance of using a lightweight sqlcmd utility to perform some basic database tasks that would otherwise require getting connected to the database through a pre-installed database tool such as SSMS (SQL Server Management Studio) or SSDT (SQL Server Data Tools) followed by getting it ready to run SQL scripts against the desired database(s).

The sqlcmd utility can be a real time-saver for database developers and DBAs since they can straight away run the required SQL scripts from the command-line.

Read More

How to Write T-SQL Queries Like a Pro

How to Write T-SQL Queries Like a Pro
4.9 (97.5%) 8 votes

The skills of writing different types of SQL Server queries require you to have good knowledge in the SQL Server T-SQL language. T-SQL stands for Transact Structure Query Language, which is a database procedural programming language that is extending the SQL language for Microsoft SQL Server RDBMS product. Read More

Multiple Ways to Remove Duplicates from SQL Tables

Multiple Ways to Remove Duplicates from SQL Tables
3.8 (76%) 5 votes

Overview

This article discusses two different approaches available to remove duplicate rows from SQL table(s) which often becomes difficult over time as data grows if this is not done on time.

The presence of duplicate rows is a common issue that SQL developers and testers face from time to time, however, these duplicate rows do fall into a number of different categories that we are going to discuss in this article.

This article focuses on a specific scenario, when data inserted into a database table, leads to the introduction of duplicate records and then we will take a closer look at methods for removing duplicates and finally remove the duplicates using these methods.

Read More

Using DATEADD, DATEDIFF and DATEPART T-SQL Functions in Simple Terms

Using DATEADD, DATEDIFF and DATEPART T-SQL Functions in Simple Terms
4.8 (96%) 5 votes

This article focuses on developing a basic understanding of how to use one of the most common Transact-SQL date functions: DATEADD, DATEDIFF, and DATEPART.

In this article, I also stressed the importance of properly using these date functions in daily date manipulations followed by some interesting scenarios in which these date functions can be used in a collaborative way to solve slightly complex date calculations.

Since these functions are primarily used in date manipulations, let us first try to understand what we mean by date manipulation.

Read More

MERGE: Updating Source and Target Tables Located on Separate Servers

MERGE: Updating Source and Target Tables Located on Separate Servers
3.3 (65%) 4 votes

What is the MERGE statement?

Using the MERGE statement, we can change data in a target table based on data in a source table. Using it, we can execute INSERT, UPDATE and DELETE on the target tables within a single query block. It joins both tables using columns, common in both tables like the primary key. Based on how column data matches, changes apply to data of target table. The following image illustrates how “MERGE” works:

MERGE statement in SQL Server Read More

How to create Snapshot Replication

How to create Snapshot Replication
3.4 (67.5%) 8 votes

The concept of snapshot replication is simple. It generates and distributes the snapshot of schema and data of articles, appeared at a specific time and updates the changes on subscriptions. When we create snapshot replication, SQL Server creates a Snapshot Agent Job which generates a snapshot of publication database objects. Snapshot is stored on either network location or on the hard drive. You can define the interval to generate the snapshot using the SQL Job schedule. Read More