Solved

Copy table fields from one DB  to another

Posted on 2014-11-10
8
115 Views
Last Modified: 2014-11-11
I am in the process of exporting data from one database to another.  (MS SQL 2008)

DB1 has many fields and not all will be exported.
DB2 will create a table with the selected fields to be imported

How can I create a script that will copy the table and the selected fields from DB1 to DB2

Example:

source: DB1
table: dbo.legal_entity
fields to export:  

legal_entity_sk
company_sk
first_name
last_name
middle_name

target:  DB2
table: users
field names to import the above data

id
employer
FirstNm
LastNm
MiddleNm

Those are the new names for the above fields (DB1). How can I copy the data and create the table and fields on DB2 with the above information ?
0
Comment
Question by:amucinobluedot
  • 3
  • 3
  • 2
8 Comments
 
LVL 48

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 250 total points
ID: 40432917
Why not use the Import/Export wizard from SQL Management Studio?
Just right-click on the database name, chose Tasks / Import Export.... then follow the wizard. It's very easy and in the end you can save the package for later (re)use.
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 250 total points
ID: 40432933
Give this a whirl..
USE DB2
GO

IF EXISTS(SELECT name FROM sys.tables WHERE name='legal_entity') 
   DROP TABLE legal_entity
GO

SELECT 
   legal_entity_sk as id, 
   company_sk as employer, 
   first_name as FirstNm
   last_name as LastNm
   middle_name as MiddleNm
INTO DB2.dbo.users
FROM DB1.dbo.legal_entity
GO

Open in new window

0
 

Author Comment

by:amucinobluedot
ID: 40432988
Thanks, ill try both approaches see which works best  :)
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 65

Expert Comment

by:Jim Horn
ID: 40433009
Thanks for the split, good luck with your export.  -Jim
0
 

Author Comment

by:amucinobluedot
ID: 40433016
Well ... as it turns out I am doing this in my development computer where I have SQL express and I cannot save the package  :(  ... will have to be a manual query
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40433054
That's right. In Express Edition the save functionality isn't available. You need to run immediately the package.
It's good for an one shot use. Otherwise it's better to write a script for future use.
0
 

Author Comment

by:amucinobluedot
ID: 40433138
Ill have to use a script .. unfortunately.  
Is there any software out there to create export packages ?
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40434447
As far as I know, only SQL Server Integration Services (SSIS).
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Format Data Field - SQL 11 37
SQL trigger 5 23
SSRS: Why is Visual Studio stripping these properties? 2 22
Current Month Filter in Visual Studio 10 23
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

838 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