Inserting Data from Another Database

Question

Post reply

Inserting Data from Another Database

Abhay Jadhav

Mr or Mrs. 500

Points: 556
More actions
March 27, 2007 at 4:19 am

#116764

Hello All,
I want to know how to insert data from table which is on different database. for eg.
suppose i have database with XYZ name
and in that databaes i have one table named abc
there is also another database named QPR
which is also having table named abc
now i want to copy the data from abc table which is in XYZ database into table abc which is in QPR
wht will be the query for the insert statement?

Plz help me out... Thx in Advance

Abhay

Viewing 11 posts - 1 through 10 (of 10 total)

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

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

Abhay

Assuming both abc tables have an identical structure and are owned by dbo:

INSERT INTO QPR.dbo.abc

SELECT * FROM XYZ.dbo.abc

John

Abhay Jadhav Mr or Mrs. 500 Points: 556 More actions · Answer 2

Thx for quick reply.

But actually i want to select only some specific columns from abc table which

for eg. there are 5 columns in abc table which is in XYZ database and i only want to select 2 columns from it. to insert into QPR database ABC table. its right that both the tables are owned by same user.

I have tried the query. like.

Insert

Into [QISPD2.dbo.VisitInfo] (VisitInfo.VisitId,VisitInfo.patientId,VisitInfo.admitDate,VisitInfo.VisitType,VisitInfo.spotriasUniqueId) Select visitInfo.visitId,visitInfo.patientId,visitInfo.admitDate,visitInfo.VisitType,visitInfo.spotriasUniqueId FROM [QISPD.dbo.visitInfo]

But its giving me error like

Invalid object name 'QISPD.dbo.visitInfo'.

Plz help me out.

Thanks

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 3

Abhay

So it sounds like visitInfo doesn't exist in QISPD, or if it does it's not owned by dbo, or maybe you don't have permissions on it. Or perhaps you have a case-sensitive collation and you haven't got the capitalisation right. By the way, you don't need to put all those qualifiers on the columns. This would be sufficient:

Insert Into [QISPD2.dbo.VisitInfo] (VisitId, patientId, admitDate, VisitType, spotriasUniqueId)

Select visitId, patientId, admitDate, VisitType, spotriasUniqueId FROM [QISPD.dbo.visitInfo]

John

Abhay Jadhav Mr or Mrs. 500 Points: 556 More actions · Answer 4

John,

Well as per your statement i have checked but its all correct over here. There is dbo user in both the database and also i am having all the rights for both the tables.

But still it is giving same error. As far as case-sensitive collation is considered i have checked all the columns and both the database and it appears same as its in query.

Please help me out. with this error. thx again.

Abhay

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 5

Abhay

I don't really know. I take it both databases are on the same server?

Do you get the same error if you just do this? SELECT * FROM QISPD.dbo.visitInfo

What about this?

USE QISPD

SELECT * FROM visitInfo

If you run this query, does the table name appear in the result set?

SELECT TABLE_SCHEMA, TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

John

Abhay Jadhav Mr or Mrs. 500 Points: 556 More actions · Answer 6

John,

Ya. both the database are on the same server. And even all the queries are executing without any error. even the schema tables are showing the name of the tables in there respective database.

But still i dont know why the query is giving error.

Abhay

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 7

Abhay

Does this work?

USE QISPD

Insert Into QISPD2.dbo.VisitInfo (VisitId, patientId, admitDate, VisitType, spotriasUniqueId)

Select visitId, patientId, admitDate, VisitType, spotriasUniqueId FROM visitInfo

John

Abhay Jadhav Mr or Mrs. 500 Points: 556 More actions · Answer 8

Sorry

Invalid object name 'qispd2.dbo.visitInfo

Abhay

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 9

Abhay

If I had to put money on it, I'd say that your tables aren't owned by dbo. You've confirmed that both tables are owned by the same user, and you've also confirmed that there is a user called dbo in both databases. But you haven't explicitly stated that dbo owns the tables. Please will you check this.

You could also try referring to the tables in the form QISPD..visitInfo.

John

Abhay Jadhav Mr or Mrs. 500 Points: 556 More actions · Answer 10

Hello John,

Thx a lot for your quick administration and efforts. Finally it worked. Actually the problem was that there was foriegn key given for qispd table. because of which it was giving error while inserting data. when i updated the values from the parent table the same query worked withoug any error.

once again thx for your great assistance. Looking foward for same in near future.

Abhay.