CODEWITHSUNDEEP
mysql
sujan mridha
sujan mridha

Reputation: 83

Copy table from one database to another database

I am trying to copy a table from one database to another database.

Already there are several solutions for this problem. I use this method to solve this problem.

 select  *
into    DbName.dbo.NewTable
from    LinkedServer.DbName.dbo.OldTableSourceDB..MySourceTable

I have two databases. One is blog including a table named engineers; another database named msdata including a table named ms. I am trying to copy the table engineers to the database msdata. My query is:

select * into msdata.dbo.ms from linkedserver.blog.dbo.engineers;

but output is 

Undeclared variable: msdata

I don't know it is the problem here. Any help will be appreciated.

Upvotes: 2

Views: 932

Answers (2)

Drew
Drew

Reputation: 24960

Just an illustration:

create table so_gibberish.fred1
(
    id int auto_increment primary key,
    what varchar(40) not null
);

insert so_gibberish.fred1 (what) values ('this'),('that');


insert into newdb789.fred1 select * from so_gibberish.fred1;

-- failed, error code 1146: Table 'newdb789.fred1' doesn't exist

create table newdb789.fred1
(
    id int auto_increment primary key,
    what varchar(40) not null
);

insert into newdb789.fred1(id,what) select id,what from so_gibberish.fred1;
insert into newdb789.fred1 (what) values ('a new thing');

select * from newdb789.fred1;

+----+-------------+
| id | what        |
+----+-------------+
|  1 | this        |
|  2 | that        |
|  3 | a new thing |
+----+-------------+

good, auto_increment preserved and resumes at 3 for new things

Upvotes: 1

ssadaqat
ssadaqat

Reputation: 158

Try this alternative query below, make sure you have already created the table in destination database:

INSERT INTO DestinationDatabase..DestinationTable 
SELECT * FROM SourceDatabase..SourceTable;

Upvotes: 1

Related Questions