Update Query with a JOIN Statement

I found something useful when I am trying to update columns in a master table (tbl1) from data in a temp table (tbl2). These updates can be done by making a query to update a table by doing INNER JOIN.

Below is the example of a joined update query:

UPDATE tbl1
SET field_name = B.field_name
FROM tbl1 A
INNER JOIN tbl2 B
ON A.ID = B.ID


Hope this helps.

Generate random rows with SQL

The secret to retrieve random rows from database is really simple. Here are some example SQL statements that do not require additional application logic.

SQL Server:
SELECT TOP 1 *
FROM Users
ORDER BY NEWID()

MySQL:
SELECT * FROM Users
ORDER BY RAND()
LIMIT 1;

Oracle:
SELECT * FROM
(SELECT * FROM Users
ORDER BY dbms_random.value )
WHERE rownum = 1

PostgreSQL:
SELECT * FROM Users
ORDER BY RANDOM()
LIMIT 1

Access:
SELECT TOP 1 *
FROM Users
ORDER BY Rnd(UserID)


Cheers!

Shrinking Transaction Log File in SQL SERVER

Sometimes it is necessary to shrink a transaction log in a database manually if the log file grows unexpectedly in order to save some disk space.

Here are the steps use to truncate transaction log in a database:

BACKUP LOG <databasename> TO DISK = '<backupfile>'
DBCC SHRINKFILE (<filename>, <targetsize>) WITH NO_INFOMSGS

e.g.:
BACKUP LOG NORTHWND TO DISK = 'C:\NorthwindLog.bak'
DBCC SHRINKFILE (Northwind_log, 100) WITH NO_INFOMSGS

To check your logical file name of your log file, you can run the query as below to get the logical file name something like 'xxx_log':
sp_helpdb '<databasename>'


More information:
http://msdn.microsoft.com/en-us/library/ms189493.aspx
http://support.microsoft.com/kb/907511

BULK INSERT in MS SQL SERVER

In IT environment, it is always a necessary to import data from data files into database.

And recently, I have to deal with import a huge load of data (around 200 MB for 1 CSV input file) from CSV files into MS SQL SERVER. So what I had done to achieve this is by using BULK INSERT.

Firstly, prepare a CSV data file with the following content and save as test.csv.
July,Singapore,25
James,Australia,50
May,China,29

And then run scripts as following to load all the data from the CSV file into database.

--Temp table to store the data
CREATE TABLE CSVTest
(
   Name VARCHAR(500),
   Country VARCHAR(500),
   Age INT
)
GO

--Bulk insert into CSVTest
BULK INSERT CSVTest
FROM 'C:\test.csv'
WITH
(
   FIELDTERMINATOR=',',
   ROWTERMINATOR = '\n'
)
GO

--Get all data from CSVTest
SELECT *
FROM CSVTest
GO

--Drop the temp table
DROP TABLE CSVTest
GO

How to Insert Value into an Identity Column

In database tables, normally identity columns are used as primary keys that automatically generates numeric values for every new inserted row.

For this identity (AutoNumber) column, you are not allow to insert your own value. So what if you really want to insert your own value into the column?

A very easy way to do this. I will show you how to do this.

Firstly, create a table Sample by using the script below into your database.

CREATE TABLE Sample (
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name VARCHAR(50) NOT NULL )
GO

Normally if we want to insert a new row into this table, you will have to only insert the value of Name, as script below:

INSERT INTO Sample(Name) VALUES ('July')

Now, we try to insert a value into the Id column.

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

When this query is executed, an error will be generated:

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'Sample' when IDENTITY_INSERT is set to OFF.

To insert this record into table without any error, you just need to enable the IDENTITY_INSERT. It should look like this:

SET IDENTITY_INSERT Sample ON

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

SET IDENTITY_INSERT Sample OFF

But there are some points that you need to be aware of about IDENTITY_INSERT.

1.   Only 1 table can have the IDENTITY_INSERT property set to ON at a time. If you try to enable IDENTITY_INSERT to ON for a second table while a SET IDENTITY_INSERT ON statement is issued for another table, SQL Server will return an error message.
2.    If the value that you have inserted is greater than the current identity seed in your table, the new inserted value will be set as the current identity seed.

For example, table Sample has its current identity value 1 and you want to insert a new row with the identity value 11. When you insert another new row, your identity value will start from 12, instead of 1.

SET IDENTITY_INSERT Sample ON

INSERT INTO Sample(Id, Name) VALUES (11, 'July')

SET IDENTITY_INSERT Sample OFF

INSERT INTO Sample(Name) VALUES ('August')

Result:

Id Name
11 July
12 August

Cheers.

@@IDENTITY, SCOPE_IDENTITY and IDENT_CURRENT in Transact-SQL

In Transact-SQL, you can use @@IDENTITY keyword to retrieve the value of the identity column whenever an INSERT, SELECT INTO or bulk copy statement is completed. It contains the last value that is generated by the statement.

For example, the query below inserts a new record into a table and returns a result set containing the ID of the inserted record.

INSERT INTO myTable (myName) VALUES (‘E-JULY’)

SELECT @@IDENTITY AS [@@IDENTITY] --Last inserted identity value will be inserted

However, beware of a potential for a subtle bug in some circumstances. For example, if you use @@IDENTITY and insert into a table (myTable) that run a trigger, and if the trigger inserts another record into another table (mySubTable), which happens to have an identity column, the @@IDENTITY will contain the identity value of table mySubTable instead of the identity value of myTable.

One option to prevent this from happening, we can use SCOPE_IDENTITY() function instead which will return the last inserted identity value in the current scope, in this case the returned value will be the identity value of myTable. Every trigger, function, procedure and batch has its own scope. SCOPE_IDENTITY shows the last inserted identity value in the current scope, which ignores any triggers that might fire.

INSERT INTO myTable (myName) VALUES (‘E-JULY’)

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

IDENT_CURRENT is limited to a specified table. It returns the last identity value generated for a specific table or view in any session and any scope.

INSERT INTO myTable (myName) VALUES (‘E-JULY’)

SELECT IDENT_CURRENT('myTable') AS [IDENT_CURR]

However, please note that SCOPE_IDENTITY and IDENT_CURRENT are only available in SQL Server 2000 or higher version.

15 Date Formats in SQL Server 2000

--'YYYYMMDD'
SELECT CONVERT(CHAR(8), GETDATE(), 112)
--'YYYY-MM-DD'
SELECT CONVERT(CHAR(10), GETDATE(), 23)
--'YYYY-MMM-DD'
SELECT STUFF(CONVERT(CHAR(10), GETDATE(), 23), 6, 2, LEFT(DATENAME(m, GETDATE()), 3))
--'YYMMDD'
SELECT CONVERT(VARCHAR(8), GETDATE(), 12)
--'YY-MM-DD'
SELECT STUFF(STUFF(CONVERT(VARCHAR(8), GETDATE(), 12), 5, 0, '-'), 3, 0, '-')
--'YY-MMM-DD'
SELECT STUFF(STUFF(STUFF(CONVERT(VARCHAR(8), GETDATE(), 12), 3, 2, LEFT(DATENAME(m, GETDATE()), 3)), 6, 0, '-'), 3, 0, '-')
--'MM-DD-YY'
SELECT CONVERT(CHAR(8), GETDATE(), 10)
--'MMDDYY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 10), '-', SPACE(0))
--'MM/DD/YY'
SELECT CONVERT(CHAR(8), GETDATE(), 1)
--'MM/DD/YYYY'
SELECT CONVERT(CHAR(10), GETDATE(), 101)
--'DD-MM-YY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', '-')
--'DD-MMM-YY'
SELECT STUFF(REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', '-'), 4, 2, LEFT(DATENAME(m, GETDATE()), 3))
--'DDMMYY'
SELECT REPLACE(CONVERT(CHAR(8), GETDATE(), 3), '/', SPACE(0))
--'DD/MM/YY'
SELECT CONVERT(CHAR(8), GETDATE(), 3)
--'DD/MM/YYYY'
SELECT CONVERT(CHAR(10), GETDATE(), 103)

Hope this will help. Cheers!

Differences between VARCHAR and NVARCHAR

VARCHAR is an abbreviation for variable-length character string with a maximum limit of 8000 bytes.

NVARCHAR are the Unicode equivalents of VARCHAR. Unicode uses two bytes per character, which allows the storage of multilingual characters with a maximum length of 4000 bytes.

The most common use of NVARCHAR is to store character data that is a mixture of English and non-English symbols, such as English and Chinese. Other languages have an extended set of character codes that need to be saved and this datatype allows for this extension. If your database will not be storing multilingual data you should use the VARCHAR datatype instead. This is because to store the extended character codes for other languages, NVARCHAR requires twice as much space as VARCHAR.

VARCHAR and NVARCHAR in SQL SERVER 2005
One major change to both of the datatypes in SQL SERVER 2005 is the creation of VARCHAR(MAX) and NVARCHAR(MAX) which allows you to stored up to 2GB in a single variable. And it also allows you to use these data types as parameters in your stored procedure, internal variables etc.

SQL Server function for datetime - DATEADD

DATEADD is a date function that will return a datetime value based on the number interval add to the particular date part of the specified date. Here is the syntax for DATEADD:

DATEADD(date_part, number_interval, specified_date)

date_part is the parameter that specifies on which part of the date to be manipulated with the number interval. You can add month, year, day and etc. You can use

MONTH, MM or M for month
YEAR, YYYY, YY for year
DAY, DD, D for day
HH for hour
SS, S for Second

For example :
SELECT DATEADD(D, -1, GETDATE())AS [Yesterday]
SELECT DATEADD(MM, 3, GETDATE())AS [FourMonthsFromNow]
SELECT DATEADD(YEAR, -2, GETDATE())AS [TwoYearsAgo]

Run a Query to Show All Table

Mysql:
This allows you to see all existing tables inside the database selected.
mysql> SHOW TABLES;

Oracle:
Oracle doesn't support the above command, though an equivalent would be to use this sql statement:
sql > SELECT * FROM tab WHERE TABLE_TYPE = 'TABLE';
or
sql > SELECT * FROM cat WHERE TABLE_TYPE = 'TABLE';
or
sql > select * from user_objects where object_type = 'TABLE';

SQL Server:
Run the script below in your query analyzer.
SELECT * FROM INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

Dealing with apostrophes in SQL strings

In many applications, the developer has side-stepped the potential use of the apostrophe in some of the text fields. So when adding values to a table, be aware that problems may be caused by embedded apostrophes in a string.

Consider the SQL Insert statement below.

INSERT INTO table_name (table_field_name) VALUES (‘O’reill’, ‘92314567’)

Notice that there is an apostrophe in the text “O’reill”. In SQL, the apostrophe is an illegal character. It is interpreted as a string delimiter or the end of the string. So when it encounters to the text and detects another apostrophe, an error will occur.

A very simple solution to solve the problem: to allow apostrophes to be inserted into a database, simply double-up all occurrences of the apostrophes. So the final SQL will look like this:

INSERT INTO table_name (table_field_name) VALUES (‘O’’reill’, ‘92314567’)

In VB.NET, in order to convert the single enclosed apostrophe to two apostrophes, you can use Replace statement to accomplish the replacement.

"'" & Replace (txtName.text, "'", "''") & "'"

Good Luck!

DELETE FROM and TRUNCATE table functionality

There are two main commands used for deleting all the data from a table: TRUNCATE and DELETE FROM. Two of these achieve the same result; however there are significant differences between the two. There are advantages, limitations and consequences that you should consider to decide which one to use.

About TRUNCATE
 TRUNCATE command is faster because it removes all records in a table by deallocating the data pages used by the table, thus reduces the resource overhead of logging in the log and the number of acquired locks as well.

 It does not generate any UNDO information, does not fire DELETE triggers and does not record any information in the snapshot log.

 The only record of the truncation is the page deallocation, so the removed data cannot be restored.

 WHERE condition cannot be applied in TRUNCATE command.

 Auto commit and cannot roll back.

 It reset the IDENTITY value back to the SEED and the deallocated pages can be re-used immediately. If you want to retain the identity counter, use DELETE FROM command instead.

 This means if you have a table with an identity column and you have 20 rows with a seed value of 1, you last record will have the identity’s value as 20. After the table is truncated, the identity column will have the value of 1 when a new record is inserted. While for DELETE command, when a new record is inserted, the identity column will have a value of 21.

 This command cannot be used on tables that are referenced by foreign keys, or involved in replication or log shipping.

 If a TRUNCATE TABLE is issued against a table that are referenced by foreign key, an error is returned: Cannot truncate table ‘xxx’.


About DELETE FROM
 DELETE FROM command logging all rows in the transaction log, thus it consumes more database resources and locks.

 WHERE clause can be applied to narrow down the rows that need to be deleted.

 Not an auto commit and the removed data can be rolled back.

 To reset the IDENTITY value back to the seed, use DBCC CHECKIDENT command once you are done with the DELETE FROM command.

 When a table requires all records to be deleted and TRUNCATE command cannot be used due to against the rules, the following statement can be used to achieve the same result as TRUNCATE command:

DELETE FROM [table_name]
DBCC CHECKIDENT(“table_name”, RESEED, “reseed_value”)

Hope this article provides some clarify to you on this issue.