Working with SQL Essays

SQL loop

--All tables that need to be backed upDECLARE @fromtbs NVARCHAR( -)='HZ_COM_USER,HZ_BAM_DEPARTMENT,HZ_HR_GZHSFW,HZ_HR_XIAOWEICOMPANY,HZ_COM_PARAMETER,HZ_BAM_POSITION,HZ_BAM_ Dep_target,hz_bom_userall_maincofnig,',@index INT,--', ' where you cut all the tables into a single table@fromtable NVARCHAR( -),--the name of the original table that needs to be backed up@totable NVARCHAR( -),--the name of the new table to be backed up@insertfields NVARCHAR( +)="',--insert time and table name corresponding to str@sql NVARCHAR( the),--SQL statement to execute because the table name is a variable@temptablename NVARCHAR( -)--table name for temporary stitching--looping using the index and SUBSTRING methods--gets the first, where the number is locatedSET @index=(SELECT CHARINDEX(',',@fromtbs))--Start Loop while @index>0BEGIN--truncate the name of the table where the first position is locatedSET @fromtable=(SELECT SUBSTRING(@fromtbs,1,@index-1))--intercept all remaining tables that need to be backed up for Next loop interceptionSET @fromtbs =(SELECT SUBSTRING(@fromtbs,@index+1,LEN(@fromtbs)))--Name the table you want to back upSET @totable=@fromtable+'_bak_'+(SELECT CONVERT(varchar( -),GETDATE(), the))        --If there is a delete does not exist select the original table into backup table    SET @sql = 'IF EXISTS (SELECT * from sys.objects WHERE object_id = object_id (N"'[dbo].'+@totable+" '"+') ) DROP table'+@totable    EXEC(@sql)    SET @sql='SELECT * into'+@totable+'From hrms_pro_20170401.dbo.'+@fromtable+"'    EXEC(@sql)    --Splicing all backup table names for easy insertion into the R_date_and_tablename table    SET @insertfields+=('," "+@totable+" '")SET @index=(SELECT CHARINDEX(',',@fromtbs))END

View Code

Working with SQL Essays