Post reply

Import Wizard, Defaults, and Nulls

  • September 21, 2005 at 9:22 am

    #68488

    Ok, i don't understand the inconsistency.  I have most of my fields set to NOT NULL in SQL Server. This is not true of my Access DB.  So there are nulls in the Access DB.  For those columns where there are nulls but info is needed in SQL SERVER, i created a default constraint.  This has worked all the way up until i got to the questions table.

    The problem is that the default only seems to input the default info when all the rows in taht column are null.  If one row has data, copying that table from Access will fail.

    Here are some examples:

    SelectionNumber in Categories table is NOT NULL.   There was no SelectionNumber column in the Access DB.  Default Constraint works excellently by adding appropriate default.

    However,

    Reference in Questions table is also NOT NULL.  There was a References column in the Access DB.  Some of the rows HAD references and some were null.  The Default Constraint does not work.  It is supposed to place "Not Available" in the blank or Null columns.

     

    It appears to me that if some rows have info and others don't, the default will not be applied to any of them. WHY??? and what can i do about it?

     

  • September 21, 2005 at 9:47 am

    #591849

    How R U Inserting the data?

    dts?

    If t-sql use coalesce(), or ISNULL()

  • September 21, 2005 at 10:09 am

    #591868

    I'm using the import/export wizard.  I select the "copy tables(s) and view(s) from teh source database option and press next.

    It then takes me to a screen of Source and Destination tables. If i press the elipses, it will allow me to see how it lined up the columns in those tables i selected to be copied.

    There is an option "Use a query to specify the data to transfer." but i don't know how to use that option between 2 different databases.

  • September 21, 2005 at 2:49 pm

    #592018

    Yeah Yeah, it will be easiest to use select using a query.

    Use the coalesce function on the field that U know has nulls.

    just do select using query, this is the data source.

    select col1, col2, col3, coalesce(col4,'') as col4

    From mytable.

    Then destination is the same table.

    You can look at transformations to make sure the columns align.

  • September 21, 2005 at 4:36 pm

    #592040

    I'm using the Import/Export Wizard.  I can create the query but it uses "create destination table" instead of "append rows to destination table" already in the db (which is usually does when i don't write the query). I can't manually tell it which table to choose. It automatically tries to save it to a new table.

    I guess it is obvious i've created both a SQL Sever Db and have an old Access db that i'm trying to pull info from.

    Or did you have another idea in mind?  Are you talking about the Import/Export Wizard or something else?

    Thanks!

  • September 21, 2005 at 7:35 pm

    #592056

    Jacob

    In the wizard, you can change the destination table in the step where you select source tables and views. If you select an existing table as the destination you can choose "delete rows..." or "append rows..." in the transformations dialog.

    You, can also alter the transformations, including NULL handling, using the transforamtions tab and writing some VBScript. Although, be aware that this will change the datapump into row-by-row processing instead of bulk loading rows.

     

    --------------------
    Colt 45 - the original point and click interface

  • September 22, 2005 at 9:02 am

    #592259

    I've found that if your source query includes the column that contains nulls and nulls are encountered, the SQL default constraint will not fire.  If you leave the column out of the query (as in your first example in which the column does not exist in the source), then the default constraint populates the field.

    I've gotten around this by wrapping as IsNull function around the columns with possible nulls in the source query.  This saves you from having to write VBScript transformations, so may be marginally faster since batch SQL should run more efficiently than script.

  • September 22, 2005 at 10:13 am

    #592321

    You are wonderful guys.  Phill thanks for the info on the destination table.  The wizard doesn't appear to always allow you to change destination info. I guess this has to do with columns.  But now i see that it changes.

    Ellen, thanks also.  I was able to use the Not Isnull to separate out the columns without nulls and put them into the DB but unfortunately i can't figure out how or WHY it won't just use the default constraint now that the WHOLE resultset is no longer mixed.  I was assuming that since the the column was null throughout the resultset it would fire the default just as it does with the other columns with no info.  Instead every time i ask it to select the columns with Nulls using ISNULL around the column, it fails. 

    The only other solution i can think of is to temporarily change the column to null then write a update query that would change the info to "Not Available" and then change it back to NOT NULL.

    I hope not to do that if it is avoidable. I really want to learn this stuff.

    Thanks very much guys. I feel a whole lot more relieved.

     

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply