Wednesday, October 13, 2010

Using SELECT to INSERT records – SQL

How to populate one table with data from other table by using one SQL statement?
Normally you would use INSERT statement to populate table with data like this:

INSERT INTO books_csharp(id, title)
VALUES (1, 'Agile Principles, Patterns, and Practices in C#');

but suppose we have already populated table books like this:
id: integertitle: nvarchar(256)tag: nvarchar(32)
1Agile Principles, Patterns, and Practices in C#C#
2Pro C# 2010 and the .NET 4 Platform, Fifth EditionC#
3Agile Testing: A Practical Guide for Testers and Agile TeamsTesting
and we would like to populate similar table books_csharp. So here we go:
INSERT INTO books_csharp (id, title)
SELECT id, title
FROM books
WHERE tag = 'C#'
results:
id: integertitle: nvarchar(256)
1Agile Principles, Patterns, and Practices in C#
2Pro C# 2010 and the .NET 4 Platform, Fifth Edition

P.S. Test and works on Microsoft SQL Server 2008 & PostgreSQL 9