In the process of maintaining SQL Server database, do you often encounter thousands of similar log20050901 log table, each table data is not many, one open to see very inconvenient, or sometimes we need to summarize the data in these tables, A single open operation is also very troublesome. The following describes an automated method for merging tables.
My idea is to create a user stored procedure to complete a series of automated operations, and here's the code.
--The stored procedure I named Backupdata, I can use my own defined name.
--the name of the target table generated by the parameter 1:@tabletarget
--Parameter 2:@tablestart the name of the table at which the merge begins
--Parameter 3:@tableend the name of the table at the end of the merge
CREATE PROCEDURE backupdata @TableTarget sysname, @TableStart sysname, @TableEnd sysname
As
DECLARE tnames_cursor Cursor
For
SELECT table_name
From INFORMATION_SCHEMA. TABLES
OPEN Tnames_cursor
DECLARE @TableName sysname
DECLARE @TablePref sysname
DECLARE @IsTargetExist Integer
--Determine if the target table exists
SET @IsTargetExist = (SELECT count (table_name) from INFORMATION_SCHEMA. TABLES WHERE table_name = @TableTarget)
--Create a new table if the target table does not exist
IF @istargetexist =0
BEGIN
The statements in--exec can be replaced with table scripts written by SQL Server. Note You cannot have fields in the target table that have the same AutoNumber type as the names that you want to merge in the table.
EXEC (' CREATE TABLE [dbo].[' @TableTarget ']
(
[LOG1] [nvarchar] (Ten) COLLATE chinese_prc_ci_as NULL,
[LOG2] [nvarchar] (Ten) COLLATE chinese_prc_ci_as NULL,
......
)')
END
FETCH NEXT from Tnames_cursor to @TableName
while (@ @FETCH_STATUS <>-1)
BEGIN
IF (@ @FETCH_STATUS <>-2)
BEGIN
SELECT @TableName = RTRIM (@TableName)
--The following two lines change depending on the name of the log table
--Take the first 3 bits of the log table name as the identity
SELECT @TablePref = Left (@TableName, 3)
--To determine if the table name meets the requirements
IF (@TablePref = ' log ') and (@TableName >= @TableStart) and (@TableName <= @TableEnd)
--Start Import
http://www.bkjia.com/PHPjc/631117.html www.bkjia.com true http://www.bkjia.com/PHPjc/631117.html techarticle in the process of maintaining SQL Server database, do you often encounter thousands of similar log20050901 this log table, each table data are not many, one open to see ...
