How to fetch data from sql server to excel
Connect SQL Server from Excel and retrieve data
Timecodes:
hello friends welcome back to sequel
minaj today I am going to talk about
Excel and sequel so this is my first
video of excel and today I will show you
how to connect to sequel server from
Excel and get the data okay so let me
take you to SSMS here we have
adventureworks 2014 database restored
already and we have lot of set of tables
and we will see how to you know get this
person dot person table okay so let me
open Excel for you and open a new blank
workbook okay so here to connect to
Excel you have this data tab click on
this data tab and you will get a lot of
options okay and these are the journal
sources from where you can get the data
okay so I have to click from other
sources and the first one is from sequel
server ok so just click on the sequel
server and you will get this dialog box
so here you have to apply the server
name so my server name is this manoj
pandey del ok and to just confirm it you
can just click on connect and click on
database engine so the server name that
you recently connected will come over
here right so you can just copy this
okay and paste it here in excel yeah so
logon credential is my windows
authentication if you have if you want
to do it by some other sa user you can
just choose the username and password
and just give it here so I'll just click
Next here it asks me which database I
want to connect so I'll select the
adventure box 2014 from this drop-down
and these are the tables that it shows
me so it shows me all the tables and all
the views that I can connect to so but
I'm interested in person table so I'll
just select the person table and click
Next okay and it gives the connection
name and you can even change it but I'll
just keep it as it is and I will click
finish okay so this input data gives me
some options to render this data so the
first option is that
so it if I choose this option I will get
data in tabular format second is the
pivot report third is the pivot chart
and fourth one is only to create the
connection and don't render the data
right so if I want to show some charts
or if have some figure related data then
I'm then I can use this but I right now
it is a person table so it will be
having person data so I have these two
options so right now what I will do is I
will just select the table okay and
click OK
so as you can see here all the details
of this person are pulled from sequel
server okay and displayed over here so
these are all the columns that are
present in person dot person table right
and these are all the records and let me
see how many records are there so there
are around nineteen thousand nine
hundred seventy three records that Excel
has pulled from sequel server and let me
just confirm how many records are there
so what I will do I will just query this
table select star from person dot person
and execute it and you will see these
columns same same columns that are
pulled in Excel and nineteen thousand
nine hundred seventy two records these
are pulled in your excel sheet okay so
now let's say you want to customize the
you know columns or if you want to you
know specify a query so let's say if you
have a specific query by oles having
specific columns let's say you have
business into the ID first name last
name middle name okay only these columns
so how can you do that so before that
I'll just create the query first name
middle name last name okay so I have
this query okay what if I want to have
this query instead of the whole table I
don't want all the columns I just want I
am just interested in these particular
columns okay so how can you do that you
have to go to the data tab and go to the
connections
and here click on properties and just
click on definition right so here in
this box you will see lot of details
like what was the connection file right
this was the connection file that was
created when you created the connection
initially this is the connection string
and command type command type right now
it is selected as table ok so if I have
and the command text is the table name
database name schema name and the table
name if I want to specify a custom query
I have to change this command type from
table to sequel ok and instead of this I
have to you know specify my query the
connection in this workbook will no
longer be identical to the connection
defined in this the link of that
external file will also be removed do
you want to proceed yes so if I just
proceed and close it you can see only
the columns that we selected are
retrieved here okay now let's say this
particular person's name got changed in
sequel server so what it reflect here so
this business entity ID is 285 ok let's
go ahead and change it so I'll just copy
it here and paste it and I'll update the
record update person dot person set let
me change the middle name too right now
it is e I'll change to Z ok and where
the business entity ID is equal to this
so this is my update query and I am
going to change the middle name of this
particular person so let me show you how
the record now looks like here right so
right now this name is Syed z Abbas and
here
still it is coming as AE because we have
not refreshed it so if you just click
here and do a refresh it will pull the
data and it will change it ok so this is
how you connect to sequel server from
excel and get the data into your excel
in the desired format like you the way
you
want okay so this is it for today in
this video I will talk more about the
Excel and sequel things like this in
mind coming videos please like and
subscribe the video thank you very much
Related queries:
how to get data from sql server to excel using vba
how to get data from sql server to excel using query
how to import data from sql server to excel
how to get data from sql server to excel
how to extract data from sql server to excel
how to import data from sql server to excel automatically
how to import data from sql server to excel using vba
how to import data from sql server to excel using macros
how to extract the data from sql server to excel using vba
