如何从 SQL Server 仅生成脚本数据中排除自动生成的列？
SQL Server 生成脚本使用高级选项中“要编写脚本的数据类型”的“仅数据”选项，可以很好地为表中的数据创建脚本。然而，生成的脚本还包括所有标识符，例如 rowid() 和整数 id。可以理解，这是为了引用完整性，但是有没有办法排除此类列？
SQL Server Generate Script does a great job of creating a script for the data in the tables using the Data Only option for 'Types of data to script' in the advanced option. However the script generated also includes all the identifiers such as rowid() and integer ids. Understandably this is for referential integrity, but is there a way to exclude such columns?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
不在 SSMS 本身中。
您可以使用第 3 方工具，例如免费的 SSMS 工具包，它具有“从网格结果生成脚本”选项。因此，您可以为
SELECT col1, col3, col6 FROM MyTable生成 INSERT（跳过某些列）。可能有用...
Not in SSMS itself.
You can use a 3rd party tool such as the free SSMS Tools pack which has a "generate script from grid results" option. So you can generate INSERTs for
SELECT col1, col3, col6 FROM MyTable(skipping some columns).May be useful...
在 SQL Server 中似乎没有办法执行此操作，但快速解决方法是创建一个初始脚本，然后使用它来创建临时表。然后，您可以删除不需要的列，并使用临时表为剩余的列生成 SQL 语句。
假设您想要复制
Events表，但不想包含id：Events生成 SQL Server 脚本。在“高级”选项中，确保您正在复制数据和架构。还要确保您没有编写主键或外键脚本（您仍然会在 SQL 脚本中看到这些列，但这将使您可以更轻松地从临时表中快速删除它们）。Events更改为EventsTemporaryEventsTemporary表添加种子。删除您不想从此表中复制的列，例如id。EventsTemporary表生成第二个脚本，但这次只需复制不带架构的“数据”。在文本编辑器中打开这个新的 SQL 脚本，并将表名称更改回EventsEvents表中。There doesn't seem to be a way to do this in SQL Server, but a quick workaround is to create an initial script and then use that to create a temporary table. Then you can delete the columns you don't want and use the temporary table to generate a SQL statement for the remaining columns.
Let's say you want to copy an
Eventstable, but you don't want to includeid:Events. In "Advanced" options, make sure that you are copying both data and schema. Also make sure you are not scripting primary keys or foreign keys (you will still see the columns in your SQL script, but this will make it easier to quickly delete them from the temporary table).EventstoEventsTemporaryEventsTemporarytable. Delete the columns you don't want to copy from this table, such asid.EventsTemporarytable, but this time just copy "data" without schema. Open this new SQL script in a text editor and change the name of the table back toEventsEventstable.