Insert Statement Cannot Contain A Select Statement -sql Server2012

declare @us table(name nvarchar(100),lastname nvarchar(100),UID bigint,available bit); declare @Name nvarchar(100), @lastname nvarchar(100), @UI

Solution 1:

Based on the information provided in the question, the variables seem to be unnecessary. You can directly do INSERT...SELECT like so:

declare@ustable(name nvarchar(100),lastname nvarchar(100),UID bigint,available bit);

insertinto@usselect name,lastname,UID,available 
from Users 
where available='1'select*from@us

Solution 2:

Alternate and complex way of doing the same task is the below one. Hope nobody likes this. Just I am showing we have one more alternative way.

declare@ustable(name nvarchar(100),lastname nvarchar(100),UID bigint,available bit);
declare@Name nvarchar(100), @lastname nvarchar(100), @UIDbigint,@Avail bit

DECLARE ALLRECORDS CURSORFORSELECT name,lastname,UID,available from Users where available='1'OPEN ALLRECORDS
     FETCH NEXT FROM ALLRECORDS INTO@Name,@lastname,@UID,@Avail

        WHILE @@FETCH_STATUS =0BEGININSERTINTO@usSELECT@Name,@lastname,@UID,@AvailFETCH NEXT FROM ALLRECORDS INTO@Name,@lastname,@UID,@AvailENDCLOSE ALLRECORDS
DEALLOCATE ALLRECORDS
SELECT*FROM@us