Q&A 002 : How to Import data from external sources to SQL-Server ?


Dear Mr. Hassan thanks a lot for your question and today we will see how to import data from external sources such as Microsoft Excel or Microsoft Access into Microsoft Sequel Server I created an Access file with one single table where all these data are saved as you can see in the screen, and from there, I will bring this data into Sequel Server table and this table will be our data source to import the data to our structure because I cannot take this data right away from Access into my structure because we have a lot of constrains and checks in the database rules so let us see how to do that open Microsoft Sequel Server Management Studio and right-click on the database
where you want to bring this data in right click on it, select tasks and from task, select import data a wizard will show up. skip the first screen, press next second screen will show choose a data source I will select Microsoft Access and I will browse the file click next and here choose a destination I will choose Microsoft Sequel Server Native Client and I will choose the database from here which is BDNEW1 and I press next, and I will choose copy data from one or more tables or views I press next and here this screen is
divided into two partitions the left side is the data source where the data come from Microsoft Access and it shows all the tables but in this case we have only one table and on the right side it shows the destination table here’s an interesting button there say edit mappings and it shows you all the data
types and everything so most of the cases this wizard, does it right if you
imports from Access of course so here I have the name, the date of birth,
everything is set with the correct data type back to the previous screen I will
press next, finish and the wizard now will start transferring the data and it
shows me success and 19 rows transferred close this wizard go back to your
database refresh the table list so you will see the table here is created
“ContactDataImport” with the columns were brought for Microsoft Access so , this is the Data, now we have to use this table to import the data into
our structure in order to do that we need to add two columns, right click on the table say design, so in the top we press insert column and we put a column we call it “TransID” this will identify every single row in order to
make a unique number for my transaction where I use on my import process and I will add another column and call it “TransStatus” where I have to
capture the errors or the status of importing this record so we’ll add “TransID” we’ll make it INT, Primary Key , and we make it Identity so it will give values for itself and “TransStatus” we’ll make it nvarchar (max) so it can handle any errors saved on it we save the table now let’s have a look after the changes we made select * from ContactDataImport here is the “TransID” is identity, in serial number and the “TransStatus”
is all NULL because I haven’t started doing anything yet now the data source is ready for start importing them into our structure in the previous video, it was an answer to Mr. Ayman Ibrahim we have made a stored procedure to import
this data, and it was handled very well with a little bit of modification to
this stored procedure we will use it to import all these data sources we brought
from Access into our database structure the difference is: last time we had to
feed the stored procedure with the parameters every single time, but now the
stored procedure will find the data source processing them, put it in our structure let us see how to do this we will create a stored procedure that uses cursor CURSOR is a database object used to retrieve data one row at a time from a result set unlike Transact-SQL command which works or operates on all the rows at one time CURSOR works same as looping concept
manipulating data row by row we create a new stored procedure and we’ll give it a name [sp_InsertContact_Phones_Bulk] this stored procedure will not have any input parameters unlike the other one we made before but yet, we must use these
parameters as part of the process then we declare a cursor, give it a name for example CUR_MASS_IMP LOCAL STATIC LOCAL means its reference to this batch only if it is GLOBAL, it can be reference to any other batch or stored procedure executed by the connection STATIC means the data fetched to the cursor will be static the cursor reads it one time in
the beginning and use it all the way until we finish my process if it is DYNAMIC every time it checks for whether the data set changes or not now the cursor is declared for what ? for select and I will select the data from the
staging table we have brought from Access, I will select: TransID,Name,Dateofbirth,Address, and I will map the PhoneType to an ID as you see using the
CASE function, and the phone number WHERE this is very important where is the
TransStatus IS NULL because I don’t need to reprocess again the rows
which already processed or the TransStatus has an error, so maybe I have another chance to reprocess the rows that have an error if this error was fixed then we open the cursor then we fetch the next row from the data set
above into my variables this part is very important they have to be fetched in
the same order if you look at the Select statement in the cursor it comes with this order TransID,Name,Dateofbirth,Address, PhoneTypeID, PhoneNumber and they have to go into my variables with the same order do not scramble that so the cursor will fetch the data set above row by row into these variables then we say WHILE @@FETCH_STATUS=0 means the fetch statement was successful sometimes the fetch status could be
-1 that happened when the statement failed to retrieve any row could be -2 if the row was deleted and the other one was -9 when the cursor is
not performing a fetch operation at all so here we use only the successful fetch status which is=0 then I will say begin and here is my process starts
all the way down till I say end for the @@FETCH_STATUS=0 I will basically copy
the same processing code we have created in the other so procedure we made it and
I will make a small modification first thing we start with BEGIN TRANSACTION
not BEGIN TRY, because we have one transaction always at a time going with
the cursor, then I’ll begin try with this transaction I will try to insert the
contact and instead of print the information on the screen I will take it
and I assign it to my transaction status using this command ISNULL(TransStatus,”) I will trim it and I added to the ‘Contact EXISTS ID:’ where this very important [email protected] the variable of transaction ID which I am capturing at the moment then I will continue, else if the contact does not exist we insert it and instead of print I will also
move this to update to the transaction status take the previous status and add
‘Contact INSERTED:’ into it and this is end now I will try to insert the phone, same
thing and instead of print I will take the print and I put in the
transaction status all the way down until we reach the end END TRY then we BEGIN CATCH we declare the errors and here is very important IF @@TRANCOUNT >0 this returns the number of BEGIN TRANSACTION statements that have happened on the current connection if I have one or more transaction happen with the BEGIN CATCH I will roll back
everything and I will update the transaction status with the error
variables instead of printing them and I will END CATCH then right after that I will say IF @@TRANCOUNT>0 again that replies to the
BEGIN TRANSACTION when it’s successful then I will commit everything happen in
the BEGIN TRY and END TRY that’s it then I will fetch the next row from the records set into my variables until the cursor looping all the records and when
they finished it will END and then after END I will have to close the cursor and de-allocate it and this is the END for the stored procedure okay now let us try
that thing but first keep in mind all these numbers we have in the staging
table already exist in our structure but we will run the store procedure to see
what happened here first I will select all the records from my staging table
they all come with status NULL and now I will execute the stored procedure and we’ll see
what happened, very fast select again and here we have contact
exist, here is one error because it has a dash and all of them exist exist
exist except another error, another error and that’s all now let us try to play
up with this data first we will change the phone numbers where the errors were
indicated we have three here transaction 2 ,12 ,15 we change the numbers and make them valid to pass the constraint then again we run the stored procedure and check the data again see
what happened as you can see the transaction status for these three records 2 ,12 ,15 were changed it to the contact exists
because they are already there so we try another thing that we truncate all the
phone numbers we have in the database set the transaction status NULL to all
of them and try again as you can see here after we execute the stored
procedure for all records contact exists and phone number inserted now we’ll try something else we truncate both tables and try again here we don’t have any records in the “tblContactsPhones” neither “tblContacts” we reset transaction status to NULL and execute the stored procedure
again we check the two tables now we have rows in “tblContacts” and we have
the phone number in “tblContactsPhones” we check our staging table, as you can
see the transaction status for all of them okay, first contact inserted phone inserted the second one contact exists because
the same one Alex we already insert it in the previous transaction and the
third, same thing for Alex and so on for the rest, okay now we will add another
record a new record to the staging table with – and see what will happen see here
transaction number 20 it shows the error of the check constraint and this will
roll back if we check our two tables we don’t have “Fox” here, he is not imported
and we don’t have any phone numbers for him okay if we fix that to “Fox” remove the – and try again now it’s imported the phone number and
the contact and the status says inserted, inserted, we check in our tables
here we have him “Fox” row number 9 and in the phone number
he has last row number 21 and I think that should be it we handled all the cases in our structure you have the stored procedure and the staging table you can put some more data have fun man 🙂 Thank you very much. Hosam

Leave a Reply

Your email address will not be published. Required fields are marked *