Sql server中用現有表中的資料創建Sql的Insert插入陳述句-有解無憂

之前，在Codeproject發表過一篇關于用現有表中資料創建Insert的Sql陳述句的存盤程序，今天將其搬到這里來，注意本存盤程序僅適用于SQL SERVER，

介紹

一些時候，你想匯出一些現有表中記錄的Sql腳本以便你可以插入這些資料到另一個資料庫的相似表中，有很多方式可以做到，現在，我將跟大家分享一個存盤程序來一起實作它，希望對各位有所幫助，

代碼

首先，請在你的SQL Server資料庫中創建如下名為[dbo].[sp_CreateInsertScript]存盤程序，其內容如下：

--=============================================
-- Author: Mark Kang
-- Company: www.ginkia.com
-- Create date: 2016-03-06
-- Description: Generat the insert sql script according to the data in the specified table.
--              It does not support the columns with timestamp,text,image.
-- Demo : exec sp_CreateInsertScript '[dbo].[Country]','[continent_name]=''North America'''
-- Change History:
--         1.2016-03-06 Created and published
--         2.2016-03-08 Based on Mike's suggestions, I optimized the codes
--         3.2019-03-09 1)Add code lines to avoid error when @con is empty string
--                      2)Based on Lukas Macedo's suggetstions, add surrounding brackets for column name
--                      3)Simplify WHEN...CASE
-- =============================================
CREATE PROC [dbo].[sp_CreateInsertScript] (
    @tablename NVARCHAR(256) -- table name
    ,@con NVARCHAR(400) -- condition to filter data
    ,@ignoreIdentityCol bit=0 --indicate if ignore columne with identity
    ,@isDebug bit=0 --indicate if this is used to debug. when 1,output the internal sql string
    )
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @sqlstr NVARCHAR(MAX);
    DECLARE @valueStr1 NVARCHAR(MAX);
    DECLARE @colsStr NVARCHAR(MAX);
    SELECT @sqlstr='SELECT ''INSERT '+@tablename;
    SELECT @valueStr1='';
    SELECT @colsStr='(';
    SELECT @valueStr1='VALUES (''+';

    IF RTRIM(LTRIM(@con))=''
        SET @con='1=1';

    SELECT @valueStr1=@valueStr1+col+'+'',''+' 
            ,@colsStr=@colsStr+'['+name +'],' 
    FROM (
            SELECT 
                    CASE
                        /* xtype=173 'binary'
                        xtype=165 'varbinary'*/
                        WHEN sc.xtype in (173,165) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR('+CONVERT(NVARCHAR(4),sc.[length]*2+2)+'),['+sc.name +'])'+' END'                        
                        /*xtype=104 'bit'*/
                        WHEN sc.xtype =104 THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(1),['+sc.name +'])'+' END'                        
                        /*xtype=61 'datetime'
                        xtype=58 'smalldatetime'*/
                        WHEN sc.xtype in(58,61) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'''N''''''+'+'CONVERT(NVARCHAR(23),'+sc.name +',121)'+ '+'''''''''+' END'
                        /*xtype=175 'char'
                        xtype=36 'uniqueidentifier'
                        xtype=167 'varchar'
                        xtype=231 'nvarchar'
                        xtype=239 'nchar'*/
                        WHEN sc.xtype in (36,175,167,231,239) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'''N''''''+'+'REPLACE(['+sc.name+'],'''''''','''''''''''')' + '+'''''''''+' END'
                        /*xtype=106 'decimal'
                        xtype=108 'numeric'*/
                        WHEN sc.xtype in(106,108) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR('+CONVERT(NVARCHAR(4),sc.xprec+2)+'),['+sc.name +'])'+' END'
                        /*xtype=59 'real'
                        xtype=62 'float'*/
                        WHEN sc.xtype in (59,62) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(23),'+sc.name +',2)'+' END'
                        /*xtype=48 'tinyint'
                        xtype=52 'smallint'
                        xtype=56 'int'
                        xtype=127 'bigint'
                        xtype=122 'smallmoney'
                        xtype=60 'money'*/
                        WHEN sc.xtype in (48,52,56,127,122,60) THEN 'CASE WHEN ['+sc.name+'] is null THEN ''NULL'' ELSE '+'CONVERT(NVARCHAR(23),['+sc.name +'])'+' END'
                        ELSE '''NULL'''
                    END    AS col
                ,sc.colid
                ,sc.name
            FROM syscolumns AS sc 
            WHERE sc.id = object_id(@tablename) 
            AND sc.xtype <>189 --xtype=189 'timestamp' 
            AND sc.xtype <>34 --xtype=34 'image' 
            AND sc.xtype <>35 --xtype= 35 'text'
            AND (columnproperty(sc.id, sc.name, 'IsIdentity') = 0 OR @ignoreIdentityCol=0)
            ) AS t 
    ORDER BY colid;
        
    SET @colsStr=left(@colsStr,len(@colsStr)-1)+') ';
    SET @valueStr1=left(@valueStr1,len(@valueStr1)-3)+')''';

    SELECT @sqlstr=@sqlstr+@colsStr+@valueStr1+' AS sql FROM '+@tablename +  ' WHERE 1=1 AND  ' + isnull(@con,'1=1');

    IF @isDebug=1
    BEGIN
        PRINT '1.columns string: '+ @colsStr;
        PRINT '2.values string: '+ @valueStr1
        PRINT '3.'+@sqlstr;
    END

    EXEC( @sqlstr);
    SET NOCOUNT OFF
END
GO

示例

下來，我舉一個例子幫大家理解如何使用它，假設在你的資料庫中有個表Country（國家），你想得到這個表中一些資料記錄的用于插入的SQL陳述句，記錄篩選條件是列continent_name（洲名）的值為North America的記錄，表的創建腳本如下：

CREATE TABLE [dbo].[Country](
    [geoname_id] [varchar](50) NULL,
    [locale_code] [varchar](50) NULL,
    [continent_code] [varchar](50) NULL,
    [continent_name] [varchar](50) NULL,
    [country_iso_code] [varchar](50) NULL,
    [country_name] [varchar](50) NULL
) ON [PRIMARY]

下來，通過呼叫你創建的存盤程序，用如下陳述句執行以便產生你想要的SQL的插入（INSERT)陳述句，呼叫執行腳本如下：

exec sp_CreateInsertScript '[dbo].[Country]','[continent_name]=''North America'''

執行之后，你會得到你想要結果，下圖為我電腦的截圖，

現在，你就可以拷貝這些結果或者通過右鍵選單的選項保持輸出結果為一個查詢或者文本檔案，以便你下來使用，Thanks!

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/28254.html

標籤：SQL Server

上一篇：SQL實用技巧：如何分割字串

下一篇：sql 基礎