สอบถามการ Query sqlsrv โดยให้เรียงรายการและแยกข้อมูลตามรายการนั้นๆ

SELECT 
  b.bill_number, 
  bo.box_number, 
  w.weight_number 
FROM 
  bill_box_weight bbw 
  INNER JOIN bill b ON bbw.bill_id = b.bill_id 
  INNER JOIN box bo ON bbw.box_id = bo.box_id 
  INNER JOIN weight w ON bbw.weight_id = w.weight_id 
ORDER BY 
  b.bill_number ASC;

CREATE TABLE bill (
    bill_id INT IDENTITY(1,1) PRIMARY KEY,
    bill_number VARCHAR(255) NOT NULL
);

CREATE TABLE box (
    box_id INT IDENTITY(1,1) PRIMARY KEY,
    box_number VARCHAR(255) NOT NULL
);

CREATE TABLE weight (
    weight_id INT IDENTITY(1,1) PRIMARY KEY,
    weight_number VARCHAR(255) NOT NULL
);

CREATE TABLE bill_box_weight (
    bill_id INT NOT NULL,
    box_id INT NOT NULL,
    weight_id INT NOT NULL,
    FOREIGN KEY (bill_id) REFERENCES bill (bill_id),
    FOREIGN KEY (box_id) REFERENCES box (box_id),
    FOREIGN KEY (weight_id) REFERENCES weight (weight_id),
    PRIMARY KEY (bill_id, box_id, weight_id)
);

// Connect to the database
$serverName = "serverName\sqlexpress";
$connectionOptions = array(
    "Database" => "old_database",
    "UID" => "username",
    "PWD" => "password"
);
$conn = sqlsrv_connect($serverName, $connectionOptions);

// Query ข้อมูลจากตารางเดิม
$oldData = array();
$query = "SELECT bill1, box1, weight1 FROM old_table";
$stmt = sqlsrv_query($conn, $query);
if ($stmt === false) {
    die(print_r(sqlsrv_errors(), true));
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    $oldData[] = $row;
}

// Insert ข้อมูลไปยังตารางใหม่
// Loop แต่ละแถวจากข้อมูลเก่า (อาร์เรย์ $oldData)
foreach ($oldData as $row) {
    // Insert ตาราง 'bill'
    $billQuery = "INSERT INTO bill (bill_number) VALUES (?)";
    $billParams = array($row['bill1']);
    $billStmt = sqlsrv_prepare($conn, $billQuery, $billParams);
    sqlsrv_execute($billStmt);

    // Insert ตาราง 'box'
    $boxQuery = "INSERT INTO box (box_number) VALUES (?)";
    $boxParams = array($row['box1']);
    $boxStmt = sqlsrv_prepare($conn, $boxQuery, $boxParams);
    sqlsrv_execute($boxStmt);

    // Insert ตาราง 'weight'
    $weightQuery = "INSERT INTO weight (weight_number) VALUES (?)";
    $weightParams = array($row['weight1']);
    $weightStmt = sqlsrv_prepare($conn, $weightQuery, $weightParams);
    sqlsrv_execute($weightStmt);

    // เก็บค่า ID จากแถวที่ insert
    $bill_id = sqlsrv_query($conn, "SELECT SCOPE_IDENTITY()")->fetch()[0];
    $box_id = sqlsrv_query($conn, "SELECT SCOPE_IDENTITY()")->fetch()[0];
    $weight_id = sqlsrv_query($conn, "SELECT SCOPE_IDENTITY()")->fetch()[0];

    // Insert ความสัมพันธ์ของข้อมูลในตาราง 'bill_box_weight'
    $bbwQuery = "INSERT INTO bill_box_weight (bill_id, box_id, weight_id) VALUES (?, ?, ?)";
    $bbwParams = array($bill_id, $box_id, $weight_id);
    $bbwStmt = sqlsrv_prepare($conn, $bbwQuery, $bbwParams);
    sqlsrv_execute($bbwStmt);
}