Solved

Copy table fields from one DB  to another

Posted on 2014-11-10
8
123 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 51

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 66

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 66

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 51

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 51

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

630 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