1

How can I export all of my rows in a table to sql script in Microsoft SQL Server 2005 and then import them to another database?

Thanks in advance

olidev
  • 20,058
  • 51
  • 133
  • 197
  • also MSSQL Server 2005. Actually, I have a database on a hosting provider. I am moving to another provider. Therefore I need to export those data and import to another database on another provider. I am using MSSQL Server 2005 Express. I do not see the import and export functions.. – olidev Nov 14 '10 at 00:03
  • What are you using to connect to either instance - Management Studio, or Management Studio Express? Are both SQL Server instances Express Editions? – OMG Ponies Nov 14 '10 at 00:16
  • Management Studio Express. The SQL Server is probably not express I think. – olidev Nov 14 '10 at 00:17
  • Is there any reason why you can't just do a backup on the source server and restore on the destination? – Donnie Nov 14 '10 at 04:52

3 Answers3

2

If you moving it to another sql db you can right click the database you want and choose tasks -> generate scripts. That will launch a a wizard - follow along, choose the option to script all tables and data. Then execute that script in the new db(assuming that you've already created one with the same name)

El Kabong
  • 717
  • 1
  • 8
  • 15
  • Hi. Thanks for your answer. But unfortunately, there is no option: script all tables and data? Could you please check it? Thanks in advance – olidev Nov 14 '10 at 00:09
  • There may not be an option literally named that, but depending on your SQL Server Management Studio version, there should be something functionally equivalent. IF nothing else, just click through each page of the wizard and choose all of the relevant items... but pay close attention to the wizard pages and be sure you aren't selecting options you don't want (some pages may list options, instead of DB objects) Note that this should only be how you do this if your database is not gigantic. The scripts generated will contain all of the data (assuming you choose those options), plus the schema. – Andrew Barber Nov 14 '10 at 00:17
  • I am running SQL SErver 2005 Management Studio Express. Probably, It does not support export data? – olidev Nov 14 '10 at 00:21
  • It does. Look for 'types of data to script' under the general options - the default will be something like Schema - look there you should be able to change it to schema and data. – El Kabong Nov 14 '10 at 00:29
  • 1
    Hi, it does not. please check this screendump: http://img237.imageshack.us/i/sqlmgstudioexpress.jpg/ – olidev Nov 14 '10 at 01:12
  • I'll see if I can log into one of my machines that still has 2005 on it and see if I can't find it. There's also a publishing wizard - http://www.microsoft.com/downloads/en/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en - I don't know if that's something you can use in your situation. – El Kabong Nov 14 '10 at 01:30
1

If you can't find a data import/export tool that will work in your particular circumstances, it's possible to write plain SQL SELECT queries that will generate SQL INSERT statements. In this way it's possible to "export" all your data to a script file that can be run against the destination database. It's kind of an ugly hack, but it's simple and it works if you don't have a lot of data to move. See my answer to this question for details: Export SQL Server 2005 query result to SQL INSERT statement?

Note that this method assumes that the destination table already exists. But it's pretty straightforward to generate table creation scripts, as J Cory's answer has already shown.

Community
  • 1
  • 1
Matt
  • 5,052
  • 4
  • 36
  • 54
1

There's a command line tool available to dump your data from particular tables into a SQL script that be executed against a different database:

http://blog.sqlauthority.com/2007/11/16/sql-server-2005-generate-script-with-data-from-database-database-publishing-wizard/

I don't believe SQL Management Studio Express supports data scripting (as your screenshot on J Cory's answer shows), but the full version does support that feature. In either case, the command line tool should accomplish what you need.

SqlRyan
  • 33,116
  • 33
  • 114
  • 199