Insert a new row and update few columns in a table

Please let me know whether I understood your question right.

To insert data from tableb to tableA - specify required columns.

INSERT INTO TABLEA (COLUMN1, COLUMN2, ...)
SELECT COLUMN1, COLUMN2, ... FROM TABLEB

Select allOpen in new window

Update newly inserted records in TableA,
UPDATE TABLEA SET COLUMN1 = <newvalue>, COLUMN2 = <newvalue>
WHERE <specify criteria>

Raj

Thanks for your time.
Actually I know how to this in seperate queries , but I would like to combined these two queries into one. Because I only want to update some columns for newly inserted rows only. Hope it make sense.

Using CASE statement you can mould the records and insert into second table.

For eg:- If you want to update the COLUMN1 values , if it is 100 insert as 1 else 2, query using CASE statement will be as show below.

INSERT INTO TABLEA (COLUMN1, COLUMN2, ...)
SELECT CASE WHEN COLUMN1 = 100 THEN 1 ELSE 2 END
, COLUMN2, ... FROM TABLEB

Select allOpen in new window

Hi RajkumarGs,

I have a insert query like this for now. So, can you please tell me how can i modify this one based on the query and update other columns in table A for only these records I have like 700 records that needs to be inserted into table A.????????????

insert  into TableA([ name])  
  select [name] from tableB
  where not exists (select TableA.[ name] from TableA
  where TableA.[Name]=tableB.[Name])

so after inserting I want to UPDATE for only these names those are not in TableA.
Appreciate your time.Thanks

Which version of SQL Server you are using ?

Raj

Ms SQL SERVER 2008

This is another excellent tutorial by Pinal Dev on MERGE

Raj

Thank you for the link ...But I have a question that still bothers me is:
how can I just modify the col 6 lets say to 'True' when only for newly inserted rows.Because those rows are not initially in the table A. Can you please tell me this point,.????

Please post the final query.

Raj