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

[buzza1234]

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

[#2]

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.

[#3]

@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.

[#4]

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