May 3, 2008 at 5:47 am
Hi SQL Team,
Data migration from MS access to Sql server 2005.
In client having existing system is MS Access. So I want to migration MS Access system to SQL server 2005.
Let suppose there MS Access one table having 10 fields.
That table data i need migrate in system (sql server 2005), but the field name are different & sql server few fields are extra suppose 15 fields or vise versa.
So can any one help me out, how to migrate the MS access to sql server system?
Please give me if any one the script or step do this.
Thanks & regards
May 3, 2008 at 6:00 am
using SSIS.
May 4, 2008 at 10:07 pm
You can make use of SQL Server Migration Assistant for Access to perform the migration. I have not used this tool but it may be helpful.
Refer these links for performing the migration,
http://whitepapers.silicon.com/0,39024759,60321928p,00.htm
[font="Verdana"]- Deepak[/font]
May 5, 2008 at 10:05 am
SSIS is probably the most simple way. If the field names are not the same, simply click on the edit mappings button when you get to that screen and manually map the data elements yourself
Marvin Dillard
Senior Consultant
Claraview Inc
May 6, 2008 at 8:53 am
I've used the SQL Server Migration Assistant (SSMA) and it works very well. But if the Access tables and relationships are substantially different from your SQL database, or if the Access data needs some clean-up, I would suggest using the SSMA to bring the Access data into a new SQL database first, and then either use SSIS or just write some queries to bring that data into the "real" SQL db.
May 6, 2008 at 9:05 am
If you're moving data from 1 table with 10 fields, and all that data is going into 1 table in SQL, then using the Import Wizard from Mangement Studio is going to be the easiest way.
Go to the database you want to import into, right click it, Tasks -> Import. Select Microsoft Access as the data source, and just go from there and follow the directions on the screen.
If the data is going to be split into multiple tables, you'll have to define how you want it to end up. Your best bet, in that case, might be to import it (as above), into a staging table, then use a script to split it up as desired.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 9:30 am
I would like to go for import wizard too. It is simple too.
"More Green More Oxygen !! Plant a tree today"
May 6, 2008 at 12:51 pm
If this is a one time import and with no data manipulation or simple manipulation, then the import wizard is easy.
If there is a recurring import or has some moderate data maniplation then SSIS is probably a good tool to use.
If this is recurring and has intense data manipulation then you might consider a .net program. If you wish to encorporate the .net dll into SQL Server you may if the CLR is enabled.
Q
Please take a number. Now serving emergency 1,203,894
May 7, 2008 at 8:20 am
Actually, if it's a repeated import with complex manipulation, you have another option which is to set up the Access database as a linked server, and do the whole thing in SQL. (Which is, after all, a reasonably good language for data manipulation and transformation.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply