Though you can generate INSERT statements using SQL Server Management Services if the Generate Scripts functionality is enabled, this script may help in cases when it is not.
USE DatabaseName GO SET NOCOUNT ON DECLARE @TableName VARCHAR(255) DECLARE @IndividualInserts BIT SET @TableName = 'TableName' SET @IndividualInserts = 0 DECLARE @TableStructure TABLE (ColumnName VARCHAR(255), DataType VARCHAR(50), [MaxLength] INT, [precision] INT, [scale] INT, is_nullable BIT, PrimaryKey BIT, ColumnID INT) DECLARE @ColumnName VARCHAR(255) DECLARE @DataType VARCHAR(50) DECLARE @MaxLength INT DECLARE @precision INT DECLARE @scale INT DECLARE @is_nullable BIT DECLARE @PrimaryKey BIT DECLARE @ColumnID INT DECLARE @SelectQuery VARCHAR(MAX) = '' DECLARE @ResultQuery VARCHAR(MAX) = '' DECLARE @SQL NVARCHAR(MAX) = '' DECLARE @CrLf VARCHAR(10) = CHAR(13) + CHAR(10) INSERT INTO @TableStructure SELECT c.name 'Column Name', t.Name 'Data type', c.max_length 'Max Length', c.precision , c.scale , c.is_nullable, ISNULL(i.is_primary_key, 0) 'Primary Key', c.column_id FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id WHERE c.object_id = OBJECT_ID(@TableName) ORDER BY c.column_id --SELECT * FROM @TableStructure DECLARE TableCursor CURSOR FOR SELECT * FROM @TableStructure ORDER BY ColumnID OPEN TableCursor FETCH NEXT FROM TableCursor INTO @ColumnName, @DataType, @MaxLength, @precision, @scale, @is_nullable, @PrimaryKey, @ColumnID WHILE @@FETCH_STATUS = 0 BEGIN --SELECT @ColumnName, @DataType, @MaxLength, @precision, @scale, @is_nullable, @PrimaryKey, @ColumnID SET @ColumnName = '[' + @ColumnName + ']' IF LEN(@SelectQuery)=0 SET @SelectQuery='SELECT' + @CrLf + '''INSERT INTO ' + @TableName + ' (' ELSE SET @SelectQuery = @SelectQuery + ',' --@CrLf + ',' SET @SelectQuery = @SelectQuery + @ColumnName -- + ' -- ' + @DataType IF LEN(@ResultQuery)=0 BEGIN IF @IndividualInserts=0 SET @ResultQuery = 'UNION ALL SELECT CASE WHEN ROW_NUMBER() OVER(ORDER BY ' + @ColumnName + ') <> 1 THEN '','' ELSE '''' END +' + @CrLf + '''(' ELSE SET @ResultQuery= @CrLf + '(' END ELSE SET @ResultQuery = @ResultQuery + ',' SELECT @ResultQuery = CASE WHEN @DataType LIKE '%CHAR' THEN @ResultQuery + '''+ COALESCE('''''''' + ' + @ColumnName + ' + '''''''',''NULL'') +''' WHEN @DataType LIKE '%DATETIME' THEN @ResultQuery + '''+ COALESCE(''CONVERT(DATETIME,'''''' + CONVERT(VARCHAR,' + @ColumnName + ',21) + '''''',21)'',''NULL'') +''' WHEN @DataType LIKE 'NUMERIC%' THEN @ResultQuery + '''+ COALESCE(CONVERT(VARCHAR,' + @ColumnName + '),''NULL'') +''' WHEN @DataType = 'TEXT' THEN @ResultQuery + '''+ COALESCE('''''''' + CONVERT(VARCHAR,' + @ColumnName + ') + '''''''',''NULL'') +''' WHEN @DataType LIKE '%INT' THEN @ResultQuery + '''+ COALESCE(CONVERT(VARCHAR,' + @ColumnName + '),''NULL'') +''' WHEN @DataType = 'FLOAT' THEN @ResultQuery + '''+ COALESCE(CONVERT(VARCHAR,' + @ColumnName + '),''NULL'') +''' ELSE @ResultQuery + '''+ COALESCE(' + @ColumnName + ',''NULL'') +''' END --SET @ResultQuery = @ResultQuery + ' -- ' + @DataType FETCH NEXT FROM TableCursor INTO @ColumnName, @DataType, @MaxLength, @precision, @scale, @is_nullable, @PrimaryKey, @ColumnID END CLOSE TableCursor DEALLOCATE TableCursor SET @SelectQuery = @SelectQuery + ') VALUES' IF @IndividualInserts=0 SET @SelectQuery = @SelectQuery + '''' SET @ResultQuery = @ResultQuery + ')'' FROM ' + @TableName + ';' SET @SQL = @SelectQuery + @ResultQuery --SELECT @SelectQuery --SELECT @ResultQuery --SELECT @SQL --PRINT @SelectQuery --PRINT @ResultQuery EXEC sp_executesql @SQL
This script may not work in every single case, as there are data types not accounted for here.
You must log in to post a comment.