Solved

Copy table fields from one DB  to another

Posted on 2014-11-10
8
113 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 47

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 47

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 47

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

815 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

8 Experts available now in Live!

Get 1:1 Help Now