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 ?
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Jim HornSQL Server Data DudeCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AleksAuthor Commented:
Thanks, ill try both approaches see which works best  :)
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Jim HornSQL Server Data DudeCommented:
Thanks for the split, good luck with your export.  -Jim
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
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.
AleksAuthor Commented:
Ill have to use a script .. unfortunately.  
Is there any software out there to create export packages ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
As far as I know, only SQL Server Integration Services (SSIS).
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.