Tag: MS SQL

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;

SELECT 'Hello World';