0

im using PHP and SQL Server i have 2 tables: 1. user (Id_user, user_name) 2. purchase (Id_purchase, Id_user, description)

i want to in insert into "user" and then insert into "purchase" with the same Id_user in "user"

the first insert works, but the problem is in the second table insert, because of the scope identity this is my code 

$sql = "INSERT INTO dbo.user(dbo.user.user_name)values('John') SELECT SCOPE_IDENTITY() AS Id";
            if($result = sqlInsert($dbc, $sql)){
                foreach($result as $r){
                    $myScopeId = $r['Id'];
                }
                $sql = "INSERT INTO dbo.purchase(dbo.purchase.Id_user,dbo.purchase.description)values(".$myScopeId.",'description example')";
                if($result = sqlInsert($dbc, $sql)){
                    $success = "success!";
                }
                else{
                    $warning = "error";
                }   
            }
            else{$warning = "error";}
Roy RW
  • 21
  • 5
  • Are you trying to run a single statement ([`INSERT SELECT`](https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189872(v=sql.105)?redirectedfrom=MSDN)) or two separate statements (an `INSERT` followed by a `SELECT`). You're also overwriting `$myScopeId` in a loop. – Álvaro González May 01 '20 at 18:10
  • Yes, i tried the insert select statement in sql and it worked, it returns an id value, the foreach will only runs onece because its only one scope identity the reponse of the select. But if you have any other idea of getting that scope identity and then use it for the next insert, please let me know – Roy RW May 01 '20 at 18:29
  • I love it when I ask "A or B?" and get "Yes". Whatever, I suspect you just want to use `@@IDENTITY` in the second query. – Álvaro González May 01 '20 at 18:35
  • 1
    @RoyRW Put `SET NOCOUNT ON` as a first line in the first statement. This [answer](https://stackoverflow.com/questions/61174842/correct-way-location-to-use-scope-identity/61202479#61202479) may help. – Zhorov May 01 '20 at 19:13
  • @Zhorov thanks! i added "SET NOCOUNT ON" and insted of sqlInsert i used sqlSelect, it worked :) – Roy RW May 01 '20 at 23:01

0 Answers0