In earlier posts “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 request of few blog readers 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'
No comments:
Post a Comment
All suggestions are welcome