Transfer Database Data

  • hello community.
    i have two databases. one is old version and other is new version ..

    in new version i have some new fields in all the tables old version doesn't has those fields.

    now there is a lot of data in old database i want to put that data in new database.

    how can i do it ? after taking data script and run on new database not works due to different few fields.

    how can i transfer all the data into new database kindly suggest me a way of doing this.

  • At a guess I assume that you're not declaring your column names when doing the insert, or your new columns aren't nullable, and so require a value. You'll need to ensure you're columns that you aren't inserting into are nullable, and declare you're columns as part of your INSERT statement, or provided values for your non-nullable columns. As a small example:

    USE TestDB;
    GO
    CREATE TABLE OldTable (CustomerName varchar(20) NOT NULL);
    CREATE TABLE NewTable (CustomerName varchar(20) NOT NULL, CustomerAge int NOT NULL);
    GO
    INSERT INTO OldTable VALUES ('Steve'),('Harry'),('Jane'),('Jasmine');
    GO
    --This will fail because there are less columns than the target table
    INSERT INTO NewTable
    SELECT *
    FROM OldTable;
    GO
    --This will fail, because CustomerAge is NOT Nullable. Thus requires a value.
    INSERT INTO NewTable(CustomerName)
    SELECT CustomerName
    FROM OldTable;
    GO
    --Let's just check there's nothing there though
    SELECT *
    FROM NewTable;
    GO
    --One option would be to provide a value for the age, let's use 0, as we don't know it.
    INSERT INTO NewTable (CustomerName, CustomerAge)
    SELECT CustomerName, 0
    FROM OldTable;
    GO
    SELECT *
    FROM NewTable;
    --Hazaar it worked!
    GO
    --Clear it for alternate option.
    DELETE FROM NewTable;
    --Check the data is gone
    SELECT *
    FROM NewTable;
    GO
    --Other method is to set age to be nullable.
    ALTER TABLE NewTable ALTER COLUMN CustomerAge int NULL;
    GO
    --This will work this time
    INSERT INTO NewTable (CustomerName)
    SELECT CustomerName
    FROM OldTable;
    GO
    --Check the INSERT worked (should have NULL for age now)
    SELECT *
    FROM NewTable;
    GO
    --Clean up
    DROP TABLE OldTable;
    DROP TABLE NewTable;
    GO

    Hmm VS Code x Ubuntu does not play well with SSC.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Messi - Sunday, September 24, 2017 1:33 AM

    --

    how can i do it ? after taking data script and run on new database not works due to different few fields.

    --

    How did you create this 'data script'?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin - Sunday, September 24, 2017 6:12 AM

    Messi - Sunday, September 24, 2017 1:33 AM

    --

    how can i do it ? after taking data script and run on new database not works due to different few fields.

    --

    How did you create this 'data script'?


    from here after setting type of data script to Data Only

  • OK, did you consider adding the new columns to the old DB and then doing the scripting?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin - Sunday, September 24, 2017 6:23 AM

    OK, did you consider adding the new columns to the old DB and then doing the scripting?

    Didn'teeven think about that as an option. :p

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Phil Parkin - Sunday, September 24, 2017 6:23 AM

    OK, did you consider adding the new columns to the old DB and then doing the scripting?

    you mean if i have new NOT NULLABLE columns in new database put those as NULLABLE in old database then take the data script and run in new database ?

  • Messi - Sunday, September 24, 2017 11:07 AM

    Phil Parkin - Sunday, September 24, 2017 6:23 AM

    OK, did you consider adding the new columns to the old DB and then doing the scripting?

    you mean if i have new NOT NULLABLE columns in new database put those as NULLABLE in old database then take the data script and run in new database ?

    If they're not nullable in the new database, then have nullable columns with NULL values in your old database won't help you. If they're not nullable you need to provide a value for the new columns or make them nullable.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Thom A - Sunday, September 24, 2017 11:19 AM

    If they're not nullable in the new database, then have nullable columns with NULL values in your old database won't help you. If they're not nullable you need to provide a value for the new columns or make them nullable.

    yes. that's right. i will surely have to do some manually data entry for it ! 🙁 🙁

  • Messi - Sunday, September 24, 2017 11:24 AM

    yes. that's right. i will surely have to do some manually data entry for it ! 🙁 🙁

    Or give them default values in the old DB.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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