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
What situation requires a user to create tables, why? What are you trying to accomplish with this process
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.
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
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?
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