Database 相关的知识 - Update from/List down local files

1.Update from

--reference :http://stackoverflow.com/questions/2334712/update-from-select-using-sql-server

1) Sample 1

        UPDATE foreignmidtidstaging
SET chsupdatestatus = 'N'
,errordescription = @ErrorMessage
FROM foreignmidtidstaging a
INNER JOIN terminalstaging b ON a.jobid = b.jobid
AND a.jobid = @JOBID
AND len(ISNULL(a.CHSUpdateStatus,''))=0

2)Sample 2

             UPDATE [FOREIGNTERMINAL]
SET [ACQUIRERID] = @ACQUIRERID 
,[MERCHANTID] = f.merchantid 
,[lastUpdatedDate] = getdate()     
FROM foreignMIDTIDStaging f
INNER JOIN terminalstaging t ON T.jobid = f.jobid AND .....
AND f.Jobid = @JOBID
AND EXISTS (
SELECT 1
FROM foreignterminal ft
WHERE ft.acquirerid = @ACQUIRERID  AND....
)
 WHERE  FOREIGNTERMINAL.acquirerid = @ACQUIRERID
           AND FOREIGNTERMINAL.merchantid = f.merchantid AND....

NOTE : The where condition as above should not be skipped, otherwise the update maybe wrong. 


2.List down local system files by using xp_cmdshell

CREATE TABLE ##SQLFiles ( SQLFileName VARCHAR(2000))
GO


INSERT INTO ##SQLFiles
EXECUTE master.dbo.xp_cmdshell 'dir /b "D:\DBScripts\20131002\*.sql"'
GO


DECLARE cFiles CURSOR LOCAL FOR
    SELECT DISTINCT [SQLFileName]
    FROM ##SQLFiles
    WHERE [SQLFileName] IS NOT NULL AND
          [SQLFileName] != 'NULL'
    ORDER BY [SQLFileName]


DECLARE @vFileName            VARCHAR(200)
DECLARE @vSQLStmt             VARCHAR(4000)

OPEN cFiles
FETCH NEXT FROM cFiles INTO @vFileName
WHILE @@FETCH_STATUS = 0
BEGIN

     PRINT ' @vFileName ' + @vFileName
     FETCH NEXT FROM cFiles INTO @vFileName
END

CLOSE cFiles
DEALLOCATE cFiles
GO

DROP TABLE ##SQLFiles
GO