SQL Server programmers can choose between two functions in SQL Server 7 and 2000 for converting expressions from one type to another. In many cases there will be a need within a stored procedure or other routine to convert data from, say, a datetime type to a varchar type; CONVERT and CAST are used for such things.

Because SQL Server provides both functions, there may be some confusion about which is best to use and under what circumstances. CONVERT is specific to SQL Server, and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers.

CAST is the more ANSI-standard of the two functions, meaning that while it’s more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it’s also less powerful. CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression. For those reasons, it’s best to use CAST first, unless there is some specific thing that only CONVERT can provide in the work you’re doing.

CAST and CONVERT can also be used in conjunction with each other to achieve certain effects. For instance, a typical way to produce a char variable with the current date would be to use:

SELECT CONVERT(CHAR(10), CURRENT_TIMESTAMP, 102)

(The 102 indicates that the ANSI date format, yy.mm.dd, is to be used.)

However, if you wanted to cast this variable explicitly as a datetime or smalldatetime value for compatibility in a specific database column, you could use:

SELECT CAST(CONVERT(CHAR(10),CURRENT_TIMESTAMP,102) AS DATETIME

This would return the value yy.mm.dd 00:00:00 (i.e., 12:00AM as the timestamp; the time information from CURRENT_TIMESTAMP would be discarded).

This is the traditional method for inserting multiple values in a table.

USE YourDBName
GO
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘First’,1);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Second’,2);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Third’,3);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Fourth’,4);
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘Fifth’,5);
GO

However this cannot be used if you want to insert multiple values into the table via your code because in this case you’ll have to write the code to insert the value in the DB in a for loop which is not at all feasible.

So you can write the code to generate the insert statement like,

USE YourDB
GO
INSERT INTO MyTableName (FirstCol, SecondCol)
SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5
GO

The above code inserts multiple values into DB with a single SQL insert query. This is the best means to use in your code as you’ll just have to create this query in your for loop and the query will run only once.

Also you can use the following query syntax for multiple inserts in SQL 2008

USE YourDB
GO
INSERT INTO MyTableName (FirstCol, SecondCol)
VALUES (‘First’,1),
(‘Second‘,2),
(‘Third‘,3),
(‘Fourth‘,4),
(‘Fifth‘,5)

Please let me know if you have any better ideas than this one.
Njoy coding!!!