CODEWITHSUNDEEP
sqlsql-server
Nick
Nick

Reputation: 500

How do you copy Sql table from one database to another database with differ field names

I have a database name "EmpOld" with a table name "Employee" and a database name "EmpNew" with a table name "Employee".

The table structures are identical on both database tables except for the names in the table.

Here is a definition of the tables:

Database "EmpOld" table name "Employee" has following field names:

int       e_id 
char(20)  e_fname 
char(25)  e_lname

Database "EmpNew" table "Employee" has following field names:

int       id 
char(20)  fname 
char(25)  lname

Notice, the only difference in the tables is the "e_" prefix for field names are removed from the EmpNew Employee table.

How do I move data from EmpOld database to EmpNew database?

Is there a code that maps these field respectively.

Thanks community,

Nick

Upvotes: 0

Views: 935

Answers (5)

Maverick
Maverick

Reputation: 1185

There are 2 ways you can do without the data loss. 1) you can use Insert statement 

  `
    Insert into EmpNew (ID,fname,lname)
        Select e_id, e_fname, e_lastname 
            from EmpOld
  `

2) You can simple use Import-Export Wizard Go to Start Menu > SQL Server 2008/2008R2/2012 > ImportandExport> This will take you the wizard box

  • Select Source :- DataSource(ServerName) and Database where you are extracting data from
    • Select Destination : DataSource(ServerName) and Database where you are extracting data to
    • Map the table
    • BE AWARE of PK/FK/Identity

you are good to go

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280320

Well, you could just name them manually:

INSERT dbo.EmpNew(fname, lname) SELECT e_fname, e_lname FROM dbo.EmpOld;

If you want to do this without manually typing out the column names, there is magic in SSMS - just drag the Columns folder from each table into the appropriate spot in your query window (and then manually remove identity columns, timestamp, computed columns etc. if relevant).

enter image description here

enter image description here

Upvotes: 3

Consult Yarla
Consult Yarla

Reputation: 1150

There is no automatic way of mapping fields, unless with some code.

This can be done in two ways:

  1. Using the SQL Import & Export Wizard

    This is the most easy way to do this and here is an article that gives step by step to do this. The key is to change the mapping between the source and destination fields while importing the data.

  2. Writing an SQL

    This method requires both the databases to be accessible. Using a simple insert statement as follows this can be achieved

    insert into EmpNew.dbo.Employee(id, fname, lname)
select 
   e_id, e_fname, e_lname
from
   EmpOld.dbo.Employee

    If they are on same sql server then the above will work good as is. If they are different sql server you may have to add a link server connection and prefix the table commands with that.

Upvotes: 1

BendEg
BendEg

Reputation: 21098

Maybe you can generate code with help from the tables sys.columns and other system tables so that you can make the copy-process run automatically.

I think you can't use:

insert into (...) (...)

because you have two databases. So just generate insert statements like:

insert into table (...) VALUES (...)

Please correct me if i misunderstood the question.

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

Is there a code that maps these field respectively.

No - you'll need to provide the mapping. If you're using an ETL tool like SSIS there may be a way to programatically map columns based on some criteria, but nothing built into SQL.

Upvotes: 0

Related Questions