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!

Answer 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