Script all data out of a single table or all tables in Sql Server 2005/8?

Question

Hey folks,

Simple question here, and I know there are lots of kludgy ways to do it :)

But I was hoping a SQL server expert had a script to easily script all the data out of a table? Or possibly all the tables in a DB?

I'm getting tired of copying and pasting data over RDC! :)

Answer 1

If you are moving data to a differnt database or some sort of file, use SSIS. IF the task is not complex, use the wizard, right click onthe datbasename, go to taks and choose Export data.

If you are doing the whole database an alternative is to simply restore the latest backup in the other location.

Answer 2

use this great script:

http://vyaskn.tripod.com/code.htm#inserts

but this is a bad way to move data, good to help fix a problem or move a little test data etc.

Answer 3

I had a similar requirement.

To script insert statements for each row in a table. Sometimes I needed the Identity column, other times I did not.

So, I wrote this:

CREATE PROCEDURE [dbo].[GenerateInsertScripts] (
    @tableName varchar(100),
    @tableSchema varchar(50) = 'dbo',
    @skipIdentity bit = 1
)
AS
BEGIN
    DECLARE @columnName varchar(800)
    DECLARE @columnType varchar(20)
    DECLARE @statementA varchar(MAX)
    DECLARE @statementB varchar(MAX)
    DECLARE @statement nvarchar(MAX)
    DECLARE @isIdentity bit
    DECLARE @commaFlag bit

    SET @statementA = 'INSERT INTO [' + @tableSchema + '].[' + @tableName + '] ('
    SET @statementB = ''' + '

    DECLARE cols CURSOR FOR
    SELECT
     COLUMN_NAME,
     DATA_TYPE,
     (SELECT COLUMNPROPERTY(OBJECT_ID('[' + @tableSchema + '].[' + @tableName + ']'),
     information_schema.columns.COLUMN_NAME, 'IsIdentity')) AS IsIdentity
    FROM
     information_schema.columns
    WHERE
     TABLE_NAME = @tableName
      AND
     TABLE_SCHEMA = @tableSchema
    ORDER BY
     ORDINAL_POSITION

    OPEN cols
    FETCH cols INTO @columnName, @columnType, @isIdentity 
    SET @commaFlag = 0
    WHILE @@FETCH_STATUS = 0
    BEGIN
     IF NOT (@isIdentity = 1 AND @skipIdentity = 1) BEGIN
     IF @commaFlag = 1 BEGIN
      SET @statementA = @statementA + ', '
      SET @statementB = @statementB + ' + '', '' + '
     END
     SET @commaFlag = 1

     SET @statementA = @statementA + '[' + @columnName + ']'
     SET @statementB = @statementB + 'CASE WHEN [' + @columnName + '] IS NULL THEN ''NULL'' ELSE ' + 
     CASE
      WHEN @columnType = 'bigint' OR @columnType = 'int' OR @columnType = 'tinyint' OR @columnType = 'bit' THEN
       'CAST([' + @columnName + '] AS varchar(MAX))'
      WHEN @columnType = 'datetime' THEN
       ''''''''' + CONVERT(varchar, [' + @columnName + '], 121) + '''''''''
      ELSE
       ''''''''' + REPLACE(CAST([' + @columnName + '] AS varchar(MAX)), '''''''', '''''''''''') + '''''''''
     END

      + ' END'
     END
    FETCH cols INTO @columnName, @columnType, @isIdentity
    END
    SET @commaFlag = 0
    CLOSE cols
    DEALLOCATE cols

    SET @statementB = @statementB + ' + '''

    SET @statement = 'SELECT ''' + @statementA + ') VALUES (' + @statementB + ')'' [Statement] FROM [' + @tableSchema + '].[' + @tableName + ']'

    EXEC sp_executesql @statement
END

Answer 4

For SQL Server 2008 you can use the Script Data option in the Generate Scripts wizard. Not for 2005 though.

You can start the wizard by right clicking on a database in the Object Explorer in SSMS, and then Tasks -> Generate Scripts....

Answer 5

For SQL Server 2005, you can use the free Database Publishing Wizard from Microsoft.