如何生成这个脚本

2010-01-28 05:21:19 313 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

nattystyle 墨衍会员 · AI 创作全网分发关注 + 加入社区

SqlServer备份，还原，生成脚本 SqlServer备份，还原，生成脚本，版本兼容性解决阅读详情

在企业管理器中右键一张表->所有任务->生成SQL脚本

此时生成的脚本，只是生成这个表的表结构的脚本。

如何生成包含表结构也包含现有数据的SQL脚本？

...全文

生成sql脚本将数据表生成sql脚本 · 当多个数据库需要相同的表，或者当前数据表需要重建时，直接执行创建表的sql脚本要比手动创建节省许多操作时间，还可以避免出错。 · 在sql server2008 中支持对数据库create、drop、 select .insert.update和delete语句脚本生成。 · 当有多个表需要生成sql脚本时，可以使用sql server 2008 的数阅读详情

nattystyle

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

szadv 2010-02-04

打赏
举报

一直在学习

yananguo_1985 2010-01-30

打赏
举报

只能导出生成SQL的脚本，导数据还是DTS

hzxsljp 2010-01-30

打赏
举报

真好

ShenLiang2025 2010-01-28

打赏
举报

用个工具好了,

Red Gate的SQL Packager 6 可以完成此功能。

以下是该工具生成的代码示例:





SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON

GO

IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors

GO

CREATE TABLE #tmpErrors (Error int)

GO

SET XACT_ABORT ON

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

GO

CREATE USER [test] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[db_datareader]

GO

GRANT CONNECT TO [test]

GO

PRINT N'Creating role db_datareader'

GO

EXEC sp_addrolemember N'db_datareader', N'test'

GO

BEGIN TRANSACTION

GO

PRINT N'Creating [db_datareader].[j]'

GO

CREATE TABLE [db_datareader].[j]

(

[jno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,

[jname] [char] (20) COLLATE Chinese_PRC_CI_AS NULL,

[jcity] [char] (20) COLLATE Chinese_PRC_CI_AS NULL,

[balance] [numeric] (7, 2) NULL

)

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating primary key [PK__j__31EC6D26] on [db_datareader].[j]'

GO

ALTER TABLE [db_datareader].[j] ADD CONSTRAINT [PK__j__31EC6D26] PRIMARY KEY CLUSTERED ([jno])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating [db_datareader].[spj]'

GO

CREATE TABLE [db_datareader].[spj]

(

[sno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,

[pno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,

[jno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,

[price] [numeric] (7, 2) NULL,

[qty] [smallint] NULL

)

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating primary key [PK__spj__38996AB5] on [db_datareader].[spj]'

GO

ALTER TABLE [db_datareader].[spj] ADD CONSTRAINT [PK__spj__38996AB5] PRIMARY KEY CLUSTERED ([sno], [pno], [jno])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating [db_datareader].[p]'

GO

CREATE TABLE [db_datareader].[p]

(

[pno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,

[pname] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL,

[color] [char] (8) COLLATE Chinese_PRC_CI_AS NULL,

[weight] [int] NULL,

[price] [int] NULL

)

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating primary key [PK__p__24927208] on [db_datareader].[p]'

GO

ALTER TABLE [db_datareader].[p] ADD CONSTRAINT [PK__p__24927208] PRIMARY KEY CLUSTERED ([pno])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating [db_datareader].[s]'

GO

CREATE TABLE [db_datareader].[s]

(

[sno] [char] (4) COLLATE Chinese_PRC_CI_AS NOT NULL,

[sname] [char] (20) COLLATE Chinese_PRC_CI_AS NOT NULL,

[status] [char] (10) COLLATE Chinese_PRC_CI_AS NULL,

[saddr] [char] (20) COLLATE Chinese_PRC_CI_AS NULL,

[man] [char] (10) COLLATE Chinese_PRC_CI_AS NULL,

[dt] [datetime] NULL,

[tel] [char] (16) COLLATE Chinese_PRC_CI_AS NULL

)

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Creating primary key [PK__s__2E1BDC42] on [db_datareader].[s]'

GO

ALTER TABLE [db_datareader].[s] ADD CONSTRAINT [PK__s__2E1BDC42] PRIMARY KEY CLUSTERED ([sno])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Adding constraints to [db_datareader].[spj]'

GO

ALTER TABLE [db_datareader].[spj] ADD CONSTRAINT [CK__spj__qty__3C69FB99] CHECK (([qty]>=(0) AND [qty]<=(10000)))

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

PRINT N'Adding foreign keys to [db_datareader].[spj]'

GO

ALTER TABLE [db_datareader].[spj] ADD

CONSTRAINT [FK__spj__jno__3B75D760] FOREIGN KEY ([jno]) REFERENCES [db_datareader].[j] ([jno]),

CONSTRAINT [FK__spj__pno__3A81B327] FOREIGN KEY ([pno]) REFERENCES [db_datareader].[p] ([pno]),

CONSTRAINT [FK__spj__sno__398D8EEE] FOREIGN KEY ([sno]) REFERENCES [db_datareader].[s] ([sno])

GO

IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END

GO

GRANT CONNECT TO [test]

IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION

GO

IF @@TRANCOUNT>0 BEGIN

PRINT 'The database update succeeded'

COMMIT TRANSACTION

END

ELSE PRINT 'The database update failed'

GO

DROP TABLE #tmpErrors

GO

SET NUMERIC_ROUNDABORT OFF

GO

SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON

GO

SET DATEFORMAT YMD

GO

-- Pointer used for text / image updates. This might not be needed, but is declared here just in case

DECLARE @pv binary(16)



BEGIN TRANSACTION



-- Drop constraints from [db_datareader].[spj]

ALTER TABLE [db_datareader].[spj] DROP CONSTRAINT [FK__spj__jno__3B75D760]

ALTER TABLE [db_datareader].[spj] DROP CONSTRAINT [FK__spj__pno__3A81B327]

ALTER TABLE [db_datareader].[spj] DROP CONSTRAINT [FK__spj__sno__398D8EEE]



-- Add 15 rows to [db_datareader].[spj]

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's1  ', N'p1  ', N'j1  ', 22.60, 80)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's1  ', N'p1  ', N'j4  ', 22.60, 60)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's1  ', N'p3  ', N'j1  ', 22.80, 100)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's1  ', N'p3  ', N'j4  ', 22.80, 60)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's1  ', N'p3  ', N'j6  ', 22.80, 6)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's3  ', N'p3  ', N'j5  ', 22.10, 100)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's3  ', N'p4  ', N'j1  ', 11.90, 30)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's3  ', N'p4  ', N'j4  ', 11.90, 60)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's3  ', N'p4  ', N'j6  ', 11.90, 6)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's4  ', N'p2  ', N'j4  ', 11.90, 60)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's4  ', N'p2  ', N'j6  ', 33.80, 8)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's5  ', N'p5  ', N'j1  ', 22.80, 20)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's5  ', N'p5  ', N'j4  ', 22.80, 60)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's5  ', N'p5  ', N'j6  ', 22.80, 8)

INSERT INTO [db_datareader].[spj] ([sno], [pno], [jno], [price], [qty]) VALUES (N's8  ', N'p3  ', N'j1  ', 13.00, 20)



-- Add constraints to [db_datareader].[spj]

ALTER TABLE [db_datareader].[spj] ADD CONSTRAINT [FK__spj__jno__3B75D760] FOREIGN KEY ([jno]) REFERENCES [db_datareader].[j] ([jno])

ALTER TABLE [db_datareader].[spj] ADD CONSTRAINT [FK__spj__pno__3A81B327] FOREIGN KEY ([pno]) REFERENCES [db_datareader].[p] ([pno])

ALTER TABLE [db_datareader].[spj] ADD CONSTRAINT [FK__spj__sno__398D8EEE] FOREIGN KEY ([sno]) REFERENCES [db_datareader].[s] ([sno])



COMMIT TRANSACTION

GO

vipper23 2010-01-28

打赏
举报

在数据库上右键》任务》生成脚本》下一步》选择数据库》选项里把编写数据的脚本选成TRUE》选择表》....

黄_瓜 2010-01-28

打赏
举报

[Quote=引用 3 楼 josy 的回复:]
SQL code--将表数据生成SQL脚本的存储过程CREATEPROCEDURE dbo.UspOutputData@tablename sysnameASdeclare@columnvarchar(1000)declare@columndatavarchar(1000)declare@sqlvarchar(4000)declare@xtypetinyintdeclare@name sysnam?-
[/Quote]
xuexi

--小F-- 2010-01-28

打赏
举报

要么用导入导出工具

要不用存储过程

****************************************************************************

 软件名称: 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

百年树人 2010-01-28

打赏
举报

--将表数据生成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 你的表名

htl258_Tony 2010-01-28

打赏
举报

用自带的导入导出工具就能将结构与数据一起导入了.

SQL77 2010-01-28

打赏
举报

不行,生成脚本,然后再导入数据吧,

如何生成这个脚本

相关推荐