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.HumanResources.Department from AdventureWorks2012.HumanResources.Department
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
LVL 1
WeTiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kuntesh ThakkerCommented:
You can use :-

For Structure & Data
SELECT *
INTO AdventureWorks2014.dbo.ErrorLog1
FROM SampleWebApi.dbo.ApiUser

I copied SampleWebApi.dbo.ApiUser to AdventureWorks2014.dbo.ErrorLog1

For Structure only

SELECT *
INTO AdventureWorks2014.dbo.ErrorLog1
FROM SampleWebApi.dbo.ApiUser where 1 = 2

You can create cron job
0
pcelbaCommented:
Yes, this command works well when both source and target database is under the same SQL Server and you have to ensure the target table does not exist....  Also the target table is created without any indexes, constraints etc.

If your trying to copy a table from database to a different SQL Server instance then you should create a linked server for this purpose.
1
WeTiAuthor Commented:
Thanks both question:
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.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark WillsTopic AdvisorCommented:
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.tablename','u') is not null drop table databaseb.dbo.tablename
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.
0
pcelbaCommented:
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.
0
Mark WillsTopic AdvisorCommented:
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.
0
WeTiAuthor Commented:
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?
0
Mark WillsTopic AdvisorCommented:
Impact will be performance and consuming server resources. All joins, search predicates, will end up being table scans

Depending on what you want to actually do, and the size of tables, you might be better served by creating the actual tables on database b first and then use INSERT

If you have IDENTITY columns, you need to allow (turn ON) identity_insert : https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql

Easiest way to replicate a table structure is in SSMS to right click on the Table Node in Object Explore and  'Script Table as' => 'CREATE to' => 'New Query Editor Window'
then change the value in the USE [database A]  to  USE [database B]   and if you choose to, you could get rid of any identity columns if the table is only ever going to be populated from database A.

so instead of SELECT * INTO you would then do:

USE [databaseA]
go
TRUNCATE TABLE databaseB.dbo.tableA                ---- be very careful  
go
INSERT databaseB.dbo.tableA (<column names>)
SELECT <column names>
FROM tableA

Now, I suggest truncate table, otherwise you would have to check for update changes, then insert new rows. https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql

And I would be inclined to USE [databaseA] so you always know which DB you are currently in and only fully qualify the receiving DB that way, things like truncate will have the full databaseb.dbo.table name qualifier and serves as a quick visual check.

Does that make sense ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WeTiAuthor Commented:
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.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.