Get insert, update and delete script in sql server
We will code each and every time when we go for an insert, update and delete. This Sql function would get you the insert, update and delete script for the table name you passed to the function. Find the code to Get insert, update and delete script in sql server.
Learn the code for Get insert, update and delete script in SQL server
Let us go over a simple instance that demonstrates how to get the Insert, Update and delete script in Sql Server using Sql Script. Below code would get the script through Sql user-defined function.
CREATE FUNCTION dbo.GetTemplate
(
@Table_Name VARCHAR(255)
, @Action CHAR(1)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Query VARCHAR(MAX)
, @TableName VARCHAR(255)
, @SchemaName VARCHAR(50)
, @Object_Id BIGINT
DECLARE @ColumnNames VARCHAR(MAX)
IF EXISTS(SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @Table_Name)
BEGIN
SELECT @SchemaName = TABLE_SCHEMA, @TableName = TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @Table_Name
IF (@Action = 'I') -- Insert template
BEGIN
SELECT @Object_Id = OBJECT_ID(@Table_Name)
SELECT @ColumnNames = COALESCE(@ColumnNames + ',', '') + Name
FROM sys.columns
WHERE object_id = @Object_Id
AND Is_Identity = 0
ORDER BY column_id
IF (@ColumnNames IS NULL)
BEGIN
SET @Query = 'There is no column available for this Table : '+ @SchemaName + '.' + @TableName
END
ELSE
BEGIN
SET @Query = 'INSERT INTO '+ @SchemaName + '.' + @TableName + '(' + @ColumnNames + ')' + CHAR(13) + CHAR(10) + 'VALUES( )'
END
END
IF (@Action = 'U') -- Update template
BEGIN
SELECT @Object_Id = OBJECT_ID(@Table_Name)
SELECT @ColumnNames = COALESCE(@ColumnNames + ', ', '') + Name + ' = ' + '''''' + CHAR(13) + CHAR(10)
FROM sys.columns
WHERE object_id = @Object_Id
AND Is_Identity = 0
ORDER BY column_id
IF (@ColumnNames IS NULL)
BEGIN
SET @Query = 'There is no column available for this Table : '+ @SchemaName + '.' + @TableName
END
ELSE
BEGIN
SET @Query = 'UPDATE '+ @SchemaName + '.' + @TableName + CHAR(13) + CHAR(10) + 'SET ' + @ColumnNames + 'WHERE '
END
END
IF (@Action = 'D') -- Update template
BEGIN
SET @Query = 'DELETE FROM ' + @SchemaName + '.' + @TableName + CHAR(13) + CHAR(10) + 'WHERE '
END
END
ELSE
BEGIN
SET @Query = 'Table_Name '''+ @Table_Name +''' Not Found !!!'
END
RETURN @Query
END
1) To Get the Insert Script you can call the function like
SELECT dbo.GetTemplate('TestTable', 'I')
Result:
-------
INSERT INTO dbo.TestTable(ID, Values)
VALUES()
2) To Get the Update Script, You can call the function like
SELECT dbo.GetTemplate('TestTable', 'U')
Result:
-------
UPDATE dbo.TestTable
SET ID = ''
, Values = ''
WHERE
3) To get the Delete Script call the functin like
SELECT dbo.GetTemplate('TestTable', 'D')
Result:
-------
DELETE FROM dbo.TestTable
WHERE
There is one more trick to do. assign the function to the keyboard short cut in Query analyzer. It would make even easy to code. Always view the result in a Text for better appearance.
nice topic generally i do using UI . i find new way to do this thanks