Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Script all data of a table Expand / Collapse
Author
Message
Posted Monday, October 26, 2009 2:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 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
Posted Friday, November 6, 2009 6:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:59 AM
Points: 77, Visits: 1,831
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
Posted Friday, November 6, 2009 6:22 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 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



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 #814885
Posted Saturday, November 7, 2009 3:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:59 AM
Points: 77, Visits: 1,831
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
Posted Saturday, November 7, 2009 4:18 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:59 AM
Points: 77, Visits: 1,831
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
Posted Saturday, November 7, 2009 6:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 4, 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



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 #815393
Posted Tuesday, November 10, 2009 2:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 30, 2014 7:59 AM
Points: 77, Visits: 1,831
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
Posted Thursday, November 12, 2009 9:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 7:35 PM
Points: 36,784, Visits: 31,242
vivek-459197 (11/7/2009)
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


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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #818225
Posted Friday, December 18, 2009 12:24 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:30 PM
Points: 672, Visits: 243
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
Posted Tuesday, January 5, 2010 1:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase ««1234»»»

Permissions Expand / Collapse