The ‘Database’ Archives
Posted October 23, 2012 by

SQL Server: Copy One Table to Another Using Stored Procedure

When modifying an important table in the database, user frequently backup the table by making a copy of the original table with a different name. Using a stored procedure makes this process simple and convenient to reuse.

Category Database, SQL Server

Posted October 14, 2012 by

SQL Server: How to Left Pad a Number with Zeros

The replicate T-SQL function in Microsoft’s SQL Server makes it simple to add leading zeros to a number value.

Category Database, SQL Server

Posted October 7, 2012 by

SQL Server: How to Swap Values between Two Columns

When working with databases, moving and swapping values between columns is a common task.

Category Database, SQL Server

Posted September 20, 2012 by

SQL Server: How to Check if a File Exists in a Directory

Frequently, when working with SQL, we need to know if a file exists in a local directory or not. This can be done using SQL Server’s built-in procedure known as master.dbo.xp_fileexist. This user-defined function (UDF) checks whether or not a file exists in a specified directory.

Category Database, SQL Server

Posted September 13, 2012 by

SQL Server: Use a CASE Expression in an Update Statement

The CASE expression is used to compare one expression with a set of expressions in SQL. The result of the CASE expression is a Boolean value, true or false. We can use various DML statements like INSERT, SELECT, DELETE and UPDATE with a CASE statement. In this Tech-Recipes tutorial, we will see how to use […]

Category Database, SQL Server

Posted June 3, 2012 by

SQL Server: Generate Insert Statement Script

When running reports, for example, the same insert statements need to be created over and over again. SQL Server includes a great feature to generate these insert statements automatically.

Category Database, SQL Server

Posted June 3, 2012 by

SQL Server Management Studio (SSMS): Display Line Numbers

Losing your patience while writing long lines of code in SQL is understandable. SSMS includes a feature to show line numbers in your query editor to assist with this process. Instructions for SQL Server versions 2005, 2008, and 2012 are included.

Category Database, SQL Server

Posted May 10, 2012 by

SQL Server: Useful Metadata queries

Metadata queries are really helpful in discovering information for a given database schema. Database information including the tables, views, columns names, data types, indexes, and table constraints are all available using queries such as these.

Category Database, SQL Server

Posted April 19, 2012 by

SQL Server – Dealing with NULL values when sorting

When working with NULL values, it’s important how you deal with NULL records in your database. This example walks through and explains how to sort and separate NULL and non-NULL values. This example should work with all database types including MySQL, MS SQL, and postgreSQL.

Category Database, SQL Server

Posted March 27, 2012 by

MySQL: Datetime Versus Timestamp Data Types

The temporal data types in MySQL can be confusing. Hopefully, this example and discussion will help to explain the differences in the timestamp and datetime data types.

Category MySQL

Posted February 18, 2012 by

How to Create a Basic MySQL Table

Creating tables in databases is an important first step for storing data. The CREATE TABLE statement is rich and sometimes confusing. This tech-recipe with an example describes the basics of creating a table in MySQL.

Category MySQL

Posted December 12, 2011 by

Oracle: Using the AFTER INSERT and AFTER UPDATE triggers

A database trigger is a stored procedure that automatically executes whenever an event occurs. The event may be insert-delete-update operations. Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has been occurred and an ‘AFTER UPDATE’ trigger after an update event has been occurred.

Category Oracle