Creating a cross-database command

[Guest]

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

 

[Theo Verweij]

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?

 

[Jeff Dillon]

Get it working in Query Analyzer first:

INSERT something into Table1 from otherdb.dbo.Table2...

 

[Guest]

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.

 

[Jeff Dillon]

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

 

[WenYuan Wang]

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.)

 

[Guest]

Thanks for the tip. The timestamp problem is different enough from the one
in this thread that I started a new thread.