Different Options for Importing Data into SQL Server

In addition to using the Import / Export wizards and/or DTS or SSIS to move data into SQL Server there are also a few other options for doing this that are built into SQL Server. Some these other options include bcp, BULK INSERT, OPENROWSET as well as others. The following examples show you some of these different options for importing data and how you can use some of these inline with your T-SQL code as well as others that can be run from the command line.

BCP
This is one of the options that is mostly widely used. One reason for this is that it has been around for awhile, so DBAs have come quite familiar with this command. This command allows you to both import and export data, but is primarily used for text data formats. In addition, this command is generally run from a Windows command prompt, but could also be called from a stored procedure by using xp_cmdshell or called from a DTS or SSIS package.

Here is a simple command for importing data from file C:\ImportData.txt into table dbo.ImportTest.

bcp dbo.ImportTest in 'C:\ImportData.txt' -T -SserverName\instanceName

BULK INSERT
This command is a T-SQL command that allows you to import data directly from within SQL Server by using T-SQL. This command imports data from file C:\ImportData.txt into table dbo.ImportTest.

BULK INSERT dbo.ImportTest
FROM 'C:\ImportData.txt'
WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )

OPENROWSET
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. By using this command along with an INSERT INTO command we can load data from the specified data source into a SQL Server table.

This command will pull in all data from worksheet [Sheet1$]. By using the INSERT INTO command you can insert the query results into table dbo.ImportTest.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', [Sheet1$])

Here is another example where data is pulled from worksheet [Sheet1$] by using a SELECT * FROM command. Again, by using the INSERT INTO command you can insert the query results into table dbo.ImportTest. The query can be any valid SQL query, so you can filter the columns and rows by using this option.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\ImportData.xls', 'SELECT * FROM [Sheet1$]')

OPENDATASOURCE
This command is a T-SQL command that allows you to query data from other data sources directly from within SQL Server. This is similar to the OPENROWSET command.

INSERT INTO dbo.ImportTest
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]

OPENQUERY
Another option is OPENQUERY. This is another command that allows you to issue a T-SQL command to select data and again with the INSERT INTO option we can load data into our table. There are two steps with this process, first a linked server is setup and then second the query is issued using the OPENQUERY command. This option allow you to filter the columns and rows by the query that is issued against your linked data source.

EXEC sp_addlinkedserver 'ImportData',
'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'C:\ImportData.xls',
NULL,
'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT *
FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')

Linked Server
Here is yet another option with setting up a linked server and then issuing a straight SQL statement against the linked server. This again has two steps, first the linked server is setup and secondly a SQL command is issued against the linked data source.

EXEC sp_addlinkedserver 'ImportData',
'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'C:\ImportData.xls',
NULL,
'Excel 8.0'
GO

INSERT INTO dbo.ImportTest
SELECT * FROM ImportData...Sheet1$

Sending Emails with Dynamic Content

I want the message of the email to like
Hi , Thank you for sending this email. See you soon. Good Bye,
Code :
try
{
StreamReader sr=new StreamReader("MyContent.htm");
sr = File.OpenText("MyContent.htm");
string result = sr.ReadToEnd();
sr.Close();
}
catch(Exception ex)
{
Response.Write(ex.Message);
return;
}
string MessageBody=result.Replace("", txtFriendName.Text);
MessageBody=MessageBody.Replace("", txtMyName.Text);
MessageBody= MessageBody.Replace("",DateTime.Today.ToString());
MailMessage mail = new MailMessage();
mail.Body = MessageBody;
mail.BodyFormat = MailFormat.Html;
mail.From = "YOUR EMAIL ID";
mail.To = "YOUR FRIEND'S EMAIL ID";
mail.Subject = "Dynamic Content Email From "+ txtMyName.Text;
SmtpMail.SmtpServer = "your email server";
SmtpMail.Send(mail);

Message Box in Asp.Net

public void ShowMessageBox(string message)
{
Page currentPage = (Page)HttpContext.Current.Handler;
if (currentPage != null)
{
ScriptManager manager = ScriptManager.GetCurrent(currentPage);
string _msg = "alert('" + message + "');";
if (manager != null && manager.IsInAsyncPostBack)
{
ScriptManager.RegisterClientScriptBlock(currentPage, currentPage.GetType(), "msg", _msg, true);
}
else
{
currentPage.ClientScript.RegisterStartupScript(currentPage.GetType(), "msg", _msg, true);
}
}
}

When I See You !!

Nothing explains,

The rush of blood

Pumping through my veins

I hope you know,

That I like You.

I hope you say

That you do too.

I knew that

Iwould like to be,

Protectedby someone like you.

How clear can I

Make it all now,

What can I say

Where why and how.

I cannot think

Words unspoken

The way i thought

My heart was broken.
Missing u a lot today…. dont […]

Why do men love women?

The feminine sex is one of the most refreshing things ever hit the humanity. I mean, come to think of it, at the root of every historical event, there is always a women involved. Even in fairytales, mythologies and legends, no fable is complete without the touch of the feminine.
Man is the residue of Woman. They are born in the blood, kicking and screaming, because they’ve been wrenched from the best place in existence. So much that Man has to spend the rest of his life in pursuit of the recreation of that consciousness. Right from a warm bed, to a cosy home, to a cushy job, to a luxury car to a… you name it.
A woman is the best concept God has ever sold to a Man. They always talk about the male getting in touch with his feminine side. How come they don’t say that about woman? Hehehe . They don’t need to discover the masculine side, as long as they have something masculine by their side. Women are like cats, you can never own them. But if you treat them like the royalty they are, then their kingdom is yours.
Men need women to fall in love with; women just don’t need a mirror. God created man before woman. But you always make a rough draft before the final masterpiece.
Women are great at communication, the best in fact. A woman can say more in a sigh than a man can in a speech. They get the last word in every argument. Anything a man says after that is the beginning of a new argument. Funny thing is , men can argue forever; only women can make them stop. All she needs to do is start crying.
A man needs a woman to make money, If women didn’t exist then all the money in the world would have no meaning. Think about it, a world without women.. no television, no chocolate, no breakfast, no channel No 5, no Bipasha Basu, no Brad Pitt.. no Madonna, and the ultimate horror – no Playboy !
A man spends his whole life trying to be a man, a woman spends hers trying not to be woman. She’s a mother, a wife, a daughter, a sister, a grandmother etc. A man will always try to be a man first. It is this conflict that is his mystery.
All art serves the feminine. There are very few female poets, that’s because most they become the poem. There are more paintings of women than there are of any other subject. That’s probably because it’s the only time a man can take his won sweet time with various women without the guilt.
Men love naked women. Men are supposed to love them! How’s that for getting in touch with your feminine side? Most “married” or “faithful” men love to look at pictures of naked women. That doesn’t mean they’re cheating, it’s just that they can’t get enough of the feminine.
A woman will not own just one pair of shoes, will she?
So will man ever “figure” out women? I don’t think so. We’re not supposed to. Man is like the Earth, he just needs to keep on spinning to keep things going. The woman is the Galaxy, she keeps the orbit right, the seasons changing, the weather favorable and the sun always shining.