Import/export

Question

Post reply

Import/export

Admingod

SSCertifiable

Points: 5997
More actions
October 14, 2020 at 1:10 pm

#3797139

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?

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

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

Site Owners SSC Guru Points: 80325 More actions · Answer 1

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

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

dataEnthusiast SSC Rookie Points: 44 More actions · Answer 2

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: 1004414 More actions · Answer 3

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.

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

frederico_fonseca SSCoach Points: 16167 More actions · Answer 4

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: 55548 More actions · Answer 5

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: 36121 More actions · Answer 6

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 4 years, 2 months ago by VastSQL.

jcarranza 23978 Ten Centuries Points: 1380 More actions · Answer 7

if it's a one time thing and the database is not complex (hundreds or thousands of tables with complex relationships) then I would do the following.

Recreate every single table in the 2017 SQL database do not put constraints
export the data from 2019 db
import the data to 2017 db
Alter the tables and add the constraints

You can right click on a database and go to tasks and generate scripts for everything in that database.