Solved

MSSQL SERVER: Copy tables from 1 database to other

Posted on 2014-12-12
25
95 Views
Last Modified: 2014-12-24
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.
0
Comment
Question by:José Perez
  • 8
  • 7
  • 4
  • +2
25 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40497834
are both database on the same server?
0
 
LVL 2

Author Comment

by:José Perez
ID: 40497897
yes, both on the same one.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40497904
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
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 40497918
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40497948
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
 
LVL 2

Author Comment

by:José Perez
ID: 40497978
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
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40498006
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
 
LVL 2

Author Comment

by:José Perez
ID: 40498040
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
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40498044
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
 
LVL 2

Author Comment

by:José Perez
ID: 40498343
What can I do to make work?
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40498380
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40498863
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40499919
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40500088
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40500142
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
 
LVL 2

Author Comment

by:José Perez
ID: 40507829
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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40507861
@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
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40508661
@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
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40508866
Ok, Victor, whatever...
0
 
LVL 2

Author Comment

by:José Perez
ID: 40511244
@Vitor Montalvão:
Can you please explain how to do replication mentioned in point 1?
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40512521
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
 
LVL 2

Author Comment

by:José Perez
ID: 40514211
Got it!
Thanks a lot, it worked like a charm!
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40514929
@oscargarin,

I gave you a valid solution before the one you accepted. Have you tried it? Did it not work?
0
 
LVL 2

Author Comment

by:José Perez
ID: 40515855
TRied but didnot work for me. same issue that the error I posted, columns error.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40516295
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Create your own, high-performance VM backup appliance by installing NAKIVO Backup & Replication directly onto a Synology NAS!
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now