“Sql Server 2005:Generate
Tables Data Script ” and “Sql Server 2008:Generate Data Script” we have discussed
methods to generate table data script for schema based script for Sql Server 2005
and specific table’s data script for Sql Server 2008 respectively.
On suggestion
of few friends following script will generate
table data script based on your given conditions. This script is useful to generate
script for only required records of a targeted table data and not the whole data
for a table.
Following are four steps to achieve our goal.
1.
Create function to get all columns name of targeted table
2.
Create function to get values for all columns of targeted
table
3.
Create a store procedure, to group our queries for future
use.
4.
Execute store procedure with following parameters
a.
Schema Name
b.
Table Name
c.
Condition with WHERE clause
--------------------------------------------------
--STEP (1)
--------------------------------------------------
CREATE FUNCTION [dbo].[fnc_GetColumnsByCommas]
(
-- Add the parameters for the function
@schemaName
VARCHAR(50),
@tableName
VARCHAR(50)
)
RETURNS VARCHAR(4000)
AS BEGIN
DECLARE @column VARCHAR(2000),
@columnS VARCHAR(4000),
@i INT
SET @i = 0
SET @column = ''
SET @columnS = ''
DECLARE Cur_Columns CURSOR
STATIC
FOR SELECT
sys.columns.name
FROM
sys.schemas
INNER JOIN sys.objects
ON sys.schemas.schema_id =
sys.objects.schema_id
INNER JOIN sys.columns
ON sys.columns.object_id =
sys.objects.object_id
INNER JOIN sys.types
ON sys.columns.user_type_id
= sys.types.user_type_id
WHERE sys.types.name
<> 'timestamp'
AND sys.objects.type =
'U'
AND sys.objects.name
= @tableName
AND sys.schemas.name
= @schemaName
ORDER BY sys.columns.column_id
OPEN Cur_Columns
FETCH FIRST FROM
Cur_Columns INTO @column
WHILE @@FETCH_STATUS
= 0
BEGIN
IF @i = 0
SET @columnS =
'[' + @column
+ ']'
ELSE
SET @columnS =
@columnS + ','
+ '['
+ @column + ']'
SET @i = @i
+ 1
FETCH NEXT FROM Cur_Columns INTO
@column
END
CLOSE Cur_Columns
DEALLOCATE Cur_Columns
-- Return
the result of the function
RETURN @columns
END
--------------------------------------------------
--STEP (2)
--------------------------------------------------
CREATE FUNCTION [dbo].[fnc_GetColumnsForValueByCommas]
(
-- Add the parameters for the function here
@schemaName
VARCHAR(50),
@tableName
VARCHAR(50)
)
RETURNS VARCHAR(4000)
AS BEGIN
DECLARE @column VARCHAR(4000),
@typeName VARCHAR(500),
@columnS VARCHAR(2000),
@ColStart VARCHAR(50),
@ColEnd VARCHAR(50),
@i INT
SET @i = 0
SET @column = ''
SET @columnS = ''
DECLARE Cur_Columns CURSOR
STATIC
FOR SELECT
sys.columns.name,
sys.types.name
FROM
sys.schemas
INNER JOIN sys.objects
ON sys.schemas.schema_id =
sys.objects.schema_id
INNER JOIN sys.columns
ON sys.columns.object_id =
sys.objects.object_id
INNER JOIN sys.types
ON sys.columns.user_type_id
= sys.types.user_type_id
WHERE sys.types.name
<> 'timestamp'
AND sys.objects.type =
'U'
AND sys.objects.name
= @tableName
AND sys.schemas.name
= @schemaName
ORDER BY sys.columns.column_id
OPEN Cur_Columns
FETCH FIRST FROM
Cur_Columns INTO @column,
@typeName
WHILE @@FETCH_STATUS
= 0
BEGIN
IF @typeName =
'text'
OR @typeName =
'uniqueidentifier'
OR @typeName =
'varbinary'
OR @typeName =
'smalldatetime'
OR @typeName =
'char'
OR @typeName =
'datetime'
OR @typeName =
'varchar'
OR @typeName =
'date'
OR @typeName =
'time'
BEGIN
SET @ColStart =
' ISNULL(CHAR(39) + CAST ( '
SET @ColEnd =
' AS VARCHAR(MAX))+ CHAR(39),''NULL'') '
END
ELSE
IF @typeName =
'nvarchar'
OR @typeName =
'ntext'
OR @typeName =
'nchar'
BEGIN
SET @ColStart =
'ISNULL( ''N''+CHAR(39)+ CAST ( '
SET @ColEnd =
' AS NVARCHAR(MAX)) + CHAR(39),''NULL'') '
END
ELSE
BEGIN
SET @ColStart =
'ISNULL(CAST ( '
SET @ColEnd =
' AS VARCHAR(MAX)), ''NULL'') '
END
IF @i = 0
SET @columnS =
@ColStart + @column +
@ColEnd
ELSE
SET @columnS =
@columnS + '+'',''+'
+ @ColStart +
@column
+ @ColEnd
SET @i = @i
+ 1
FETCH NEXT FROM Cur_Columns INTO
@column, @typeName
END
CLOSE Cur_Columns
DEALLOCATE Cur_Columns
-- Return the result of
the function
RETURN @columns
END
--------------------------------------------------
--STEP (3)
--------------------------------------------------
SET ANSI_NULLS
ON
SET QUOTED_IDENTIFIER
ON
GO
CREATE PROCEDURE [dbo].[proc_CreateInsertStatmentsBySchema]
(
-- Add the parameters for the function here
@SchemaName
VARCHAR(50),
@TableName
VARCHAR(50),
@Condition
VARCHAR(2000)
)
AS
BEGIN
DECLARE @tbID INT,
@tbNAME VARCHAR(50),
@tbColumn VARCHAR(4000),
@tbColumnforVal
VARCHAR(4000),
@SQLstr VARCHAR(4000),
@label VARCHAR(500),
@Count INT
SELECT @tbNAME =
'[' +
sys.schemas.name +
'].[' + sys.objects.name
+ ']',
@tbColumn = dbo.fnc_GetColumnsByCommas(@schemaName,
sys.objects.name),
@tbColumnforVal = dbo.fnc_GetColumnsForValueByCommas(@schemaName,
sys.objects.name)
FROM
sys.schemas
INNER JOIN sys.objects
ON sys.schemas.schema_id =
sys.objects.schema_id
WHERE sys.objects.type =
'U'
AND sys.schemas.name
= @schemaName
AND sys.objects.name
= @TableName
SET @SQLstr =
'SELECT ' + CHAR(39)
+ 'INSERT INTO '
+ @tbNAME +
' ('
+ @tbColumn +
') VALUES ( ''+' +
@tbColumnforVal
+ ' +'')'' FROM '
+ @tbNAME +
@Condition
EXEC ( @SQLstr
)
END
--------------------------------------------------
STEP (4)Execute store procedure
with “Result to Text” option
--------------------------------------------------
Use AdventureWorks
EXEC [dbo].[proc_CreateInsertStatmentsBySchema] 'HumanResources',
'Department',
' WHERE DepartmentID <10 span="">
I got a reference from one of wonderfull site i.e
- http://connectsql.blogspot.com/2010/11/sql-server-management-studio-generate.html
- http://connectsql.blogspot.com/2009/09/generate-tables-data-script.html
