sqlserver 导出插入脚本代码

2022-05-24 0 560

当然有其它工具可以做这件事,但如果客户不允许你在服务器乱装东西时这个脚本就会有用了。


复制代码 代码如下:

DECLARE @tbImportTables table(tablename varchar(128), deleted tinyint)

— append tables which you want to import

Insert Into @tbImportTables(tablename, deleted) values(‘tentitytype’, 1)

Insert Into @tbImportTables(tablename, deleted) values(‘tattribute’, 1)

— append all tables

–Insert Into @tbImportTables(tablename, deleted) select table_name, 1 from INFORMATION_SCHEMA.tables where table_type = ‘BASE TABLE’

DECLARE @tbImportScripts table(script varchar(max))

Declare @tablename varchar(128),

@deleted tinyint,

@columnname varchar(128),

@fieldscript varchar(max),

@valuescript varchar(max),

@insertscript varchar(max)

Declare curImportTables Cursor For

Select tablename, deleted

From @tbImportTables

Open curImportTables

Fetch Next From curImportTables Into @tablename, @deleted

WHILE @@Fetch_STATUS = 0

Begin

  If (@deleted = 1)

  begin

    Insert into @tbImportScripts(script) values (‘Truncate table ‘ + @tablename)

  end

  Insert into @tbImportScripts(script) values (‘SET IDENTITY_INSERT ‘ + @tablename + ‘ ON’)

  set @fieldscript = ”

  select @fieldscript = @fieldscript + column_name + ‘,’ from INFORMATION_SCHEMA.columns where table_name = @tablename and data_type not in(‘timestamp’, ‘image’)

  set @fieldscript = substring(@fieldscript, 0, len(@fieldscript))

  set @valuescript = ”

  select @valuescript = @valuescript + ‘case when ‘ + column_name + ‘ is null then ”null” else ”””” + convert(varchar(max), ‘ + column_name + ‘) + ”””” end +”,”+’   from INFORMATION_SCHEMA.columns where table_name = @tablename and data_type not in(‘timestamp’, ‘image’)

  set @valuescript = substring(@valuescript, 0, len(@valuescript) – 4)

  set @insertscript = ‘select ”insert into ‘ + @tablename + ‘(‘ + @fieldscript + ‘) values(‘ + ”’+’ + @valuescript + ‘ + ”)” from ‘ + @tablename

  Insert into @tbImportScripts(script) exec ( @insertscript)

  Insert into @tbImportScripts(script) values (‘SET IDENTITY_INSERT ‘ + @tablename + ‘ OFF’)

  Insert into @tbImportScripts(script) values (‘GO ‘)

  Fetch Next From curImportTables Into @tablename, @deleted

End

Close curImportTables

Deallocate curImportTables

Select * from @tbImportScripts

免责声明:
1、本网站所有发布的源码、软件和资料均为收集各大资源网站整理而来;仅限用于学习和研究目的,您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容。 不得使用于非法商业用途,不得违反国家法律。否则后果自负!

2、本站信息来自网络,版权争议与本站无关。一切关于该资源商业行为与www.niceym.com无关。
如果您喜欢该程序,请支持正版源码、软件,购买注册,得到更好的正版服务。
如有侵犯你版权的,请邮件与我们联系处理(邮箱:skknet@qq.com),本站将立即改正。

NICE源码网 MsSql sqlserver 导出插入脚本代码 https://www.niceym.com/60483.html