通过脚本克隆创建一些自定义的 SQL 表 [英] To create some customized SQL tables clone by script

问题描述

在 Asp.net 项目中，我需要有一个沙箱环​​境，以便用户能够测试进程而没有任何损害数据库的风险，所以我想制作一个存储过程来创建一种数据库克隆，其中有些表带有数据，而有些表没有任何数据，我认为它应该是一个存储过程，它将表名作为参数和另一个布尔参数，它确定是否应该使用数据或只是表结构进行表的克隆, 如果也考虑 Views 就更好了.

In an Asp.net project I need to have a sandbox environment so that user be able to test the processes without any risk to hurt the database, so I want to make a stored procedure to create a sort of database clone in which some tables come with data and some come without any data, I think it should be a stored procedure which takes the table name as an argument and another Boolean argument which determines if it's supposed to make a clone of table with data or just the table structure, It will be so better if it considers Views too.

我在这个网站上阅读了一些文章和一些问答，比如这个，它创建了一个数据库备份，然后以另一个名称将其恢复为克隆数据库，我什至在 这里 但他们都没有提供我倾向于拥有的这种定制脚本.

I have read some articles and some Q&A in this website like this one that creates a database backup and then restores it by another name as a clone database, I even read about DBCC in here but none of them have offered such a customized script I tend to have.

提前致谢.

推荐答案

试试这个过程.如果您不想将数据移动到其他 'N'，则将表名称传递给变量 @TableName 并将 'Y' 传递给变量 @WithDate.如果您没有指定任何值，则默认为'Y'

Try This Procedure. Pass The Table Name to Variable @TableName and 'Y' to variable @WithDate if you want t move the data else 'N'. If you didn't specify any values, it will be taken as 'Y' by default

CREATE PROCEDURE dbo.sProc_MoveTable
(
    @TableName VARCHAR(255),
    @WithData CHAR(1) = 'Y'
)   
AS
BEGIN

    DECLARE @Sql VARCHAR(MAX)

    SELECT
        @sql = 'SELECT 
                    * 
                    INTO [DestDatabase].[dbo].['+LTRIM(RTRIM(@TableName))+']
                    FROM [SourceDatabase].[dbo].['+LTRIM(RTRIM(@TableName))+'] 
                        WHERE '
                        +CASE @WithData WHEN 'Y'
                            THEN ' 1=1'
                        ELSE ' 1=0 ' END

    PRINT @sql

    EXEC(@sql)

END
GO

这篇关于通过脚本克隆创建一些自定义的 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！