March 4, 2009 at 10:11 pm
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
March 4, 2009 at 11:33 pm
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
March 5, 2009 at 3:13 pm
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" 😉
March 6, 2009 at 2:41 am
Thank you for this ..
but any automatic option in sql server 2005 to take script with data.
Thanks
Dastagiri.D
August 8, 2013 at 7:54 am
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.
August 16, 2013 at 3:19 am
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
August 16, 2013 at 3:48 am
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
August 19, 2013 at 4:57 am
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.
August 19, 2013 at 5:14 am
No data scripting is not available in 2005. Other scripting are available in 2005, sorry for miscommunication
Regards
Durai Nagarajan
August 28, 2013 at 6:14 am
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)
September 9, 2013 at 6:06 am
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)
September 18, 2014 at 7:07 am
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