WeTi
asked on
Copy database A table to database B table
Dear expert
I would like to copy a table all info to another table info I found this in MS site
Select * into SQLShackDemo.HumanResource
I haven't really tested this but surely it works fine... but anyway now I would like to copy everything from a table of database A to a table of database B. How do i do that?
And is it possible to schedule this work so it automaticly runs at end of month? How to do then?
Thanks
I would like to copy a table all info to another table info I found this in MS site
Select * into SQLShackDemo.HumanResource
I haven't really tested this but surely it works fine... but anyway now I would like to copy everything from a table of database A to a table of database B. How do i do that?
And is it possible to schedule this work so it automaticly runs at end of month? How to do then?
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you are going to schedule, then the receiving table cannot exist when doing a SELECT * INTO
So, while it is possible to do
select *
into databaseb.dbo.tablename
from databasea.dbo.tablename
you would first have to drop the table
if object_id('databaseb.dbo.t
select *
into databaseb.dbo.tablename
from databasea.dbo.tablename
It will not copy indexes or anything else, jut a copy of the data with the column names. Be careful.
BUT before you do anything, please describe what you need to achieve.
So, while it is possible to do
select *
into databaseb.dbo.tablename
from databasea.dbo.tablename
you would first have to drop the table
if object_id('databaseb.dbo.t
select *
into databaseb.dbo.tablename
from databasea.dbo.tablename
It will not copy indexes or anything else, jut a copy of the data with the column names. Be careful.
BUT before you do anything, please describe what you need to achieve.
You may schedule the job in SQL Server Agent. To define the job requires admin permissions.
Linked Server is a standard SQL Server Object and you may define it in SSMS or by SQL script. To link 2 SQL Server is relatively easy. They just need to see each other in the network.
Linked Server is a standard SQL Server Object and you may define it in SSMS or by SQL script. To link 2 SQL Server is relatively easy. They just need to see each other in the network.
Oh and please read : https://docs.microsoft.com/en-us/sql/ssms/agent/create-a-job
Once you are happy with your T-SQL, you can create a job, create a job step and copy / paste your T-SQL code, then set the schedule.
And for linked servers, if it is only different DB on same instance of SQL, then you dont need a linked server - is this part of your requirement ? because you will also need the 4 part name identifier in your table name conventions, and it may impact on creating a job.
Once you are happy with your T-SQL, you can create a job, create a job step and copy / paste your T-SQL code, then set the schedule.
And for linked servers, if it is only different DB on same instance of SQL, then you dont need a linked server - is this part of your requirement ? because you will also need the 4 part name identifier in your table name conventions, and it may impact on creating a job.
ASKER
Mark Wills what I wanted to do is copy table A from Database A to table A from Database B there are 3 tables to copy so: table A and B and C from Database A to table A and B and C from Database B. after I done that I would like to run a query to database B to give me a static result. When I do like you did, then the index wouldn't be copied, what is the impact of that?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well I need to say this, we did a work around about this, that we are going to do query everyday and export the result to a excel file and this way we know everydays data and make compare than just copy one database to another... But Mark thanks for now I think I will do this sometime in the future tho so I might get back to you then for the solution with a new open ticket of course, thanks for the effort to Kuntesh and Pcelba too.
ASKER
Kuntesh your solution is you copy to a error table? I dont understand your case what i want is what pcelba said, from a database server to another one.
pcelba how do I create a linked server? and how do I copy the tables to other one.
And also none of you answered about the schedule, I want to create a schedule at end of the month, if its possible to do.