Script all data of a table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Florian Reischl » Script all data of a table

34 posts, Page 2 of 4

««1 2 3 4 »»»

Script all data of a table

Rate Topic

Display Mode

Topic Options

Author

Message

Florian Reischl

Posted Monday, October 26, 2009 2:57 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

Thanks, Jeff!

I intend to update the script to add some missing data types like HIERARCHYID, UDTs, GEOMETRY and GEOGRAPHY.

Wish you best!
Flo

PS: Sorry for being out of the forums since a long time. I'm quiet busy with new projects. I'll be back as soon as possible!

The more I learn, the more I know what I do not know
Blog: Things about Software Architecture, .NET development and T-SQL

How to Post Data/Code to get the best Help How to Post Performance Problems

Post #808540

Vivdix

Posted Friday, November 06, 2009 6:15 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 17, 2012 3:46 AM
Points: 77, Visits: 1,805

Hi,

I tried this on SQL Server 2000 but getting error on VARCHAR(MAX) line.
Will it not work on SQL Server 2000?

Thanks,
Vivek

Post #814882

Florian Reischl

Posted Friday, November 06, 2009 6:22 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

Hi Vivek

The script was made for SQL Server 2005. However, if you change VARCHAR(MAX) to VARCHAR(8000) it might work, if your tables are not too wide.

In addition change:

SELECT @object_id = object_id, @schema_id = schema_id 
   FROM sys.tables 
   WHERE object_id = OBJECT_ID(@table_name)

... to something like this:

SELECT @object_id = id, @schema_id = schema_id 
   FROM systables 
   WHERE id = OBJECT_ID(@table_name)

Unfortunately I cannot test it on 2000 at the moment.

Hope this helps
Flo

Post #814885

Vivdix

Posted Saturday, November 07, 2009 3:21 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 17, 2012 3:46 AM
Points: 77, Visits: 1,805

Hi Florian,

Still getting few errors -

WHERE TABLE_SCHEMA = SCHEMA_NAME(@schema_id)
'SCHEMA_NAME' is not a recognized function name.

DECLARE @select VARCHAR(MAX)
Line 82: Incorrect syntax near 'MAX'.

Please suggest on this.

Thanks,
Vivek

Post #815373

Vivdix

Posted Saturday, November 07, 2009 4:18 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 17, 2012 3:46 AM
Points: 77, Visits: 1,805

Hi Florian,

Resolve the issue replacing -
DECLARE @select VARCHAR(MAX)
With
DECLARE @select VARCHAR(8000)

&
changing this query
SELECT @object_id = object_id, @schema_id = schema_id
FROM sys.tables
WHERE object_id = OBJECT_ID(@table_name)
to
SELECT @object_id = id, @schema_id = schema_id
FROM sysobjects
WHERE id = OBJECT_ID(@table_name) and type = 'U'

But still getting error at schema_id
Please help on this.

Thanks
Vivek

Post #815385

Florian Reischl

Posted Saturday, November 07, 2009 6:10 AM

SSCrazy

Group: General Forum Members
Last Login: Sunday, November 04, 2012 12:23 PM
Points: 2,087, Visits: 3,932

Hi Vivek

Unfortunately I don't have a SS2k test environment at the moment. I'll try it on 2000 on Monday, when I'm back at work.

Greets
Flo

Post #815393

Vivdix

Posted Tuesday, November 10, 2009 2:08 AM

SSC Journeyman

Group: General Forum Members
Last Login: Saturday, November 17, 2012 3:46 AM
Points: 77, Visits: 1,805

Hi Florian,

Still waiting for your reply, may be you bit busy but please take some time to make it run on MSSQL 2K.

Thanks
Vivek

Post #816351

Jeff Moden

Posted Thursday, November 12, 2009 9:08 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:57 PM
Points: 32,893, Visits: 26,771

vivek-459197 (11/7/2009)

It would be real handy if you'd post the actual error you're getting...

What do you get when you execute the following?
SELECT * FROM dbo.SysObjects

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #818225

MSzI

Posted Friday, December 18, 2009 12:24 AM

Say Hey Kid

Group: General Forum Members
Last Login: Thursday, April 04, 2013 8:11 AM
Points: 669, Visits: 240

Hi Florian,

First of all congratulation, it's a nice work/script, end very useful for many of uss!
One thing that's missing, and I hope you will add to the script is the case of IDETITY column.
Yes I know what to do or, what to change in the destination table to make the script working,
but for many others it will be a huge help to resolve that issue in the script.

bye

Post #836150

hanson.richard

Posted Tuesday, January 05, 2010 1:52 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, February 12, 2010 8:34 AM
Points: 2, Visits: 6

Very nice job! I modified the script a bit to make it a stored proc that spits out the insert statements automatically. I'm always writing scripts such as this or looking for them on the internet to make my job easier - you did a nice job with this one.

Here is the modified script:

CREATE PROC dbo.pScriptTableData
@TableName SYSNAME,
@handle_big_binary BIT = 1,
@column_names BIT = 1
AS
BEGIN
--Converted to Stored Procedure by RHanson 1/5/2010
--EXEC pScriptTableData 'dbo.DimReseller'

/******************************************************************

Author
======
Florian Reischl

Summary
=======
Script to create a SELECT statement to script all data of a specified table

Parameters
==========

@table_name
The name of the table to be scripted

@handle_big_binary
If set to 1 the user defined function udf_varbintohexstr_big will be used
to convert BINARY, VARBINARY and IMAGE data. For futher information see remarks.

@column_names
If set to 0 only the values to be inserted will be scripted; the column names wont.
This saves memory but the destination tables needs exactly the same columns in
same order.
If set to 1 also the names of the columns to insert the values into will be scripted.

Remarks
=======
Attention:
In case of colums of type BINARY, VARBINARY or IMAGE
you either need the user defined function udf_varbintohexstr_big
and option @handle_big_binary set to 1 or you risk a loss of data
if the data of a cell are larger than 3998 bytes

Data type sql_variant is not supported.

History
=======
V01.00.00.00 (2009-01-15)
* Initial release
V01.01.00.00 (2009-01-25)
* Added support for IMAGE columns with user defined function udf_varbintohexstr_big
V01.01.01.00 (2009-02-04)
* Fixed bug for NTEXT and XML
V01.02.00.00 (2009-02-21)
* Added possibility to script column names

******************************************************************/

SET NOCOUNT ON

DECLARE @table_name SYSNAME
--DECLARE @handle_big_binary BIT --Removed by RHanson 1/5/2010 - Variable moved to SP Param
--DECLARE @column_names BIT --Removed by RHanson 1/5/2010 - Variable moved to SP Param
DECLARE @SQLStmt VARCHAR(MAX) --Added by RHanson 1/5/2010

-- ////////////////////
-- -> Configuration
SET @table_name = @TableName
-- <- Configuration
-- ////////////////////

DECLARE @object_id INT
DECLARE @schema_id INT

--SELECT * FROM sys.all_objects
SELECT @object_id = object_id, @schema_id = schema_id
FROM sys.tables
WHERE object_id = OBJECT_ID(@table_name)

DECLARE @columns TABLE (column_name SYSNAME, ordinal_position INT, data_type SYSNAME, data_length INT, is_nullable BIT)

-- Get all column information
INSERT INTO @columns
SELECT column_name, ordinal_position, data_type, character_maximum_length, CASE WHEN is_nullable = 'YES' THEN 1 ELSE 0 END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = SCHEMA_NAME(@schema_id)
AND TABLE_NAME = OBJECT_NAME(@object_id)

DECLARE @select VARCHAR(MAX)
DECLARE @insert VARCHAR(MAX)
DECLARE @crlf CHAR(2)
DECLARE @sql VARCHAR(MAX)
DECLARE @first BIT
DECLARE @pos INT
SET @pos = 1

SET @crlf = CHAR(13) + CHAR(10)

WHILE EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position >= @pos)
BEGIN
DECLARE @column_name SYSNAME
DECLARE @data_type SYSNAME
DECLARE @data_length INT
DECLARE @is_nullable BIT

-- Get information for the current column
SELECT @column_name = column_name, @data_type = data_type, @data_length = data_length, @is_nullable = is_nullable
FROM @columns
WHERE ordinal_position = @pos

-- Create column select information to script the name of the source/destination column if configured
IF (@select IS NULL)
SET @select = ' ''' + QUOTENAME(@column_name)
ELSE
SET @select = @select + ','' + ' + @crlf + ' ''' + QUOTENAME(@column_name)

-- Handle NULL values
SET @sql = ' '
SET @sql = @sql + 'CASE WHEN ' + QUOTENAME(@column_name) + ' IS NULL THEN ''NULL'' ELSE '

-- Handle the different data types
IF (@data_type IN ('bigint', 'bit', 'decimal', 'float', 'int', 'money', 'numeric',
'real', 'smallint', 'smallmoney', 'tinyint'))
BEGIN
SET @sql = @sql + 'CONVERT(VARCHAR(40), ' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type IN ('char', 'nchar', 'nvarchar', 'varchar'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(' + QUOTENAME(@column_name) + ', '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type = 'date')
BEGIN
SET @sql = @sql + '''CONVERT(DATE, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(3), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'time')
BEGIN
SET @sql = @sql + '''CONVERT(TIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(5), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'datetime2')
BEGIN
SET @sql = @sql + '''CONVERT(DATETIME2, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'smalldatetime')
BEGIN
SET @sql = @sql + '''CONVERT(SMALLDATETIME, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(4), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'text')
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(VARCHAR(MAX), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('ntext', 'xml'))
BEGIN
SET @sql = @sql + ''''''''' + REPLACE(CONVERT(NVARCHAR(MAX), ' + QUOTENAME(@column_name) + '), '''''''', '''''''''''') + '''''''''
END
ELSE IF (@data_type IN ('binary', 'varbinary'))
BEGIN
-- Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ' dbo.udf_varbintohexstr_big (' + QUOTENAME(@column_name) + ')'
ELSE
SET @sql = @sql + ' master.sys.fn_varbintohexstr (' + QUOTENAME(@column_name) + ')'
END
ELSE IF (@data_type = 'timestamp')
BEGIN
SET @sql = @sql + '''CONVERT(TIMESTAMP, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(8), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'uniqueidentifier')
BEGIN
SET @sql = @sql + '''CONVERT(UNIQUEIDENTIFIER, '' + master.sys.fn_varbintohexstr (CONVERT(BINARY(16), ' + QUOTENAME(@column_name) + ')) + '')'''
END
ELSE IF (@data_type = 'image')
BEGIN
-- Use udf_varbintohexstr_big if available to avoid cutted binary data
IF (@handle_big_binary = 1)
SET @sql = @sql + ' dbo.udf_varbintohexstr_big (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
ELSE
SET @sql = @sql + ' master.sys.fn_varbintohexstr (CONVERT(VARBINARY(MAX), ' + QUOTENAME(@column_name) + '))'
END
ELSE
BEGIN
PRINT 'ERROR: Not supported data type: ' + @data_type
RETURN
END

SET @sql = @sql + ' END'

-- Script line end for finish or next column
IF EXISTS (SELECT TOP 1 * FROM @columns WHERE ordinal_position > @pos)
SET @sql = @sql + ' + '', '' +'
ELSE
SET @sql = @sql + ' + '

-- Remember the data script
IF (@insert IS NULL)
SET @insert = @sql
ELSE
SET @insert = @insert + @crlf + @sql

SET @pos = @pos + 1
END

-- Close the column names select
SET @select = @select + ''' +'

/* - SECTION REMOVED BY RHanson 1/5/2010 and replaced by automated execution script below
-- Print the INSERT INTO part
PRINT 'SELECT ''INSERT INTO ' + @table_name + ''' + '

-- Print the column names if configured
IF (@column_names = 1)
BEGIN
PRINT ' ''('' + '
PRINT @select
PRINT ' '')'' + '
END

PRINT ' ''VALUES ('' +'

-- Print the data scripting
PRINT @insert

-- Script the end of the statement
PRINT ' '')'''
PRINT ' FROM ' + @table_name
*/

--SECTION ADDED BY RHanson 1/5/2010 to support automated execution of script
-- Print the INSERT INTO part
SET @SQLStmt = 'SELECT ''INSERT INTO ' + @table_name + ''' + '

-- Print the column names if configured
IF (@column_names = 1)
BEGIN
SET @SQLStmt = @SQLStmt + ' ''('' + '
SET @SQLStmt = @SQLStmt + @select
SET @SQLStmt = @SQLStmt + ' '')'' + '
END

SET @SQLStmt = @SQLStmt + ' ''VALUES ('' +'

-- Print the data scripting
SET @SQLStmt = @SQLStmt + @insert

-- Script the end of the statement
SET @SQLStmt = @SQLStmt + ' '')'''
SET @SQLStmt = @SQLStmt + ' FROM ' + @table_name

PRINT @SQLStmt

EXEC(@SQLStmt)
END

Post #842407

« Prev Topic | Next Topic »

34 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions