Good table editor for MS SQL Server? [closed]

Question

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).

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

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

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.

Thanks

Answer 1

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

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

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

Answer 2

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.

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).

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.

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.

/*
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