Copying complete table from one MSSQL database toanother

[lther]

I will soon have to move a complete table from one database to another (including the table structure and data in the table).

Is there an easy way to do this? Both are MSSQL databases.

Thanks!

[mpwoodward *TMM*]

lther wrote:

> I will soon have to move a complete table from one database to another (including the table structure and data in the table).
>
> Is there an easy way to do this? Both are MSSQL databases.
>
> Thanks!

SQL Server's DTS functionality will handle this--you just point one
server to the other and it will copy straight over.

Matt

--
Matt Woodward
Team Macromedia - ColdFusion

[lther]

Thanks for the reply. I don't have access to the DTS Import/Export Wizard... Is there a way to do it through some sort of a query (either ColdFusion or MSSQL)?

[paross1]

Perhaps by using SELECT INTO (see SQL Server Books Online).

The SELECT INTO statement creates a new table and populates it with the result
set of the SELECT. The structure of the new table is defined by the attributes
of the expressions in the select list, for example:

SELECT Shippers.*, Link.Address, Link.City,
Link.Region, Link.PostalCode
INTO NewShippers
FROM Shippers
JOIN LinkServer.DB.dbo.Shippers AS Link
ON (Shippers.ShipperID = Link.ShipperID)

SELECT INTO can be used to combine data from several tables or views into one
table. It can also be used to create a new table containing data selected from
a linked server.

Phil

[The ScareCrow]

I don't think Phil's solution will create the table with any keys or
identities. So you may need to do this manually.

The best/easist way is to use enterprise manager, just right click on the
table and export it.

Ken

[paross1]

I agree. Of course, if he had access to Enterprise Manager, he would have access to DTS Import/Export Wizard, which, as previously stated, was not the case.

Phil

[sameel85@gmail.com]

IN MSSQL Server Does not Support to IN BACKUP_TestDB but my sql is working. how can i resolve. Without that IN 'BACKUP_TestDB' we can copy the data into new table call PersonOrdered

SELECT P.FirstName, O.OrderNo
INTO PersonOrdered IN 'BACKUP_TestDB'
FROM Person AS P
INNER JOIN [Order] AS O
ON P.P_Id=O.P_Id