导出表中数据为脚本形式，即INSERT INTO schemaname.tablename(col1,col2) values(value1,value2)
方法1：使用如下存储过程
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_generate_insert_script] Script Date: 04/18/2009 15:12:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_generate_insert_script]
(
@tablename_mask varchar(30) = NULL,
@Where varchar(1000)=NULL,
@col sysname=null
)
AS
BEGIN
CREATE TABLE #Tmp(ID int NOT NULL IDENTITY,COL nvarchar(max))
DECLARE @tablename varchar (128)
DECLARE @tablename_max varchar (128)
DECLARE @tableid int
DECLARE @columncount numeric (7,0)
DECLARE @columncount_max numeric (7,0)
DECLARE @columnname varchar (30)
DECLARE @columntype int
DECLARE @string varchar (30)
DECLARE @leftpart varchar (max)
DECLARE @rightpart varchar (max)
DECLARE @hasident int
SET NOCOUNT ON
IF (@tablename_mask IS NULL)
BEGIN
SELECT @tablename_mask = '%'
END
CREATE TABLE #columninfo
(num numeric (7,0) identity,
name varchar(30),
usertype smallint)
SELECT name,
id
INTO #tablenames
FROM sysobjects
WHERE type IN ('U' ,'S')
AND name LIKE @tablename_mask
SELECT @tablename_max = MAX (name),
@tablename = MIN (name)
FROM #tablenames
WHILE @tablename <= @tablename_max
BEGIN
SELECT @tableid = id
FROM #tablenames
WHERE name = @tablename
IF (@@ROWCOUNT <> 0)
BEGIN
SELECT @hasident = max( status & 0x80 )
FROM syscolumns
WHERE id = @tableid
TRUNCATE TABLE #columninfo
INSERT INTO #columninfo (name,usertype)
SELECT name, type
FROM syscolumns C
WHERE id = @tableid
AND type <> 37 AND name=CASE WHEN @col IS NULL THEN name ELSE @col END -- do not include timestamps
SELECT @leftpart = 'SELECT ''INSERT INTO [email=]'+@tablename[/email]
SELECT @leftpart = @leftpart + '('
SELECT @columncount = MIN (num),
@columncount_max = MAX (num)
FROM #columninfo
WHILE @columncount <= @columncount_max
BEGIN
SELECT @columnname = name,
@columntype = usertype
FROM #columninfo
WHERE num = @columncount
IF (@@ROWCOUNT <> 0)
BEGIN
IF (@columncount < @columncount_max)
BEGIN
SELECT @leftpart = @leftpart + @columnname + ','
END
ELSE
BEGIN
SELECT @leftpart = @leftpart + @columnname + ')'
END
END
SELECT @columncount = @columncount + 1
END
SELECT @leftpart = @leftpart + ' values('''
SELECT @columncount = MIN (num),
@columncount_max = MAX (num)
FROM #columninfo
SELECT @rightpart = ''
WHILE @columncount <= @columncount_max
BEGIN
SELECT @columnname = name,
@columntype = usertype
FROM #columninfo
WHERE num = @columncount
IF (@@ROWCOUNT <> 0)
BEGIN
IF @columntype IN (39,47) /* char fields need quotes (except when entering NULL);
* use char(39) == ', easier readable than escaping
*/
BEGIN
SELECT @rightpart = @rightpart + '+'
SELECT @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+REPLACE(' + @columnname + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'
END
ELSE IF @columntype = 35 /* TEXT fields cannot be RTRIM-ed and need quotes */
/* convert to VC 1000 to leave space for other fields */
BEGIN
SELECT @rightpart = @rightpart + '+'
SELECT @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+REPLACE(CONVERT(varchar(1000),' + @columnname + ')' + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6 ) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'
END
ELSE IF @columntype in (58,61,111) /* datetime fields */
BEGIN
SELECT @rightpart = @rightpart + '+'
SELECT @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+CONVERT(varchar(20),' + @columnname + ',120)+'+ replicate( char(39), 4 ) + ',''NULL'')'
END
ELSE /* numeric types */
BEGIN
SELECT @rightpart = @rightpart + '+'
SELECT @rightpart = @rightpart + 'ISNULL(CONVERT(varchar(99),' + @columnname + '),''NULL'')'
END
IF ( @columncount < @columncount_max)
BEGIN
SELECT @rightpart = @rightpart + '+'','''
END
END
SELECT @columncount = @columncount + 1
END
END
SELECT @rightpart = @rightpart + '+'');''' + ' FROM ' + @tablename
IF @Where IS NOT NULL
SELECT @rightpart = @rightpart + ' WHERE [email=]'+@Where+'[/email] ORDER BY 1'
ELSE
SELECT @rightpart = @rightpart + ' ORDER BY 1'
IF @hasident > 0
SELECT 'SET IDENTITY_INSERT ' + @tablename + ' ON'
INSERT INTO #Tmp(COL)
EXEC ( @leftpart + @rightpart )
IF @hasident > 0
SELECT 'SET IDENTITY_INSERT ' + @tablename + ' OFF'
SELECT @tablename= MIN (name)
FROM #tablenames
WHERE name> @tablename
END
SELECT COL FROM #Tmp ORDER BY ID ASC
DROP TABLE #Tmp
END
方法2：使用SQL2008的SSMS
