| Author | Ian - Tutorial Posts = 62 |
| 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 create a forum message for this tutorail. |
|
| Copyright@CodingFriends, 2005-2006. All Rights Reserved. | |
| Home | Forums | Tutorials | Users
RSS Feeds - Global
 - Tutorials - Forums - News
|
|
Users
Tutorials
Forum
Projects
Site
Search