MS SQL Server 的好表编辑器？

Good table editor for MS SQL Server?

提问by Victor Rodrigues

I have MS SQL Management Studio for editing table data, and it is doesn't have a good usability. I need to edit some hundred rows like in Excel, being able to order columns to easy editing process (SQL Mgmt only has 'Open table' feature, without ordering columns, updates diferent than that is only possible using UPDATE SQL code).

我有用于编辑表数据的 MS SQL Management Studio，但它没有很好的可用性。我需要像在 Excel 中一样编辑几百行，能够对列进行排序以进行简单的编辑过程（SQL Mgmt 仅具有“打开表”功能，没有对列进行排序，与只能使用 UPDATE SQL 代码才能进行的更新不同）。

LinqPad is wonderful, but only for queries. I would like to edit table results.

LinqPad 很棒，但仅用于查询。我想编辑表格结果。

I installed Acqua Studio and it has everything, but trial expired. Do you know any software free alternatives which can do that?

我安装了 Acqua Studio，它拥有一切，但试用期已过。您知道任何可以做到这一点的无软件替代品吗？

EDIT: I really need to alter and input data, of course I can do it by SQL code, but it is not fast when you have to update manually tons of rows. I need an editable ordered grid. I'll try MSManager Lite.

编辑：我真的需要更改和输入数据，当然我可以通过 SQL 代码来完成，但是当您必须手动更新大量行时，速度并不快。我需要一个可编辑的有序网格。我会尝试 MSManager Lite。

Thanks

谢谢

回答by x0n

I have this tool permanently on a USB stick - really, really good for a free "lite" edition (a pro version is available too)

我在 U 盘上永久安装了这个工具 - 真的非常适合免费的“精简版”（专业版也可用）

http://sqlmanager.net/products/mssql/manager

http://sqlmanager.net/products/mssql/manager

It is a single monolithic exe, so great for portability.

它是一个单一的整体 exe，非常便于携带。

回答by Jon Erickson

I would suggest learning the necessary SQL to update the appropriate data in the tables. You can use SELECT statements with ORDER BY clauses to view the data in the order that you wish to view it, and then build a query to update that data.

我建议学习必要的 SQL 来更新表中的适当数据。您可以使用带有 ORDER BY 子句的 SELECT 语句以您希望查看的顺序查看数据，然后构建一个查询来更新该数据。

You can use transactions to make sure what your updating is correct as you go (if you are still learning the SQL and don't want to mess up the database).

您可以使用事务来确保您的更新是正确的（如果您仍在学习 SQL 并且不想弄乱数据库）。

BEGIN TRANSACTION -- starts a transaction
ROLLBACK          -- stops the transaction and rolls back all changes to the tables
COMMIT            -- stops the transaction and commits all changes to the tables

What are you trying to accomplish/update, maybe we can help you with that?

您想完成/更新什么，也许我们可以帮助您？

EDIT

编辑

You mentioned that you wanted to edit some product names that are stored inside of a table. and that this would be a one-time task. I've set up a small demo below that I hope will help guide you towards a solution that may work for your situation. copy and paste this into a SQL Management Studio session.

您提到要编辑一些存储在表中的产品名称。并且这将是一项一次性任务。我在下面设置了一个小演示，希望能帮助您找到适合您情况的解决方案。将其复制并粘贴到 SQL Management Studio 会话中。

Also if you wanted, you can export your current data to say excel, edit that data in excel, import it as a new temporary table and run a SQL update script to update the original table.

此外，如果您愿意，您可以将当前数据导出为 excel，在 excel 中编辑该数据，将其作为新的临时表导入并运行 SQL 更新脚本来更新原始表。

/*
Products Before Update          Products After Update
===========================     =============================================
ID      ProductName             ID      ProductName
---------------------------     ---------------------------------------------
1       MSFT                    1       Microsoft Corp.
2       APPL                    2       Apple Inc.
3       Cisco Systems, Inc.     3       Cisco Systems, Inc.
4       IBM                     4       International Business Machines Corp.
5       JAVA                    5       Sun Microsystems, Inc.
6       ORCL                    6       Oracle Corp.
*/

-- Imagine that this table is a table in your database
DECLARE @products TABLE (
                        ID          INT,
                        ProductName VARCHAR(255)
                        )

-- And this table has some product information
-- which you are trying to update with new information
INSERT  @products
SELECT  1, 'MSFT' UNION ALL
SELECT  2, 'APPL' UNION ALL
SELECT  3, 'Cisco Systems, Inc.' UNION ALL
SELECT  4, 'IBM' UNION ALL
SELECT  5, 'JAVA' UNION ALL
SELECT  6, 'ORCL'

-- Either build an in-memory temporary table of the product names you wish to update
-- Or do a database task to import data from excel into a temporary table in the database
DECLARE @products_update TABLE  (
                                ID          INT,
                                ProductName VARCHAR(255)
                                )

INSERT  @products_update
SELECT  1, 'Microsoft Corp.' UNION ALL
SELECT  2, 'Apple Inc.' UNION ALL
SELECT  4, 'International Business Machines Corp.' UNION ALL
SELECT  5, 'Sun Microsystems, Inc.' UNION ALL
SELECT  6, 'Oracle Corp.'

-- Update the table in the database with the in-memory table
-- for demo purposes, we use @products to represent the database table
UPDATE      p1
SET         ProductName = ISNULL(p2.ProductName, p1.ProductName)
FROM        @products p1
LEFT JOIN   @products_update p2
        ON  p1.ID = p2.ID

-- Now your products table has been updated
SELECT      *
FROM        @products

回答by gbn

MS Access allows some flexibility: there's a good chance you'll have that installed already.

MS Access 提供了一些灵活性：您很有可能已经安装了它。

Either an adp or mdb (with linked tables) should allow you to do what you want (easier sorting, reordering columns etc to manage data.

adp 或 mdb（带有链接表）应该允许你做你想做的事情（更容易排序、重新排序列等来管理数据。

回答by Ted Johnson

You might consider just using Excel.http://support.microsoft.com/kb/306397/EN-US/I Believe there are also Excel plug-ins/VB Macros that will make this integration tighter.

您可能会考虑只使用 Excel。http://support.microsoft.com/kb/306397/EN-US/我相信还有 Excel 插件/VB 宏可以使这种集成更加紧密。

Otherwise MS Access is a reasonable answer, stated as well by gbn.

否则 MS Access 是一个合理的答案，也由 gbn 声明。

回答by John Saunders

Consider Apex SQL Edit. It has all the features you mentioned, and more, though it's not free.

考虑Apex SQL 编辑。它具有您提到的所有功能以及更多功能，但它不是免费的。

BTW, if you need only free products, you should edit your subject line to say so.

顺便说一句，如果你只需要免费产品，你应该编辑你的主题行来说明。

回答by ChrisE

You can do it straight in SQL Server Management Studio. When you right-click the table an option is to "edit top 200 rows". You can extend that by going to Tools->Options and clicking on SQL Server Object Explorer. Change the "Value for edit top rows" to 0 to edit all rows.

您可以直接在 SQL Server Management Studio 中完成。当您右键单击表格时，一个选项是“编辑前 200 行”。您可以通过转到工具->选项并单击 SQL Server 对象资源管理器来扩展它。将“编辑顶行的值”更改为 0 以编辑所有行。

(Answer taken from previous postby user Adam Maras)

（答案取自用户 Adam Maras之前的帖子）

回答by Gromer

Use SQL Server Management Studio. You are using the Open function with a table, correct (right click the table -> Open)? Still do that, but look for the highlighted button:

使用 SQL Server 管理工作室。您正在对表格使用打开功能，正确（右键单击表格 -> 打开）？仍然这样做，但寻找突出显示的按钮：

http://www.bgalert.com/images/ssms1.jpg

http://www.bgalert.com/images/ssms1.jpg

You can then add an order by or whatever you feel like and hit the execute button as highlighted:

然后，您可以添加一个订单或任何您喜欢的东西，然后点击突出显示的执行按钮：

http://www.bgalert.com/images/ssms2.jpg

http://www.bgalert.com/images/ssms2.jpg