Tuesday, 22 September 2009

SQL Test Data Generator

Great script for generating test data in a SQL Server table:

declare @select varchar(max), @insert varchar(max), @column varchar(100),
    @type varchar(100), @identity bit, @db nvarchar(100)

set @db = N'aqs_Lead'
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 AQSProd2.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'
            when 'decimal' 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

Courtesy of Keith Bloom: http://stackoverflow.com/questions/1118960/create-test-data-in-sql-server

Tuesday, 8 September 2009

ThreadPool threads

Note to self:  When debugging inside Thread Pool threads, the debugger may miss out lines and exhibit erratic behaviour, which may drive you insane.  Best thing to do is use your own Thread for all but the tiniest operations – or change back to a thread pool thread after debugging.