MSSQL SERVER: Copy tables from 1 database to other

Hi,
I've been requested to automatize the process of copying several tables from a production database (dbProd) (tables purchase_orders, sales_orders, sales_invoices and credits_notes) to the development database (dbDev) in an scheduled way (Once at 13:00 and another time at 23:00).
Can somebody tell how can we do this?

We are using MS SQL Server 2012.
LVL 2
José PerezAsked:
Who is Participating?
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.

Éric MoreauSenior .Net ConsultantCommented:
are both database on the same server?
0
José PerezAuthor Commented:
yes, both on the same one.
0
ZberteocCommented:
You can create a job that will execute this statement:
Insert into table target_database..table_name (pkcol, col1,col2...,coln)
select pkcol, col1,col2...,coln from source_database..table_name s 
where not exist (select * from target_database..table_name where pkcol=s.pkcol)

Open in new window

You will scheduled it as needed.

Of course, I assumed here that the 2 tables have the same structure but you can use the same idea even if they are different.
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!

Éric MoreauSenior .Net ConsultantCommented:
0
SimonCommented:
Zberteoc's suggestion is what I would have said, though in that basic format it only appends new rows and doesn't update existing ones that have changed.
Depending on requirements and available column types (i.e. a timestamp or rowversion), you might copy the data first to staging tables and then use the TSQL merge command to synchronise the data, or use a combination of INSERT, DELETE and UPDATE statements, or even truncate the destination and INSERT everything from the source.

It might be that you need the quickest method for the 13:00 refresh and a more thorough one for the overnight version.
0
José PerezAuthor Commented:
Interesting, but how can we adapt the following query to copy from the source database to the target one?

This is the query that can extract the tables and columns we are interested in:

Select Top 10
      UserCode, Title, UserName as "Nombre y Apellido", Position as "Cargo", Address, Tel1, MobilePhone as "Celular", fax, E_MailL
From Customers
0
SimonCommented:
Your 'top 10' query didn't seem appropriate for this task, unless the dbDev only needs the latest few records, and you didn't mention the table named 'Customers' in your original question?

There's no point in aliasing the column names in this scenario, as only the order of the columns in the VALUES() and SELECT parts of the statement determine which columns the data is inserted into.

insert into  [dbDev].[dbo].[Customers] values (UserCode, Title, UserName, Position, Address, Tel1, MobilePhone, fax, E_MailL)
Select 
      UserCode, Title, UserName, Position, Address, Tel1, MobilePhone, fax, E_MailL
From Customers 

Open in new window


You can create a job, insert the above code as step 1 and then schedule the job to run at 13:00 or whenever.
0
José PerezAuthor Commented:
mmmm it displays an error "Invalid colum name..." probably because the tables do not exists in the target database. Can the query create the tables if those do not exists? How?
0
SimonCommented:
Yes, assuming you have appropriate CREATE TABLE permissions, you can do it with the SELECT...INTO...FROM syntax
Select 
      UserCode, Title, UserName, Position, Address, Tel1, MobilePhone, fax, E_MailL
into  [dbDev].[dbo].[Customers]
From Customers 

Open in new window


...But this will only work once, as the destination table will then exist. For all following scheduled copies you'd need to use the previous syntax (or drop the destination table first each time).
0
José PerezAuthor Commented:
What can I do to make work?
0
SimonCommented:
Taking a step back...
have you checked whether the Customers table exists in dbDev?
If it exists, does it have the column names specified in the query? - I'm suspicious that "E_mailL" is a typo.

Please check these issues, as they may resolve the problem.

If not... You wrote earlier that the two databases are on the same server. Are they on the same instance of SQL Server?

If they are on different instances of SQL server (even though on the same server) you would need to setup a linked server and use 4 part notation to reference the source table (linkedServername.dbname.schemaname.tablename). From the instance that dbDev resides on, setup the instance that dbProd is attached to as linked server (call it PROD for example). If the destination table doesn't already exist on dbDev then use this query to initially create and populate the table (or use Eric Moreau's 'copy database wizard' suggestion) to initially copy the table across to dbDev. However, I'd be surprised if the Customers table doesn't exist if dbDev is the development version of dbProd.

use dbDev
GO

Select 
      UserCode, Title, UserName, Position, Address, Tel1, MobilePhone, fax, E_MailL
into  [Customers]
From PROD.dbProd.dbo.Customers 

Open in new window


Once the table exists:
insert into  [Customers] values (UserCode, Title, UserName, Position, Address, Tel1, MobilePhone, fax, E_MailL)
Select 
      UserCode, Title, UserName, Position, Address, Tel1, MobilePhone, fax, E_MailL
From PROD.[dbProd].[dbo].Customers 

Open in new window

0
ZberteocCommented:
Use this MERGE statement to keep the tables in sync in all cases: insert,update,delete:
;WITH src AS
(
	select 
		UserCode, 
		Title, 
		UserName, 
		Position, 
		Address, 
		Tel1, 
		MobilePhone, 
		fax, 
		E_MailL
	from 
		SourceDatabase.dbo.Customers
)
,trg as
(
	select 
		UserCode, 
		Title, 
		UserName, 
		Position, 
		Address, 
		Tel1, 
		MobilePhone, 
		fax, 
		E_MailL
	from 
		[dbDev].[dbo].[Customers]
)
MERGRE	src
USING	trg
	ON trg.UserCode=src.UserCode
WHEN MATCHED THAN
	update
		trg.Title		=src.Title, 
		trg.UserName	=src.UserName, 
		trg.Position	=src.Position, 
		trg.Address		=src.Address, 
		trg.Tel1		=src.Tel1, 
		trg.MobilePhone	=src.MobilePhone, 
		trg.fax			=src.fax, 
		trg.E_MailL		=src.E_MailL
WHEN NOT MATCHED THEN	
	into  	(UserCode, Title, UserName, Position, Address, Tel1, MobilePhone, fax, E_MailL)
	values 	(UserCode, Title, UserName, Position, Address, Tel1, MobilePhone, fax, E_MailL)
WHEN NOT MATCHED BY SOURCE THEN	
	delete
;

Open in new window

Make sure you keep ; as they are. Put that into a schedule job to run every 5min or whatever interval fits you.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Strange that you have a server with Production and Developments environments.
Anyway, you can accomplish this with a Snapshot Replication, specially if the database is small and the two periods you gave are periods with less use of the Production database.
0
ZberteocCommented:
Especially because the databases are on the same server you don't need replication. Much simpler a job with a MERGE statement. Replication is recommendable when you sync 2 or more different servers.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Especially because the databases are on the same server you don't need replication. Much simpler a job with a MERGE statement. Replication is recommendable when you sync 2 or more different servers.
I can accept that there's more than one solution but the statement of not recommending the use of Replication just because the databases are in the same server don't make sense.
0
José PerezAuthor Commented:
I have added 5 steps before the Insert into the dbDev database. These steps drop the existing table and the re-create each one and they are working ok, but when the 'INSERT' part comes I receive an error.

Executed as user: NT SERVICE\SQLSERVERAGENT. Invalid column name 'CntctCode'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'CardCode'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Name'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Position'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Address'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Tel1'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Tel2'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Cellolar'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Fax'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'E_MailL'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Pager'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Notes1'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Notes2'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'DataSource'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'UserSign'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Password'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'LogInstanc'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'ObjType'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'BirthPlace'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'BirthDate'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Gender'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Profession'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'updateDate'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'updateTime'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Title'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'BirthCity'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'Active'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'FirstName'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'MiddleName'. [SQLSTATE 42S22] (Error 207)  Invalid column name 'LastName'. [SQLSTATE 42S22] (Error 207).  The step failed.
0
ZberteocCommented:
@oscargarin:

Steps:

1. Drop the table in the dbDev database
2. Generate the CREATE TABLE script from prod database and execute it on dbDev database to create table with the same exact structure
2. Run the script I gave you.

@victormontalvo

The fact that you can use replication within the same server doesn't mean that you should actually go for it. The goal is here to implement the SIMPLEST and most effective solution to sync a prod table to a development table. Replication is a complex operation that  is not easy to maintain, not to mention that its purpose is mainly to sync data between different servers/environment.

The asker should choose what is simplest for him and replication is definitely not, especially that he doesn't have experience with it. Maintaining a SQL job with a simple one statement script is much  easier.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
@Zberteoc:
I'm sorry but I can't agree with you in this one, for many reasons:

1. Replication is a complex operation

Why? It even provides a wizard where you only need to chose the tables and columns to be replicated. You don't need to write a single line of code

2. is not easy to maintain

Why? It provides a replication monitor where you can see the error log. Also, you can add or remove tables and columns quick and easily

3. its purpose is mainly to sync data between different servers/environment.

Between servers I can accept but not between environments. You won't want to mess data between a test environment and production, for example.

4. The asker should choose what is simplest for him and replication is definitely not

Looks like he's here for one week already and still having troubles to set the "simplest" solution

5. especially that he doesn't have experience with it

Well, no one born knowing everything and if he won't start to use the Replication then he would never have experience with it for sure
0
ZberteocCommented:
Ok, Victor, whatever...
0
José PerezAuthor Commented:
@Vitor Montalvão:
Can you please explain how to do replication mentioned in point 1?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
In SSMS go to Replication folder and right-click on it and chose Configure Distribution... This is the first wizard where you need to configure the instance for Replication and it will create a distribution database in the current MSSQL instance.
After that you can start creating a new Publication by right-clicking on folder Local Publications (subfolder of Replication) and chose New Publication... Here you'll chose the tables and columns that you want to be replicated.
Last step is to create the Subscription by right-clicking on folder Local Subscriptions (subfolder of Replication)  and chose New Subscriptions... Here is where you'll chose which database will receive the replicated tables.
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
José PerezAuthor Commented:
Got it!
Thanks a lot, it worked like a charm!
0
ZberteocCommented:
@oscargarin,

I gave you a valid solution before the one you accepted. Have you tried it? Did it not work?
0
José PerezAuthor Commented:
TRied but didnot work for me. same issue that the error I posted, columns error.
0
ZberteocCommented:
Well, the column names you gave are totally different from the ones in the error. Of course that if you use column names that don't exist you will get that error...

I was just curios.
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.