Coding Friends

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. </b>
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</b>
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;