Volpet Software

How to copy data from one table to another in SQL

Posted on 19. Jan, 2010 by Gia in Blog

We often need to copy the data (the records) from one table to another. This can be achieved in two ways:

1. Using a custom SQL statement;
2. Using a tool such as Table Diff.

This time I will explain how to copy data from one table to another using a custom SQL statement.

Assume that following is the structure of our tables:

-- CREATE the 'UsersLeft' table.
CREATE TABLE [UsersLeft](
 [ID] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Surname] [nvarchar](50) NULL
 CONSTRAINT [PK_UsersLeft] PRIMARY KEY CLUSTERED
 (
 [ID] ASC
 ))

-- Create the 'UsersRight' table;
CREATE TABLE [UsersRight](
 [ID] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Surname] [nvarchar](50) NULL
 CONSTRAINT [PK_UsersRight] PRIMARY KEY CLUSTERED
 (
 [ID] ASC
 ))

As you can see, the two tables have identical structure. They feature the same attributes (the same columns).

Assuming that you want to copy the records found in UsersLeft into UsersRight, and that UsersRight is empty, following is the simple query that can be executed:

INSERT INTO
 UsersRight ([ID], [Name], [Surname])
SELECT
 [ID], [Name], [Surname]
FROM
 UsersLeft;

If the two tables are located on different databases on the same SQL Server instance, then you will need to specify the database containing the tables. For example:

INSERT INTO
 TargetDatabase.dbo.UsersRight ([ID], [Name], [Surname])
SELECT
 [ID], [Name], [Surname]
FROM
 SourceDatabase.dbo.UsersLeft;

Note that you will need to replace “dbo” with the actual owner of the database.

You can also copy data between tables located in databases that are hosted by different instances of SQL Server. This can be achieved by linking the two SQL Servers.

Table Diff allows you to easily copy data between tables hosted by linked and non-linked SQL Server instances. Table Diff also allows you to view data differences, generate synchronization scripts and merging two tables by creating bidirectional synchronization scripts. Have you tried our fully functional 30-day trial yet?

Table Diff

How to merge two tables in SQL

Posted on 19. Jan, 2010 by Gia in Blog

In this article, I explain how to merge two tables using plain SQL.

When merging two tables, what you want to achieve is:

• Create missing records (e.g. if Table A contains a record that cannot be found in Table B, then create such record in Table B);
• Update mismatching records (e.g. if a record having the same unique key can be found in both tables but it has at least one mismatching non-key attribute, then update its values by copying them from the other table).

Following is the definition of the test tables we’re going to use in this example.

-- CREATE the 'UsersLeft' table.
CREATE TABLE [UsersLeft](
 [ID] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Surname] [nvarchar](50) NULL
 CONSTRAINT [PK_UsersLeft] PRIMARY KEY CLUSTERED
 (
 [ID] ASC
 ))

-- Create the 'UsersRight' table;
CREATE TABLE [UsersRight](
 [ID] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Surname] [nvarchar](50) NULL
 CONSTRAINT [PK_UsersRight] PRIMARY KEY CLUSTERED
 (
 [ID] ASC
 ))

Following is the SQL for creating missing records (without updating mismatching records):

BEGIN TRAN t1;

-- Insert missing records in the 'UsersLeft' table.
INSERT INTO
   UsersLeft
SELECT
   UsersRight.ID, UsersRight.[Name], UsersRight.Surname
FROM
   UsersRight
LEFT JOIN
   UsersLeft
ON
   UsersRight.ID = UsersLeft.ID
WHERE
   UsersLeft.ID IS NULL;

-- Insert missing records in the 'UsersRight' table.
INSERT INTO
   UsersRight
SELECT
   UsersLeft.ID, UsersLeft.[Name], UsersLeft.Surname
FROM
   UsersLeft
LEFT JOIN
   UsersRight ON UsersLeft.ID = UsersRight.ID
WHERE
   UsersRight.ID IS NULL;

COMMIT TRAN t1;

And following is the SQL for creating missing records and updating mismatching records with the values from the ‘UsersRight’ table:

BEGIN TRAN t1;

-- Update the 'Name' attribute in the UsersLeft table if
-- the value differs from the one if the UsersRight table.
UPDATE
 UsersLeft
SET
 UsersLeft.Name = UsersRight.Name
FROM
 UsersLeft
INNER JOIN
 UsersRight
ON
 UsersLeft.ID = UsersRight.ID
WHERE
 UsersLeft.Name <> UsersRight.Name;

-- Update the 'Surname' attribute in the UsersLeft table if
-- the value differs from the one if the UsersRight table.
UPDATE
 UsersLeft
SET
 UsersLeft.Surname = UsersRight.Surname
FROM
 UsersLeft
INNER JOIN
 UsersRight
ON
 UsersLeft.ID = UsersRight.ID
WHERE
 UsersLeft.Surname <> UsersRight.Surname;

COMMIT TRAN t1;

This and much more can also be done using Table Diff, our table comparison tool. Table Diff also allows you to merge tables located on disparate SQL Servers.

Hope this helps.

How to compare two tables in SQL

Posted on 17. Jan, 2010 by Gia in Blog

In this brief blog post, I will introduce how to compare two tables having the same structure using SQL.

The method I am going to explain works if both tables are hosted on the same instance of Microsoft SQL Server, or hosted on two separate but linked instances of Microsoft SQL Server.

If you want to easily compare and synchronize the data stored in two tables hosted on linked or non-linked Microsoft SQL Servers, then try Table Diff.

Assume you have two tables having identical structure:

-- CREATE the 'UsersLeft' table.
CREATE TABLE [UsersLeft](
 [ID] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Surname] [nvarchar](50) NULL
 CONSTRAINT [PK_UsersLeft] PRIMARY KEY CLUSTERED
 (
 [ID] ASC
 ))

-- Create the 'UsersRight' table;
CREATE TABLE [UsersRight](
 [ID] [int] NOT NULL,
 [Name] [nvarchar](50) NOT NULL,
 [Surname] [nvarchar](50) NULL
 CONSTRAINT [PK_UsersRight] PRIMARY KEY CLUSTERED
 (
 [ID] ASC
 ))

When comparing two tables, you will want to:

• Understand what records are different;
• Understand what records are identical;
• Understand what records exist in only one of the two tables.

Different records are those records that have matching primary key, but that have at least one different non-key attribute.

Example of Different Record

Without further ado, here is the SQL query you can use to compare the two tables:

SELECT  UsersLeft.ID,
 ' ' = CASE WHEN UsersLeft.ID = UsersRight.ID THEN '=' ELSE '<>' END,
 UsersRight.ID,
 UsersLeft.Name,
 ' ' = CASE WHEN UsersLeft.Name = UsersRight.Name THEN '=' ELSE '<>' END,
 UsersRight.Name,
 UsersLeft.Surname,
 ' ' = CASE WHEN UsersLeft.Surname = UsersRight.Surname THEN '=' ELSE '<>' END,
 UsersRight.Surname,
 'Status' = CASE WHEN UsersLeft.ID IS NULL THEN 'Missing from UsersLeft'
  WHEN UsersRight.ID IS NULL THEN 'Missing from UsersRight'
  WHEN UsersLeft.Name <> UsersRight.Name OR
  UsersLeft.Surname <> UsersRight.Surname THEN 'Different' ELSE
  'Identical' END
FROM UsersLeft FULL OUTER JOIN UsersRight ON UsersLeft.ID = UsersRight.ID

And here is an example of the result of the query:

Result of the table comparison query

And following is a screenshot of a similar operation performed with Table Diff:

Example result using Table Diff

Table Diff also allow you to generate custom synchronization scripts to eliminate the data differences between the two tables.

Hope it helps, and any feedback is appreciated.

Who, What and How – Three questions to define your business strategy

Posted on 14. Jan, 2010 by Gia in Business Strategy, Starting Up

When defining a business strategy, it is a good practice to ask yourself three questions:

1. Who?
2. What?
3. How?

The first question (”Who?”) aims at making you think about who are your customers. Who is going to buy your products? Who is going to need your services?

The second question (”What?”) aims at making you think about what product or service your organisation is going to offer. What are we going to sell?

The third question (”How?”) aims at making you think about, for example, how you are going to develop your product, how you are going to deliver your service and how you are going to reach your customers.

If you can’t answer one of these three questions, you’re in trouble.

Good luck!

Introduction to Table Diff

Posted on 13. Jan, 2010 by Gia in Blog, Table Diff

I am writing a brief introduction about Table Diff, to clarify its purpose and why we developed it.

Table Diff is a database comparison tool that allows to easily compare and synchronize the data between two database tables. Table Diff currently supports only Microsoft SQL Server (2000, 2005, 2008 and MSDE) and runs only on Windows.

Table Diff

The reason why we first developed Table Diff is that we needed to compare the data between development and production environments to ensure that data created during the development process (e.g. new records in lookup tables) could be easily copied to the production environment.

There were already few tools out there offering this functionality, but none of them was either reliable enough or cheap enough. Most data comparison tools cost between 150 and 400 US Dollars, which we believe is not an amount of money any software developer or small business can afford spending for performing a data comparison task.

The reason for this is that comparing data is not something that people do very often. You might need to do this a couple of times per month, at most. Maybe there are special cases where you want to compare data more often that that, but we believe that most people won’t compare two tables very often, and therefore they are not willing to spend a fortune on a tool that will facilitate such task.

When developing Table Diff, we aimed at providing a great user interface, and a lot of flexibility.

For what concerns the user interface, we introduced something quite new, which consists in smart context menus that allow you to easily synchronize two values by simply clicking on them. This feature is very useful when you want to quickly synchronize values here and there and you are not really interested in saving a SQL script to be executed at later stage.

Context menus for quickly eliminating data differences

To achieve great flexibility, we introduced features such as custom column mapping (which allows you to map two columns having different name) and custom unique key definition (which allows you to define a custom unique key to use for the comparison).

This week we tested Table Diff with two relatively large tables containing 5,000,000 records each, and it worked like a charm. We’re very happy with it.

Introduction to Porter’s Five Forces

Posted on 13. Jan, 2010 by Gia in Blog, Business Strategy

Brief introduction to Porter’s Five Forces, featuring Michael Porter

Volpet Software Fanpage of Facebook

Posted on 08. Jan, 2010 by Gia in Blog, Social

Hey guys,

I have just created the Volpet Software fanpage on Facebook.

Volpet Software fanpage on Facebook

I have linked it with my Twitter account, so updates to that page will also be published on Twitter.

Become a fan!

Happy New Year

Posted on 03. Jan, 2010 by Gia in Blog

Happy New Year to everybody! We hope the new year will bring all you wish for.

2009 was a great here for us, as we completed the development of the first version of Table Diff. Let’s hope 2010 will be as great!

Screencasting

Posted on 02. Jan, 2010 by Gia in Blog, Starting Up

After a couple of days of work, I managed to create the first screencast for Table Diff. The screencast is not final, but is a starting point. Here’s how I’ve done it:

First, I have created a script document, which included approximately 25 sentences. This, in my case, was a simple Google Docs document. I have then recorded each one of the sentences using Audacity. Each sentence to a separate .wav file.

I have then installed Microsoft Movie Maker, and imported all audio files. Once that was done, all was left to do was to record the video while listening to my voice.

Since I knew that I will have to create the same video again in the future (e.g. when Table Diff’s UI changes), I have first created a SQL script which creates a test database that I can use for all demos. This allows me not to waste time to re-create the database structure and the data every time I need to record the video.

For what concerns the microphone, the one that comes with my laptop (Dell Vostro 1500) was no good. Too much noise (due to the fan, I guess). I bought a Logitech USB Microphone (Part Nr. 980186-0403), and I am really happy with it. Plus it was very cheap (approximately 25 Euro).

For what concerns the software, I am really impressed with Microsoft Movie Maker. I was a bit skeptic at the beginning, but it turned out to be exactly what I was looking for: easy to use. Plus, it’s free.

Before trying Microsoft Movie Maker I tried using Wax 2.0, but it kept on crashing. I also had quite a few crashes with Audacity, especially when recording long files.

My take on screencasting is that it’s not an easy job. It can get quite frustrating, as it is very time consuming and it is difficult to be 100% happy with the output. However, it needs to be done, as it does help passing the message accross.

First Table Diff Update

Posted on 28. Dec, 2009 by Gia in Blog, Table Diff

The first update to Table Diff has been released.

This includes syntax highlighiting in the “View and Execute SQL” screen, and some other minor usability improvements.

For what concerns syntax highlighting I strongly recommend Scintilla and its .NET wrapper Scintilla.NET.

Back to work!