Copying a table from one SQL server and DB to another using SQL Server management studio

Question

I have a website I have developed using a databse and I have created some security on the site. This ceates another DB called ASPNETDB.MDF.

The main site database is on a server at the website host. At this time the aspnetdb is in the APP_DATA folder of my website.

I want to copy the tables and their data from the aspnetdb to the main database. I have connected to both in SQL Server Mgt studio. I right clicked one of the tables I want to copy and selected AScript Table>Creat to>New Query. I copied and pasted the query to a new query for the database I want to copy the table to and ran the query. the first lines of the query are:

USE [\C:\WEBSITES\CHAMBEROFCOMMERCE - WITH SECURITY\APP_DATA\ASPNETDB.MDF]
GO

/****** Object:  Table [dbo].[aspnet_Users]    Script Date: 12/09/2009 14:20:00 ******/
SET ANSI_NULLS ON
GO

The first line is errored as not existing. Presumably because it does not exist on the server I am trying to copy to. What syntax should i use to point at the correct database please?

Geoff

Answer 1

For this kind of task, we use

SQL Compare

This is a realy very nice tool to use, and makes these task very simple.

Answer 2

That line is telling it to use the local database. You can (should) remove it if you are creating a query in the database on your hosting server. That is, you right-click on the DB in the hosting server and choose New Query -- that will automatically connect the query to the database in which to create the tables.

If you are simply changing the connection on the script, then you will need to replace it with a USE statement for the database on the remote system.

use [remote-db-name]

BTW, I'd also recommend SQL Compare from Red Gate -- much better tool for the job, IMO.