Jan 3 2012

Year In Review 2011

It is that time of year where we reflect on the accomplishments of the passing year and look forward to the one to come. Here is a window into the past year in technology through this year’s popular posts on TechKnow Juixe.

Code and Design

Reviews and Rants

Retweet 2011

Year in Review


Sep 28 2011

Copy Records From One Database To Another

Most applications require a database of some sorts, and when working with a database you most likely will have a production, development, and test database to work with in addition to backups. A common problem I found is that sometimes I want to move rows of data for a particular database table between the different databases I work with. For example, I found I need to copy a subset of data from a table from the production database to the test database so that I had good data to test with. A typical approach is to create a backup from the production database and restore it as a test database, but if you just want to copy a subset of rows from a particular table that approach is overkill.

In SQL Server, you can use a insert statement to insert new rows in a database where you select and filter the data from a different database. This is also true for other database vendors but the SQL might be slightly different.

To further illustrate this, imagine I have a database table blog_posts in a production database called BlogProduction and a test database is called BlogTest and they both reside on the same SQL Server instance. From SQL Server Management Studio or SQLCMD or from where ever you run your SQL statement you can run the following statement.

INSERT INTO blog_posts
SELECT * FROM BlogProduction.dbo.blog_posts

If you run the above statement from the perspective of the BlogTest database, you are will insert all the records from the blog_posts table in the BlogProduction database into the blog_posts table in BlogTest. The above example, assumes the database owner is dbo. It is not required to name the database owner and the following does the same.

INSERT INTO blog_posts
SELECT * FROM BlogProduction..blog_posts

You can also add a where clause to the select statement to filter out certain data to insert to your current database.

INSERT INTO blog_posts
SELECT * FROM BlogProduction..blog_posts
WHERE id > 100

In this way, you can move data from different database tables from different databases.


Sep 16 2011

Create SQL Server Database and Database Tables From a File

When working with a database, it’s is always handy to develop a series of scripts to backup, restore, create, move databases around. As of late, I’ve been working with SQL Server a lot so I’ve come to appreciate the sqlcmd utility.

You can run the sqlcmd utility from the command line and run a input sql script and capture the output in a file. The input sql script file can run any sql statement, such as creating a new databases, views, tables, or procedures. You can also restore and backup any given database or whatever else you require.

To run the sqlcmd utility and have it read a file, createTables.sql, you can run something like the following from your command line, of course if you have SQL Server Management Studio and sqlcmd installed.

sqlcmd -S localhost -d dbName -U user -P password -i createTables.sql -o output.out

Apr 20 2006

Show SQL Tables

When working with a database sometimes you will want to look up all the available database tables. To show the available tables in SQL Server you can do the following:

select * from information_schema.tables;

In Oracle you can use any of the following statements:

select * from user_tables;
select * from all_tables;

In MySQL I have been using the following since MySQL 3:

show tables;

But in MySQL 5 you can use the following:

select * from information_schema.tables;