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: Sunday, April 13, 2014 1:50 PM
Points: 62, Visits: 214
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: Saturday, April 12, 2014 11:40 AM
Points: 2,795, Visits: 8,297
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


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:43 AM
Points: 756, Visits: 631
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: Monday, April 14, 2014 11:50 PM
Points: 1,867, Visits: 2,275
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: Sunday, April 13, 2014 1:50 PM
Points: 62, Visits: 214
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


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 4:43 AM
Points: 756, Visits: 631
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: 2 days ago @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy


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

Add to briefcase

Permissions Expand / Collapse