INSERTS across databases

[tbode2006]

Hi all,

Database A Database B
Table 1 Table 1
Column Column
A A
B B
C C
D D
E

First:
I want to INSERT A,B,C,D from Database 1 Table 1 INTO Database 2 Table 1 WHERE Database 1 Table 1 [C] = 'Y'

Second
UPDATE Database 1 Table 1 SET Database 1 Table 1 [C] = 'N' WHERE Database 1 Table 1 [A] = Database 2 Table 1 [A]

Both databases are currently in the same SQL Server instance. What changes (if any) if Database 1 is located on another computer?

 

[ptheriault]

TO access databases on the same instance use the 3 part naming convention.

database.schema.object

If you need to access another server you would create a link server to server b and use the 4 part naming convention.

server.database.schema.object

Of course the user will need permissions to all the databases where ever they are.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[tbode2006]

Working in Database2

CREATE PROCEDURE [dbo].[spLocalBOM] 

AS

INSERT INTO table1 SELECT * FROM  [Database1].dbo.table1 WHERE D = '1'
GO

in Query Analyzer

exec spLocalBOM

update [database1].dbo.table1
set D = 0

{THE UPDATE WORKS WITHOUT A WHERE CLAUSE}

Server: Msg 213, Level 16, State 4, Procedure spLocalBOM, Line 5
Insert Error: Column name or number of supplied values does not match table definition.

(4 row(s) affected)

 

[gmmastros]

The table structures must be different between the 2 databases.

Instead, you should specify the columns

INSERT 
INTO   table1(Col1, Col2, col3)
SELECT Col1, Col2, Col3
FROM   [Database1].dbo.table1 
WHERE  D = '1'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tbode2006]

Got it working!

Thanks