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 🙂