June 5, 2013 at 1:56 am
I am using sql server 2008 r2 express. I am getting some problems when i use import data feature to import data from one database to another. I am using SSMS to do it. While using the wizard i choose "Select identity insert" option to copy the identity element property. By using this identity elements are imported properly. But after importing i compared the source database and destination database and found some changes!!. All NULL values in the table are replaced by 0. So how can i avoid it.Is there any option to copy it as it while using import data wizard? Please help
June 5, 2013 at 2:34 am
Check the settings in the "edit mappings" section of the wizard. Are you creating a new table or appending to an existing table?
Check for default values when you are appending to an existing table.
June 5, 2013 at 3:35 am
In edit option there is no option for it.When i checked there was only 3 options Drop and recreate the destination table,Delete rows in destination table,Enable identity insert. So i didn't find any option for NULL replacing by 0 problem.
Also now what i want to do is to duplicate full database using import data. There are about 125 tables in it.
Now i am using generate scripts option(right click on db and task and then generate script) and by using creating all objects of the database.Then i run it so that i create a new database with same structure with all primary keys and constraints properly set.
Then i use import data method( right click on db and task and then Import data..) to import data from all tables to destination database.There is option for checking identity insert. But how null value replaced by 0 problem can be solved?
June 5, 2013 at 4:16 am
Unfortunatly I can't reproduce this issue on my system.
For testing purposes: what happens when you import the data using the "INSERT destination SELECT * FROM source" statement? Do you have the same issue here?
June 5, 2013 at 4:36 am
Can you check to see whether the "Nullable" check box is ticked for the columns in which the nulls are being replaced with 0s? I would have thought this would cause an error, but it's worth a check.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
June 5, 2013 at 6:14 am
I've seen this before. The import process is recognizing the empty string in the source data. Your destination data type is numeric, so the import process replaces the empty string with a zero.
The approach we've taken to solve this is to never import directly into the destination table. Create an "inbox" table and import into that instead. Then do any data transformation, cleaning, etc. and then insert into your destination table. The whole thing can be done in a stored procedure for repeatability. You can opt to truncate your inbox table either at the beginning or the end of your procedure. Sometimes you might want to see the data you've received and compare it to what you've published or you might want to break it just before the insert into the destination table to check things out.
June 5, 2013 at 6:23 am
Allow null is allowed for these column. I also checked that for which column this difference comes? For about columns with datatype int NULL was replaced with 0. For another column of datatype nvarchar(255) also NULL was replaced by 0. There are so many column with nvarchar(255) for those NULL was imported as it is!!. So i didnt get what exactly reason behind this.
June 5, 2013 at 6:28 am
I don't get the exact reason for it either; perhaps somebody else does. I was just describing the workaround we use to solve the problem. I've never found the root cause behind it.
As for the nvarchar(255) being imported as zero, make sure your column in nullable in your inbox table.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply