Import Access Database into MS SQL Server

hello youtube welcome to make powered tech solutions um today we're gonna be looking at how to import data from microsoft access into um um microsoft sql server so i'm gonna launch my microsoft sql server management studio and so it's it's assumed that you have installed microsoft sql server and you also have the management studio so in this video it's just gonna be how to import okay data from that is how to import a database okay from microsoft access into microsoft xql server so the data it is already already backed i already have a backup of microsoft access okay all i need to do is to bring in this database into microsoft excel server so let me let's let's just see what's inside this database um i'm going to open it as microsoft access or my open it with microsoft access okay guys now just for you to have a feel of what you know the components or the tables and views that i have inside this database or inside my assets so these are the tables in this database i said ac group etc now i want exactly these tables inside my xqr server so what i'm going to do i have it saved already i'm going to login so you going to log in as sa system administrator and i'm going to put in my password i'm gonna login okay guys now i'm in my microsoft sql server and what i need to do first is to create a database okay if you see this is my server the name of my server is the name of my pc desktop goal 6aa sql server now i have my database and the rest of the component here so what we need to do i need to check now if you look at i don't have any database yet i only have the system databases which is which is needed for me to run the my own database so i have the database snapshot which i don't have any yet so i'm going to right click on database and i'm going to create a new database now because i'm creating this database for my biometric machine which is uh the z zk t complaint so i'm going to give my database a name such as i said take oh okay exactly time because it's actually my time database are you with me now and i'm going to click on ok guys once you create the database that's the first thing you need to do now i have a database created by me so what you need to do next is to right click on the database and you go to tax and you move down to import data over there guys and when you click on import data you click on nest now you're going to select the data stops where is the source coming from so i'm going to use this driver i'm going to use this source is coming from microsoft access so i'm going to use microsoft access microsoft jet database engine and what's the file name i'm going to browse it out that's the backup i showed you earlier i'm going to open it and put the file name here and click on nest now the next thing is the destination where is it coming to the data is coming to mysql server here inside my zk time database that i've just created now i'm going to choose the framework i'm going to use the sql server native client driver and it has it detected my server name if i have two servers there it will show you the other servers that are available so let's see let's see guys so before system is wasting little time so please let's be patient normally i just needed to save the other database um other server but i'm going to stick with this currently because that's what i'm connected to here so i'm going to use the first one without the slash sql without the slash sql express now i'm going to now i'm not going to use the windows authentication i'm going to use my sql server authentication which i'm using my system administrator account and i'm going to use my password known to me alone and it automatically it has detected the database inside the server if i chosen the second server i have not printed any database in that server so i won't see this database there this is the only database i think i have with the system databases which is master model on the rest nowadays i'm going to click on nest i'm going to use the first one copy data from one or more tables of view okay i'm going to use the first option and i click on nest now in this case now we're gonna do some manual um tweak okay now selecting everything i'm gonna edit the mapping and i'm gonna make sure that identity insert is enabled and the delete was an existing destination table so in this table now what it what it's saying is that it should delete or where's my it should delete the rows in existing tables before creating a new one so i'm going to click on ok now i'm going to remove some tables here that are not needed because of some mappings that i'm going to be doing i'm removing the don't forget this is my database so i know what i'm importing i'm also going to remove this last one the statistics monthly statistics and i'm going to work with in short i shouldn't remove anything let's or let's see let's see let's see let's see let's see let's see okay since i'm just importing there is no cost for alarm let me see if i'm going to import everything i click on nest oh you see now it's giving me so these two tables this column information for the source and destination could not be retrieved so you're trying to locate this from this destination now you're saying column code there's a column called code the source data type 130 was not found in the data type mapping file so what i'm going to do here is to come to my leave class edit i'm going to do the mapping and i'm going to change this code from ignore i want you to ignore that code source now for the other leaf class we have destination we have source which is called destination i want it to be to ignore okay so you should ignore that for the destination now for checking out i'm to check okay i have nothing to check here department i think i need to put the default shadow and let's see how it goes now so i'm going to click on nest i'm going to click on nest and i'm going to click on finish now let's see let's see oh guys can you see that now the execution was successful so i've successfully imported my database from microsoft access into sql server so guys thank you for watching i really appreciate please like and subscribe

Channel: MickyparyGeek

Views: 88

Keywords:

Id: mnoX4jaGtSU

Length: 9min 36sec (576 seconds)

Published: Sat Nov 28 2020