Home > SQL Server > Generate script chèn dữ liệu trong SQL Server 2005

Generate script chèn dữ liệu trong SQL Server 2005

Cách 1 : 

Đoạn script sau sẽ tạo 1 store procedure CreateInserts, cho phép bạn truyền vào tên bảng cần gen lệnh insert dữ liệu, mệnh đề điều kiện và mệnh đề sắp xếp thứ tự. Chú ý rằng :

– Tất cả các giá trị dữ liệu đều được chuyển sang dạng HEX, bởi vậy bạn không cần lo lắng về các dấu nháy kép.

– Nếu bảng có 1 cột identity, đoạn script sẽ generate lệnh IDENTITY_INSERT khá tốt.

/* EXAMPLES
–Entire table
CreateInserts ‘Webpages’
–Entries WHERE WebpageID > 100
CreateInserts ‘Webpages’, ‘WebpageID > 100’
–Entries WHERE WebpageID > 100 ORDER BY Title
CreateInserts ‘Webpages’, ‘WebpageID > 100’, ‘Title’
*/

Script tạo SP này như sau :

--If stored procedure already exists, drop it
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = 
OBJECT_ID(N'[dbo].[CreateInserts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[CreateInserts]
GO
--Create the stored procedure
CREATE PROC CreateInserts
@tableName nvarchar(100),
@whereClause nvarchar(MAX) = '',
@orderByClause nvarchar(MAX) = ''
AS
--Declare variables
DECLARE @tableHasIdentity bit
DECLARE @sql nvarchar(MAX)
DECLARE @cols nvarchar(MAX)
DECLARE @vals nvarchar(MAX)
SET @cols = ''
SET @vals = ''
--Determine if table has an identity column
SELECT @tableHasIdentity = 
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = @tableName
--Do we need 'SET IDENTITY_INSERT tableName ON' statement?
IF @tableHasIdentity = 1
 BEGIN
 SET @sql = 'SELECT TOP 1 ''SET IDENTITY_INSERT ' + 
@tableName + ' ON '' FROM ' + @tableName
 EXEC sp_executesql @sql
 END
--Build list of columns and values
SELECT @cols = @cols + ',' + '[' + column_name + ']', @vals = @vals + 
 '+'',''+ISNULL(master.dbo.fn_varbintohexstr(cast([' + 
column_name + '] as varbinary(max))),''NULL'')' 
FROM INFORMATION_SCHEMA.columns 
WHERE TABLE_NAME = @tableName and DATA_TYPE != 'timestamp'
--Build SQL string
SET @sql = 'SELECT ''INSERT INTO [' + @tableName + '] (' + 
SUBSTRING(@cols,2,LEN(@cols)) + ') ' + 
 'VALUES (''+' + SUBSTRING(@vals, 6, 
LEN(@vals)) + '+'')'' FROM ' + @tableName
--Adjust @whereClause and @orderByClause
IF LEN(@whereClause) > 0
 SET @sql = @sql + ' WHERE ' + @whereClause
IF LEN(@orderByClause) > 0
 SET @sql= @sql + ' ORDER BY ' + @orderByClause
--Execute SQL string
exec sp_executesql @sql
--Do we need 'SET IDENTITY_INSERT tableName OFF' statement?
IF @tableHasIdentity = 1
 BEGIN
 SET @sql = 'SELECT TOP 1 ''SET IDENTITY_INSERT ' + 
@tableName + ' OFF '' FROM ' + @tableName
 EXEC sp_executesql @sql
 END
GO

( Tham khảo tại : http://keylimetie.com/blog/2009/3/24/sql-server-2005-script-to-generate-insert-statements/ )

Cách 2 : 

Chạy script sau, trong đó tham số @tableName là tên bảng, @seedCnt là số rows cần gen.

Use northwind
Declare @dbName varchar(100),
 @tableName varchar(100),
 @seedCnt int

Set @tableName = 'customers'
Set @seedCnt = 50
Declare @execStr varchar(8000)
--If there is an image column in the specified table, display warning
if exists(Select * From Information_Schema.columns Where table_Name = @tablename and data_type='image')
 Select 'Image Type not supported'
--If there is an ntext column in the specified table, display warning
if exists(Select * From Information_Schema.columns Where table_Name = @tablename and data_type='ntext')
 Select 'nText Type not supported'

--build column translations
 Select
 @execStr = isnull(@execStr + ' +' + char(13) + char(10),'') + 
 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') 
 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 ('int','float','decimal','money','bit','smallint','real','bigint') 
 then char(9) + '''' + column_name + '='' + isnull(convert(varchar,' + column_Name + '),''null'') + '', ''' end
 From Information_Schema.columns Where table_Name = @tablename and data_type not in ('image','ntext') 
-- and Ordinal_Position<=1

 --Select @execStr
 Select @execStr=left(@execStr,len(@execStr)-4) + ''''''
 Select @execStr = 'Select top ' + convert(varchar,@seedCnt) + char(13) + char(10) + '''Insert Into #' + replace(@tableName,' ','') + ' Select '' + ' + char(13) + char(10) + @execStr + char(13) + char(10) + 'from [' + @tableName + ']'
 --Select @execStr

 Exec(@execStr)

(Tham khảo tại : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53420 )

Cách 3 : 

Thêm 1 số thay đổi so với cách 2

– Thêm 1 vài kiểu dữ liệu bị lỗi ( tinyint, smallmoney, … )

– Cột trong INFORMATION_SCHEMA.COLUMNS trả lại kiểu nvarchar, điều đó có nghĩa là câu lệnh CASE ở dạng Nvarchar, và chỉ có 4000 ký tự được chấp nhận –> lựa chọn varchar (8000) để thay thế ( đây có thể không phải là ý kiến hay, tuy nhiên chấp nhận được )

– Thêm tính mềm dẻo cho các bảng với rất nhiều cột – có thể lên tới 600 cột

USE Northwind
GO
DECLARE @tableName varchar(100),
 @seedCnt int

SET @tableName = 'customers'
SET @seedCnt = 50
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 *
 FROM INFORMATION_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'
 FROM INFORMATION_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)
FROM INFORMATION_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 + ']'
-- Comment in for Debug
-- Select @execStr0, @execStr1, https://sites.google.com/site/quyetdo289/co-so-du-lieu/sqlserver2005scripttogenerateinsertstatements@execStr2, @execStr3, @execStr4, @execStr5, @execStr6, @execStr7, @execStr8, @execStr9
EXEC (@execStr0 + @execStr1 + @execStr2 + @execStr3 + @execStr4 
 + @execStr5 + @execStr6 + @execStr7 + @execStr8 + @execStr9

(Tham khảo tại : http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=53420 )

Cách 4 : Đâylà một hướng dẫn trên CodeProject

– Tạo store InsertGenerator

– Trước khi chạy, đối với bảng có cột Identity : SET IDENTITY_INSERT <TABLENAME>ON để cho phép có thể add dữ liệu vào

( Xem chi tiết tại :  http://www.codeproject.com/Articles/5598/Generating-INSERT-statements-in-SQL-Server )

Cách 5 : Sử dụng tool SQL Script Generator

( Download tại : http://www.sqlscriptgenerator.com/ )

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: