Is this possible without using cursors?

Hi!

I have 2 tables: Person and Address

Person
(
PersonID int PK
)

Adress
(
AddresID int PK,
PersonID int FK,
Default -- 1 if address is default for person
)

so when I join those table it yelds (for example):

p1 a1 1
p1 a2 0
p1 a3 0
p2 a4 1
p3 a5 0
p3 a6 0

Person may:
- have one default addres and some non-default;
- haven't default address;
- have only default adres

So proper result is:

p1 a1 p1 a1
p2 a4 OR p2 a4
p3 a5 p3 a6

I want to get list of persons and their adresses in following manner:

Get person and
- default adress ID if exists for this person
- any address ID else

Is this possible without using cursors?

Regards,
Walter

Walter:

This can be done with an outer join. The main thing to verify is what you want to do when you have NULL returned for OUTER TABLE results:

select p.personID,
a.addressID,
a.[default]
from Person p
left join address a
on p.personID = a.personID
and a.[default] = 1

-- Sample Output:

-- personID addressID default
-- -- -- -
-- 1 1 1
-- 2 4 1
-- 3 NULL NULL

Dave

|||

Hmmm...

I that statement, you have persons with defaults addresses, or info that there's no default address. But there is no addressID when it is non-default. We don't want NULLs, but any non-default addressID...

Walte

|||

Sorry about that. Is this closer to what you have in mind?

select personID,
addressID
from ( select p.personID,
a.addressID,
a.[default],
row_number () over
( partition by p.personID
order by a.[default] desc, a.addressID
) as seq
from Person p
inner join address a
on p.personID = a.personID
) a
where seq = 1

-- Sample Output:

-- personID addressID
-- -- --
-- 1 1
-- 2 4
-- 3 5

|||

Please mention the version of SQL Server so it is easy to suggest the correct solution that will work in that version or all versions. You can do below in SQL Server 2005:

select ...

from Person as p

cross apply (

select top 1 *

from Address as a

where a.PersonID = p.PersonID

order by a.Default desc

) as pa

And add an index on (Address.PersonID, Address.Default DESC) to get the best performance. If you need solution for older version of SQL Server then please reply back.

|||

I simulated 32767 rows of person data with about 75000 rows of address data with:

drop table dbo.Person
go
create table dbo.Person
(
PersonID int primary key
)
go
drop table dbo.address
go

create table dbo.Address
(
AddressID int primary key,
PersonID int,
[default] tinyint
)
go

insert into person
select iter
from small_iterator

insert into address
select personID,
personID,
0
from person
where personID % 11 > 1

insert into address
select 32768 + personID,
personID,
0
from person
where personID % 11 < 2

insert into address
select 2*32768 + personID,
personID,
0
from person
where personID %17 not in (3, 5, 13)

insert into address
select 3*32768 + personID,
personID,
0
from person
where personID %23 not in (7, 10, 13, 19)

First, I ran this query with these results:

declare @.begDt datetime
set @.begDt = getdate()

select personID,
addressID
from ( select p.personID,
a.addressID,
a.[default],
row_number () over
( partition by p.personID
order by a.[default] desc, a.addressID
) as seq
from Person p
inner join address a
on p.personID = a.personID
) a
where seq = 1

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- Sample Output:

-- personID addressID
-- -- --
-- 1 32769
-- 2 2
-- ...
-- 32766 32766
-- 32767 32767

-- |--Filter(WHERE:([Expr1004]=(1)))
-- |--Sequence Project(DEFINE:([Expr1004]=row_number))
-- |--Compute Scalar(DEFINE:([Expr1006]=(1)))
-- |--Segment
-- |--Sort(ORDER BY:([p].[PersonID] ASC, .[default] DESC, .[AddressID] ASC))
-- |--Hash Match(Inner Join, HASH:([p].[PersonID])=(.[PersonID]), RESIDUAL:([Mugambo].[dbo].[Address].[PersonID] as .[PersonID]=[Mugambo].[dbo].[Person].[PersonID] as [p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Person].[PK__Person__7D63964E] AS [p]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Address].[PK__Address__7F4BDEC0] AS ))

-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Address'. Scan count 1, logical reads 196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 55, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- Elapsed Time
--
-- 656

And then ran this query with these results:

declare @.begDt datetime
select @.begDt = getdate()

select p.personID,
pa.addressID
from person p
cross apply
( select top 1 *
from address as a
where a.personID = p.personID
order by a.[default] desc
) pa

print ' '
select datediff (ms, @.begDt, getdate()) as [Elapsed Time]

-- |--Nested Loops(Inner Join, OUTER REFERENCES:([p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Person].[PK__Person__7D63964E] AS [p]))
-- |--Sort(TOP 1, ORDER BY:(.[default] DESC))
-- |--Index Spool(SEEK:(.[PersonID]=[Mugambo].[dbo].[Person].[PersonID] as [p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([Mugambo].[dbo].[Address].[PK__Address__7F4BDEC0] AS ))

-- Table 'Worktable'. Scan count 32767, logical reads 242508, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Address'. Scan count 1, logical reads 196, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 55, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- Elapsed Time
--
-- 2563

Dave

|||Please add an index on (Address.PersonID, Address.Default DESC) and you will get the best performance with the APPLY approach. Also, the results from both queries will not be identical because of the additional ordering on AddressID in your query.|||

Hi,

thanks for yours posts :) Unfortunately I use SQL SERVER 2000....

Regards,
Walter

|||

The following query might help you

Select Person.PersonID,Isnull(Adress.AddresID,Adress2.AddresID) from Person
left Outer Join Adress on Person.PersonID = Adress.PersonID And Adress.isDefault = 1
left Outer Join Adress Adress2 On Person.PersonID = Adress2.PersonID And Adress2.isDefault = 0
and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

|||

Select Person.PersonID,Isnull(Adress.AddresID,Adress2.AddresID) from Person
left Outer Join Adress on Person.PersonID = Adress.PersonID And Adress.isDefault = 1
left Outer Join Adress Adress2 On Person.PersonID = Adress2.PersonID And Adress2.isDefault = 0
and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

This query has me confused; I am not sure that I am getting the correct return data for my large test case.

|||

Here if there is a default value then it will pull that value..

If there is no default value instead of fetching Min/Max address id it will pull the random address from the DB.

May be your test case fail if you try to get Max/Min address id as expected.

Change your test case as "IN Non Default Address List instead of Particular Address"

|||Well, I am getting nulls for some address IDs and some rows that have duplicate personIDs. And I agree with you in that this might be a data problem. (I gotta get something to eat.)|||

Mani:

Ran the following three queries and got the results that follow:

select count(*) as [Person Records] from person
select count(distinct personID) as [Address Records] from address
select count(distinct a.personID) [Matched Person Records] from person a inner join address b on a.personID = b.personID

-- Person Records
-- --
-- 32767

-- Address Records
--
-- 32767

-- Matched Person Records
-- -
-- 32767

Therefore, I do not think that the problem is a data problem. I re-examined your select and I question this line:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

specifically, the "... Where Address2.PersonID ..." portion. I think this is the source of the NULL and duplicate PersonID records. When I change this line to:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

I get what I perceive to be "correct" results. Please verify whether or not you aggree.

Also, I completely agree with Umachandar's assessment that we need an additional index. I am therefore going to add a cover index based on (1) personID, (2) default and (3) addressID.

Running one more test.

Dave

|||

Mani:

Ran the following three queries and got the results that follow:

select count(*) as [Person Records] from person
select count(distinct personID) as [Address Records] from address
select count(distinct a.personID) [Matched Person Records] from person a inner join address b on a.personID = b.personID

-- Person Records
-- --
-- 32767

-- Address Records
--
-- 32767

-- Matched Person Records
-- -
-- 32767

Therefore, I do not think that the problem is a data problem. I re-examined your select and I question this line:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

specifically, the "... Where Address2.PersonID ..." portion. I think this is the source of the NULL and duplicate PersonID records. When I change this line to:

and Adress2.AddresID = (Select Top 1 AddresID From Adress Sub Where Adress2.PersonID = Sub.PersonID Order By newId())

I get what I perceive to be "correct" results. Please verify whether or not you aggree.

Also, I completely agree with Umachandar's assessment that we need an additional index. I am therefore going to add a cover index based on (1) personID, (2) default and (3) addressID.

Running one more test.

Dave

|||

I ran the modified version of Mani's query against my mock tables and got the following results:

-- |--Compute Scalar(DEFINE:([Expr1010]=isnull([Address].[AddressID], [Address].[AddressID])))
-- |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Person].[PersonID]))
-- |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Address].[PersonID])=([Person].[PersonID]), RESIDUAL:([Address].[PersonID]=[Person].[PersonID]))
-- | |--Sort(ORDER BY:([Address].[PersonID] ASC))
-- | | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Address].[PK__Address__701695AD]), WHERE:([Address].[default]=1))
-- | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Person].[PK__Person__6E2E4D3B]), ORDERED FORWARD)
-- |--Hash Match(Cache, HASH:([Person].[PersonID]), RESIDUAL:([Person].[PersonID]=[Person].[PersonID]))
-- |--Nested Loops(Inner Join, OUTER REFERENCES:([Address].[AddressID]))
-- |--Sort(TOP 1, ORDER BY:([Expr1009] ASC))
-- | |--Compute Scalar(DEFINE:([Address].[AddressID]=[Address].[AddressID], [Expr1009]=newid()))
-- | |--Index Spool(SEEK:([Address].[PersonID]=[Person].[PersonID]))
-- | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Address].[PK__Address__701695AD]))
-- |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Address].[PK__Address__701695AD]), SEEK:([Address].[AddressID]=[Address].[AddressID]), WHERE:([Address].[PersonID]=[Person].[PersonID] AND [Address].[default]=0) ORDERED FORWARD)

-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0.
-- Table 'Address'. Scan count 3, logical reads 585, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 85514, logical reads 347474, physical reads 0, read-ahead reads 0.

-- Elapsed Time
--
-- 2186

I next added this index:

create index address_personID_ndx
on address (personID, [default] desc, addressID)

And then I re-tested the modified version of Mani's query and received these improved results:

-- |--Compute Scalar(DEFINE:([Expr1010]=isnull([Address].[AddressID], [Address].[AddressID])))
-- |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Person].[PersonID]))
-- |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([Address].[PersonID])=([Person].[PersonID]), RESIDUAL:([Address].[PersonID]=[Person].[PersonID]))
-- | |--Index Scan(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx]), WHERE:([Address].[default]=1) ORDERED FORWARD)
-- | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Person].[PK__Person__6E2E4D3B]), ORDERED FORWARD)
-- |--Hash Match(Cache, HASH:([Person].[PersonID]), RESIDUAL:([Person].[PersonID]=[Person].[PersonID]))
-- |--Nested Loops(Inner Join, OUTER REFERENCES:([Address].[AddressID]))
-- |--Sort(TOP 1, ORDER BY:([Expr1009] ASC))
-- | |--Compute Scalar(DEFINE:([Address].[AddressID]=[Address].[AddressID], [Expr1009]=newid()))
-- | |--Index Seek(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx]), SEEK:([Address].[PersonID]=[Person].[PersonID]) ORDERED FORWARD)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx]), SEEK:([Address].[PersonID]=[Person].[PersonID] AND [Address].[default]=0 AND [Address].[AddressID]=[Address].[AddressID]) ORDERED FORWARD)

-- Table 'Address'. Scan count 65535, logical reads 131671, physical reads 0, read-ahead reads 0.
-- Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 1953

I then tested a different query and obtained the results that follow:

select p.personID,
( select top 1 addressID from address b
where p.personID = b.personID
order by [default] desc
) as addressID
from Person p

-- |--Compute Scalar(DEFINE:(.[AddressID]=.[AddressID]))
-- |--Nested Loops(Left Outer Join, OUTER REFERENCES:([p].[PersonID]))
-- |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Person].[PK__Person__6E2E4D3B] AS [p]))
-- |--Compute Scalar(DEFINE:(.[AddressID]=.[AddressID]))
-- |--Top(1)
-- |--Index Seek(OBJECT:([tempdb].[dbo].[Address].[address_personID_ndx] AS ), SEEK:(.[PersonID]=[p].[PersonID]) ORDERED FORWARD)

-- Table 'Address'. Scan count 32767, logical reads 65593, physical reads 0, read-ahead reads 0.
-- Table 'Person'. Scan count 1, logical reads 54, physical reads 0, read-ahead reads 0.
-- Elapsed Time
--
-- 1890

There is not a lot of difference with respect to execution time between this query and the modified version of Mani's query. This version has one less join and therefore half the scans and half the logical reads. Also, if you neglect to add the index this query is liable to run slow because of the NESTED LOOP join and associated TABLE SCANS instead of INDEX SEEKS.

Either way, BE SURE TO ADD THE INDEX RECOMMENDED BY Umachandar!

Dave

sql

Is this possible without using Cursors?

Hello!
I have a stored proc that checks the file existance in one location and
copy them to another location. The stored proc reads the record one by
one and builds the DOS COPY command. In the end, it exccutes the DOS
command using xp_cmdshell. See below the code.
The stored proc is working great but I had to use the CURSOR for reading
the records. I was wondering if I can avoid using it? Is this possible?
how? Thanks in advance for your help!
CREATE TABLE [dbo].[FILE_PATH] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Source] [varchar] (150) NULL ,
[Destination] [char] (150) NULL ,
[Environment] [char] (25) NULL ,
[Filename] [varchar] (50) NULL
) ON [PRIMARY]
GO
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverA\folder1','\\serverD\folder1
','Development',
'file1')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverA\folder1','\\serverD\folder1
','Development',
'file2')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
go
insert into FILE_PATH(Source, Destination, Environment, [FileName])
values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
CREATE proc CopyFiles
as
set nocount on
declare @.source varchar(150)
declare @.destination varchar(150)
declare @.DOScmd varchar(300)
declare @.source_dir varchar(200)
declare @.filename varchar(30)
declare @.environment varchar(200)
declare @.environementTmp varchar(200)
declare @.errortext varchar(200)
select
@.environementTmp =
case @.@.servername
when 'server A' then 'Development'
when 'server B' then 'UAT'
when 'server C' then 'Production'
end
create table #files(filename sysname NULL)
declare filecursor cursor for
select source, destination, environment, [filename]
from file_path
open filecursor
fetch next from filecursor into @.source, @.destination, @.environment,
@.filename
while (@.@.fetch_status = 0)
begin
if @.environementTmp = @.environment
begin
set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
+ ' /b'
insert #files exec master..xp_cmdshell @.source_dir
if (select filename from #files where filename is not null) = @.filename
begin
set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
' +'"'+ rtrim(@.destination)+'"'
exec master.dbo.xp_cmdshell @.DOScmd
end
else
begin
set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
exist!!!'
exec master.dbo.xp_sendmail
@.recipients = 'abc123',
@.copy_recipients = 'abc123'
@.subject = @.errortext,
@.message = @.errortext
end
end
truncate table #files
fetch next from filecursor into @.source, @.destination, @.environment,
@.filename
end
close filecursor
deallocate filecursor
GO
*** Sent via Developersdex http://www.examnotes.net ***Test Test wrote:
> Hello!
> I have a stored proc that checks the file existance in one location
> and copy them to another location. The stored proc reads the record
> one by one and builds the DOS COPY command. In the end, it exccutes
> the DOS command using xp_cmdshell. See below the code.
> The stored proc is working great but I had to use the CURSOR for
> reading the records. I was wondering if I can avoid using it? Is this
> possible? how? Thanks in advance for your help!
> CREATE TABLE [dbo].[FILE_PATH] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (150) NULL ,
> [Destination] [char] (150) NULL ,
> [Environment] [char] (25) NULL ,
> [Filename] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
>
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file1')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production',
> 'file5') go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file2')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production',
> 'file5')
>
> CREATE proc CopyFiles
> as
>
> set nocount on
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> declare @.source_dir varchar(200)
> declare @.filename varchar(30)
> declare @.environment varchar(200)
> declare @.environementTmp varchar(200)
> declare @.errortext varchar(200)
>
> select
> @.environementTmp =
> case @.@.servername
> when 'server A' then 'Development'
> when 'server B' then 'UAT'
> when 'server C' then 'Production'
> end
> create table #files(filename sysname NULL)
> declare filecursor cursor for
> select source, destination, environment, [filename]
> from file_path
> open filecursor
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> while (@.@.fetch_status = 0)
> begin
> if @.environementTmp = @.environment
> begin
> set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
> + ' /b'
> insert #files exec master..xp_cmdshell @.source_dir
> if (select filename from #files where filename is not null) =
> @.filename begin
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
> ' +'"'+ rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> end
> else
> begin
> set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
> exist!!!'
> exec master.dbo.xp_sendmail
> @.recipients = 'abc123',
> @.copy_recipients = 'abc123'
> @.subject = @.errortext,
> @.message = @.errortext
> end
> end
> truncate table #files
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> end
> close filecursor
> deallocate filecursor
> GO
>
Something tells me more time is spent executing xp_cmdshell than the
time used for the cursor, so you're probably fine. Cursors are good for
this type of operation IMO since it makes the code relatively clear. You
should always use local, read-only forward-only cursors, for this type
of read-only processing so update your declare statement accordingly.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||We had a similar problem - we solved this by using COM objects.
We created our own custom set of COM objects, but WSH.FileSystemObject does
the same thing.
Essentially:-
Call master.dbo.sp_OACreate to create the com object (returns @.lpObject)
Then create a UDF to do the file exists check
Call master.dbo.sp_OAMethod for file exists passing filename returning file
exists
Then destroy the object using master.dbo.sp_OADestory
Then you'd do
UPDATE
tableWithFileNamesIn
SET
FileExists = dbo.fnFileExists( @.lpObject , FullPathToFile ) AS
FileExists
Assuming: tableWithFileNamesIn( FullPathToFile VARCHAR(260) , FileExists
BIT )
http://msdn.microsoft.com/library/d.../>
sotutor.asp
If the process isn't going to run as system admin, create a role in master
called COMCreator, and add all the sp_OA... (except for OAStop) to that
role and add the process user account to COMCreator.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uB58v9A0FHA.268@.TK2MSFTNGP09.phx.gbl...
> Hello!
> I have a stored proc that checks the file existance in one location and
> copy them to another location. The stored proc reads the record one by
> one and builds the DOS COPY command. In the end, it exccutes the DOS
> command using xp_cmdshell. See below the code.
> The stored proc is working great but I had to use the CURSOR for reading
> the records. I was wondering if I can avoid using it? Is this possible?
> how? Thanks in advance for your help!
> CREATE TABLE [dbo].[FILE_PATH] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Source] [varchar] (150) NULL ,
> [Destination] [char] (150) NULL ,
> [Environment] [char] (25) NULL ,
> [Filename] [varchar] (50) NULL
> ) ON [PRIMARY]
> GO
>
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file1')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file3')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverA\folder1','\\serverD\folder1
','Development',
> 'file2')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverB\folder1','\\serverD\folder2
','UAT', 'file4')
> go
> insert into FILE_PATH(Source, Destination, Environment, [FileName])
> values ('\\serverC\folder1','\\serverD\folder3
','Production', 'file5')
>
> CREATE proc CopyFiles
> as
>
> set nocount on
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> declare @.source_dir varchar(200)
> declare @.filename varchar(30)
> declare @.environment varchar(200)
> declare @.environementTmp varchar(200)
> declare @.errortext varchar(200)
>
> select
> @.environementTmp =
> case @.@.servername
> when 'server A' then 'Development'
> when 'server B' then 'UAT'
> when 'server C' then 'Production'
> end
> create table #files(filename sysname NULL)
> declare filecursor cursor for
> select source, destination, environment, [filename]
> from file_path
> open filecursor
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> while (@.@.fetch_status = 0)
> begin
> if @.environementTmp = @.environment
> begin
> set @.source_dir = 'dir ' + '"'+rtrim(@.source) + rtrim(@.filename)+ '"'
> + ' /b'
> insert #files exec master..xp_cmdshell @.source_dir
> if (select filename from #files where filename is not null) = @.filename
> begin
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) + rtrim(@.filename) +'"'+ '
> ' +'"'+ rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> end
> else
> begin
> set @.errortext = 'The File ' + rtrim(@.filename) + ' does not
> exist!!!'
> exec master.dbo.xp_sendmail
> @.recipients = 'abc123',
> @.copy_recipients = 'abc123'
> @.subject = @.errortext,
> @.message = @.errortext
> end
> end
> truncate table #files
> fetch next from filecursor into @.source, @.destination, @.environment,
> @.filename
> end
> close filecursor
> deallocate filecursor
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***