Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

It is possible to insert data from instance to other instance database. If yes then how to do. Expand / Collapse
Author
Message
Posted Saturday, August 17, 2013 8:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:51 PM
Points: 72, Visits: 263
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.
Post #1485490
Posted Saturday, August 17, 2013 9:17 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 2,832, Visits: 8,507
What is your select statement ?
What is the insert statement that you have tried, and what is the error ?



Post #1485491
Posted Saturday, August 17, 2013 4:20 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
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
Post #1485531
Posted Saturday, August 17, 2013 8:32 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 4:54 AM
Points: 1,921, Visits: 2,345
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/
Post #1485547
Posted Saturday, August 17, 2013 9:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 16, 2014 11:51 PM
Points: 72, Visits: 263
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 ?

Post #1485548
Posted Sunday, August 18, 2013 3:00 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Saturday, October 11, 2014 11:41 AM
Points: 806, Visits: 723
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
Post #1485571
Posted Monday, August 19, 2013 1:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:06 PM
Points: 7,125, Visits: 12,720
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


  Post Attachments 
521176b2.jpg (69 views, 52.66 KB)
Post #1485677
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse