Connect SQL Server from Excel and retrieve data

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

Channel: SQL with Manoj

Views: 156,249

Keywords: SQL Server, SQL, Microsoft SQL Server, Excel and SQL

Id: h1ljz9uVW4c

Length: 7min 1sec (421 seconds)

Published: Sun Aug 07 2016