Forum Discussion

buzza1234's avatar
buzza1234
Copper Contributor
Jun 17, 2024

how to merge data from one table to another, student id assign

Good Morning,

 

how to assign query one results to query 2 student id, each record should have each value, rather assigning one to all 20k students. please advise. i tried one but it is assigned same id to all STG_Student. please

 

-- Query 1
SELECT STATESTUDENTID
FROM DEV_SLDS.LEGACY.STUDENTIDPOOL
WHERE IDSTATUS = 'UNASSIGNED'
AND DATEINITIALASSIGNED IS NULL
--- This query resylts gives several thousands of studentid numbers that are never assigned to student.


-- Query 2 this STATEIDENTIFICATIONNUMBER need to populate with query 1 each record should have one unique record from query 1
SELECT * FROM STAGE.STG_STUDENT s
SET STATEIDENTIFICATIONNUMBER = Assign from STUDENTIDPOOL.STATESTUDENTID
WHERE COMMENTS = 'NEW ID FROM POOL REQUIRED'
and trim(FILEID) = trim('a0xHv000000fNWqIAM ')
AND STATEIDENTIFICATIONNUMBER IS NULL;

 

 

Thank you,

Asit

 

Data Warehouse

3 Replies

Sort By
  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jun 17, 2024
    We don't have your database to test it, so please post table design as DDL, some sample data as DML statement and the expected result.
    • buzza1234's avatar
      buzza1234
      Copper Contributor
      Jun 17, 2024

      olafhelper 

       

      Here is details please help.

      Thank you much in advance

      CREATE TABLE STUDENTIDPOOL (
    ID INT IDENTITY(1,1) NOT NULL,
    STATESTUDENTID NUMERIC(10,0),
    IDSTATUS VARCHAR(MAX) DEFAULT 'UNASSIGNED',
    DATECREATED DATETIMEOFFSET DEFAULT GETDATE(),
    DATEINITIALASSIGNED DATETIMEOFFSET,
    CREATEDBY NVARCHAR(50) DEFAULT SUSER_SNAME(),
    PRIMARY KEY (ID)
);

INSERT INTO STUDENTIDPOOL (STATESTUDENTID, IDSTATUS, DATECREATED, DATEINITIALASSIGNED, CREATEDBY)
VALUES 
    (1234567890, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
    (9876543210, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
    (5555555555, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
    (7777777777, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
    (8888888888, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
    (9999999999, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
    (1111111111, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
    (2222222222, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
    (3333333333, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME()),
    (4444444444, 'UNASSIGNED', GETDATE(), NULL, SUSER_SNAME());

----- So above query has randon studentid that are unassigned yet to any students.

CREATE TABLE STG_STUDENT (
    ID INT IDENTITY(1,1) NOT NULL,
    FILEID VARCHAR(40),
    INSERTDATETIME DATETIMEOFFSET,
    COMMENTS VARCHAR(1000),
    STATEIDENTIFICATIONNUMBER NUMERIC(10,0),
    FIRSTNAME VARCHAR(40),
    MIDDLENAME VARCHAR(40),
    LASTNAME VARCHAR(50),
    GENDER VARCHAR(40),
    DATEOFBIRTH VARCHAR(10),
    CITYOFBIRTH VARCHAR(40),
    STATEOFBIRTH VARCHAR(40),
    COUNTRYOFBIRTH CHAR(6)
);


INSERT INTO STG_STUDENT (FILEID, INSERTDATETIME, COMMENTS, STATEIDENTIFICATIONNUMBER, FIRSTNAME, MIDDLENAME, LASTNAME, GENDER, DATEOFBIRTH, CITYOFBIRTH, STATEOFBIRTH, COUNTRYOFBIRTH)
VALUES 
    ('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'John', 'Michael', 'Doe', 'Male', '1990-01-01', 'New York', 'NY', 'USA'),
    ('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Jane', NULL, 'Smith', 'Female', '1985-05-15', 'Los Angeles', 'CA', 'USA'),
    ('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Michael', 'James', 'Johnson', 'Male', '1982-09-30', 'Chicago', 'IL', 'USA'),
    ('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Sarah', NULL, 'Brown', 'Female', '1995-03-20', 'Houston', 'TX', 'USA'),
    ('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'William', 'Robert', 'Davis', 'Male', '1993-07-10', 'Phoenix', 'AZ', 'USA'),
    ('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Emily', 'Grace', 'Anderson', 'Female', '1988-11-25', 'Philadelphia', 'PA', 'USA'),
    ('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Daniel', 'Thomas', 'Wilson', 'Male', '1991-04-05', 'San Diego', 'CA', 'USA'),
    ('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Jessica', NULL, 'Martinez', 'Female', '1987-06-12', 'Dallas', 'TX', 'USA'),
    ('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Matthew', 'Andrew', 'Taylor', 'Male', '1984-08-18', 'Miami', 'FL', 'USA'),
    ('a80sdl20a390aXyski', GETDATE(), 'NEW ID FROM POOL REQUIRED', NULL, 'Lauren', 'Elizabeth', 'Clark', 'Female', '1994-02-28', 'Seattle', 'WA', 'USA');


Here we need to assign studentid from pool table to the STG_STUDENT.STATEIDENTIFICATIONNUMBER one id for each one record.

       

       

      • olafhelper's avatar
        olafhelper
        Bronze Contributor
        Jun 18, 2024

        buzza1234 I don't see any logical relation between the tables nor do I know which result you expect here?

Resources