Database

Eddy Vluggen wrote:

a difference between deleting a file from disk and a file from a database

Eddy Vluggen wrote:

whether they've got the appropriate rights to delete it

PIEBALDconsult wrote:

I'm pretty sure none of that has anything to do with the original question.

Bastard Programmer from Hell

IF NOT EXISTS (SELECT DISTINCT keyname FROM <table_name>)
  BEGIN 
    INSERT INTO.....
  END

Declare @tblSource table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
insert into @tblSource Select 'Sam', 'Sam1' Union all Select 'Joy','Joy2'
--Select * from @tblSource

Declare @key as varchar(10)
set @key  = 'Joy2'

if(( Select count([KEY]) from @tblSource where [key] = @key) = 1)
begin
insert into @tblSource Select 'NewName', 'NewKey'
end 

Select * from @tblSource

Declare @tblSource table([ID] int identity,[Name] varchar(20),[Key] varchar(10))
insert into @tblSource Select 'Sam', 'Sam1' Union all Select 'Joy','Joy2' 

Declare @tblDestination table([ID] int identity,[Name] varchar(20),[Key] varchar(10))

MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);	

Select * from @tblDestination

ID	Name	Key
1	Sam	Sam1
2	Joy	Joy2

insert into @tblSource Select 'Joy','Joy2' 

ID	Name	Key
1	Sam	Sam1
2	Joy	Joy2
3	Joy	Joy2

MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);
Select * from @tblDestination

ID	Name	Key
1	Sam	Sam1
2	Joy	Joy2

insert into @tblSource Select 'NewName','NewKey' 

ID	Name	Key
1	Sam	Sam1
2	Joy	Joy2
3	NewName	NewKey

ID	Name	Key
1	Sam	Sam1
2	Joy	Joy2
3	NewName	NewKey

MERGE INTO @tblDestination AS Target
USING (SELECT [Name],[Key] FROM @tblSource) AS Source
ON Target.[Key] = Source.[Key]
WHEN NOT MATCHED BY TARGET THEN
	INSERT (Name, [Key]) VALUES (Source.Name, Source.[Key]);

current1999 wrote:

· The total number of employees in the town.

current1999 wrote:

Your propositions have been tested without success.

select  mt.town , sum(mt.CompanySize) as TotalNrOfEmployees,
sum(mt.CompanySize)/(select count(*) from mytable) as AverageEmployeesPerTown,
sum(mt.CompanySize)/(select count(*) from mytable where companysize<20) as AverageEmployeesPerTownLessThen20
		
from mytable mt
group by mt.town
having (sum(mt.CompanySize) > 20)

order by ( sum(mt.CompanySize)) desc

<br />
Town            TotalNrOfEmployees     AverageEmployeesPerTown    AverageEmployeesPerTownLessThen20<br />
Texas	        53	               8	                  13<br />
Maryland	49	               8	                  12