none
Deleted RRS feed

Answers

  • hi,

    I'd go in a 2 stepa change for each table..

    you import data "as is" from the Access database.. that probably means leaving the old varchar id column(s) remains as is... the imported table just serves as a work table, so you are not required to define and enforce constraints, both domains and integrity ones, even if you should of course "check the import result" is correct

     

    then you can define and create the new "real" table as required, with the correct data types, constraints (not FK) and the like.. you then copy data from the work table to the destination table allowing the override of the IDENTITY automatic generated value via a proper statement, SET IDENTITY_INSERT ... OFF;

    obviously the data pump will consider the cast of all required columns, so you can

    INSERT destinationTable SELECTing CONVERT(new_datatype, old_value) FROM workTable...

    when done, for all the involved tables, you can delete all the work tables and define all required FK constraints...

    similar to (only with 1 table with no FKs)

    SET NOCOUNT ON;
    USE tempdb;
    GO
    CREATE TABLE dbo.t ( 
     id varchar(10) NOT NULL,
     data varchar(20) NOT NULL
     );
    GO
    INSERT INTO dbo.t VALUES ( '1', 'd1' );
    INSERT INTO dbo.t VALUES ( '2', 'd2' );
    INSERT INTO dbo.t VALUES ( '4', 'd4' );
    INSERT INTO dbo.t VALUES ( '6', 'd6' );
    INSERT INTO dbo.t VALUES ( '15', 'd..' );
    INSERT INTO dbo.t VALUES ( '10', 'd..' );
    INSERT INTO dbo.t VALUES ( '20', 'd..' );
    GO
    PRINT 'converted original data..';
    SELECT * FROM dbo.t;
    GO
    PRINT 'creating the actual real table as convenient..';
    CREATE TABLE dbo.NewT (
     Id int NOT NULL IDENTITY PRIMARY KEY,
     Data varchar(10)
     );
    GO
    PRINT 'allowing insertion data maintaining original Id.ToInt values..';
    SET IDENTITY_INSERT dbo.NewT ON;
    GO
    PRINT 'inserting data casting the original (string) Id to int..';
    INSERT INTO dbo.NewT (Id, Data)
     SELECT CONVERT(int, Id), Data
      FROM dbo.t
      ORDER BY CONVERT(int, Id)
    GO
    PRINT 'disabling IDENTITY override..';
    SET IDENTITY_INSERT dbo.NewT OFF;
    GO
    PRINT 'selecting from the new actual real table with correct datatypes..';
    PRINT 'after the INSERT operation the old work table can be deleted..';
    SELECT * FROM dbo.NewT ORDER BY Id;
    GO
    PRINT 'perform the same operation for all involved tables..';
    PRINT '';
    PRINT 'after all imports have been performed, you can drop all';
    PRINT 'work tables and arrange all referential integrity constraints..';
    GO
    DROP TABLE dbo.t, dbo.NewT;
    --<---------
    converted original data..
    id         data
    ---------- --------------------
    1          d1
    2          d2
    4          d4
    6          d6
    15         d..
    10         d..
    20         d..
    creating the actual real table as convenient..
    allowing insertion data maintaining original Id.ToInt values..
    inserting data casting the original (string) Id to int..
    disabling IDENTITY override..
    selecting from the new actual real table with correct datatypes..
    after the INSERT operation the old work table can be deleted..
    Id          Data
    ----------- ----------
    1           d1
    2           d2
    4           d4
    6           d6
    10          d..
    15          d..
    20          d..
    perform the same operation for all involved tables..
     
    after all imports have been performed, you can drop all
    work tables and arrange all referential integrity constraints..
    

    regards

    Friday, August 1, 2008 8:39 PM
    Moderator