how to take script for database table data

  • hi,

    I have database and datables with full of data.I need script with data.

    I need script file for datatables data(not backup)..

    how can i take script to database table data.

    Thanks
    Dastagiri.D

  • Hello,

    You could accomplish this with a third party tool such Redgate’s Data Compare:-

    1) Script the DB Objects.

    2) Create an empty DB from this script.

    3) Compare the original DB to the new empty one and use the tool’s wizard to build a script to synchronise the data.

    Regards,

    John Marsh

    www.sql.lu
    SQL Server Luxembourg User Group

  • dastagirid

    i am assuming from your post that you want a script to generate an insert statement for each row in a table, is that correct? If so, take the following scenario

    table structure is

    CREATE TABLE [dbo].[aspnet_Users](

    [ApplicationId] [uniqueidentifier] NOT NULL,

    [UserId] [uniqueidentifier] NOT NULL DEFAULT (newid()),

    [UserName] [nvarchar](256) NOT NULL,

    [LoweredUserName] [nvarchar](256) NOT NULL,

    [MobileAlias] [nvarchar](16) NULL DEFAULT (NULL),

    [IsAnonymous] [bit] NOT NULL DEFAULT ((0)),

    [LastActivityDate] [datetime] NOT NULL

    )

    i have 2 sample rows in this table as follows

    ac2c83f9-7e04-4896-a91d-81e6b366284f f51699ad-5cc0-4f65-ac3c-a1b39a895833 Admin admin NULL False 10/02/2009 21:21:29

    ac2c83f9-7e04-4896-a91d-81e6b366284f ab6ca125-a595-4851-894f-9d949371eeb9 user user NULL False 10/02/2009 21:18:54

    to generate an insert statement for each row for the columns username, loweredusername, mobilealias, isanonymous and lastactivitydate you would use something along the lines of the following

    select 'INSERT INTO NewTable (Username, LoweredUserName, MobileAlias, IsAnonymous, lastActivityDate)

    VALUES (''' + username + ''', ''' + LoweredUserName + ''', ''' +

    isnull(MobileAlias, '') + ''', ' +

    cast(IsAnonymous as nvarchar(2)) + ', ''' + cast(LastActivityDate as nvarchar(12)) + ''')' AS InsertString

    from dbo.aspnet_Users

    which would produce the following

    INSERT INTO NewTable (Username, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate) VALUES ('Admin', 'admin', ' ', '0', 'Feb 10 2009')

    INSERT INTO NewTable (Username, LoweredUserName, MobileAlias, IsAnonymous, LastActivityDate) VALUES ('user', 'user', ' ', '0', 'Feb 10 2009')

    notice you will have to cast or convert non char items to generate the insert strings and also account for NULL values

    PS this would probably have been better posted in the T-SQL forum 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Thank you for this ..

    but any automatic option in sql server 2005 to take script with data.

    Thanks
    Dastagiri.D

  • Hi,

    If this helps - you can script the database items using the Generate Scripts option for your selected database.

    [p]

    1. Right click on your database in SSMS, select Tasks and then Generate Scripts.

    2. Select the "Specific database option" for scripting.

    3. Select your object viz Tables , Views, Stored Procedures, UDF et al.

    4. Importantly in the next step of the wizard click the Advanced Button.

    5. Change the "Script for server version" in accordance to your needs.

    6. Change the "Types of data to script" to Schema and Data or select as required.

    7. Have a look through all the other options if required.

    8. Save the results in a file or clipboard or New query.

    9. Review your selections &

    10. You will have your script ready.

    [/p]

    Hope this helps.

    🙂

    Mehernosh.

  • Mehernosh (8/8/2013)


    Hi,

    If this helps - you can script the database items using the Generate Scripts option for your selected database.

    [p]

    1. Right click on your database in SSMS, select Tasks and then Generate Scripts.

    2. Select the "Specific database option" for scripting.

    3. Select your object viz Tables , Views, Stored Procedures, UDF et al.

    4. Importantly in the next step of the wizard click the Advanced Button.

    5. Change the "Script for server version" in accordance to your needs.

    6. Change the "Types of data to script" to Schema and Data or select as required.

    7. Have a look through all the other options if required.

    8. Save the results in a file or clipboard or New query.

    9. Review your selections &

    10. You will have your script ready.

    [/p]

    Hope this helps.

    🙂

    Hopw this is not available in SQL 2005.

    Regards
    Durai Nagarajan

  • Long time ago I found this script on the internet. I don't know where, so I can't give credit to the creator. This script has served me well in creating INSERT statements.

    DECLARE@table_name varchar(776)

    DECLARE@schema varchar(64)

    -- enter a schema name below if the table is not in the default one

    --SET @schema = 'schema_name'

    SET @table_name = 'table_name'

    DECLARE@target_table varchar(776)

    DECLARE@include_column_list bit

    DECLARE@from varchar(800)

    DECLARE@include_timestamp bit

    DECLARE@debug_mode bit

    DECLARE@ommit_images bit

    DECLARE@ommit_identity bit

    DECLARE@top int

    DECLARE@cols_to_include varchar(8000)

    DECLARE@cols_to_exclude varchar(8000)

    DECLARE@disable_constraints bit

    DECLARE@ommit_computed_cols bit

    SET @include_column_list = 1

    SET NOCOUNT ON

    --Making sure user only uses either @cols_to_include or @cols_to_exclude

    IF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL))

    BEGIN

    RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1)

    END

    --Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper format

    IF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0))

    BEGIN

    RAISERROR('Invalid use of @cols_to_include property',16,1)

    PRINT 'Specify column names surrounded by single quotes and separated by commas'

    PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"'

    END

    IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0))

    BEGIN

    RAISERROR('Invalid use of @cols_to_exclude property',16,1)

    PRINT 'Specify column names surrounded by single quotes and separated by commas'

    PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"'

    END

    --Checking to see if the database name is specified along wih the table name

    --Your database context should be local to the table for which you want to generate INSERT statements

    --specifying the database name is not allowed

    IF (PARSENAME(@table_name,3)) IS NOT NULL

    BEGIN

    RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1)

    END

    --Checking for the existence of 'user table' or 'view'

    --This procedure is not written to work on system tables

    --To script the data in system tables, just create a view on the system tables and script the view instead

    IF @schema IS NULL

    BEGIN

    IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL))

    BEGIN

    RAISERROR('User table or view not found.',16,1)

    PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @schema parameter to specify the owner name.'

    PRINT 'Make sure you have SELECT permission on that table or view.'

    END

    END

    ELSE

    BEGIN

    IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @schema)

    BEGIN

    RAISERROR('User table or view not found.',16,1)

    PRINT 'You may see this error, if you are not the owner of this table. In that case use @schema parameter to specify the owner name.'

    PRINT 'Make sure you have SELECT permission on that table or view.'

    END

    END

    --Variable declarations

    DECLARE@Column_ID int,

    @Column_List varchar(8000),

    @Column_Name varchar(128),

    @Start_Insert varchar(max),

    @Data_Type varchar(128),

    @Actual_Values varchar(8000),--This is the string that will be finally executed to generate INSERT statements

    @IDN varchar(128)--Will contain the IDENTITY column's name in the table

    --Variable Initialization

    SET @IDN = ''

    SET @Column_ID = 0

    SET @Column_Name = ''

    SET @Column_List = ''

    SET @Actual_Values = ''

    IF @schema IS NULL

    BEGIN

    SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'

    END

    ELSE

    BEGIN

    SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@schema)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']'

    END

    --To get the first column's ID

    SELECT@Column_ID = MIN(ORDINAL_POSITION)

    FROMINFORMATION_SCHEMA.COLUMNS (NOLOCK)

    WHERE TABLE_NAME = @table_name AND

    (@schema IS NULL OR TABLE_SCHEMA = @schema)

    --Loop through all the columns of the table, to get the column names and their data types

    WHILE @Column_ID IS NOT NULL

    BEGIN

    SELECT @Column_Name = QUOTENAME(COLUMN_NAME),

    @Data_Type = DATA_TYPE

    FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)

    WHERE ORDINAL_POSITION = @Column_ID AND

    TABLE_NAME = @table_name AND

    (@schema IS NULL OR TABLE_SCHEMA = @schema)

    IF @cols_to_include IS NOT NULL --Selecting only user specified columns

    BEGIN

    IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0

    BEGIN

    GOTO SKIP_LOOP

    END

    END

    IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns

    BEGIN

    IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0

    BEGIN

    GOTO SKIP_LOOP

    END

    END

    --Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column

    IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@schema,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1

    BEGIN

    IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column

    SET @IDN = @Column_Name

    ELSE

    GOTO SKIP_LOOP

    END

    --Making sure whether to output computed columns or not

    IF @ommit_computed_cols = 1

    BEGIN

    IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@schema,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1

    BEGIN

    GOTO SKIP_LOOP

    END

    END

    --Tables with columns of IMAGE data type are not supported for obvious reasons

    IF(@Data_Type in ('image'))

    BEGIN

    IF (@ommit_images = 0)

    BEGIN

    RAISERROR('Tables with image columns are not supported.',16,1)

    PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.'

    PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.'

    END

    ELSE

    BEGIN

    GOTO SKIP_LOOP

    END

    END

    --Determining the data type of the column and depending on the data type, the VALUES part of

    --the INSERT statement is generated. Care is taken to handle columns with NULL values. Also

    --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns

    SET @Actual_Values = @Actual_Values +

    CASE

    WHEN @Data_Type IN ('char','varchar','nchar','nvarchar')

    THEN

    'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'

    WHEN @Data_Type IN ('datetime','smalldatetime')

    THEN

    'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')'

    WHEN @Data_Type IN ('uniqueidentifier')

    THEN

    'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')'

    WHEN @Data_Type IN ('text','ntext')

    THEN

    'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')'

    WHEN @Data_Type IN ('binary','varbinary')

    THEN

    'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'

    WHEN @Data_Type IN ('timestamp','rowversion')

    THEN

    CASE

    WHEN @include_timestamp = 0

    THEN

    '''DEFAULT'''

    ELSE

    'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')'

    END

    WHEN @Data_Type IN ('float','real','money','smallmoney')

    THEN

    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')'

    ELSE

    'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')'

    END + '+' + ''',''' + ' + '

    --Generating the column list for the INSERT statement

    SET @Column_List = @Column_List + @Column_Name + ','

    SKIP_LOOP: --The label used in GOTO

    SELECT @Column_ID = MIN(ORDINAL_POSITION)

    FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)

    WHERE TABLE_NAME = @table_name AND

    ORDINAL_POSITION > @Column_ID AND

    (@schema IS NULL OR TABLE_SCHEMA = @schema)

    --Loop ends here!

    END

    --To get rid of the extra characters that got concatenated during the last run through the loop

    SET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)

    SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)

    IF LTRIM(@Column_List) = ''

    BEGIN

    RAISERROR('No columns to select. There should at least be one column to generate the output',16,1)

    END

    --Forming the final string that will be executed, to output the INSERT statements

    IF (@include_column_list <> 0)

    BEGIN

    SET @Actual_Values =

    'SELECT ' +

    CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +

    '''' + RTRIM(@Start_Insert) +

    ' ''+' + '''(' + RTRIM(@Column_List) + '''+' + ''')''' +

    ' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' +

    COALESCE(@from,' FROM ' + CASE WHEN @schema IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@schema)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')

    END

    ELSE IF (@include_column_list = 0)

    BEGIN

    SET @Actual_Values =

    'SELECT ' +

    CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +

    '''' + RTRIM(@Start_Insert) +

    ' '' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' +

    COALESCE(@from,' FROM ' + CASE WHEN @schema IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@schema)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)')

    END

    --Determining whether to ouput any debug information

    IF @debug_mode =1

    BEGIN

    PRINT '/*****START OF DEBUG INFORMATION*****'

    PRINT 'Beginning of the INSERT statement:'

    PRINT @Start_Insert

    PRINT ''

    PRINT 'The column list:'

    PRINT @Column_List

    PRINT ''

    PRINT 'The SELECT statement executed to generate the INSERTs'

    PRINT @Actual_Values

    PRINT ''

    PRINT '*****END OF DEBUG INFORMATION*****/'

    PRINT ''

    END

    PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'

    PRINT '--Build number: 22'

    PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com'

    PRINT '--http://vyaskn.tripod.com'

    PRINT ''

    PRINT 'SET NOCOUNT ON'

    PRINT ''

    --Determining whether to print IDENTITY_INSERT or not

    IF (@IDN <> '')

    BEGIN

    PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@schema,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON'

    PRINT 'GO'

    PRINT ''

    END

    IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@schema,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)

    BEGIN

    IF @schema IS NULL

    BEGIN

    SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'

    END

    ELSE

    BEGIN

    SELECT 'ALTER TABLE ' + QUOTENAME(@schema) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily'

    END

    PRINT 'GO'

    END

    PRINT ''

    PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''

    PRINT ''

    PRINT '-- remove REMARK signs in next line if target needs to be cleared before insert'

    PRINT '-- TRUNCATE TABLE [' + RTRIM(COALESCE(@target_table,@table_name)) + ']'

    --All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!

    EXEC (@Actual_Values)

    PRINT 'PRINT ''Done'''

    PRINT ''

    IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@schema,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL)

    BEGIN

    IF @schema IS NULL

    BEGIN

    SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'

    END

    ELSE

    BEGIN

    SELECT 'ALTER TABLE ' + QUOTENAME(@schema) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints'

    END

    PRINT 'GO'

    END

    PRINT ''

    IF (@IDN <> '')

    BEGIN

    PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@schema,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF'

    PRINT 'GO'

    END

    PRINT 'SET NOCOUNT OFF'

    SET NOCOUNT OFF

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Hi Durai,

    Reading your reply - are you suggesting that the generation of scripts is not possible in SSMS 2005 ?

    Hopw this is not available in SQL 2005.

    Kindly check the following web links for more reference.

    http://msdn.microsoft.com/en-us/library/ms178078(v=SQL.90).aspx

    http://technet.microsoft.com/en-us/library/ms178078(v=sql.90).aspx

    http://vyaskn.tripod.com/code/generate_inserts.txt (--> Please Note this is just as an example. There might be several other similar websites which will show you scripts to create Insert records. E&OE <-- )">

    http://msdn.microsoft.com/en-us/library/ms178078(v=SQL.90).aspx

    http://technet.microsoft.com/en-us/library/ms178078(v=sql.90).aspx

    http://vyaskn.tripod.com/code/generate_inserts.txt (--> Please Note this is just as an example. There might be several other similar websites which will show you scripts to create Insert records. E&OE <-- )

    Hope this will be helpful.

    E&OE

    Mehernosh.

  • No data scripting is not available in 2005. Other scripting are available in 2005, sorry for miscommunication

    Regards
    Durai Nagarajan

  • You could write a script to BCP out the data?

    Here is something I run to generate data from a table to convert it into a SELECT statement:

    USE MyDatabase

    Go

    DECLARE @Line VARCHAR(max), @TName varchar(128)

    SET @TName = 'MyTable'

    SET @Line = ''

    SELECT @Line = @Line + '+ ISNULL(QUOTENAME('+

    CASE WHEN C.Data_Type IN ('bit','datetime','smalldatetime','date','timestamp','int','smallint','bitint','tinyint', 'money', 'float','decimal','numeric')

    THEN 'CAST('+C.COLUMN_NAME+' as varchar)' ELSE C.COLUMN_NAME END

    +',''''''''),''NULL'')+'',''' FROM information_schema.tables T

    JOIN information_schema.columns C

    ON t.table_name = c.table_name

    WHERE t.table_type = 'base table'

    --and t.table_name like 'L_%'

    and T.Table_Name = @TName

    SELECT 'SELECT ''SELECT ''+'+SUBSTRING(@Line, 3, LEN(@Line) -4)+' UNION'''

    Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)

  • Here is a script for generating INSERT statements for 1 table. You could modify it for all tables or a subset. I use it when generating a subset of data for testing. I think someone here on this forum provided it .... Sorry, I did not note who it was (although I usually do try to keep the authors name).

    Edit: Maybe I got it from here:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53420

    -- Instrucions!

    -- Change current db

    -- Change source table (in red)

    -- Change sample rows (in red)

    -- run to generate insert statements

    -- optional 'WHERE' clause near the bottom to specify records

    use MyDatabase

    DECLARE@tableName varchar(100),

    @seedCnt int

    SET @tableName = 'MyTable'-- Table Name

    SET @seedCnt = 50-- # of records to select

    DECLARE@execStr0 varchar(8000),

    @execStr1 varchar(8000),

    @execStr2 varchar(8000),

    @execStr3 varchar(8000),

    @execStr4 varchar(8000),

    @execStr5 varchar(8000),

    @execStr6 varchar(8000),

    @execStr7 varchar(8000),

    @execStr8 varchar(8000),

    @execStr9 varchar(8000)

    -- Display warning for unsupported types

    IF EXISTS(SELECT *

    FROMINFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = @tablename

    AND DATA_TYPE NOT IN

    ('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname',

    'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney',

    'money', 'bit', 'smallint', 'real', 'bigint'))

    BEGIN

    SELECT DISTINCT DATA_TYPE + ' Type not supported'

    FROMINFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = @tablename

    AND DATA_TYPE NOT IN

    ('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname',

    'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney',

    'money', 'bit', 'smallint', 'real', 'bigint')

    END

    -- Build column translations

    SELECT

    @execStr1 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr2 ELSE @execStr1 END,

    @execStr2 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr3 ELSE @execStr2 END,

    @execStr3 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr4 ELSE @execStr3 END,

    @execStr4 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr5 ELSE @execStr4 END,

    @execStr5 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr6 ELSE @execStr5 END,

    @execStr6 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr7 ELSE @execStr6 END,

    @execStr7 = CASE WHEN LEN(@execStr8) > 7500 THEN @execStr8 ELSE @execStr7 END,

    @execStr8 = CASE WHEN LEN(@execStr8) > 7500 THEN NULL ELSE @execStr8 END,

    @execStr8 = IsNull(@execStr8 + ' +' + CHAR(13) + CHAR(10),'') +

    CONVERT(varchar(8000),

    CASE

    WHEN DATA_TYPE IN ('uniqueidentifier')

    THEN CHAR(9) + '''' + COLUMN_NAME

    + '=''+IsNull('''''''' + CONVERT(varchar(50),' + COLUMN_NAME + ') + '''''''',''null'')+'', '''

    WHEN DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'sysname')

    THEN CHAR(9) + '''' + COLUMN_NAME

    + '=''+IsNull('''''''' + REPLACE(' + COLUMN_NAME + ','''''''','''''''''''') + '''''''',''null'')+'', '''

    WHEN DATA_TYPE IN ('datetime')

    THEN CHAR(9) + '''' + COLUMN_NAME

    + '=''+IsNull('''''''' + CONVERT(varchar,' + COLUMN_NAME + ',121)+'''''''',''null'') + '', '''

    WHEN DATA_TYPE IN ('tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney', 'money', 'bit', 'smallint', 'real', 'bigint')

    THEN CHAR(9) + '''' + COLUMN_NAME

    + '=''+IsNull(CONVERT(varchar,' + COLUMN_NAME + '),''null'')+'', '''

    ELSE

    ' ** DATA TYPE ' + DATA_TYPE + ' NOT SUPPORTED **'

    END)

    FROMINFORMATION_SCHEMA.COLUMNS

    WHERE TABLE_NAME = @tablename

    AND DATA_TYPE IN

    ('uniqueidentifier', 'char', 'nchar', 'varchar', 'nvarchar', 'sysname',

    'datetime', 'tinyint', 'int', 'float', 'numeric', 'decimal', 'smallmoney',

    'money', 'bit', 'smallint', 'real', 'bigint')

    ORDER BY ORDINAL_POSITION

    SELECT@execStr0 = 'Select top ' + CONVERT(varchar, @seedCnt) + CHAR(13) + CHAR(10)

    + '''Insert Into #' + REPLACE(@tableName,' ','') + ' Select '' + '

    + CHAR(13) + CHAR(10)

    SELECT@execStr1 = IsNull(@execStr1+', ', '')

    SELECT@execStr2 = IsNull(@execStr2+', ', '')

    SELECT@execStr3 = IsNull(@execStr3+', ', '')

    SELECT@execStr4 = IsNull(@execStr4+', ', '')

    SELECT@execStr5 = IsNull(@execStr5+', ', '')

    SELECT@execStr6 = IsNull(@execStr6+', ', '')

    SELECT@execStr7 = IsNull(@execStr7+', ', '')

    SELECT@execStr8 = left(@execStr8, len(@execStr8)-4) + ''''''

    SELECT@execStr9 = CHAR(13) + CHAR(10) + 'from [' + @tableName + ']' --+ 'where Answer_ID < 20'

    -- Comment in for Debug

    -- Select @execStr0, @execStr1, @execStr2, @execStr3, @execStr4, @execStr5, @execStr6, @execStr7, @execStr8, @execStr9

    EXEC (@execStr0 + @execStr1 + @execStr2 + @execStr3 + @execStr4

    + @execStr5 + @execStr6 + @execStr7 + @execStr8 + @execStr9)

  • I use http://www.sqlserverbooster.com

    This software generates complete script backup, full, differential and log; as you wish. Supports since 2000 MS SQL Server versions to the present. Also let you generates your script in batch files, because if it's too big at the moment to generate the database again it is little tricky to restore with a huge script.

    Besides let you upload the backups to the cloud, schedule the backups an more.

    The good thing is that it's FREE.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply