SQLTeam.com | Weblogs | Forums

Copy table from another Database


#1

Hi,
I want to copy table data from another database into my new database , it is the same named table.
I usually use the import and export function on the database. But know I need to create procedure or function so the user can do by their own.
Does anyone can help for the syntax?

thx


#2

What situation requires a user to create tables, why? What are you trying to accomplish with this process


#3

Actually user cannot create the table it is only importing from another table from another database. The condition is there are 2 system with the same database (sql server) and the second system needs to copy the record of the table on the 1st system. the name of the table is same so I need to import the data into the 2nd database.


#4

Is this a one time copy or continuous
Dies table have primary key and do you need to preserve these

If content is static I would go with a shared resources database both systems can use


#5

This doesn't explain the issue - why do you need this data in the second system? How does the user determine what data to pull over and why is that necessary?

Wouldn't it be easier to setup a daily extract from the first system and populate the second system every day, instead of requiring a user to 'select' data to be extracted/imported?

What is this process trying to accomplish?


#6

for eg:
In order to copy tables from the Test database to the Test1, we can use the Select into SQL statement. This statement will create the tables in the destination database first, then it will copy the data to these tables. If you manage to copy the database objects like indexes and constraints, you need to generate script for it individually, after that you need to apply the scripts to the destination database.

In our example, to copy the Department, Employee, EmployeeDepartmentHistory and EmployeePayHistory tables under the HumanResources schema from the Test database to the Test1 database, we will run the below script:

You can copy table from one database to another by:
For eg: The tables that to be copied from source database to destination one are: Department , Employee , EmployeeDepartmentHistory and EmployeePayHistory under the HumanResources schema.

Create schema HumanResources

Go

Select * into Test1.HumanResources.Department from

Test.HumanResources.Department

Select * into Test1.HumanResources.Employee from

Test.HumanResources.Employee

Select * into Test1.HumanResources.EmployeeDepartmentHistory from

Test.HumanResources.EmployeeDepartmentHistory

Select * into Test1.HumanResources.EmployeePayHistory from

Test.HumanResources.EmployeePayHistory