|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Creating a cross-database commandI need to use the SqlCommand object to execute a query that copies records from one MSSQL Database to another. I'm not using stored procedures, I'm setting the CommandText property of the command in code. The SQL is pretty simple. Given two databases A and B, both of which have an identical file File1, the SQL is... INSERT INTO A.File1 SELECT * FROM B.File1 WHERE <Some Condition> I create and open two SQLConnection objects, one to Database A and one to B. When the command is executed, the error I get says that A.File1 is not recognized. Can this be done? If so, how? Thanks. BBM 1. Are the databases on the same server?
2. Why are you not using stored procedures? 3. How do you attach 2 connections to 1 command? BBM wrote: Show quote > Hi, > > I need to use the SqlCommand object to execute a query that copies records > from one MSSQL Database to another. I'm not using stored procedures, I'm > setting the CommandText property of the command in code. > > The SQL is pretty simple. Given two databases A and B, both of which have > an identical file File1, the SQL is... > > INSERT INTO A.File1 SELECT * FROM B.File1 WHERE <Some Condition> > > I create and open two SQLConnection objects, one to Database A and one to B. > When the command is executed, the error I get says that A.File1 is not > recognized. > > Can this be done? If so, how? > > Thanks. > > BBM Hi Theo,
Thanks for your response. Yes the databases are on the same server. I don't like stored procedures because I don't like having logic that's not a part of my code. I didn't, I opened two connections. Turns out my problem was that I wasn't using complete names for my tables. The names that work are A.dbo.File1 and B.dbo.File1. I found this by following Jeff's suggestion below. I'm still stuck, one of the fields in the file I'm copying is a timestamp, and I can't get it to work. But the "two database" problem is solved. Thanks again. Show quote "Theo Verweij" wrote: > 1. Are the databases on the same server? > 2. Why are you not using stored procedures? > 3. How do you attach 2 connections to 1 command? > > BBM wrote: > > Hi, > > > > I need to use the SqlCommand object to execute a query that copies records > > from one MSSQL Database to another. I'm not using stored procedures, I'm > > setting the CommandText property of the command in code. > > > > The SQL is pretty simple. Given two databases A and B, both of which have > > an identical file File1, the SQL is... > > > > INSERT INTO A.File1 SELECT * FROM B.File1 WHERE <Some Condition> > > > > I create and open two SQLConnection objects, one to Database A and one to B. > > When the command is executed, the error I get says that A.File1 is not > > recognized. > > > > Can this be done? If so, how? > > > > Thanks. > > > > BBM > It's not a file. You might consider using correct grammar! It's a table. It
generally goes Server/Database/Table/Field Are you getting an error? Show the exact syntax you are using Jeff Show quote "BBM" <bbm@newsgroups.nospam> wrote in message news:DF5168AF-87A5-48F2-9E44-15898C476AD0@microsoft.com... > Hi Theo, > > Thanks for your response. Yes the databases are on the same server. I > don't like stored procedures because I don't like having logic that's not > a > part of my code. I didn't, I opened two connections. > > Turns out my problem was that I wasn't using complete names for my tables. > The names that work are A.dbo.File1 and B.dbo.File1. > > I found this by following Jeff's suggestion below. I'm still stuck, one > of > the fields in the file I'm copying is a timestamp, and I can't get it to > work. But the "two database" problem is solved. > > Thanks again. > > "Theo Verweij" wrote: > >> 1. Are the databases on the same server? >> 2. Why are you not using stored procedures? >> 3. How do you attach 2 connections to 1 command? >> >> BBM wrote: >> > Hi, >> > >> > I need to use the SqlCommand object to execute a query that copies >> > records >> > from one MSSQL Database to another. I'm not using stored procedures, >> > I'm >> > setting the CommandText property of the command in code. >> > >> > The SQL is pretty simple. Given two databases A and B, both of which >> > have >> > an identical file File1, the SQL is... >> > >> > INSERT INTO A.File1 SELECT * FROM B.File1 WHERE <Some Condition> >> > >> > I create and open two SQLConnection objects, one to Database A and one >> > to B. >> > When the command is executed, the error I get says that A.File1 is not >> > recognized. >> > >> > Can this be done? If so, how? >> > >> > Thanks. >> > >> > BBM >> Thanks for the tip. The timestamp problem is different enough from the one
in this thread that I started a new thread. Show quote "Jeff Dillon" wrote: > It's not a file. You might consider using correct grammar! It's a table. It > generally goes Server/Database/Table/Field > > Are you getting an error? Show the exact syntax you are using > > Jeff > > "BBM" <bbm@newsgroups.nospam> wrote in message > news:DF5168AF-87A5-48F2-9E44-15898C476AD0@microsoft.com... > > Hi Theo, > > > > Thanks for your response. Yes the databases are on the same server. I > > don't like stored procedures because I don't like having logic that's not > > a > > part of my code. I didn't, I opened two connections. > > > > Turns out my problem was that I wasn't using complete names for my tables. > > The names that work are A.dbo.File1 and B.dbo.File1. > > > > I found this by following Jeff's suggestion below. I'm still stuck, one > > of > > the fields in the file I'm copying is a timestamp, and I can't get it to > > work. But the "two database" problem is solved. > > > > Thanks again. > > > > "Theo Verweij" wrote: > > > >> 1. Are the databases on the same server? > >> 2. Why are you not using stored procedures? > >> 3. How do you attach 2 connections to 1 command? > >> > >> BBM wrote: > >> > Hi, > >> > > >> > I need to use the SqlCommand object to execute a query that copies > >> > records > >> > from one MSSQL Database to another. I'm not using stored procedures, > >> > I'm > >> > setting the CommandText property of the command in code. > >> > > >> > The SQL is pretty simple. Given two databases A and B, both of which > >> > have > >> > an identical file File1, the SQL is... > >> > > >> > INSERT INTO A.File1 SELECT * FROM B.File1 WHERE <Some Condition> > >> > > >> > I create and open two SQLConnection objects, one to Database A and one > >> > to B. > >> > When the command is executed, the error I get says that A.File1 is not > >> > recognized. > >> > > >> > Can this be done? If so, how? > >> > > >> > Thanks. > >> > > >> > BBM > >> > > > Hi BBM,
First of all, I'd like to confirm my understanding of your issue. According to your description, I understand that you got an error message when copying a value into a timestamp column from one table to another table in a different database. If I misunderstood anything here, please don't hesitate to correct me. Timestamp is an auto-updated field. So we don't need to insert an explicit value into a timestamp column. If we tried to do that, we will get the error message as below: "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column." If you want to copy a table from one database to another database, you can use insert with a column list excluding the timestamps. For example INSERT INTO A.dbo.File1 (column1,column2...) SELECT column1,column2... FROM B.dbo.File1 WHERE <Some Condition> In this way, Database will insert the timestamp value into column. Please test the above code and let me know whether it is what you need. If there is any question, please feel free to reply here and I am glad to work with you. Wen Yuan =============================== When responding to posts, please "Reply to Group" via your newsreader so that others may learn and benefit from your issue. =============================== (This posting is provided "AS IS", with no warranties, and confers no rights.) Get it working in Query Analyzer first:
INSERT something into Table1 from otherdb.dbo.Table2... Show quote "BBM" <bbm@newsgroups.nospam> wrote in message news:08B007FE-C7B4-444A-BA6C-140ED455CE1E@microsoft.com... > Hi, > > I need to use the SqlCommand object to execute a query that copies records > from one MSSQL Database to another. I'm not using stored procedures, I'm > setting the CommandText property of the command in code. > > The SQL is pretty simple. Given two databases A and B, both of which have > an identical file File1, the SQL is... > > INSERT INTO A.File1 SELECT * FROM B.File1 WHERE <Some Condition> > > I create and open two SQLConnection objects, one to Database A and one to > B. > When the command is executed, the error I get says that A.File1 is not > recognized. > > Can this be done? If so, how? > > Thanks. > > BBM  |
|||||||||||||||||||||||
Other interesting topics