Insert query using select with a condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Insert query using select with a condition

Author

Topic

reddymade
Posting Yak Master

165 Posts

Posted - 2008-07-17 : 10:56:28

I want to use the following insert query, insert data from one database table to another database table.

the only condition is to check this field: [physicalname], if a row exists with the same [physicalname] in DB1 then i should exclude those rows in the select query.
INSERT INTO [DB1].[dbo].[TAB_MyProjReports]
([ModuleName]
,[rptcat]
,[physicalname]
,[minParm]
,[englishrptname]
,[englishrptdescription]
,[spanishrptname]
,[spanishrptdescription]
,[deleted]
,[updatedby])
select [ModuleName]
,[rptcat]
,[physicalname]
,[minParm]
,[englishrptname]
,[englishrptdescription]
,[spanishrptname]
,[spanishrptdescription]
,[deleted]
,[updatedby] from Db2.dbo.TAB_MyProjReports

Thank you very much for the helpful information.

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-07-17 : 11:07:18

where not exists (
select *
from Db1.dbo.TAB_MyProjReports
where physicalname = Db2.dbo.TAB_MyProjReports.physicalname
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-17 : 11:43:35

INSERT INTO [DB1].[dbo].[TAB_MyProjReports]
([ModuleName]
,[rptcat]
,[physicalname]
,[minParm]
,[englishrptname]
,[englishrptdescription]
,[spanishrptname]
,[spanishrptdescription]
,[deleted]
,[updatedby])
select t1.[ModuleName]
,t1.[rptcat]
,t1.[physicalname]
,t1.[minParm]
,t1.[englishrptname]
,t1.[englishrptdescription]
,t1.[spanishrptname]
,t1.[spanishrptdescription]
,t1.[deleted]
,t1.[updatedby] 
from Db2.dbo.TAB_MyProjReports t1
left join [DB1].[dbo].[TAB_MyProjReports] t2
ON t2.physicalname=t1.physicalname
WHERE t2.physicalname IS NULL

reddymade
Posting Yak Master

165 Posts

Posted - 2008-07-17 : 11:48:33

Thanks Visakh.

quote:

Originally posted by visakh16

or

INSERT INTO [DB1].[dbo].[TAB_MyProjReports]
([ModuleName]
,[rptcat]
,[physicalname]
,[minParm]
,[englishrptname]
,[englishrptdescription]
,[spanishrptname]
,[spanishrptdescription]
,[deleted]
,[updatedby])
select t1.[ModuleName]
,t1.[rptcat]
,t1.[physicalname]
,t1.[minParm]
,t1.[englishrptname]
,t1.[englishrptdescription]
,t1.[spanishrptname]
,t1.[spanishrptdescription]
,t1.[deleted]
,t1.[updatedby] 
from Db2.dbo.TAB_MyProjReports t1
left join [DB1].[dbo].[TAB_MyProjReports] t2
ON t2.physicalname=t1.physicalname
WHERE t2.physicalname IS NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources