Ways to compare and find differences for SQL Server tables and data

Problem

Sometimes we need to compare tables and/or data to know what was changed. This tip shows you different ways to compare data, datatypes and tables.

Solution

I will show you different methods to identify changes. Let's say that we have two similar tables in different databases and we want to know what is different:

create database dbtest01
go
USE dbtest01
GO
CREATE TABLE [dbo].[article](
 [id] [nchar](10) NOT NULL,
 [type] [nchar](10) NULL,
 [cost] [nchar](10) NULL,
 CONSTRAINT [PK_article] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into [dbo].[article] values
('001','1','40'),
('002','2','80'),
('003','3','120')
GO
create database dbtest02
go
USE dbtest02
GO
CREATE TABLE [dbo].[article](
 [id] [nchar](10) NOT NULL,
 [type] [nchar](10) NULL,
 [cost] [nchar](10) NULL,
 CONSTRAINT [PK_article] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into [dbo].[article] values
('001','1','40'),
('002','2','80'),
('003','3','120'),
('004','4','160')
GO

The T-SQL code generates 2 tables in different databases. The table names are the same, but the table in database dbtest02 contains an extra row:

Let's look at ways we can compare these tables using different methods.

Compare Tables Using a LEFT JOIN

With the left join we can compare values of specific columns that are not common between two tables.

For example:

select * 
from dbtest02.dbo.article d2
left join dbtest01.dbo.article d1 on d2.id=d1.id

The left join shows all rows from the left table "dbtest02.dbo.article", even if there are no matches in the "dbtest01.dbo.article":

In this example, we are comparing 2 tables and the values of NULL are displayed if there are no matching rows. This method works to verify new rows, but if we update other columns, the left join does not help. Is there another method to compare tables?  Let's use the Except clause to see what we can find.

Compare Tables Using the EXCEPT Clause

The Except method shows the difference between two tables (the Oracle guys use minus instead of except and the syntax and use is the same). It is used to compare the differences between two tables. For example, let's see the differences between the two tables:

Now let's run a query using except:

select * from dbtest02.dbo.article
except
select * from dbtest01.dbo.article

The except returns the difference between the tables from dbtest02 and dbtest01:

If we flip the tables around in the query we won't see any records, because the table in database dbtest02 has all of the records plus one extra.

This method is better than the first one, because if we change values for other columns like the type and cost, the except will notice the difference. Here is an example if we update id "001" in database dbtest01 and change the cost from "40" to "1".  If we update the records and then run the query again we will see these differentness now:

Unfortunately it does not create a script to synchronize the tables. Is there a way to compare tables and synchronize results?

Compare Tables Using the Tablediff Tool

There is a nice command line tool used to compare tables.  This can be found in "C:\Program Files\Microsoft SQL Server\110\COM\" folder. This command line tool is used to compare tables. It also generates a script with the insert, update and delete statements to synchronize the tables. For more details, refer to this tablediff article.

Compare Changes in a Table Using the Change Data Capture CDC

This feature was added in SQL Server 2008. You need to enable this feature and you also need to have SQL Server Agent running. Basically it creates system tables that track the changes in your tables that you want to monitor. It does not compare tables, but it tracks the changes in tables.

For more information, refer to this article: Using Change Data Capture (CDC) in SQL Server 2008.

Compare Data Types Between Two Tables

What happen if we want to compare the data types? Is there a way to compare the datatypes?

Yes, we can use the [INFORMATION_SCHEMA].[COLUMNS] system views to verify and compare the information. We are going to create a new table named dbo.article2 with a column with different data type than the dbo.article table:

USE dbtest01
GO
CREATE TABLE [dbo].[article2](
 [id] [int] NOT NULL,
 [type] nchar(10) NULL,
 [cost] nchar(10) NULL,
 CONSTRAINT [PK_article1] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

The difference is that the id is now an int instead of nchar(10) like our other tables.

The query to compare data types of the article and article 1 would be:

USE dbtest01
GO
select c1.table_name,c1.COLUMN_NAME,c1.DATA_TYPE,c2.table_name,c2.DATA_TYPE,
c2.COLUMN_NAME 
from [INFORMATION_SCHEMA].[COLUMNS] c1
left join [INFORMATION_SCHEMA].[COLUMNS] c2 on c1.COLUMN_NAME=c2.COLUMN_NAME
where c1.TABLE_NAME='article'
and c2.TABLE_NAME='article2'
and c1.data_type<>c2.DATA_TYPE
  

The results are as follows:

The query compares the data types from these two tables. All the information of the columns can be obtained from the [INFORMATION_SCHEMA].[COLUMNS] system view. We are comparing the table "article" with the table "article2" and showing if any of the datatypes are different.

Compare if there are Extra Columns Between Tables

Sometimes we need to make sure that two tables contain the same number of columns. To illustrate this we are going to create a table named "article3" with 2 extra columns named extra1 and extra2:

USE dbtest01
GO
CREATE TABLE [dbo].[article3](
 [id] [int] NOT NULL,
 [type] nchar(10) NULL,
 [cost] nchar(10) NULL,
 extra1 int,
 extra2 int
)

In order to compare the columns I will use this query:

USE dbtest01
GO
select c2.table_name,c2.COLUMN_NAME
from [INFORMATION_SCHEMA].[COLUMNS] c2
where table_name='article3'
and c2.COLUMN_NAME not in (select column_name 
    from [INFORMATION_SCHEMA].[COLUMNS] 
    where table_name='article')

The query compares the different columns between table "article" and "article3". The different columns are extra1 and extra2. This is the result of the query:

Compare Tables in Different Databases

Now let's compare the tables in database dbtest01 and dbtest02. The following query will show the different tables in dbtest01 compared with dbtest02:

select 'dbtest01' as dbname, t1.table_name
from dbtest01.[INFORMATION_SCHEMA].[tables] t1
where table_name not in (select t2.table_name
    from 
    dbtest02.[INFORMATION_SCHEMA].[tables] t2  
    )
union
select 'dbtest02' as dbname, t1.table_name
from dbtest02.[INFORMATION_SCHEMA].[tables] t1
where table_name not in (select t2.table_name
    from 
    dbtest01.[INFORMATION_SCHEMA].[tables] t2  
    )

Third Party Tools

There are great tools to compare tables including data and schemas. You can use Visual Studio or use other SQL Server Comparison tools.

ref:http://www.mssqltips.com/sqlservertip/2779/ways-to-compare-and-find-differences-for-sql-server-tables-and-data/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20140124

 By: Daniel Calbimonte

Import data from Excel to SQLServer

Many times we come across a situation where we have readily available excel sheets having enormous data that needs to be imported in database system for enhanced querying capabilities or as a backend datasource for some sort of software development.

We could achieve this goal either by copy-pasting the excel rows directly to the destined database table in SQL Server Management Studio or by querying directly on the excel sheet from SQL Server Management Studio itself!

The former way (copy-pasting) looks pretty simple but consider a situation where you are having 10K records in a single excel sheet and wanted to fetch some selective/filtered records only! At that time, the earlier way is much efficient and ideal.

I've created this sample excel file to import/retrieve the data in SQL server, and is also the file I will be using in this article. Here is the screenshot for the same.

Excel sheet - Cars

OPENROWSET() to query excel files

Here, I've used the OPENROWSET() function to query excel files. This is a T-SQL function that can be used to access any OLE DB data source. All you need is the right OLE DB driver.

The following are the queries to retrieve data from Excel files.

--Excel 2007-2010
SELECT * --INTO #Cars
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\cars.xlsx','SELECT * FROM [cars$]');

--Excel 97-2003

SELECT * --INTO #Cars

FROM OPENROWSET('Microsoft.ACE.OLEDB.4.0',

'Excel 8.0;HDR=YES;Database=C:\cars.xls','SELECT * FROM [cars$]');

Understanding SQL Server Configuration Manager

By coded007, 15 Sep 2013 on codeproject

Introduction

SQL Server Configuration Manager is a tool to configure the network protocols used by the SQL Server, and to manage network connectivity.
We will find SQL Server Configuration Manager API in the below path:
Start -> Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager
SQL Server Configuration Manager gets information for WMI (Windows Management Instrumentation) Provider which takes information for “SQLServerManager10.msc” which is located in “C:\Windows\System32\” path. Generally SQL Server Configuration Manager will use to Start, Stop, resume and Configure services of SQL Server.

Left pane has list of configurations we can do for SQL Server.

• SQL Server Services
• SQL Server Network Configuration
• SQL Server Native Client Configuration

How To Create an ADO.NET Data Access Utility Class for SQL Server

By: Ray Barley

Problem

I am a .NET developer and I typically write applications that use a SQL Server database.  I'm looking for a really simple, reusable class that encapsulates my ADO.NET database access code for create, read, update and delete (CRUD).  As I see it I need two methods in the class: one that executes a stored procedure that returns a result set and another that executes a stored procedure that does an insert, update or a delete.  Can you provide an example of how to do it?

Solution

While there are many code samples readily available to encapsulate ADO.NET database access, I prefer the simple, bare-bones approach that satisfies your requirements of a method that executes a query and another that executes a command.  In this tip I will review a solution that has a class library for the database utility and a console application that uses the class library.

I will assume that the reader is familiar with creating .NET applications using Visual Studio.

Connection Strings

When you write ADO.NET code to access a database, you need a connection string to specify the database that you want to access.  The connection string can be stored in your application's app.config file or web.config file (for a web application).  The following is an example of an app.config file:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
  <connectionStrings>
    <add name="mssqltips"
         connectionString="data source=localhost;initial catalog=mssqltips;Integrated Security=SSPI;"
         providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

The following are the main points about the app.config file:

• mssqltips is the name of the connection string; we will use the name mssqltips to access the connection string
• Data source is the server name of the SQL Server database instance
• Initial catalog is the database name
• Integrated Security=SSPI means we are using windows authentication to connect to the database
• Provider name is the ADO.NET data provider for SQL Server

You can specify many more settings in the connection string than I have shown here.  Take a look atSqlConnection.ConnectionString Property for the details.

Database Utility Class

I will use a class library project named DataAccessUtility to implement the database access utility class.  When a class library is compiled it generates a dynamic link library (.DLL) which can then be referenced from any .NET application.  The class library will have a single class named SqlDatabaseUtility with the following methods:

• GetConnection() opens a database connection
• ExecuteQuery() executes a stored procedure that performs a query
• ExecuteCommand() executes a stored procedure that performs an insert, update or delete

Before I get to reviewing the code in the methods, here are a couple of things that need to be done:

• Add a reference to System.Configuration to the class library project; I need this to access the connection string in the app.config file
• Add using statements to the SqlDatabaseUtility class for the namespaces System.Configuration, System.Data and System.Data.SqlClient; I am using classes from these namespaces

The GetConnection() method has the following code:

public SqlConnection GetConnection(string connectionName)
{
  string cnstr = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
  SqlConnection cn = new SqlConnection(cnstr);
  cn.Open();
  return cn;
}

The main points about the GetConnection() method are:

• Reads the connection string from the app.config (or web.config) file
• Creates an instance of a SqlConnection object passing the connection string into the constructor
• Calls the Open() method on the SqlConnection object which "opens" a database connection
• Returns the SqlConnection object to the caller

The ExecuteQuery() method has the following code:

 public DataSet ExecuteQuery(
  string connectionName,
  string storedProcName,
  Dictionary procParameters
)
{
  DataSet ds = new DataSet();
  using(SqlConnection cn = GetConnection(connectionName))
  {
      using(SqlCommand cmd = cn.CreateCommand())
      {
          cmd.CommandType = CommandType.StoredProcedure;
          cmd.CommandText = storedProcName;
          // assign parameters passed in to the command
          foreach (var procParameter in procParameters)
          {
            cmd.Parameters.Add(procParameter.Value);
          }
          using (SqlDataAdapter da = new SqlDataAdapter(cmd))
          {
            da.Fill(ds);
          }
      }
 }
 return ds;
}

The main points about the ExecuteQuery() method are:

• Creates a Dataset that will be used to return the query results to the caller
• Calls the GetConnection() method to open a database connection
• Creates a SqlCommand object from the Connection, and sets the CommandType and CommandText properties
• Adds any parameters passed in to the SqlCommand parameter collection
• Creates a SqlDataAdapter for the SqlCommand, and calls the Fill method to execute the query and populate a dataset
• Returns the Dataset to the caller
• The SqlConnection, SqlCommand, and SqlDataAdapter objects are wrapped with a "using" statement which ensures that the objects are disposed; the caller is not responsible for "freeing" these objects

The ExeuteCommand() method has the following code:

public int ExecuteCommand(
  string connectionName,
  string storedProcName,
  Dictionary<string, SqlParameter> procParameters
)
{
  int rc;

  using (SqlConnection cn = GetConnection(connectionName))
  {
    // create a SQL command to execute the stored procedure
    using (SqlCommand cmd = cn.CreateCommand())
    {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.CommandText = storedProcName;

      // assign parameters passed in to the command
      foreach (var procParameter in procParameters)
      {
        cmd.Parameters.Add(procParameter.Value);
      }

      rc = cmd.ExecuteNonQuery();
    }
  }

  return rc;
}

The main points about the ExecuteCommand() method are:

• Calls the GetConnection() method to open a database connection; the using construct is used to close the database connection automatically
• Creates a SqlCommand object from the Connection and sets the CommandType and CommandText properties
• Adds any parameters passed in to the SqlCommand parameter collection
• Calls the SqlCommand ExecuteNonQuery method to call the stored procedure; the return value is the number of rows affected; e.g. the number of rows inserted, update or deleted by the command
• The SqlConnection and SqlCommand objects are wrapped with a "using" statement which ensures that the objects are disposed; the caller is not responsible for "freeing" these objects

Console Application

In this section I will review a .NET console application that will access a SQL Server database by using the SqlDatabaseUtility  class.  Here is a T-SQL script that creates a table, and two stored procedures - one that inserts a row and another that performs a query:

use mssqltips
go

create table [dbo].[customer] (
 [id] [int] identity(1,1) NOT NULL,
 [name] [varchar](50) NOT NULL,
 [state] [varchar](2) NOT NULL,
 constraint [pk_customer] primary key clustered ([id] asc)
)
go

create procedure dbo.AddCustomer
 @name  varchar(50)
,@state char(2)
as
begin
 insert into dbo.customer
  ([name], [state])
 values
  (@name, @state)
end
go

create procedure dbo.GetCustomerList
as
begin
 select [id], [name], [state]
 from dbo.customer
end
go

Before I get to reviewing the code in the console application, here are a couple of things that need to be done:

• Add a reference to the DataAccessUtility class library to the console application; I need this to call the methods in the SqlDatabaseUtility class
• Add a using statement for the DataAccessUtility, System.Data and System.Data.SqlClient namespaces
• Create an mssqltips database and run the above T-SQL script in it
• Put the connectionStrings element (shown in the Connection Strings section above) into the app.config file in the console application project

Here is the code to call the AddCustomer stored procedure:

SqlDatabaseUtility dbutility = new SqlDatabaseUtility();
            
// add a customer
Dictionary cmdParameters = new Dictionary();
cmdParameters["name"] = new SqlParameter("name", "Smith");
cmdParameters["state"] = new SqlParameter("state", "MD");
dbutility.ExecuteCommand("mssqltips", "dbo.AddCustomer", cmdParameters);

The main points about the above code are:

• Create an instance of the SqlDatabaseUtility class
• Create a Dictionary collection for parameters; it's like a name-value pair
• Add parameters to the collection; parameter names must match the stored procedure parameters
• Call the SqlDatabaseUtility ExecuteCommand method passing in the connection name, stored procedure name, and the parameter collection

Here is the code to call the GetCustomerList stored procedure:

Dictionary<string, SqlParameter> queryParameters = new Dictionary<string, SqlParameter>();
DataSet ds = dbutility.ExecuteQuery("mssqltips", "dbo.GetCustomerList", queryParameters);
DataTable t = ds.Tables[0];
foreach(DataRow r in t.Rows)
{
  Console.WriteLine(string.Format("{0}\t{1}\t{2}", 
    r[0].ToString(), 
    r[1].ToString(), 
    r[2].ToString()));
}

The main points about the above code are:

• Create a Dictionary collection for parameters; even though the GetCustomerList does not take any parameters, you still have to pass an empty collection
• Call the SqlDatabaseUtility ExecuteQuery method passing the connection name, stored procedure name, and empty parameter collection
• ExecuteQuery returns a Dataset which is a collection of DataTables
• Get the first Datatable from the Dataset, iterate through the rows and print the column values to the console

Next Steps

• The above code is an example of a very simple approach to calling stored procedures from .NET code.
• To keep the code as simple as possible, there is no exception handling shown.  You do need try/catch blocks around your database calls.  Take a look at the SqlCommand methods to see the kinds of exceptions that you need to catch. 
• Download the sample code here to experiment on your own.

Import Excel unicode data with SQL Server Integration Services

By: Greg Robidoux   |   Read Comments (41)   |   Related Tips: 1 | 2 | 3 | 4 | 5 | More > Integration Services Excel

Database-related security breaches can’t be taken lightly - Learn more...

Problem

One task that most people are faced with at some point in time is the need to import data into SQL Server from an Excel spreadsheet.  We have talked about different approaches to doing this in previous tips using OPENROWSET, OPENQUERY, Link Servers, etc... These options are great, but they may not necessarily give you as much control as you may need during the import process.

Another approach to doing this is using SQL Server Integration Services (SSIS).  With SSIS you can import different types of data as well as apply other logic during the importing process.  One problem though that I have faced with importing data from Excel into a SQL Server table is the issue of having to convert data types from Unicode to non-Unicode.  SSIS treats data in an Excel file as Unicode, but my database tables are defined as non-Unicode, because I don't have the need to store other code sets and therefore I don't want to waste additional storage space.  Is there any simple way to do this in SSIS?

Solution

If you have used SSIS to import Excel data into SQL Server you may have run into the issue of having to convert data from Unicode to non-Unicode.  By default Excel data is treated as Unicode and also by default when you create new tables SQL Server will make your character type columns Unicode as well (nchar, nvarchar,etc...)  If you don't have the need to store Unicode data, you probably always use non-Unicode datatypes such as char and varchar when creating your tables, so what is the easiest way to import my Excel data into non-Unicode columns?

The following shows two different examples of importing data from Excel into SQL Server.  The first example uses Unicode datatypes and the second does not.

Here is what the data in Excel looks like.

Example 1 - Unicode data types in SQL Server

Our table 'unicode" is defined as follows:

CREATE TABLE [dbo].[unicode](
[firstName] [nvarchar](50) NULL,
[lastName] [nvarchar](50) NULL
) ON [PRIMARY]

If we create a simple Data Flow Task and an Excel Source and an OLE DB Destination mapping firstname to firstname and lastname to lastname the import works great as shown below.

Example 2- non-Unicode data types in SQL Server

Our table 'non_unicode" is defined as follows:

CREATE TABLE [dbo].[non_unicode](
[firstName] [varchar](50) NULL,
[lastName] [varchar](50) NULL
) ON [PRIMARY]

If we map the columns firstname to firstname and lastname to lastname we automatically get the following error in the OLE DB Destination.

Columns "firstname" and "firstname" cannot convert between unicode and non-unicode data types...

If we execute the task we get the following error dialog box which gives us additional information.

Solving the Problem

So based on the error we need to convert the data types so they are the same types.

If you right click on the OLE Destination and select "Show Advanced Editor" you have the option of changing the DataType from string [DT_STR] to Unicode string [DT_WSTR].  But once you click on OK it looks like the changed was saved, but if you open the editor again the change is gone and back to the original value.  This makes sense since you can not change the data type in the actual table.

If you right click on the Excel Source and select "Show Advanced Editor" you have the option of changing the DataType from Unicode string [DT_WSTR] to string [DT_STR] and the change is saved. 

If you click OK the change is saved, but now you get the error in the Excel Source that you can not convert between unicode and non-unicode as shown below.  So this did not solve the problem either.

Using the Data Conversion Task

So to get around this problem we have to also use a Data Conversion task.  This will allow us to convert data types so we can get the import completed.  The following picture shows the "Data Conversion" task in between the Excel Source and the OLE DB Destination.

If you right click on "Data Conversion" and select properties you will get a dialog box such as the following.  In here we created an Output Alias for each column.

Our firstname column becomes firstname_nu (this could be any name you want) and we are making the output be a non-unicode string.  In addition we do the same thing for the lastname column.

If we save this and change the mapping as shown to use our new output columns and then execute the task we can see that the import was successful.

As you can see this is pretty simple to do once you know that you need to use the Data Conversion task to convert the data types.

How to create a simple database backup using SQL Server Management Studio (SSMS)

By: Graham Okely

Problem

You are brand new to SQL Server and you need to create a SQL Server database backup, but you have no idea what to click on.  In this tip we walk through the steps to create a simple backup using SQL Server Management Studio.

Solution

This tip guides you through the steps to make a simple one-off backup.

Step 1

Open SSMS and expand the Database tree as shown below and right mouse click on the database you wish to backup. Then move your mouse carefully over Tasks and then click on Back Up... as shown below.

Step 2

At this point pause and look at the options before you click.

When we see so many options, and there are more under the options tab, it is hard to know what to do so let's look at only the essential things. I have circled the essentials in red.

(If you wish to know more about the options page look at this page at Back Up Database (Options Page) at Microsoft. You may wish to visit this MSSQLTips backup tutorial as well.)

• First the source database is listed, confirm it is the database you wish to backup.
• Next the backup type is Full. This option will give us a full backup. There are other types, but let's leave it as Full.
• Notice the "Copy-only Backup" check box. Check it if you wish to use this option. You may be thinking, a backup is a copy of the database. Yes that is true however the copy it is referring to relates to a "chain" of backups. This is important if you do not wish to break the backup chain. My tip is to check that check-box. See tip 1772 by Atif Shehzad or Copy-Only Backups at Microsoft for more information.
• Next leave the "Database" as the component we wish to backup.  This will backup the entire database.
• Next unless you know the path and name displayed are what you want, click "Remove" to remove the default backup path and name. Note this does not remove that file if it exists it simply removes that path and file name from this dialog box.

Step 3

Next click on "Add..." and browse to a path you know has room for your backup. It is possible to enter a URL at this point. (\\Server\Drive\Path\File_Name)

Note that if at all possible you should place your backup into the usual location for backups. This means it is easy to find all your backups should you need to restore it and any automated clean up process will clean out your one-off backup.

Step 4

Enter the file name. I suggest the format "Database_Name_backup_YYYY_MM_DD.bak" as it is simple and follows the pattern of an automated SQL Server backup.

Once ready click OK.

Step 5

Here are the options, file path and name ready to backup.

It is ready to backup now. However at this point we could choose to click OK and back it up or click on the "Script" menu item. Clicking on Script will not run the backup, but produce a script for you.

(Note that tip 1070 by Greg Robidoux explains one TSQL method of performing backups.)

Let’s click on "OK" to do the backup. Notice the green circle icon indicates the backup percentage. Then it pops up a message once completed.

Click "OK" and the screen goes back to normal.

If we browse to that location we should see the backup.

Well done on a successful backup using the SSMS GUI.