回転:役に立つSQLコード

テーブルのデータからSQLスクリプトを生成し、クエリー・アナライザでこれらのスクリプトを実行した後、自動的にSQL Serverにデータをインポートする機能です.

-- =============================================

-- Author:        

-- Description:          Insert    

--                      

-- =============================================

CREATE PROCEDURE  sp_ConvertDataToInsertSql

    @tablename   sysname   -- source table name

AS

BEGIN

    DECLARE   @column   varchar(1000)

    DECLARE   @columndata   varchar(1000)

    DECLARE   @sql   varchar(4000)

    DECLARE   @xtype   tinyint

    DECLARE   @name   sysname

    DECLARE   @objectId   int

    DECLARE   @objectname   sysname

    DECLARE   @ident   int



    SET NOCOUNT ON



    SET @objectId=object_id(@tablename)

    --         

    IF @objectId IS NULL

    BEGIN

        print  @tablename +  '     '

        RETURN

    END



    SET @objectname=RTRIM(object_name(@objectId))

    IF @objectname IS NULL OR CHARINDEX(@objectname,@tablename) = 0

    BEGIN

        print  @tablename +  '          '     

        RETURN

    END

    --        

    IF OBJECTPROPERTY(@objectId,'IsTable') <> 1

    BEGIN

        print @tablename + '     '

        RETURN

    END



    SELECT @ident=status&0x80

      FROM syscolumns

     WHERE id=@objectid

       AND status&0x80=0x80

    IF @ident IS NOT NULL

        PRINT 'SET   IDENTITY_INSERT   '+ @TableName + '   ON'



    --    ，        Insert  

    DECLARE syscolumns_cursor CURSOR FOR

        SELECT c.name,c.xtype FROM syscolumns c

         WHERE c.id=@objectid

         ORDER BY c.colid

    --    

    open syscolumns_cursor

    SET @column=''

    SET @columndata=''

    FETCH NEXT FROM syscolumns_cursor INTO @name, @xtype

    WHILE @@fetch_status <> -1

    BEGIN

        IF @@fetch_status <> -2

        BEGIN 

            --timestamp    ，image,text,ntext,sql_variant      

            IF @xtype NOT IN(189,34,35,99,98)

                BEGIN

                SET @column=@column +

                    CASE WHEN len(@column)=0 THEN '' ELSE ',' END + @name

                SET @columndata = @columndata +

                    CASE WHEN len(@columndata)=0 THEN '' ELSE ','','',' END +

                    CASE WHEN @xtype IN(167,175) THEN '''''''''+' + @name+'+'''''''''   --varchar,char

                         WHEN @xtype IN(231,239) THEN '''N''''''+' + @name+'+'''''''''   --nvarchar,nchar

                         WHEN @xtype=61 THEN '''''''''+convert(char(23),' + @name+',121)+'''''''''   --datetime

                         WHEN @xtype=58 THEN '''''''''+convert(char(16),' + @name+',120)+'''''''''   --smalldatetime

                         WHEN @xtype=36 THEN '''''''''+convert(char(36),' + @name+')+'''''''''   --uniqueidentifier

                         ELSE @name

                END

            END

        END

        FETCH NEXT FROM syscolumns_cursor INTO @name,@xtype

    END

    --     

    CLOSE   syscolumns_cursor

    DEALLOCATE   syscolumns_cursor



    SET @sql = 'SET NOCOUNT ON SELECT ''INSERT '

        + @tablename + '(' + @column + ') VALUES(''as ''--'','

        + @columndata + ','')'' FROM ' + @tablename

    PRINT '--'+@sql



    EXEC(@sql)

    IF @ident IS NOT NULL

        PRINT 'SET IDENTITY_INSERT '+@TableName+' OFF'

END

 
ストアド・プロシージャの呼び出し:
 

EXEC sp_ConvertDataToInsertSql 'persons' 

 
結果セットは次のようになります.
 

INSERT persons(username,lastname,firstname,city,id) VALUES('Bill','Bill','Zheng','Xuzhou',1)