Export SharePoint Lists To SQL Server Tables

[Music] today we are going to take an in-depth look into sequence manager we are going to we are going to replicate three lists from SharePoint into a sequel server database and then we are going to compare and see what kind of columns are generated in sequel server from the ship one list columns so the three lists that we are going to use for today's MOS products which contains the list of all the products and if we have a look at the list settings we can see that we have different types of columns created in this list this will help us to see how these columns not going to be transformed into sequel server columns next we are going to take invoices list now invoices list has all the invoices details and each invoice is each invoice consists of multiple products so and each invoice belongs to a customer so a customer is a lookup column and product IDs is also a lookup column but with allow multiple values check so we are going to have a look at how this column gets transformed in sequel server as well and there is one more list which is customers which contains the details of all the customers all right so let's jump into it let's start off by creating a database in sequence over so we are going to give it a name and we are going to use this database for the replication later on now next step is to create the replication first step is to give SharePoint connection details so we are going to set up our SharePoint collection now this is where all our lists exist and we are going to use our Microsoft credentials for authentication all right our connection works let's move on next step is to basically select the lists that we are going to use for this demo and as we discuss we are going to select the three lists products invoices and customers so we have our customers you have invoices and we have products next now this is where we define our sequel connection and we are going to use windows authentication let's test it out and finally we are going to give our application a name so that we can identify it and since this is checked so this once we finish it then the application will be enabled automatically and this in the replication of data I will start off so if we go back to our Seco server database so now when we look at our database we can see that all the tables are created for our three invoices now for instance if we see we have customers or a customer list and voices when voices list and products for products list but since our invoices has a column for lookup column which has multiple values so what it has done it has created another table we just call invoices product IDs now this will store the multiple values they stored for each invoice no before we jump let's just have a look at all the different types of columns that has been created so let's have a look at invoices and parked so let's run this Kure on this database and let's see how columns of SharePoint has been transformed into sequel so let me just drag it here so that we can look at the data so here we can see the table name so for example point voices and we are at invoices right now let's look at the list settings and we are primarily more interested in to the columns that our user created rather than system generated so let's have a look at for title which is a single line of text is converted into an and watch our customer ID which is a lookup has become an integer total amount number which has become a decimal ID is integer which is which is a running ID in the list and if we look at product IDs we see that this new product IDs mentioned here now why they are not mentioned here so if we also include so right now our invoices and invoices and invoices and products tables data right let's let's add one more column let's say I have one more table which includes the invoices products and that is that is the table right here so let's just copy that over here and so now we see in our in YC product IDs it has a parent invoices ID which contains the idea of the of the invoice and we have child invoices dyes product IDs which which contains all the IDS for all the products that exist for that invoice so it has a one to many relationship between invoices and in and invoicing under scope product IDs similarly if we go into our products so this is our products list settings and if we look at the different columns that exist here and then compare them with the columns that has been generated by a sequel in sequel server so we can see that the unit price which is which is currency in SharePoint has been transformed into money which is correct category metadata is a unique identifier again category ID which is a look up here we are storing an ID for that current stock is decimal and calculate it so the calculated price plus v8e is also converted into money so if we have a look at calculated price column we will see that the result has currency type so that's why it has transformed it into money here and similarly it has it has more or less created similar columns in see Kosovo alright so now let's have a look at the data that we get from SharePoint and how it is converted in and how it is stored in s equals our table so what we're going to do here we are going to run a Curie which basically gets all the invoices and all the products associated to it along with which goes to each customer that invoice belongs to so if we have a look at our Curie here let me just expand it so if we look at our Curie here it is basically getting all the invoices and then we have three inner joins one for invoices product IDs to get all the invoice products then for two products look at the product title and then to customer to get the customer name alright so let's run the security and we have our result here so let's compare the data in sequel server and in SharePoint let's so if we go back to our invoices list here let's see invoice number two so it wise number two has four products and so we are getting four rows here it belongs to one customer so we are getting the customer right there getting all the products correct and then the rest is just data that is coming out from that list total amount and invoice title etc so as you can see it is it has very intelligently created a sort of a relationship between invoice and enjoys product IDs so that you can have a column which has multiple values and then you can easily store it and then later use it for your reporting purposes from your sequence over data right now let's see how it is getting synchronized so let's create a new invoice here let's call it demo invoice let's select it is different customer this time let's call it chart with let's call it mr. Albert white mr. Albert white is going to place an order for balloons and path phasers right so and let's let's add bow as well so we have selected three products let us give it an amount and let's save it all right now since the service is running it is going to synchronize the data into our sequel server database so if we go back to our sequel server database and if I run this query then you can see at the bottom I'll have I have one more invoice damn invoice which we just created for mr. Albert and which has three products that we selected along with the amount now let's run another query to get all the data from both products and invoices and see how the data appears so the bottom we have our noises and on top we have our product so and if we compare it so let's go back to our products list and if we do a quick comparison of the data here along with that we can see some similarities and we can see that data is more or less appearing in a similar manner of course apart from apart from the lookup column or stored in a separate table created for each lookup column so there you go guys so today we have seen how the data specifically columns in SharePoint lists gets transformed into sequel server table columns when when we replicate the data using stickerless manager and as you can see the way it transforms the data and stores the data in sequel server is going to be very helpful with your reporting needs thank you [Music] [Applause]

Channel: AxioWorks Admin

Views: 3,279

Rating: 4.8000002 out of 5

Keywords:

Id: MFXrrlZ5tAA

Length: 11min 57sec (717 seconds)

Published: Tue Feb 25 2020