Import/export

  • Admingod

    SSCertifiable

    Points: 5943

    I am looking to export data from SQL 2019 to sql 2017. What if the tables have primary key and foreign key relationship how would be the export is done in that case. Please advise?

  • Site Owners

    SSC Guru

    Points: 80372

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • dataEnthusiast

    Grasshopper

    Points: 12

    Hello Admingod,

    I am thinking Backup and Restore is not an option as there is no backward compatibility for this. I have not tried it.

    Looks like SQL 2019 has not done any changes to age old Import Export wizard. This can be used to load data from other databases and servers. That being said, the relations between tables will not be copied over if this is used.

    I would try the Generate Script option and select schema and data if you would want to move data and schema objects along with any constraints and indices for few tables.  SSIS packages is a great option to schedule it leave it running if your database is huge. Have you tried these options?

     

  • Jeff Moden

    SSC Guru

    Points: 997316

    Admingod wrote:

    I am looking to export data from SQL 2019 to sql 2017. What if the tables have primary key and foreign key relationship how would be the export is done in that case. Please advise?

    For the PK/FK stuff, the answer is "very carefully".  Obviously, you're going to need to populate the PK tables on the other end first and then populate the FK tables.

    I've not used the data import/export wizard since SQL Server 2000 because it wasn't great back then and I never looked back at it since.

    There are multiple methods to do such a thing... I guess my question is, how often do you need to do this?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • frederico_fonseca

    SSChampion

    Points: 14776

    and what was the result when you tried it?

    Wizard might be able to sort it out for you - but YOU need to put some effort on testing it yourself before even asking around for something that may not be a problem at all.

  • homebrew01

    SSC Guru

    Points: 55199

    What is the purpose, use & "priority" of the target server ? Does it already have data, or is it a copy into an empty database ?

    Is this a 1 time event, or regular "refresh" process ?

  • VastSQL

    SSC-Dedicated

    Points: 35917

    Admingod wrote:

    I am looking to export data from SQL 2019 to sql 2017. What if the tables have primary key and foreign key relationship how would be the export is done in that case. Please advise?

    Are you importing to existing tables having data or to a new table? you can to do the export in order of the tables other wise it will fail because of the constraint.

     

    • This reply was modified 19 hours, 2 minutes ago by  VastSQL.

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

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