Better way to export data from one database to another (SQLSever)

Question

I'm importing a big flat file (about 400.000 rows and 255+ columns) into SQL Server Management Studio through the import wizard.

To get the right variables I use Suggest types, but I have found that I need to search through all the rows to get the right variable types. It takes a very long time. Is there a way to avoid this or do it faster?

Furthermore, my real goal is to transfer data from one sql server database to another on another computer. I do this by exporting it as a flat file. But maybe this is stupid since I lose the information about the correct format?

Thanks!

You can use Script with data in SQL Server 2008 and later. It will copy all the information to the related database. In the generated script you might change database name if new database needed on other computer. — Iqbal
– Iqbal, Commented Feb 3, 2014 at 9:47

Vignesh Kumar A · Accepted Answer · 2014-08-16 01:49:18Z

1

According to Copy one database to another database:

There are several ways to do this, below are two options:

Option 1

Right click on the database you want to copy

Choose 'Tasks' > 'Generate scripts'

'Select specific database objects'

Check 'Tables'

Mark 'Save to new query window'

Click 'Advanced'

Set 'Types of data to script' to 'Schema and data'

Next, Next

You can now run the generated query on the new database.

Option 2

Right click on the database you want to copy

'Tasks' > 'Export Data'

Next, Next

Choose the database to copy the tables to

Mark 'Copy data from one or more tables or views'

Choose the tables you want to copy

Finish

edited Aug 16, 2014 at 1:49

user456814

answered Feb 3, 2014 at 9:56

Vignesh Kumar A

28.6k14 gold badges67 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mace Over a year ago

Thanks for the reply. I tried option 2 for a start, and I managed to export the tables from the database. But how do I import it on the other computer. I have copied the .mdf and .ldf files to the new computer, and I have tried using 'Tasks' > 'Import data', but I can't see where I can specify the location of the .mdf and .ldf files.

Vignesh Kumar A Over a year ago

please check the sql server installed file location.. Mark answer or upvote if it helped you

Jade · Accepted Answer · 2014-02-03 09:47:24Z

1

Backup your database and restores it on the other server (the tager server must be equal or higher version) or simply copy the database files to other server and attached it (when copying database files, you must ensures that either you have detached the database or stop the sql server service).

answered Feb 3, 2014 at 9:47

Jade

2,9941 gold badge14 silver badges9 bronze badges

Collectives™ on Stack Overflow

Better way to export data from one database to another (SQLSever)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related