好吧,我想我应该抽出手指给自己写一个轻量级数据生成器:
declare @select varchar(max), @insert varchar(max), @column varchar(100),
@type varchar(100), @identity bit, @db nvarchar(100)
set @db = N'Orders'
set @select = 'select '
set @insert = 'insert into ' + @db + ' ('
declare crD cursor fast_forward for
select column_name, data_type,
COLUMNPROPERTY(
OBJECT_ID(
TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'IsIdentity') AS COLUMN_ID
from Northwind.INFORMATION_SCHEMA.COLUMNS
where table_name = @db
open crD
fetch crD into @column, @type, @identity
while @@fetch_status = 0
begin
if @identity = 0 or @identity is null
begin
set @insert = @insert + @column + ', '
set @select = @select +
case @type
when 'int' then '1'
when 'varchar' then '''test'''
when 'nvarchar' then '''test'''
when 'smalldatetime' then 'getdate()'
when 'bit' then '0'
else 'NULL'
end + ', '
end
fetch crD into @column, @type, @identity
end
set @select = left(@select, len(@select) - 1)
set @insert = left(@insert, len(@insert) - 1) + ')'
exec(@insert + @select)
close crD
deallocate crD
给定任何表,脚本将创建一个记录,其中包含一些任意类型值; int、varchar、nvarchar、smalldatetime 和位。 case 语句可以用函数替换。它不会向下移动依赖项,但会跳过任何种子列。
我创建这个的动机是针对一个包含 50 列的表测试我的 NHibernate 映射文件,因此我需要一个可以重复使用的快速简单脚本。