|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Insert into another db on same serverHi, all. What's the syntax for an insert from one database's table into
another database's table on the same server? Here's what I've got and it keeps blowing up in SQL Query Analyzer, while connected to db1 which has Table1. I need to insert into db2's Table2. It keeps giving an "Invalid object name db2.Table2." error. I'm logged in with NT authentication and am DBO on both databases. Thanks :) insert into [db2].[Table2] (domainid,firstname,middlename,lastname,age, school,class) (select mail,f_name,m_name,l_name,age,college,class from Table1 where id = '111-11-1111') A fully qualified table name takes the form
<server>.<database>.<owner>.<table>. Since you are on the same server, it looks like you are missing the table's owner in your statement. -- Show quote--Brian (Please reply to the newsgroups only.) "dw" <cougarmana_NOSPAM@uncw.edu> wrote in message news:eEdsre8rFHA.260@TK2MSFTNGP11.phx.gbl... > Hi, all. What's the syntax for an insert from one database's table into > another database's table on the same server? Here's what I've got and it > keeps blowing up in SQL Query Analyzer, while connected to db1 which has > Table1. I need to insert into db2's Table2. It keeps giving an "Invalid > object name db2.Table2." error. I'm logged in with NT authentication and > am DBO on both databases. Thanks :) > > insert into [db2].[Table2] > (domainid,firstname,middlename,lastname,age, > school,class) > (select mail,f_name,m_name,l_name,age,college,class from Table1 > where id = '111-11-1111') > > Thanks, Brian. Now it's giving this error,
Could not find server 'DBSRV' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. It looks like this now, DBSRV.db2.[domain\username].table2 I've also tried DBSRV.db2.[dbo].table2 Show quote "Brian Lawton" <brian.k.law***@redtailcreek.com> wrote in message news:OwqSBl8rFHA.2592@TK2MSFTNGP09.phx.gbl... >A fully qualified table name takes the form ><server>.<database>.<owner>.<table>. Since you are on the same server, it >looks like you are missing the table's owner in your statement. > > -- > --Brian > (Please reply to the newsgroups only.) > > > "dw" <cougarmana_NOSPAM@uncw.edu> wrote in message > news:eEdsre8rFHA.260@TK2MSFTNGP11.phx.gbl... >> Hi, all. What's the syntax for an insert from one database's table into >> another database's table on the same server? Here's what I've got and it >> keeps blowing up in SQL Query Analyzer, while connected to db1 which has >> Table1. I need to insert into db2's Table2. It keeps giving an "Invalid >> object name db2.Table2." error. I'm logged in with NT authentication and >> am DBO on both databases. Thanks :) >> >> insert into [db2].[Table2] >> (domainid,firstname,middlename,lastname,age, >> school,class) >> (select mail,f_name,m_name,l_name,age,college,class from Table1 >> where id = '111-11-1111') >> >> > > If you're on the same server, you don't NEED the DB prefix. You need
three-part naming, not four. Also, is the table really owned by [domain\username]? Show quote "dw" <cougarmana_NOSPAM@uncw.edu> wrote in message news:el9h1o8rFHA.1552@TK2MSFTNGP10.phx.gbl... > Thanks, Brian. Now it's giving this error, > > Could not find server 'DBSRV' in sysservers. Execute sp_addlinkedserver to > add the server to sysservers. > > It looks like this now, > > DBSRV.db2.[domain\username].table2 > > I've also tried > > DBSRV.db2.[dbo].table2 > Thanks, Aaron. By the way, do you have a blog or site?
Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:OnIysr8rFHA.3060@TK2MSFTNGP09.phx.gbl... > If you're on the same server, you don't NEED the DB prefix. You need > three-part naming, not four. > > Also, is the table really owned by [domain\username]? > > > > "dw" <cougarmana_NOSPAM@uncw.edu> wrote in message > news:el9h1o8rFHA.1552@TK2MSFTNGP10.phx.gbl... >> Thanks, Brian. Now it's giving this error, >> >> Could not find server 'DBSRV' in sysservers. Execute sp_addlinkedserver >> to add the server to sysservers. >> >> It looks like this now, >> >> DBSRV.db2.[domain\username].table2 >> >> I've also tried >> >> DBSRV.db2.[dbo].table2 >> > Thanks, Aaron.
Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uvAF2K9rFHA.3264@TK2MSFTNGP12.phx.gbl... >> Thanks, Aaron. By the way, do you have a blog or site? > > www.aspfaq.com > Hi
You forgot the owner of the object. insert into [db2].dbo.[Table2] (domainid,firstname,middlename,lastname,age, school,class) (select mail,f_name,m_name,l_name,age,college,class from db1.dbo.Table1 where id = '111-11-1111') When doing cross DB work, it is best to specify in all queries the full database.owner.object format so there are no possibilities for confusion and error. -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "dw" wrote: > Hi, all. What's the syntax for an insert from one database's table into > another database's table on the same server? Here's what I've got and it > keeps blowing up in SQL Query Analyzer, while connected to db1 which has > Table1. I need to insert into db2's Table2. It keeps giving an "Invalid > object name db2.Table2." error. I'm logged in with NT authentication and am > DBO on both databases. Thanks :) > > insert into [db2].[Table2] > (domainid,firstname,middlename,lastname,age, > school,class) > (select mail,f_name,m_name,l_name,age,college,class from Table1 > where id = '111-11-1111') > > > You need an owner name, and your parentheses are a little out of whack.
INSERT db2.dbo.Table2(col1, col2) SELECT col1, col2 FROM othertable Show quote "dw" <cougarmana_NOSPAM@uncw.edu> wrote in message news:eEdsre8rFHA.260@TK2MSFTNGP11.phx.gbl... > Hi, all. What's the syntax for an insert from one database's table into > another database's table on the same server? Here's what I've got and it > keeps blowing up in SQL Query Analyzer, while connected to db1 which has > Table1. I need to insert into db2's Table2. It keeps giving an "Invalid > object name db2.Table2." error. I'm logged in with NT authentication and > am DBO on both databases. Thanks :) > > insert into [db2].[Table2] > (domainid,firstname,middlename,lastname,age, > school,class) > (select mail,f_name,m_name,l_name,age,college,class from Table1 > where id = '111-11-1111') > > Thanks, Brian, Mike, and Aaron. I finally got it! I had to take the server
name off the db2.dbo.Table2 and also remove the parenthesis from the SELECT. Thanks for your help :) Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:%23nZPNl8rFHA.2596@TK2MSFTNGP09.phx.gbl... > You need an owner name, and your parentheses are a little out of whack. > > INSERT db2.dbo.Table2(col1, col2) SELECT col1, col2 FROM othertable > > > > "dw" <cougarmana_NOSPAM@uncw.edu> wrote in message > news:eEdsre8rFHA.260@TK2MSFTNGP11.phx.gbl... >> Hi, all. What's the syntax for an insert from one database's table into >> another database's table on the same server? Here's what I've got and it >> keeps blowing up in SQL Query Analyzer, while connected to db1 which has >> Table1. I need to insert into db2's Table2. It keeps giving an "Invalid >> object name db2.Table2." error. I'm logged in with NT authentication and >> am DBO on both databases. Thanks :) >> >> insert into [db2].[Table2] >> (domainid,firstname,middlename,lastname,age, >> school,class) >> (select mail,f_name,m_name,l_name,age,college,class from Table1 >> where id = '111-11-1111') >> >> > >  |
|||||||||||||||||||||||
Other interesting topics