How to concat a table and then insert the results in the original table-CodePudding

I use the following query:

SELECT 
    [ABCDEF],
    RIGHT(CONCAT('0', [ABCDEF), 6) AS ABCDEFnew
FROM 
    dbo.Table1

Now I would like to return ABCDEFnew into dbo.Table1.

Can somebody help me with that?

CodePudding user response：

To append to an existing table try something like:

INSERT INTO
    dbo.Table2
SELECT
    RIGHT(CONCAT('0', [ABCDEF), 6)
FROM
    dbo.Table1

This assumes that Table2 has the same columns as Table1 otherwise you would to need to add the column names.

To create a new table try something like:

SELECT     
    RIGHT(CONCAT('0', [ABCDEF), 6) AS ABCDEFnew
INTO
    dbo.NewTable
FROM 
    dbo.Table1

And to update the original table use something like (but not recommended - see below):

UPDATE
    T1
SET
    T1.ABCDEFnew = RIGHT(CONCAT('0', [ABCDEF), 6)
FROM
    dbo.Table1 AS T1

Or, as suggested by Smor

CREATE VIEW dbo.UpdatedTable1
AS
SELECT 
    [ABCDEF],
    RIGHT(CONCAT('0', [ABCDEF), 6) AS ABCDEFnew
FROM 
    dbo.Table1