Forum Discussion
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
3 Replies
- olafhelperBronze ContributorWe 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.
- buzza1234Copper Contributor
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.
- olafhelperBronze Contributor
buzza1234 I don't see any logical relation between the tables nor do I know which result you expect here?