sql server2005不能导出数据为insert这种sql文件

sql server2005导出所有表中的数据，是不是不能导成insert这样sql语句的文件啊？有没有什么办法能导成这样的文件？

11 个解决方案

#1

导出数据可以是DBF,EXCEL,TXT,然后可以利用BCP,BULK INSERT导入

#2

--将表数据生成SQL脚本的存储过程 

CREATE PROCEDURE dbo.UspOutputData 
@tablename sysname 
AS 
declare @column varchar(1000) 
declare @columndata varchar(1000) 
declare @sql varchar(4000) 
declare @xtype tinyint 
declare @name sysname 
declare @objectId int 
declare @objectname sysname 
declare @ident int 

set nocount on 
set @objectId=object_id(@tablename) 

if @objectId is null -- 判断对象是否存在 
begin 
print 'The object not exists' 
return 
end 
set @objectname=rtrim(object_name(@objectId)) 

if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密 
begin 
print 'object not in current database' 
return 
end 

if OBJECTPROPERTY(@objectId,'IsTable') < > 1 -- 判断对象是否是table 
begin 
print 'The object is not table' 
return 
end 

select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80 

if @ident is not null 
print 'SET IDENTITY_INSERT '+@TableName+' ON' 

declare syscolumns_cursor cursor 

for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid 

open syscolumns_cursor 
set @column='' 
set @columndata='' 
fetch next from syscolumns_cursor into @name,@xtype 

while @@fetch_status < >-1 
begin 
if @@fetch_status < >-2 
begin 
if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理 

begin 
set @column=@column+case when len(@column)=0 then'' else ','end+@name 

set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','',' 
end 

+case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char 
when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar 
when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime 
when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime 
when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier 
else @name end 

end 

end 

fetch next from syscolumns_cursor into @name,@xtype 

end 

close syscolumns_cursor 
deallocate syscolumns_cursor 

set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename 

print '--'+@sql 
exec(@sql) 

if @ident is not null 
print 'SET IDENTITY_INSERT '+@TableName+' OFF' 

GO 

exec UspOutputData 你的表名

#3

引用楼主 jon_wd 的回复:

sql server2005导出所有表中的数据，是不是不能导成insert这样sql语句的文件啊？有没有什么办法能导成这样的文件？

这个好像只能自己写动态sql对应了。 然后bcp导出成脚本样的东西。 

#4

2楼的能不能排排版啊，对不上了都

#5

****************************************************************************
 软件名称: May Flower Erp
 版权所有: (C) 2005-2006 May Flower ERP 开发组
 功能描述: 获取指定表的创建脚本,包括表和字段的属性、外键(注释掉的)
----------------------------------------------------------------------------
 参数列表: 
        1: @TableName 需要创建脚本的表的名称
****************************************************************************
|</PRE>*/
CREATE PROCEDURE [dbo].[sp_HelpTable](@TableName sysname)
AS
  SET NOCOUNT ON
  DECLARE @ObjectID int
  DECLARE @TableScript table(Iden int IDENTITY(1, 1), ScriptLine nvarchar(4000))
  SET @ObjectID = object_id(@TableName)
  IF @ObjectID IS NULL OR OBJECTPROPERTY(@ObjectID, 'IsTable') = 0
  BEGIN
    RAISERROR('指定的对象不是表对象', 16, 1)
    RETURN
  END 
  --获取表的创建脚本
  --插入表头
  INSERT INTO @TableScript(ScriptLine)
    SELECT N'CREATE TABLE [' + USER_NAME(OBJECTPROPERTY(@ObjectID, N'OwnerId')) + N'].[' + object_name(@ObjectID) + N']('

  --插入字段
  INSERT INTO @TableScript(ScriptLine)
    SELECT N'  [' + a.Name + N'] [' + b.name + N']' + 
           CASE WHEN c.Object_id IS NOT NULL THEN N' IDENTITY(' + CONVERT(nvarchar, c.seed_value) + N', ' + CONVERT(nvarchar, c.increment_value) + N')' 
                ELSE '' END +
           CASE WHEN b.xusertype IN (167, 175, 231, 239) THEN N'('+CONVERT(nvarchar, a.prec)  + N')'
                WHEN b.xusertype in (106, 108)           THEN N'('+CONVERT(nvarchar, a.xprec) + N', ' + CONVERT(nvarchar, a.xscale) + N')'
                ELSE '' END +
           CASE a.isnullable WHEN 1 THEN N'' ELSE N' NOT' END + N' NULL' +
           CASE WHEN d.Name IS NOT NULL THEN N' DEFAULT ' + d.Definition ELSE N'' END +
           N',' 
      FROM sys.syscolumns a
        LEFT JOIN sys.systypes b ON a.xusertype = b.xusertype
        LEFT JOIN sys.identity_columns c ON c.Object_id = a.ID AND c.Column_ID = a.ColID
        LEFT JOIN sys.default_constraints d ON d.Parent_Object_ID = a.ID AND d.Parent_column_ID = a.ColID
    WHERE a.[ID] = @ObjectID
    ORDER BY a.ColOrder

  --插入主键和索引
  DECLARE @IndexID int, @IndexScript nvarchar(4000)
  DECLARE IndexCursor CURSOR FOR 
    SELECT b.Index_ID, N'  CONSTRAINT [' + a.Name + N'] ' + 
           CASE a.Type WHEN 'PK' THEN N'PRIMARY KEY ' WHEN 'UQ' THEN N'UNIQUE ' END + 
           CASE b.Type WHEN 1    THEN N'CLUSTERED'   WHEN 2     THEN N'NONCLUSTERED ' END + N'('
      FROM sys.key_constraints a
        LEFT JOIN sys.indexes b ON b.Object_ID = a.Parent_Object_ID AND b.index_id = a.unique_index_id
    WHERE a.Parent_Object_ID = @ObjectID
  OPEN IndexCursor
  FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @IndexScript = @IndexScript + N'[' + INDEX_COL(object_name(@ObjectID), 2 , 1) + N'],'
      FROM sys.index_columns 
    WHERE Object_ID = @ObjectID
      AND Index_ID = 2
    
    SET @IndexScript = LEFT(@IndexScript, LEN(@IndexScript) -1) + N'),'
    INSERT INTO @TableScript(ScriptLine) VALUES(@IndexScript)
    FETCH NEXT FROM IndexCursor INTO @IndexID, @IndexScript
  END
  Close IndexCursor
  DEALLOCATE IndexCursor

  insert into @TableScript(ScriptLine) VALUES(')')
  select * from @TableScript 



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/fredrickhu/archive/2009/10/14/4669511.aspx

#6

生成insert语句？2楼即可

#7

学习中。。。。

#8

各位大哥好牛X,小弟佩服。。。。。。。。。

#9

我告诉你一个好办法，动软软件能满足你的要求

#10

Hi,

Trying this: 

--拷贝生成的代码 再执行

SET NOCOUNT ON
DECLARE @tblName nvarchar(100)
DECLARE @DBName nvarchar(100)
DECLARE @i int
DECLARE @j int
DECLARE @ssql nvarchar(max)
DECLARE @sCloumns nvarchar(max)
DECLARE @CloumnName nvarchar(100)
DECLARE @xtype int
DECLARE @isnullable int
SET @tblName = 'tbl_job'
SET @DBName = 'RSMS'
CREATE TABLE #tbl_columnIDs(name nvarchar(100),id nvarchar(100),xtype int,isnullable int,row int identity)
INSERT INTO #tbl_columnIDs
SELECT name,id,xtype,isnullable
FROM SYSCOLUMNS WHERE id = OBJECT_ID(@tblName)
SET @i = 1
SELECT @j = max(row) FROM #tbl_columnIDs

SET @ssql = 'SELECT ''INSERT INTO ' + @tblName + '('''+CHAR(10)
WHILE (@i < @j + 1)
BEGIN
	SELECT @CloumnName = name FROM #tbl_columnIDs WHERE ROW = @i
	IF @i = @j 
	BEGIN
		SET @ssql = @ssql + '+''' + @CloumnName + ')'''+ CHAR(10)
	END
	ELSE
	BEGIN
		SET @ssql = @ssql + '+''' + @CloumnName + ',''' + CHAR(10)
	END
	SET @i = @i + 1
END

SET @ssql = @ssql + '+'' VALUES (''' + CHAR(10)

SET @i = 1
SELECT @j = max(row) FROM #tbl_columnIDs
WHILE (@i < @j + 1)
BEGIN
	SELECT @CloumnName = name,@xtype = xtype,@isnullable = isnullable FROM #tbl_columnIDs WHERE ROW = @i
	IF @isnullable = 0 
	BEGIN
		SET @CloumnName = CASE @xtype WHEN 61 THEN '+'',''+''''''''+CONVERT(nvarchar,'+@CloumnName+',121)+'''''''''  --DateTime 61
									WHEN 58  THEN '+'',''+''''''''+CONVERT(nvarchar,'+@CloumnName+',121)+'''''''''  --smalldatetime 58
									WHEN 48	 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' --tinyint
									WHEN 52	 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' 
									WHEN 56	 THEN '+'',''+CONVERT(nvarchar(50),' + @CloumnName + ')+' 
									WHEN 59	 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' 
									WHEN 60	 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' 
									WHEN 62	 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' 
									WHEN 104 THEN '+'',''+CONVERT(nvarchar(50),' + @CloumnName + ')+' 
									WHEN 106 THEN '+'',''+CONVERT(nvarchar(50),' + @CloumnName + ')+' 
									WHEN 108 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' 
									WHEN 122 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' 
									WHEN 127 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' 
									WHEN 189 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' 
									WHEN 231 THEN '+'',''+''N''+''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''''
									WHEN 239 THEN '+'',''+''N''+''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''''
							ELSE '+'',''+''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+''''''''' 
							END
	END
	ELSE
	BEGIN
		SET @CloumnName = CASE @xtype WHEN 61 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar,'+@CloumnName+',121)+'''''''',''NULL'')'  --DateTime 61
									WHEN 58  THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar,'+@CloumnName+',121)+'''''''',''NULL'')'  --smalldatetime 58
									WHEN 48	 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')' --tinyint
									WHEN 52	 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
									WHEN 56	 THEN '+'',''+ISNULL(CONVERT(nvarchar(50),'+@CloumnName+'),''NULL'')'
									WHEN 59	 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
									WHEN 60	 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
									WHEN 62	 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
									WHEN 104 THEN '+'',''+ISNULL(CONVERT(nvarchar(50),'+@CloumnName+'),''NULL'')'
									WHEN 106 THEN '+'',''+ISNULL(CONVERT(nvarchar(50),'+@CloumnName+'),''NULL'')'
									WHEN 108 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
									WHEN 122 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
									WHEN 127 THEN '+'',''+ISNULL(''''''''+CONVERT(nvarchar(50),'+@CloumnName+')+'''''''',''NULL'')'
									WHEN 189 THEN '+'',''+''''''''+CONVERT(nvarchar(50),' + @CloumnName + ')+''''''''' 
									WHEN 231 THEN '+'',''+ISNULL(''N''+''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''',''NULL'')'
									WHEN 239 THEN '+'',''+ISNULL(''N''+''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''',''NULL'')'
							ELSE '+'',''+ISNULL(''''''''+REPLACE('+@CloumnName+','''''''','''''''''''')+'''''''',''NULL'')'
							END
	END
	IF @i = 1
	BEGIN
		SET @CloumnName = SUBSTRING(@CloumnName,5,LEN(@CloumnName))
	END
	SET @ssql = @ssql + @CloumnName + CHAR(10)
	SET @i = @i + 1
END
SET @ssql = @ssql + '+'')''' + CHAR(10)
SET @ssql = @ssql + 'FROM '+@DBName + '..' + @tblName

DECLARE @FULLSTMT varchar(max)  
DECLARE @StmtIndex int 

SET @FULLSTMT = @ssql  
SET @StmtIndex = 1  
WHILE @StmtIndex < LEN(@FULLSTMT)  
BEGIN  
  PRINT SUBSTRING(@FULLSTMT, @StmtIndex, 6000)  
  SET @StmtIndex = @StmtIndex + 6000  
END

PRINT 'UNION ALL' + CHAR(10)+'SELECT ''GO'''
DROP TABLE #tbl_columnIDs

#11

我开始也用UspOutputData这个存储过程，不过比较麻烦，用datebasetool 这个很小的绿色软件就可以解决，就几百K，还不用安装，百度搜一下就能找到