CODEWITHSUNDEEP
sql-serversql-server-2008
nsilva
nsilva

Reputation: 5622

Insert table data from table to another table

I'm trying to insert from one database to another using SQL Server 2008.

I'm trying to insert from 'EG-COMPUTER1\FTLIVE' to 'EG-COMPUTER2\FTSTANDBY'.

Basically from a database on one computer to the same database on a different computer which is on the same network.

How would I able to do this?

Note: I can connect to both databases on the same computer so there must be an easy way to insert from one database to another?

EDIT::

Just to add that one computer is using SQL Server 2008-R2 and the other is using SQL Server 2008

Upvotes: 0

Views: 4149

Answers (5)

nsilva
nsilva

Reputation: 5622

I've found what I need to do, this is:

INSERT INTO [DatabaseNameA].[dbo].[Table]  
([Column1],[Column2] etc...)
SELECT 
[Column1],[Column2] etc...
 SET IDENTITY_INSERT [DatabaseNameB].[dbo].[Table] On;
GO

This will select the database I want to select from, turn the identity_insert to on so the data can be inserted and transfer the data from one database to another.

Upvotes: 1

Shantanu Gupta
Shantanu Gupta

Reputation: 21108

You can either use OpenRowSet if this is not a recurring activity otherwise if you need to perform actions frequently then you should create linked server.

Upvotes: 0

Franklin
Franklin

Reputation: 905

Nislva ,

kindly verify both tables are acessable or not with your login. if both tables are acessable then try to copy of one database table to another database table other wise create one view from one database table and transfer that view to another database table.

there after you can do your required task. this is best practice method and in future also you may not get performance problems.

if you are not intrested to do this task then simply follow the below query

insert into ur-tablename(column-names) select sourcedbname.schemaname.column-name1 , sourcedbname2.schemaname.column-name ... from sourcedatabsename.schemaname.tablename

Upvotes: 0

sudhansu63
sudhansu63

Reputation: 6200

There are several ways you can do this.

      1. RightClick On DataBase -> Tasks -> Import -> Import Export wizard ->Choose Source database -> choose Destination database -> choose Copy data from One or more table -> choose required tables -> Import.


     2.RighClick On Database -> Task -> Generate Script -> Choose tables -> Advanced -> Change "Types of Data to Script " to "Schema and Data or data or schema(as required)" -> Change "script Drop and Create" to Drop and Create(optional) - > create.
Copy the script file/files and run in the other computer.


     3.Right Click the database -> task -> BackUp -> create backup -> take the back up to other computer(.bak file can be found inside the Backup directory inside installation directory)  -> right click the database -> task -> restore   -> choose the bakup file -> done.


     4. Can use sql azure migration tool  http://sqlazuremw.codeplex.com/ (similar to step 1)

Upvotes: 2

Vikdor
Vikdor

Reputation: 24134

If you would like to just use the database level transfer of data then you should configure those two instances as linked servers (msdn doc).

Otherwise, if you are familiar with Java (or any high level language that provides APIs to interact with databases), you can write a simple program that retrieves the data from the table in one server and inserts into the table in the other server (quicker way without reaching out to DBAs to configure linked servers).

Upvotes: 1

Related Questions