How can I update the data row by row by the loop after insert those records into another table in mssql?

Question

Does anyone know how can I update the data row by row by the loop after insert those records into another table in mssql?

Example: I have the following table (tableA)

ID    Name    is_Feeded
1     Alvin   0
2     Ben     0
3     Lee     1
4     David   0

I want to insert those table from tableA to tableB then update the column is_Feeded to 1 in tableA through a loop?

Anyone know how can I do it in mssql?

Q: Why do you need a loop? Look at this link: [SQ Insert into values Select From](http://stackoverflow.com/questions/25969/sql-insert-into-values-select-from) — paulsm4, Sep 16 '13 at 05:42

ErikE · Answer 1 · 2013-09-16T05:54:44.267

Assuming SQL Server 2005 or higher, you can do this in a single statement.

UPDATE A
OUTPUT
   inserted.ID,
   inserted.Name
INTO
   dbo.TableB (ID, Name)
SET
   A.is_Feeded = 1 -- is fed?
FROM
   dbo.tableA A
WHERE
   A.is_Feeded = 0
;

A trigger is also possible, but I don't recommend using one if you can avoid it. If you must to use a trigger (such as perhaps a case where you can't control updates to tableA) then:

CREATE TRIGGER TableA_U ON dbo.TableA FOR UPDATE
AS
INSERT dbo.tableB (ID, Name)
SELECT
   I.ID,
   I.Name
FROM
   inserted I
;

To me it is more natural to insert to tableB based on an update to tableA than to update tableA in response to an insert to tableB.

score 1 · Answer 2 · answered Sep 16 '13 at 05:43

1

I would write a trigger for tableB. After you insert a row there, the trigger can update the specific value in tableA

answered Sep 16 '13 at 05:43

OkieOth

3,604
1
19
29

score 0 · Answer 3 · answered Sep 16 '13 at 05:44

First copy data from tableA to tableB

INSERT INTO tableB
  SELECT Name, id FROM tableA;

then set is feeded:

UPDATE tableA SET is_feeded = true

and finally you should do this in one transaction (syntax depends on your DB system, e.g. MySQL: http://dev.mysql.com/doc/refman/5.0/en/commit.html)

score 0 · Answer 4 · answered Sep 16 '13 at 05:47

0

you should directly add update field is_feeded when inserting data to TABLEB.

answered Sep 16 '13 at 05:47

Gopal Joshi

2,350
22
49

score 0 · Answer 5 · answered Sep 16 '13 at 06:22

CREATE PROCEDURE xxxxx
AS 
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
        SET NOCOUNT ON ;
        DECLARE @iOK INT ;

        SET @iOK = 0 ;

        BEGIN TRY
            BEGIN TRANSACTION    -- Start the transaction


        --start Inserting --
            INSERT  INTO tableB
                    SELECT  Name ,
                            id
                    FROM    tableA ;

            UPDATE  tableA
            SET     is_feeded = true    

        -- If we reach here, success!
            COMMIT

            SET @iOK = 1 ;
        END TRY
        BEGIN CATCH
        -- Whoops, there was an error
            IF @@TRANCOUNT > 0 
                ROLLBACK

        -- Raise an error with the details of the exception
            DECLARE @ErrMsg NVARCHAR(4000) ,
                @ErrSeverity INT
            SELECT  @ErrMsg = ERROR_MESSAGE() ,
                    @ErrSeverity = ERROR_SEVERITY()

            RAISERROR(@ErrMsg, @ErrSeverity, 1)
        END CATCH

        SELECT  @iOK ;
    END

How can I update the data row by row by the loop after insert those records into another table in mssql?

5 Answers5