August 30, 2005 at 7:06 am
Hi all
i want to Copy all the records of one table from one database to another table in another database
inside same server.
Pl help me any one
Than'x
August 30, 2005 at 7:08 am
Right click on the database in EM / all tasks / import data
then follow the wizard.
August 30, 2005 at 7:12 am
MR Remy
Than'x
But I am always Query Analyzer. So I need the query
So pl help if u can
Than'x for ur help
August 30, 2005 at 7:14 am
The quick and dirty way - (if it's a very large table, watch so you don't interfere with others during the operation)
SELECT *
INTO db2.dbo.myNewTable
FROM db1.dbo.myTable
..will make a copy along with all the data in db1.dbo.myTable and create a new table in db2 named dbo.myNewTable.
Some constraints may not be transferred by this method, though the basic table structure and data will.
/Kenneth
August 30, 2005 at 7:27 am
Option B :
Insert into db2.dbo.TableName (col list) select col list from db1.dbo.TableName2
August 30, 2005 at 11:31 am
As Kenneth said, be careful if it's a large table, especially in a production environment because you may create locks on the table that will affect other users of that table.
August 30, 2005 at 11:34 am
And you can lock the tempdb with select into... which could be even worse.
August 31, 2005 at 6:37 am
This would be a better approach.
Create the table first:
create table Table2 (col list)
Then insert all the records from Table 1 to table 2:
Insert into Table2
select (col list)
From Table1 with (nolock)
Using the nolock option prevents table 1 from being locked
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply