generate script file

[arun]

i want to generate a sql script file along with the data

how to generate it?

[MAK]

you can do it in many ways. here are the two script that can be used. one for creating the object and another for creating insert statements for the table data.

--to create script

DECLARE @ServerName varchar(30)
DECLARE @DBName varchar(30)
DECLARE @ObjectName varchar(50)
DECLARE @ScriptFile varchar(255)

DECLARE @CmdStr Varchar(255)
DECLARE @object int
DECLARE @out int
DECLARE @hr int

SET NOCOUNT ON

SET @ServerName = 'Servername'
SET @CmdStr = 'Connect('+@ServerName+')'

SET @DBName = 'Databasename'
SET @ObjectName = 'Tablename'
SET @ScriptFile = 'c:\temp\script.sql'

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT

EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE

EXEC @hr = sp_OAMethod @object,@CmdStr

--Drop and re-Creating table
SET @CmdStr = 'Databases("' + @DBName + '").Tables("' + @ObjectName + '").Script(5,"' + @ScriptFile + '")'

--print @CmdStr
EXEC @hr = sp_OAMethod @object, @CmdStr

EXEC @hr = sp_OADestroy @object



--to create insert statement for the object

CREATE PROC sp_DataAsInsCommand (
@TableList varchar (8000))
AS
SET NOCOUNT ON
DECLARE @position int, @exec_str varchar (2000), @TableName varchar
(50)
DECLARE @name varchar(128), @xtype int, @status tinyint, @IsIdentity
tinyint
SELECT @TableList = @TableList + ','
SELECT @IsIdentity = 0
SELECT @position = PATINDEX('%,%', @TableList)
WHILE (@position <> 0)
BEGIN

SELECT @TableName = SUBSTRING(@TableList, 1, @position - 1)
SELECT @TableList = STUFF(@TableList, 1, PATINDEX('%,%',
@TableList),'')
SELECT @position = PATINDEX('%,%', @TableList)

SELECT @exec_str = 'DECLARE fetch_cursor CURSOR FOR ' + 'SELECT
a.name, a.xtype, a.status FROM syscolumns a, sysobjects b WHERE a.id =
b.id and b.name = ''' + @TableName + ''''
EXEC (@exec_str)
OPEN fetch_cursor
FETCH fetch_cursor INTO @name, @xtype, @status
IF (@status & 0x80) <> 0
BEGIN
SELECT @IsIdentity = 1
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
SELECT 'GO'
END
SELECT @exec_str = "SELECT 'INSERT INTO " + @TableName + " VALUES
(' + "
Select ' -- The table name is: ' + @TableName
--text or ntext
IF (@xtype = 35) OR (@xtype = 99)
SELECT @exec_str = @exec_str + '''"None yet"'''
ELSE

--image
IF (@xtype = 34)
SELECT @exec_str = @exec_str + '"' + '0xFFFFFFFF' + '"'
ELSE

--smalldatetime or datetime
IF (@xtype = 58) OR (@xtype = 61)
SELECT @exec_str = @exec_str + 'Coalesce(' + ' + ''"'' + ' + '
+ CONVERT(varchar,' + @name + ',101)' + ' + ''"''' + ',"null")'
ELSE

--varchar or char or nvarchar or nchar
IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR (@xtype =
239)
SELECT @exec_str = @exec_str + 'Coalesce(' + '''"'' + ' + @name
+ ' + ''"''' + ',"null")'
ELSE

--uniqueidentifier
IF (@xtype = 36)
SELECT @exec_str = @exec_str + ' + Coalesce(''"'' + ' + ' +
CONVERT(varchar(255),' + @name + ')' + ' + ''"''' + ',"null")'
ELSE

--binary or varbinary
IF (@xtype = 173) OR (@xtype = 165)
SELECT @exec_str = @exec_str + '"' + '0x0' + '"'
ELSE

SELECT @exec_str = @exec_str + 'Coalesce(CONVERT(varchar,' +
@name + '), "null")'

WHILE @@FETCH_STATUS <> -1
BEGIN
FETCH fetch_cursor INTO @name, @xtype, @status
IF (@@FETCH_STATUS = -1) BREAK
IF (@status & 0x80) <> 0
BEGIN
SELECT @IsIdentity = 1
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' ON'
SELECT 'GO'
END

--text or ntext
IF (@xtype = 35) OR (@xtype = 99)
SELECT @exec_str = @exec_str + ' + ","' + ' + ''"None
yet"'''
ELSE

--image
IF (@xtype = 34)
SELECT @exec_str = @exec_str + ' + "," + ' + '"' +
'0xFFFFFFFF' + '"'
ELSE

--smalldatetime or datetime
IF (@xtype = 58) OR (@xtype = 61)
SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(''"''
+ ' + ' + CONVERT(varchar,' + @name + ',101)' + ' + ''"''' + ',"null")'
ELSE

--varchar or char or nvarchar or nchar
IF (@xtype = 167) OR (@xtype = 175) OR (@xtype = 231) OR
(@xtype = 239)
SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(''"''
+ ' + @name + ' + ''"''' + ',"null")'
ELSE

--uniqueidentifier
IF (@xtype = 36)
SELECT @exec_str = @exec_str + ' + ","' + ' + Coalesce(''"''
+ ' + ' + CONVERT(varchar(255),' + @name + ')' + ' + ''"''' +
',"null")'
ELSE

--binary or varbinary
IF (@xtype = 173) OR (@xtype = 165)
SELECT @exec_str = @exec_str + ' + "," + ' + '"' + '0x0' +
'"'
ELSE

SELECT @exec_str = @exec_str + ' + ","' + ' +
Coalesce(CONVERT(varchar,' + @name + '), "null")'
END

CLOSE fetch_cursor
DEALLOCATE fetch_cursor

SELECT @exec_str = @exec_str + '+ ")" FROM ' + @TableName
EXEC(@exec_str)
-- print (@exec_str)
SELECT 'GO'

IF @IsIdentity = 1
BEGIN
SELECT @IsIdentity = 0
SELECT 'SET IDENTITY_INSERT ' + @TableName + ' OFF'
SELECT 'GO'
END
END

[arun]

thanks .. i will try this solution.
Before that,
Could you tell me how to get(generate) the script(with data) using enterprise manager

[skhanal]

You have to do it in two steps.

1. Select the database and right click to select generate script option. Select the objects you want to script and follow the wizard to save the script.

2. Select the database and right click to select the option to export data, you will see a wizard which will allow you to save the data in text or other formats.

[rmiao]

Why don't just backup the db?

[arun]

what i'm looking for is
script file with 'insert statement'.
how to get this done using enterprise manager?

Mak, please let me know what parameter value should i pass to the 'sp_DataAsInsCommand' stored procedure.

[MAK]

sp_DataAsInsCommand 'TableName'

eg:
sp_DataAsInsCommand 'Orders,employees,territories'

[arun]

thank mak for the swift reply...
I tried the insert stored procedure but getting a error at the line

<code>
SELECT @exec_str = "SELECT 'INSERT INTO " + @TableName + " VALUES
(' + "
Select ' -- The table name is: ' + @TableName

</code>

hlp me out plz..

[MAK]

add this statement before executing Create procedure.

set quoted_identifier off

[arun]

thanks a million Mak!!
That certainly did the trick.
For knowledge sake, Is there a way to the same in enterprise manager in sql server 2000?
If yes, How?

[MAK]

No. You cant create insert statement from EM.

[arun]

oops!
I wonder y microsoft left out this option in EM.

[chadmat]

Try the free product called SQLDataScripter available at www.clrsoft.com

It does the datascripting for you. the next version will likely have object scripting built in as well.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime