query to fetch the table and values from other db

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

query to fetch the table and values from other db

Author

Topic

vidhya
Posting Yak Master

108 Posts

Posted - 2008-08-05 : 06:15:33

Hi friends,
I need query for transfering table and corresponding value for that table from one database to other database.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 06:19:56

insert into db1.dbo.table1(column_list)
select column_list from db2.dbo.table1(column_list)

Madhivanan

Failing to plan is Planning to fail

vidhya
Posting Yak Master

108 Posts

Posted - 2008-08-05 : 06:31:54

hi
I m getting error "'blogid' is not a recognized OPTIMIZER LOCK HINTS option."
blogid is identity column.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 06:37:50

quote:
Originally posted by vidhya

hi
I m getting error "'blogid' is not a recognized OPTIMIZER LOCK HINTS option."
blogid is identity column.

You need to omit that column when iserting data to a table

Madhivanan

Failing to plan is Planning to fail

vidhya
Posting Yak Master

108 Posts

Posted - 2008-08-05 : 06:50:34

hi,

I omitted the blogid column in colum_list.Now i m getting the error "'username' is not a recognized OPTIMIZER LOCK HINTS option."

My query below...

insert into sample.dbo.blog(username,title,content,deleted,createddatetime,modifieddatetime)
select username,title,content,deleted,createddatetime,modifieddatetime from master.dbo.blog(username,title,content,deleted,createddatetime,modifieddatetime)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 08:32:29

The query should be

insert into sample.dbo.blog(username,title,content,deleted,createddatetime,modifieddatetime)
select username,title,content,deleted,createddatetime,modifieddatetime from master.dbo.blog

Madhivanan

Failing to plan is Planning to fail

vidhya
Posting Yak Master

108 Posts

Posted - 2008-08-05 : 09:06:25

I need to write the query after use sample or use master. from which database i need to execute the query either sample or master.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 09:39:09

quote:
Originally posted by vidhya

I need to write the query after use sample or use master. from which database i need to execute the query either sample or master.

Why do you want to use USE?
What is wrong with my suggestion?

Madhivanan

Failing to plan is Planning to fail

vidhya
Posting Yak Master

108 Posts

Posted - 2008-08-05 : 10:02:07

I have selected the "sample" database. under sample database i had written the query that u gave.
It shows an error "Invalid object name 'master.dbo.blog'".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 10:32:32

quote:
Originally posted by vidhya

I have selected the "sample" database. under sample database i had written the query that u gave.
It shows an error "Invalid object name 'master.dbo.blog'".

Is there a table called blog in master database?

Madhivanan

Failing to plan is Planning to fail

vidhya
Posting Yak Master

108 Posts

Posted - 2008-08-05 : 10:35:51

no table called blog in master database.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-05 : 10:39:30

quote:
Originally posted by vidhya

no table called blog in master database.

So, do you want to create it in master db?

select sername,title,content,deleted,createddatetime,modifieddatetime from into master.dbo.blog from sample.dbo.blog

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources