I'm interested in adding a column to an existing table with a set of explicit values that should duplicate existing records (similar to common join constructs).
For example, say we're starting with a table with a single column:
CREATE TABLE #DEMO (
COLUMN_A NVARCHAR(100) NOT NULL
PRIMARY KEY (COLUMN_A)
);
| COLUMN_A |
|---|
| ACCOUNT_001 |
| ACCOUNT_002 |
| ACCOUNT_003 |
...and I want to add Column_B with row values of 'A', 'B', and 'C'. The end goal would be a table that looks like:
| COLUMN_A | COLUMN_B |
|---|---|
| ACCOUNT_001 | A |
| ACCOUNT_001 | B |
| ACCOUNT_001 | C |
| ACCOUNT_002 | A |
| ACCOUNT_002 | B |
| ACCOUNT_002 | C |
| ACCOUNT_003 | A |
| ACCOUNT_003 | B |
| ACCOUNT_003 | C |
Is this possible? Bonus Points if there is a name or phrase for this you know of.
CodePudding user response：
So I think you need couple of steps to first insert new rows and then update existing:
alter table #demo add COLUMN_B char(1);
with x as (
select * from (values('A'),('B'))x(B)
)
insert into #demo(COLUMN_A, COLUMN_B)
select COLUMN_A, B
from #DEMO cross join x
update #DEMO set COLUMN_B = 'C'
where COLUMN_B is null