• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 126
  • Last Modified:

Copy table fields from one DB to another

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
Aleks
Asked:
Aleks
  • 3
  • 3
  • 2
2 Solutions
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
AleksAuthor Commented:
Thanks, ill try both approaches see which works best  :)
0
Industry Leaders: 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!

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split, good luck with your export.  -Jim
0
 
AleksAuthor Commented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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
 
AleksAuthor Commented:
Ill have to use a script .. unfortunately.  
Is there any software out there to create export packages ?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
As far as I know, only SQL Server Integration Services (SSIS).
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now