Thanks Adrian,
This worked, but i had to specify all the fields otherwise it gave me an
'Column name or number of supplied values does not match table definition'
error, although the column names ,data types and lenghts were exactly the
same.
Could not say 'select a.* '



-----Original Message-----
From: Adrian Smith
[mailto:mssqldba-ezmlmshield-x89494069.[Email address protected]
Sent: Friday, July 29, 2005 4:37 PM
To: LazyDBA Discussion
Subject: RE: SQL syntax advice please?


Hi


This would be a better solution on the server.

Insert into db1.dbo.tableb
Select
a.*
from
db2.dbo.tablea a
left join
db1.dbo.tableb b
on < Full Pk Join>
where b.<Not Null Field> is null


Cheers

Adrian

-----Original Message-----
From: Fadeyi Kunle
[mailto:mssqldba-ezmlmshield-x80817830.[Email address protected]
Sent: 29 July 2005 16:36
To: LazyDBA Discussion
Subject: RE: SQL syntax advice please?

insert into db1.dbo.tableb
select * from db2.dbo.tablea
where not exists ( select 1 from db1.dbo.tableb where tableb.colname =
tablea.colname)

something like that.

-----Original Message-----
From: annelised
[mailto:mssqldba-ezmlmshield-x99712655.[Email address protected]
Sent: Friday, July 29, 2005 10:23 AM
To: LazyDBA Discussion
Subject: SQL syntax advice please?


hi all,
i have tablea in one database, tableb in another. The layouts are
exactly
the same. I want to insert everything from tablea that does not exist in
tableb.

Insert into db1.dbo.tableb
select * from db2.dbo.tablea


where would the 'not exists' go?




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
****************************************************************************
*************************
The email legal notice that governs the transmission of this electronic
communication is available at
http://www.mrpricegroup.com/legalnotice.htm.
Should you not have access to the internet please telephone +27 (0)31
3108401 to request a copy.
Mr Price Group Ltd.
****************************************************************************
*************************


---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



MS Sql Server LazyDBA home page