CODEWITHSUNDEEP
sqlsql-server
Omer Bokhari
Omer Bokhari

Reputation: 59578

SQL script to aggregate column values

i'd appreciate some help putting together a sql script to copy data from one table to another. essentially what i need to do for each row in the source table is aggregate the column values and store them into a single column in the target table.

TableA: ID, ColumnA, ColumnB, ColumnC
TableB: Identity, ColumnX

so, ColumnX needs to be something like 'ColumnA, ColumnB, ColumnC'.

in addition though, i need to keep track of each TableA.ID -> SCOPE_IDENTITY() mapping in order to update a third table.

thanks in advance!

EDIT: TableA.ID is not the same as TableB.Identity. TableB.Identity will return a new identity value on insert. so either i need to store the mapping in a temp table or update TableC with each insert into TableB.

Upvotes: 2

Views: 2081

Answers (2)

Fender
Fender

Reputation: 17

Assuming: TableB exists

INSERT INTO TableB (ColumnX)
SELECT [TableA]![ColumnA]+","+[TableA]![ColumnB]+","+[TableA]![ColumnC] AS ColumnX
FROM TableA;

Upvotes: 0

beach
beach

Reputation: 8640

Here is a row-by-row processing example. This will provide you the results in a way where you can process each row at a time. Or you can use TableC at the end and do whatever processing you need to do.

However, it would be a lot faster if you added an extra column to TableB (Called TableA_ID) and just INSERTED the result into it. You would have instant access to TableA.ID and TableB.Identity. But without knowing your exact situation, this may not be feasible. (But you could always add the column and then drop it afterwards!)

USE tempdb
GO

CREATE TABLE TableA (
    ID int NOT NULL PRIMARY KEY,
    ColumnA varchar(10) NOT NULL,
    ColumnB varchar(10) NOT NULL,
    ColumnC varchar(10) NOT NULL
)

CREATE TABLE TableB (
    [Identity]  int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    ColumnX varchar(30) NOT NULL
)

CREATE TABLE TableC (
    TableA_ID  int NOT NULL,
    TableB_ID  int NOT NULL,
    PRIMARY KEY (TableA_ID, TableB_ID)
)

GO

INSERT INTO TableA VALUES (1, 'A', 'A', 'A')
INSERT INTO TableA VALUES (2, 'A', 'A', 'B')
INSERT INTO TableA VALUES (3, 'A', 'A', 'C')
INSERT INTO TableA VALUES (11, 'A', 'B', 'A')
INSERT INTO TableA VALUES (12, 'A', 'B', 'B')
INSERT INTO TableA VALUES (13, 'A', 'B', 'C')
INSERT INTO TableA VALUES (21, 'A', 'C', 'A')
INSERT INTO TableA VALUES (22, 'A', 'C', 'B')
INSERT INTO TableA VALUES (23, 'A', 'C', 'C')
GO

-- Do row-by-row processing to get the desired results
SET NOCOUNT ON
DECLARE @TableA_ID int
DECLARE @TableB_Identity int
DECLARE @ColumnX varchar(100)

SET @TableA_ID = 0

WHILE 1=1 BEGIN

  -- Get the next row to process
  SELECT TOP 1 
    @TableA_ID=ID,
    @ColumnX = ColumnA + ColumnB + ColumnC
  FROM TableA
  WHERE ID > @TableA_ID

  -- Check if we are all done
  IF @@ROWCOUNT = 0
    BREAK

  -- Insert row into TableB
  INSERT INTO TableB (ColumnX)
    SELECT @ColumnX

  -- Get the identity of the new row
  SET @TableB_Identity = SCOPE_IDENTITY()

  -- At this point, you have @TableA_ID and @TableB_Identity.
  -- Go to town with whatever extra processing you need to do
  INSERT INTO TableC (TableA_ID, TableB_ID)
    SELECT @TableA_ID, @TableB_Identity

END
GO

SELECT * FROM TableC
GO

SELECT * FROM TableA

ID          ColumnA    ColumnB    ColumnC
----------- ---------- ---------- ----------
1           A          A          A
2           A          A          B
3           A          A          C
11          A          B          A
12          A          B          B
13          A          B          C
21          A          C          A
22          A          C          B
23          A          C          C

SELECT * FROM TableB

Identity    ColumnX
----------- ------------------------------
1           AAA
2           AAB
3           AAC
4           ABA
5           ABB
6           ABC
7           ACA
8           ACB
9           ACC

SELECT * FROM TableC

TableA_ID   TableB_ID
----------- -----------
1           1
2           2
3           3
11          4
12          5
13          6
21          7
22          8
23          9

Upvotes: 3

Related Questions