Grab data from tables

Alter the variable @tableName to the table and also the @tableWhere for the where condition, I found that if you use the standard sql dumps that you was taking allot of other crap with you as well.

The code

declare @colName varchar(100), @colSql varchar(500), @colSQLInsert varchar(500), @type int, @auto int, @tableName varchar(50), @tableWhere varchar(500);
set @tableName = 'tablename';
set @tableWhere = 'the where condition';

-- grab the table column names
declare tablecol cursor for
select name, typestat, autoval from syscolumns where id = (select id from sysobjects where name = @tableName);

--The @auto is the auto generated fields e.g. primary key. 
set @colSql = '';
set @colSQLInsert = '';
open tablecol;
fetch tablecol into @colName, @type, @auto;
while (@@fetch_status = 0)
begin
       if (@auto is null)
       begin
              if (charindex('.',@colName) > 0) set @colName = '['+@colName+']';
              set @colSQL = @colName + ',' +@colSQL;
              if (@type = 2)
                     set @colSQLInsert = '''''''+isnull(' + @colName + ',0)+'''''',' + @colSQLInsert;
              else
                     set @colSQLInsert = '''+str(isnull(' + @colName + ',''''))+'',' + @colSQLInsert;
       end
       fetch next from tablecol into @colName, @type, @auto;
end
close tablecol;
deallocate tablecol;
-- to build the sql statement, since it stops at 255 charactes split the outputs
select 'select (''insert into '+@tableName+' (';
declare @loopingVal int;
set @loopingVal =0;
while (len(@colSQL) > @loopingVal)
begin
       select substring(@colSQL, @loopingVal, 255);
       set @loopingVal = @loopingVal + 255;
end
select ') values (';

set @loopingVal =0;
while (len(@colSQLInsert) > @loopingVal)
begin
       select substring(@colSQLInsert, @loopingVal, 255);
       set @loopingVal = @loopingVal + 255;
end
select ')'') from '+@tableName + ' ' + @tableWhere;

The output will be x lines, and if you just copy them into a single line and this will display (once executed) the insert lines.

If anyone has any better methods, please comment 🙂

Leave a Reply

Your email address will not be published.