Link to home
Start Free TrialLog in
Avatar of José Perez
José PerezFlag for Chile

asked on

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.
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

are both database on the same server?
Avatar of José Perez

ASKER

yes, both on the same one.
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.
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.
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
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.
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?
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).
What can I do to make work?
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

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.
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.
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.
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.
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.
@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.
@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
Ok, Victor, whatever...
@Vitor Montalvão:
Can you please explain how to do replication mentioned in point 1?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Got it!
Thanks a lot, it worked like a charm!
@oscargarin,

I gave you a valid solution before the one you accepted. Have you tried it? Did it not work?
TRied but didnot work for me. same issue that the error I posted, columns error.
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.