How to import data from Excel file to SQL Server:

Posted on December 10, 2012 by Paras Doshi • Posted in Microsoft Office, SQL, SQL server • Tagged Database, Excel, FAQs Help and Tutorials, Microsoft Excel, Microsoft SQL Server, Select (SQL), sql, sql server, SQL Server Integration Services • 1 Comment

In this post, we would see how to import data from Excel file to SQL Server using Import and Export Wizard, we would import data from four excel worksheets to SQL server. Here are the steps:

1) Right click the Target Database > Tasks > Import Data to open the SQL Server Import and export wizard

2) select the Data Source. In this case, Excel file. You would select the worksheets to fetch data from later, for now – select the excel file as shown below:

3) choose the Target Database:

4) Select the tables (in our case- worksheets) or specify a query. For the purpose of this demo, we would go with “copy data from one or more table or views” option:

5) Select the worksheets from the Excel file and also specify the tables in the target systems. If the tables in the target database are not already created, then would be created by this wizard:

6) You have the option of running this wizard immediately or you have the option of creating a SSIS package and then run it later. For the purpose of this post, we would select Run Immediately

7) Review the information and click Finish

8) After successful execution:

Conclusion:

In this blog post, we saw how to import data from Excel file to SQL server.

Guest Blog: How we use Fuzzy Lookup add-in in our company to solve data inconsistency problems: (parasdoshi.com)

Back to basics: What’s CRUD?

Posted on November 30, 2012 by Paras Doshi • Posted in Back to Basics, SQL, SQL server • Tagged Create read update and delete, CRUD, Database, FAQs Help and Tutorials, Languages, Select (SQL), sql • 2 Comments

Some year’s ago – I got introduced to SQL. At that time, I recall, I was sitting in a lab and one of the first exercises we did was to create a table in a database and adding data in it. In next lab, we ran SQL commands that updated records and deleted few. After we’re done – our instructor told us what we learned were the most basic programming functions i.e CRUD operations

CRUD stands for Create, Read, Update and Delete.

C: Create

R: Read

U: update

D: delete

Let’s see the SQL equivalent of CRUD operations:

Operation	SQL
Create	INSERT
Read	SELECT
Update	UPDATE
Delete	DELETE

Is the concept of CRUD just applicable to SQL?

No. in fact, if you start learning programming or web development – one of the first things that you get to learn is how to run CRUD operations with that particular language.

Conclusion:

In this blog post, I documented four (4) basic programming functions i.e. Create, Read, Update and Delete.

[Video] I am 22 years old, So is SQL server! check out, history of SQL server:

Posted on February 16, 2012 by Paras Doshi • Posted in Paras Doshi - Blog, SQL, SQL server • Tagged Database, Microsoft SQL Server, Server, sql, sqlserver • Leave a comment

Book Review: SQL server Interview Questions and Answers by Pinal Dave and Vinod Kumar.

Posted on November 22, 2011 by Paras Doshi • Posted in book-review, SQL, SQL server, TSQL • Tagged Database, Microsoft SQL Server, Pinal Dave, Relational database, sql, vinod kumar sql • 4 Comments

Though SQL server is an huge ocean – this book does justice to touch-base on every different aspect of the product. Starting from the basic concepts and then discussing Tricky questions, authors Pinal Dave and Vinod Kumar lucidly explains each aspect in a Question Answer Format.

Personally, i liked the Tricky question section because it made me think about the topic at hand. And i believe, this exercise of thinking on my feet after reading the question will help me tackle questions during interviews that require quick thinking. And for questions which i knew, i learned the perfect way of answering them.

And every chapter, ends with a summary (called points to ponder) which is an excellent place to reflect upon and test one’s knowledge. And even if you are not planning to go for interview’s in near future – this book is a handy reference for all FAQ’s you may have on a day-to-day basis.

It’s an excellent book to master the SQL server Basics and Gain confidence before a SQL server based Interview. And perfect for any who is a SQL server Database Developer or a SQL server DBA or planning to be one!

Review cross-posted from: http://www.amazon.com/review/R2R7QM1NFVI8RE/ref=cm_cr_pr_perm?ie=UTF8&ASIN=1466405643&nodeID=&tag=&linkCode=

How to create users with limited access in SQL Azure?

Posted on November 4, 2011 by Paras Doshi • Posted in #sqlazure, SQL, SQL Azure, SQL-Azure, SQLAzure • Tagged Database, Login, Master, Microsoft, Microsoft SQL Server, Server (computing), sql, sql azure • 1 Comment

In this blog post, we will see how to mange the SQL Azure login and learn how to create users with limited access in SQL Azure.

Now, To create a user, we need to have a login. And we create a login by logging into Master database with server-level principal login or user with loginmanager role.

So now i log into Master database of one of my SQL Azure server with server level principal login. And I run the following query to create a login named ‘parasdoshicom’:

create login parasdoshicom with password=’Passw0rd’

go

Quick note: choose a strong password – otherwise you may get an error. Strong passwords are those that has 8 character long with combination of symbols, numbers and letters. So i have ‘zero – 0′ in my password.

Now, we want to create a user in a user-database that has limited access. To do so, first login to that database. In my case, I want to create an user in the ‘adventureworksltaz2008r2’ and give him just the db_datareader role. let’s see how we do that.

So first I login to ‘adventureworksltaz2008r2’ with server-level principal login. And then:

1. run the command to create a user (let’s name it parasdoshicom which is same as the login name in this case) from the login parasdoshicom

create user parasdoshicom from login parasdoshicom

go

2. Assign the db_datareader role to user ‘parasdoshicom’

EXEC sp_addrolemember N’db_datareader’,N’parasdoshicom’

go

Now to test what we have just done. I am going to login to the “adventureworksltaz2008r2” database with parasdoshicom user credentials.

1. And I am going to run a select query <- This should work

2. And I am going to run a delete query <- This should not work

Let’s see:

1. select query with new login “parasdoshicom”

As expected, it runs!

2. Now let’s try running a delete command:

And it did not work. Notice that permission was denied. And that is what we intended, right?

So we were successful at creating a user “parasdoshicom” that can only read data from the “adventureworksltaz2008r2” database. And thus we have limited the access for the user in SQL Azure.

Quick summary of Ideas that I have logged at My Great Windows Azure Idea site (parasdoshi.com)
Top two ways you can screw up the established SQL Azure connection from SSMS 2008 R2 running locally (parasdoshi.com)
part 5 of “Getting started with SQL Azure” is live! (parasdoshi.com)

How to rename a SQL Azure database?

Posted on November 3, 2011 by Paras Doshi • Posted in #sqlazure, SQL, SQL Azure, SQL server, SQL-Azure, SQLAzure • Tagged azure, Cloud computing, Microsoft, Microsoft SQL Server, sql azure, sqlazure, windows azure • 1 Comment

Can we rename a database in SQL Azure? Absolutely! In blog post, we will see how we can do that?

First, few things to note:

1. You need to connect to Master Database.

2. And you can rename only user databases. That means the master database cannot be renamed.

3. And the Alter database (which is the TSQL statement to rename database) should be the only statement in the batch.

Now, Here is the query:

alter database <old-database-name>

modify name =<new-database-name>

go
Here is the screenshot when I tried to rename the database name by login with the principal server login into the Master Database:

So that’s it – you can rename the database in SQL Azure.

Part 2 of “Getting started with SQL Azure” is live!

Posted on September 19, 2011 by Paras Doshi • Posted in #sqlazure, Paras Doshi - Blog, SQL, SQL Azure, SQL-Azure, SQLAzure, Windows Azure • Tagged Cloud computing, Database, Microsoft, Microsoft SQL Server, Programming, sql, sql azure • 1 Comment

Aim of “Getting started with SQL Azure” series is to offer you a set of brief articles that could act as a Launchpad for your to-be wonderful journey of exploring Microsoft’s cloud based database solution i.e. SQL Azure.

In this blog post, i have discussed:

1) How to sign up for a Free trial of windows azure (to play with SQL Azure)!

2) How to create your very first SQL Azure database (and a table too!)

3) How to connect to SQL Azure server Via SQL server Management studio.

Migrating Data from SQL server to SQL Azure using BCP

Posted on August 8, 2011 by Paras Doshi • Posted in #sqlazure, Paras Doshi - Blog, SQL, SQL server, SQL-Azure, SQLAzure • Tagged Data migration, Database, FAQs Help and Tutorials, Microsoft, Microsoft SQL Server, Programming, sql, sql azure • 2 Comments

BCP is an ideal straightforward command prompt driven tool to push data from one table to another similar table. This makes it an excellent tool to carry out data migration from SQL server to SQL Azure. And here is a blog post: Migrating SQL server Data to SQL Azure using BCP << Paras Doshi, i have written to show how we can migrate SQL server data to SQL Azure using BCP tool.

My article on “Tuning SQL Azure database – Part 1” got published in SolidQ Journal (July 2011) << Paras Doshi

Posted on July 11, 2011 by Paras Doshi • Posted in #sqlazure, solidq.com, SQL, SQL server, SQL-Azure, SQLAzure • Tagged SolidQ Journal, sql azure • 2 Comments

My article on “Tuning SQL Azure database – part 1” got published in SolidQ journal July 2011 Issue. The editorial of the Journal written by Fernando G. Guerrero [Global CEO, SolidQ] summed up the article by following words: "Even if cloud computing hides some back-end complexity from us, databases still need to be properly designed and queries properly written. To this end, Paras Doshi explains how to tune SQL Azure databases".

LINKs:

Read : Tuning SQL Azure Database – Part 1 by Paras Doshi

Download: Tuning SQL Azure Database – part 1 by Paras Doshi

Download: SolidQ Journal July 2011