SELECT and INSERT in SQL Server

insert into table from another table sql server
insert into select oracle
insert into sql multiple rows
insert into sql server
insert into select postgres
insert into select mysql
sql select into
create table as select sql server

Can you help me please? I wrote this code and I get an error

Incorrect syntax near the keyword SELECT

Here is my code

CREATE PROCEDURE dbo.spSetCommand
    @Client_id INT,
    @BcNumber INT,
    @ArticleId INT,
    @EntryNumber DECIMAL
AS
    DECLARE @CommandId INT 
BEGIN
    INSERT INTO commands(client_id, bc_number, date_command) 
    VALUES (@Client_id, @BcNumber, GETDATE());

    SET @CommandId = SELECT command_id 
                     FROM commands 
                     WHERE bc_number = @BcNumber;

    INSERT INTO entries(command_id, article_id, entry_number)  
    VALUES (@CommandId, @ArticleId, @EntryNumber);
END

Can someone help find the issue?

-- need to have brackets
set @CommandId = (SELECT command_id FROM commands WHERE bc_number = @BcNumber);

if there are multiple results from the query, this will fail, an error will come.

you can also use this:

SELECT @CommandID = command_id 
FROM commands
WHERE bc_number = @BcNumber

In case of multiple results, this will give you the last value (which may/may not be your logical thing)

SQL Server INSERT INTO SELECT By Practical Examples, This tutorial shows you how to use the SQL Server INSERT INTO SELECT statement to insert data from other tables into a table. Introduction to SQL Server INSERT INTO SELECT statement To insert data from other tables into a table, you use the following SQL Server INSERT INTO SELECT statement: INSERT [ TOP (expression) [ PERCENT ] ] INTO target_table (column_list) query In this syntax, the statement inserts rows returned by the query into the target_table.

In SQL SELECT SCOPE_IDENTITY() Return Recent add record Identity Key in your case command_id

CREATE PROCEDURE dbo.spSetCommand
        @Client_id INT,
        @BcNumber INT,
        @ArticleId INT,
        @EntryNumber DECIMAL
    AS
        DECLARE @CommandId INT 
    BEGIN
        INSERT INTO commands(client_id, bc_number, date_command) 
        VALUES (@Client_id, @BcNumber, GETDATE());
SET @CommandId = SELECT SCOPE_IDENTITY()
INSERT INTO entries(command_id, article_id, entry_number)  
        VALUES (@CommandId, @ArticleId, @EntryNumber);
    END

There is an error in the below line code:

SET @CommandId = SELECT command_id 
                     FROM commands 
                     WHERE bc_number = @BcNumber;

if there are multiples records exists WHERE bc_number = @BcNumber then this will generate an error other wise working fine.

Atrenate way is below:

SELECT @CommandId =command_id  commands WHERE bc_number = @BcNumber;

but the most appropriate way is to get recent added record in current table by current instant you can use SCOPE_IDENTITY() SQL function

INSERT (Transact-SQL), However, starting with SQL Server 2016 (13.x) and database compatibility level 130, a single INSERT INTO … SELECT statement can be  By: Jeremy Kadlec Overview. In some of our earlier examples we used the a SELECT statement to verify the results of the INSERT statements previously issued. As you know, the INSERT command adds records to a table and the SELECT statement retrieves data from one or more tables.

SET @CommandId = SELECT command_id 
FROM commands 
WHERE bc_number = @BcNumber;

Instead try

SELECT @CommandId = top 1 command_id 
FROM commands 
WHERE bc_number = @BcNumber order by command_id desc;

I use set @foo = some int like 4; and select @foo = some query;

SQL INSERT INTO SELECT Statement | Values, SQL INSERT SELECT INTO. Problem: Copy all Canadian suppliers into the Customer table. INSERT INTO Customer (FirstName, LastName, City, Country,  The syntax for the SQL Server INSERT statement when inserting a single record using the DEFAULT VALUES keyword is: INSERT INTO table (column1, column2, ) DEFAULT VALUES; Or In its simplest form, the syntax for the SQL Server INSERT statement when inserting multiple records using a sub-select is: INSERT INTO table (column1, column2,

Insert into values ( SELECT FROM ), Oracle; MS SQL Server; MySQL; Postgres; SQLite v3; Teradata; DB2; Sybase; Vertica; HSQLDB; H2; AWS RedShift; SAP HANA. Here, we create a stored procedure with SELECT, INSERT, UPDATE, and DELETE SQL statements. The SELECT SQL statement is used to fetch rows from a database table. The INSERT statement is used to add new rows to a table. The UPDATE statement is used to edit and update the values of an existing record.

SQL Server, You can use SELECT FROM statement to retrieve data from this table, then use an INSERT INTO to add that set of data into another table, and  INSERT INTO dbo.Destination (Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM dbo.Source If you had tables with the same columns or a result set that had the same columns as your destination, you don't have to specify columns in the INSERT. INSERT INTO dbo.Destination SELECT * FROM dbo.Source

INSERT INTO SELECT statement overview and examples, SQL Server INSERT INTO SELECT Error messsage. We can map the column between the source and destination table using the following  The following Transact-SQL statements insert three rows into the PointValue column of the Points table. This column uses a CLR user-defined type (UDT). The Point data type consists of X and Y integer values that are exposed as properties of the UDT.

Comments
  • I've never seen a DECLARE before BEGIN. Not sure if that might be your issue
  • @Rakotoarisoa try this one. i hope this will work for you
  • Thanks, it works. And I made a mistake to put the declare outside of the begin