Today was browsing the Blog http://blog.sqlauthority.com/ Pinal Dave expert on Sql server and great Blog writer. Got surprise to see the comments on his Blog. Great job and thank you Pinal for having such a wonderful site and sharing valuable information.

Found following information helpful to me today (*as I was having some issue related to this)

Bulk insert CSV and xls:

With this script we can import the CSV data into sql server table.
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

http://www.sqlteam.com/article/using-bulk-insert-to-load-a-text-file

Following script helped me to import excel file

SELECT
* FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=D:\excelsourcefile.xls;Extended Properties=Excel 8.0')...[Sheet1$]

I used sql server 2000 and sheet 1$ data

http://msdn.microsoft.com/en-us/library/ms179856.aspx

and if we want to export into xls we can use the below query but we have to give heading Name on excelTargetfile.xls file before running this query:

INSERT INTO
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=e:\excelTargetfile.xls;Extended Properties=Excel 8.0;')...[Sheet1$]
SELECT name FROM master.dbo.sysdatabases
GO

we can use the same with OPENROWSET

http://www.mssqltips.com/tip.asp?tip=1202

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

Job server name change (Instance name). Error 14274:

While working on msdb database I restore msdb of other server/instance to different instance. found Pinal’s Blog helped me to solve my issue. by changing the server name in sysjobs table on sql server 2000.

http://blog.sqlauthority.com/2006/12/20/sql-server-fix-error-14274-cannot-add-update-or-delete-a-job-or-its-steps-or-schedules-that-originated-from-an-msx-server-the-job-was-not-saved/

We get the same error when server name changes for that we may have to follow Microsoft KB link as follows

http://support.microsoft.com/kb/281642

Insert multiple records with single insert

Before sql server 2008 to insert multiple records with single query was very difficult, got the very good Blog by Pinal where he shows a trick to insert multiple records with single insert statement as follows link:

http://blog.sqlauthority.com/2007/06/08/sql-server-insert-multiple-records-using-one-insert-statement-use-of-union-all/

As Microsoft introduce new feature in insert statement were we can insert multiple records with single insert statement simply by a comma separated with multiple records value as follows:
http://msdn.microsoft.com/en-us/library/dd776381.aspx

Sending mail from sql server

using SMTP :

Following is a great KB articles by Microsoft, which explains everything and gives a sample script for the same.

http://support.microsoft.com/kb/312839

We can use sqlmail and database mail for sending mail via sql server.

http://support.microsoft.com/kb/311231

http://msdn.microsoft.com/en-us/library/ms186358.aspx

Writing this blog to keep things as a reference to me.

Thanx

Vinay

Twitter @thakurvinay

Reference:

http://blog.sqlauthority.com/