Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
rajeshjaiswalraj
rajeshjaiswalraj
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 405
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.
homebrew01
homebrew01
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2980 Visits: 9071
What is your select statement ?
What is the insert statement that you have tried, and what is the error ?



Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 866
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?

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
kapil_kk
kapil_kk
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2417 Visits: 2763
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
rajeshjaiswalraj
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 405
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
Erland Sommarskog
SSC Eights!
SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)SSC Eights! (933 reputation)

Group: General Forum Members
Points: 933 Visits: 866
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.)

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8239 Visits: 14368
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
Attachments
521176b2.jpg (70 views, 52.00 KB)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search