Geek Answers Handbook

The easiest way to transfer tables between MSSQL databases without a network connection?

I have a small application / site on a dev server with a data table in SQL Server [varchar (40), varchar (40), varchar (MAX)]. I want to take a data table and push it to a live server. I tried to export to txt, Excel and Access, but every time an error occurs during import, due to the inability to parse due to data in the varchar (MAX) or unicode field to convert non-Unicode from Access.

It seems to me that I do not see a good solution here. This does not need to be automated at the moment. I believe that there are ways to transfer tables between databases connected via a network / Internet connection, but our live database is not accessible from our office. I used data export to MySQL before creating a script to work in another database to insert all data records, but I do not think that this is available in MSSQL.

Any suggestions?

+4
source share
4 answers

I used data export to MySQL before this script is created to run another database to insert all the data records, but I do not think that this is available in MSSQL.

Yeah! You can do this in Microsoft SQL, basically. A little-known Microsoft utility, the SQL Server Database Publishing Wizard, exists and does what you are talking about.

http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en

Why this is not a standard part of SQL Server, I have no idea.

+4
source

You can use the backup and restore functions of the sql server ( details here )

If you want to limit the backup to one table (because the database is too large, for example), you can put your table in your own file group and use the sql server backup function in this file group.

If you do not know what a filegroup is, you will find it here.

+4
source

I would bcp it in native format and then bcp again. This is from memory, but the syntax looks something like this:

BCP MyTable OUT MyTable.dat -n -Usqlusername -Psqlpassword -Ssqlservername

and then return it again.

BCP MyTable IN MyTable.dat -n -Usqlusername -Psqlpassword -Ssqlservername

+2
source

You can use a tool like EMS . It will generate an SQL script for you, including creating a database and, if required, inserting data.

If necessary, update the database name, save the script, take it to a new server and execute it. Everything is ready.

+1
source

More articles:


All Articles