收藏几段SQL Server语句和存储过程

       END

       FETCH NEXT FROM read_cursor INTO @tbl,@fld

END

CLOSE read_cursor

DEALLOCATE read_cursor

GO

SET ANSI_NULLS ON

GO

SET NOCOUNT OFF

GO

select count(*)  from #t

DROP TABLE #t

GO

select count(*)-1  from #tc

select * into ##tx from #tc order by tablename

DROP TABLE #tc

--select * from ##tx

declare @db nvarchar(60),@sql nvarchar(3000)

set @db=db_name()

--请修改用户名和口令 导出到Excel 中

set @sql=''''exec master.dbo.xp_cmdshell ''''''''bcp ..dbo.##tx out c:\''''+@db+''''_exp.xls -w -C936 -Usa -Psa ''''''''''''

print @sql

exec(@sql)

GO

DROP TABLE ##tx

GO

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

--根据表中数据生成insert语句的存储过程

--建立存储过程，执行 spGenInsertSQL 表名

--感谢playyuer

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

CREATE   proc spGenInsertSQL (@tablename varchar(256))

as

begin

  declare @sql varchar(8000)

  declare @sqlValues varchar(8000)

  set @sql ='''' (''''

  set @sqlValues = ''''values (''''''''+''''

  select @sqlValues = @sqlValues + cols + '''' + '''''''','''''''' + '''' ,@sql = @sql + ''''['''' + name + ''''],''''

    from

        (select case

                  when xtype in (48,52,56,59,60,62,104,106,108,122,127)                               

                       then ''''case when ''''+ name +'''' is null then ''''''''NULL'''''''' else '''' + ''''cast(''''+ name + '''' as varchar)''''+'''' end''''

                  when xtype in (58,61)

                       then ''''case when ''''+ name +'''' is null then ''''''''NULL'''''''' else ''''+'''''''''''''''''''''''''''''''''''' + '''' + ''''cast(''''+ name +'''' as varchar)''''+ ''''+''''''''''''''''''''''''''''''''''''+'''' end''''

                 when xtype in (167)

                       then ''''case when ''''+ name +'''' is null then ''''''''NULL'''''''' else ''''+'''''''''''''''''''''''''''''''''''' + '''' + ''''replace(''''+ name+'''','''''''''''''''''''''''''''''''','''''''''''''''''''''''''''''''''''''''''''''''')'''' + ''''+''''''''''''''''''''''''''''''''''''+'''' end''''

                  when xtype in (231)

                       then ''''case when ''''+ name +'''' is null then ''''''''NULL'''''''' else ''''+''''''''''''N'''''''''''''''''''''''' + '''' + ''''replace(''''+ name+'''','''''''''''''''''''''''''''''''','''''''''''''''''''''''''''''''''''''''''''''''')'''' + ''''+''''''''''''''''''''''''''''''''''''+'''' end''''

                  when xtype in (175)

                       then ''''case when ''''+ name +'''' is null then ''''''''NULL'''''''' else ''''+'''''''''''''''''''''''''''''''''''' + '''' + ''''cast(replace(''''+ name+'''','''''''''''''''''''''''''''''''','''''''''''''''''''''''''''''''''''''''''''''''') as Char('''' + cast(length as varchar)  + ''''))+''''''''''''''''''''''''''''''''''''+'''' end''''

                  when xtype in (239)

                       then ''''case when ''''+ name +'''' is null then ''''''''NULL'''''''' else ''''+''''''''''''N'''''''''''''''''''''''' + '''' + ''''cast(replace(''''+ name+'''','''''''''''''''''''''''''''''''','''''''''''''''''''''''''''''''''''''''''''''''') as Char('''' + cast(length as varchar)  + ''''))+''''''''''''''''''''''''''''''''''''+'''' end''''

                  else ''''''''''''NULL''''''''''''

                end as Cols,name

           from syscolumns 

          where id = object_id(@tablename)

        ) T

  set @sql =''''select ''''''''INSERT INTO [''''+ @tablename + '''']'''' + left(@sql,len(@sql)-1)+'''') '''' + left(@sqlValues,len(@sqlValues)-4) + '''')'''''''' from ''''+@tablename

  --print @sql

  exec (@sql)

end

GO

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

--根据表中数据生成insert语句的存储过程

--建立存储过程，执行 proc_insert 表名

--感谢Sky_blue

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

CREATE proc proc_insert (@tablename varchar(256))

as

begin

       set nocount on

       declare @sqlstr varchar(4000)

       declare @sqlstr1 varchar(4000)

       declare @sqlstr2 varchar(4000)

       select @sqlstr=''''select ''''''''insert ''''+@tablename

       select @sqlstr1=''''''''

       select @sqlstr2='''' (''''

       select @sqlstr1= '''' values ( ''''''''+''''

       select @sqlstr1=@sqlstr1+col+''''+'''''''',''''''''+'''' ,@sqlstr2=@sqlstr2+name +'''','''' from (select case

--     when a.xtype =173 then ''''case when ''''+a.name+'''' is null then ''''''''NULL'''''''' else ''''+''''convert(varchar(''''+convert(varchar(4),a.length*2+2)+''''),''''+a.name +'''')''''+'''' end''''

       when a.xtype =104 then ''''case when ''''+a.name+'''' is null then ''''''''NULL'''''''' else ''''+''''convert(varchar(1),''''+a.name +'''')''''+'''' end''''

       when a.xtype =175 then ''''case when ''''+a.name+'''' is null then ''''''''NULL'''''''' else ''''+''''''''''''''''''''''''''''''''''''+''''+''''replace(''''+a.name+'''','''''''''''''''''''''''''''''''','''''''''''''''''''''''''''''''''''''''''''''''')'''' + ''''+''''''''''''''''''''''''''''''''''''+'''' end''''

    &nb

上一页  [1] [2] [3]  下一页

Copyright ＠ 2007-2012 敏韬网(敏而好学，文韬武略--MinTao.Net)（学习笔记） Inc All Rights Reserved.
闵涛 E_mail:admin@mintao.net(欢迎提供学习资源)

鄂公网安备 42011102001154号

站长：MinTao ICP备案号：鄂ICP备11006601号-18