INSERT & SELECT data in same recordset

[Discrusio]

I want to insert an user in a table, and get the ID back in one recordset.

In Microsoft SSMS it works fine, but INSERT and SELECT in same recordset will not work in Labview😡

No errors and no Result data?

Why can't I use INSERT and SELECT in same recordset?

//Jesper

[wiebe@CARYA]

Because it is not supported...

Speculation:

The query might return a multi table, and I have noticed those are not supported.

The code doesn't care the first table is empty (or null), it simply doesn't look further.

[Discrusio]

So if I want to insert data into a table, and get the ID back, it's not possible in LabView?

[wiebe@CARYA]

---

@Discrusio wrote:

So if I want to insert data into a table, and get the ID back, it's not possible in LabView?

---

Of course it is: do 2 queries.

[Discrusio]

I'm new to SQL so maybe I'm doing it the wrong way.

I have created a SQL-procedure with username input.

The procedure checks if the username not present in the table, then it will insert the new user.

Then it will return the index for the user.

The procedure will not work in Labview, because both INSERT and SELECT is present in the procedure.

[wiebe@CARYA]

---

@Discrusio wrote:

I'm new to SQL so maybe I'm doing it the wrong way.

I have created a SQL-procedure with username input.

The procedure checks if the username not present in the table, then it will insert the new user.

Then it will return the index for the user.

The procedure will not work in Labview, because both INSERT and SELECT is present in the procedure.

---

The insert and select query is really two queries. So, split it up into two queries. First do the insert, then the select. Fetch the select results.

[RamonG]

Another option is to create a stored procedure that does the same, but yeah 2 queries might be the better solution.

[Eric1977]

Both suggestions are correct. Running the two queries you have in the very first post is not supported (as you have it written) but you could use a stored procedure as well, but reading that you were a beginner in SQL, I'd pass on that until you are more familiar with the T-SQL language.

This is how I would attack it.

• Check for Bob first (SELECT statement)
• If he isn't there, Insert a record for Bob (INSERT statement)
• Get his information (SELECT statement)

[Discrusio]

I started with a procedure, with INSERT data and SELECT inserted data_id, and it didn't work with Labview.

Then a tried with the simple attached script instead, with same result.

But, now I found a solution.

Instead of the SELECT cmd, I can use OUTPUT INSERTED cmd.

INSERT INTO [dbo].[USERS] ([User_Name])
OUTPUT INSERTED.[USER.ID]
VALUES (@User)

I updated my Procedure with the OUTPUT cmd, so now it will create a new user, if the user not exist, and rerurns the id for the user.

[wiebe@CARYA]

---

@Discrusio wrote:

I started with a procedure, with INSERT data and SELECT inserted data_id, and it didn't work with Labview.

---

It always worked for me.

I think your code didn't work.

---

@Discrusio wrote:

Then a tried with the simple attached script instead, with same result.

---

No attachment

---

@Discrusio wrote:

But, now I found a solution.

Instead of the SELECT cmd, I can use OUTPUT INSERTED cmd.

INSERT INTO [dbo].[USERS] ([User_Name])
OUTPUT INSERTED.[USER.ID]
VALUES (@User)

I updated my Procedure with the OUTPUT cmd, so now it will create a new user, if the user not exist, and rerurns the id for the user.

---

That's nice. Didn't know SQL could do that. Thanks (and a kudo, your 1st!) for teaching me something!