Forum Discussion
buzza1234
Jun 17, 2024Copper Contributor
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 sa...
olafhelper
Jun 17, 2024Bronze Contributor
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.
- buzza1234Jun 17, 2024Copper 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.
- olafhelperJun 18, 2024Bronze Contributor
buzza1234 I don't see any logical relation between the tables nor do I know which result you expect here?