请帮忙看下这句存储过程原理是什么,急啊
CREATE PROCEDURE spu_GenerateInsert @Table varchar(128)
AS
DECLARE @InsertStmt varchar(8000),
@Fields varchar(8000),
@SelList varchar(8000),
@Data varchar(8000),
@ColName varchar(128),
@IsChar tinyint,
@FldCounter int,
@TableData varchar(8000) SELECT @InsertStmt = 'INSERT INTO ' + @Table + ' (',
@Fields = '',
@Data = '',
@SelList = 'SELECT ',
@FldCounter = 0
DECLARE CR_Table CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME,
'IsChar' = CASE
WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint') THEN
0
WHEN DATA_TYPE in ('char', 'varchar' ) THEN 1
WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2
ELSE 9 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Table
AND DATA_TYPE <> 'timestamp'
ORDER BY ORDINAL_POSITION
FOR READ ONLY
OPEN CR_Table
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @FldCounter = 0
BEGIN
SELECT @Fields = @Fields + @ColName + ', '
SELECT @SelList = CASE
WHEN @IsChar = 1 THEN @SelList + ' '''''''' + ISNULL( REPLACE('+ @ColName
+ ', '''''''', ''''''''''''),'''') + '''''','' +' + ' '
WHEN @IsChar = 2 THEN @SelList + ' '''''''' + ISNULL(CONVERT(varchar(20),'
+ @ColName + '),''12/30/1899'') + '''''''' +' + ' '
ELSE @SelList + 'ISNULL(CONVERT(varchar(2000),'+@ColName + '),0)' + '+
'''' + ' END
SELECT @FldCounter = @FldCounter + 1
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END
SELECT @Fields = @Fields + @ColName + ', '
SELECT @SelList = CASE
WHEN @IsChar = 1 THEN @SelList + ' '','''''' + ISNULL(REPLACE(' + @ColName
+ ', '''''''',''''''''''''), '''') + '''''''' +' + ' '
WHEN @IsChar = 2 THEN @SelList + ' '','''''' + ISNULL(CONVERT(varchar(20),'
+ @ColName + '),''12/30/1899'') + '''''''' +' + ' '
ELSE @SelList + ' '','' + ISNULL(CONVERT(varchar(2000),'+@ColName +
'),0)' + '+' END
END
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END
CLOSE CR_Table
DEALLOCATE CR_Table
SELECT @Fields = SUBSTRING(@Fields, 1,(len(@Fields)-1))
--SELECT @Fields AS TheFields
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1))
SELECT @SelList = @SelList + ' FROM ' + @Table
--SELECT @SelList AS TheSelList
SELECT @InsertStmt = @InsertStmt + @Fields + ')'
--SELECT @InsertStmt AS TheInsertStmt
--RETURN (0)
--go
--exec spu_GenerateInsert 'Art'
--for debugging...
--exec (@SelList)
--PRINT @SelList
CREATE TABLE #TheData (TableData varchar(8000))
INSERT INTO #TheData (TableData) EXEC (@SelList)
--Cursor through the data to generate the INSERT statement / VALUES clause
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @TableData
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT @InsertStmt
PRINT ' VALUES (' + @TableData + ')' + char(13) + 'GO'
END
FETCH NEXT FROM CR_Data INTO @TableData
END
CLOSE CR_Data
DEALLOCATE CR_Data
RETURN (0) GO
CREATE PROCEDURE spu_GenerateInsert @Table varchar(128)
AS
DECLARE @InsertStmt varchar(8000),
@Fields varchar(8000),
@SelList varchar(8000),
@Data varchar(8000),
@ColName varchar(128),
@IsChar tinyint,
@FldCounter int,
@TableData varchar(8000) SELECT @InsertStmt = 'INSERT INTO ' + @Table + ' (',
@Fields = '',
@Data = '',
@SelList = 'SELECT ',
@FldCounter = 0
DECLARE CR_Table CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME,
'IsChar' = CASE
WHEN DATA_TYPE in ('int', 'money', 'decimal', 'tinyint', 'smallint') THEN
0
WHEN DATA_TYPE in ('char', 'varchar' ) THEN 1
WHEN DATA_TYPE in ('datetime', 'smalldatetime') THEN 2
ELSE 9 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @Table
AND DATA_TYPE <> 'timestamp'
ORDER BY ORDINAL_POSITION
FOR READ ONLY
OPEN CR_Table
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @FldCounter = 0
BEGIN
SELECT @Fields = @Fields + @ColName + ', '
SELECT @SelList = CASE
WHEN @IsChar = 1 THEN @SelList + ' '''''''' + ISNULL( REPLACE('+ @ColName
+ ', '''''''', ''''''''''''),'''') + '''''','' +' + ' '
WHEN @IsChar = 2 THEN @SelList + ' '''''''' + ISNULL(CONVERT(varchar(20),'
+ @ColName + '),''12/30/1899'') + '''''''' +' + ' '
ELSE @SelList + 'ISNULL(CONVERT(varchar(2000),'+@ColName + '),0)' + '+
'''' + ' END
SELECT @FldCounter = @FldCounter + 1
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END
SELECT @Fields = @Fields + @ColName + ', '
SELECT @SelList = CASE
WHEN @IsChar = 1 THEN @SelList + ' '','''''' + ISNULL(REPLACE(' + @ColName
+ ', '''''''',''''''''''''), '''') + '''''''' +' + ' '
WHEN @IsChar = 2 THEN @SelList + ' '','''''' + ISNULL(CONVERT(varchar(20),'
+ @ColName + '),''12/30/1899'') + '''''''' +' + ' '
ELSE @SelList + ' '','' + ISNULL(CONVERT(varchar(2000),'+@ColName +
'),0)' + '+' END
END
FETCH NEXT FROM CR_Table INTO @ColName, @IsChar
END
CLOSE CR_Table
DEALLOCATE CR_Table
SELECT @Fields = SUBSTRING(@Fields, 1,(len(@Fields)-1))
--SELECT @Fields AS TheFields
SELECT @SelList = SUBSTRING(@SelList, 1,(len(@SelList)-1))
SELECT @SelList = @SelList + ' FROM ' + @Table
--SELECT @SelList AS TheSelList
SELECT @InsertStmt = @InsertStmt + @Fields + ')'
--SELECT @InsertStmt AS TheInsertStmt
--RETURN (0)
--go
--exec spu_GenerateInsert 'Art'
--for debugging...
--exec (@SelList)
--PRINT @SelList
CREATE TABLE #TheData (TableData varchar(8000))
INSERT INTO #TheData (TableData) EXEC (@SelList)
--Cursor through the data to generate the INSERT statement / VALUES clause
DECLARE CR_Data CURSOR FAST_FORWARD FOR SELECT TableData FROM #TheData FOR
READ ONLY
OPEN CR_Data
FETCH NEXT FROM CR_Data INTO @TableData
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT @InsertStmt
PRINT ' VALUES (' + @TableData + ')' + char(13) + 'GO'
END
FETCH NEXT FROM CR_Data INTO @TableData
END
CLOSE CR_Data
DEALLOCATE CR_Data
RETURN (0) GO
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货