It is possible to insert data from instance to other instance database. If yes then how to do.

Question

Post reply

It is possible to insert data from instance to other instance database. If yes then how to do.

rajeshjaiswalraj

SSCrazy

Points: 2574
More actions
August 17, 2013 at 8:05 am

#297893

It is possible to insert data from instance to other instance database. If yes then how to do.
I have already created linked server. I am able to select data from other instance but not able to insert.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

homebrew01 SSC Guru Points: 55548 More actions · Answer 1

What is your select statement ?

What is the insert statement that you have tried, and what is the error ?

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 2

Syntaxwise, it simple:

INSERT mytable (...)

SELECT ...

FROM SERVER.db.dbo.tbl

What do you mean with "not able to insert"? You are just staring into the blue and can't figure out the syntax? Or did you trying something like above, but got an error message? Could you be more detailed?

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

kapil_kk SSC-Insane Points: 21316 More actions · Answer 3

Creating Linked Server will solve your problem......

Can you please post what exact error you are getting while inserting?

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

rajeshjaiswalraj SSCrazy Points: 2574 More actions · Answer 4

Thanks for the reply. what i m doing it is only for test purpose.

I having two instances - "Rajesh\SQLINST1 " and Rajesh

1. My database is in "Rajesh" instance - Database (name is test), (schema is dbo), (table is block)

2. I have created linked server in "Rajesh\SQLINST1 " which is other instance

3. I was trying to access data from ""Rajesh\SQLINST1" - (for test database which is in other instance)

(select * from Rajesh.test.dbo.block) this data base is in other instance "Rajesh" - Completed successfully

Now I want to insert data from "Rajesh\SQLINST1 " to "Rajesh"

begin tran

insert into Rajesh.test.dbo.block values (30)

error - Msg 8501, Level 16, State 3, Line 2

MSDTC on server 'RAJESH\SQLINST1' is unavailable.

but in other case ( if i will removed "begin tran) and using only

(insert into Rajesh.test.dbo.block values (30)) - it is working fine.

my question - using link server it is possible or not to insert uncommited data ?

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 5

When you have a transaction that spans servers, you have a distributed transaction. Distributed transactions are handled by Microsoft Distributed Transaction Coordinator. This service is not running by default.

In the Control Panel, select Administrative Tools, and then Services. You find MSDTC under D. Right-click and select Start.

If you have the instances on two different computers, you need DTC to be running on both. (And this can be quite a headache to get working.)

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 6

The Import and Export Wizard reachable via SSMS is quite handy to copy data to a test instance. In SSMS Object Explorer, right-click your source database, select Tasks, and choose Export Data...

Follow the Wizard to select your source and destination options. The option to save the SSIS Package that results from completing the Wizard can be helpful to automate repetitive data copying tasks.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato