Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Timax
Starting Member
37 Posts |
 Posted - 2015-05-06 : 07:06:21
|
| I kind of have a feeling that everyone has this question during up sizing from ACCESS to SQL SERVER. Please share your experience if possible. I am moving my tables from access to SQL Server 2008 and can't find a right way to do it. My problems are:1. If I use build in to SQL Import wizard then I have a problem with Autonumber that is not being reproduced while migrating to SQL.2. When I am using SSMA tool (6.0) then autonumber is fine and my problem is that I can not map Date and Time fields there because only conversion option I see is datetime but I need specificaly Date (12/12/2005) and time (17:34:03 PM) which I have this way in access but it being imported as 2014-08-07 00:00:00.000 for Date and 1899-12-30 16:53:04.000 for Time which is not acceptable for me. Any one has any solution for that? |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-06 : 07:28:42
|
1. There is a setting to "preserve" IDENTITY numbers during INSERT.SET IDENTITY_INSERT MyTable ONINSERT INTO MyTable( ... A list off ALL columns being inserted IS required (can't leave this out for ALL columns) ...)SELECT * or Column ListFROM SourceTableSET IDENTITY_INSERT MyTable OFF 2. SQL 2008 introduced two new data types DATE and TIME (in addition to the original composite DATETIME data type). Looks like your SQL table columns are being set to DATETIME datatype, but the data being imported is "just" date / time. Perhaps your migration tool is older than SQL2008 ...I expect that, after data import from Access (or perhaps before that, but after Database Table Create) you can just change the data type to DATE / TIME, and the data should be fine.I don't know how your migration tools work, but if they generate a script (to create the database / tables), rather than just "doing it for you directly in the target database", then perhaps an option would be to hand edit that script to change the DATETIME to DATE / TIME - i.e. before the script is executed to create the tables.If this is a one-time operation I don't suppose it matters very much, either way, but if the database is huge changing the column datatype after the table is populated with data could take some time and need a lot of disk space, which then requires shrinking the database and sorting things out etc.Best would be:Manually adjust the Table Create script or Adjust the column datatype after table create but before data import.If that is not possible then adjust datatype after data import. |
 |
|
|
Timax
Starting Member
37 Posts |
Posted - 2015-05-08 : 01:28:40
|
| Thank you Kristen for the advice. I ended up using Import wizard and have to modify SQL statement in wizard by adding IDENTITY(1,1). Not sure how it work before with out me adding this line but It did... I successfully imported a lot of tables before on the same system but now it started to give me this problem. Can this be corrupted Wizard module or permissions or these is a hidden IDENTITY setting on another field that I don't see? Just guessing. Looks like all tables have this problem now. I have a workaround after 2 days of intensive research but I would love to get to the bottom of it. Looks like Enable Identity radio button is not doing it even if it sets to on. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-08 : 04:14:55
|
SO when you use Upgrade Wizard is it NOT setting the original AutoNumber column to be have IDENTITY attribute in SQL Server? (but, if I understand you correctly, the Upgrade Wizard USED to do that automatically?)If so you can set the IDENTITY attribute on the column after upgrading, but that will cause SQL to recreate the table, and copy over all the data rows, so its a slow operation (on a large table). Also a bit of a pain to ahve to do, if you have lots of tables  |
|
|
|
Timax
Starting Member
37 Posts |
Posted - 2015-05-08 : 19:05:36
|
| I do have a lot of tables (over 100) and will have to do this manually. thank you for your help and at least I have a workaround now :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-05-09 : 03:43:32
|
| If you make the "first" change in SSMS (SQL Server Management Studio) using the GUI tools to change the column (e.g. from plain INT to IDENTITY) you can then press the "Script the changes" button (left of the toolbar button row). That will give you a SQL script to modify your table (this works for any change you want to make, not just IDENTITY, however complex). You can then abandon the change in the Table Designer and use the script instead.The script will show you exactly how SSMS would make the change (if you were to just press the SAVE button), and you might be able to adapt that script to convert all your tables.I suspect that changing a column to IDENTITY is a "Create temporary table with new column attribute, copy all data over, drop original table, rename temp table to original name, recreate all constraints, indexes, FKeys etc." which is of course a huge amount of effort for what seems to be a simple change. Other changes (and maybe this one??) can be done with a single simple ALTER TABLE command.You can get a list of tables / columns etc. by querying the System Tables, so you could get a list of everything that needed scripting to be changed. Might not be worth it in this case, might help in future. Scripting the changes (rather than just pressing SAVE in SSMS) means you can repeat them - e.g. if you need to upsize from Access several times trying different things, or if you need to apply the changes to a DEV/TEST database first and then to a PRODUCTION databaseAnyways, just food for thought if you are new to MS SQL. |
|
|
|
|
|
|
|
|
Date fileds conversion from access to SQL