We use Windows Azure around here a lot, it works great with Access but it has one limitation: you can’t copy data from one database to another since they don’t support the USE statement.
Use Generate Script Instead
To get around this limitation use the following technique:
- Launch SQL Server Management Studio and login to your database
- Right click on your database name and click on Generate Scripts
- Select Choose Objects on the left hand side menu
- Click on Select Specific database objects on the right part of the window
- Checkmark the tables you wish to copy
- Click on Set Scripting Options on the left
- Select Save scripts to a specific location and Save to new query window
- Click on the Advanced button as shown below:
When you click on Advanced you will get a list of options, go down to the bottom of the list and select either Data, Schema and Data or Schema only for Types of data to script:
Here’s an explanation of the three choices:
- Data Only – Will create a script that will insert all of your data into the destination table. Choose this option to copy data to an exact copy of the table. (Both tables have the dame fields)
- Schema and Data – Will create your table and then insert it
- Schema Only – Will only create the table, but not insert any data
Click on OK and click on Next until your script is created. The system will create a new query window, change the USE statement at the top and copy/paste in a new query window of your destination database. Run the script there and it will insert all of your data.
Too much data?
This works great if you are not inserting a lot of data, otherwise it may create a script that is too long to run.
