SQL Select in an Insert statement

sql insert multiple rows from select
sql insert into select
insert into table from another table sql server
sql select into
create insert statement from select
how to insert data from one database table to another database table in sql server
insert into select mysql
insert into select oracle

How can I combine these two scripts? I basically don't want to hard code the number 34 in Query 2. I want the script to take the next number of job_id. Query 1 returns 34, so Query 2 should take 35 as the job_id.

Query 1

select top 1 job_id from job
order by job_id desc

Output of query 1 - 34

Query 2

insert into job (job_id, name)
values (35, 'Defend them')

There is no need to use order by job_id desc and top 1 to get the value you want. You need max(job_id) + 1

insert into job (job_id, name)
select max(job_id) + 1, 'Defend them'
from job

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. The SQL INSERT INTO Statement. The INSERT INTO statement is used to insert new records in a table. INSERT INTO Syntax. It is possible to write the INSERT INTO statement in two ways. The first way specifies both the column names and the values to be inserted:

You do this by defining job_id as an identity column:

create table jobs (
     job_id int identity(1, 1) primary key,
     name varchar(255)
);

Then you can just do:

insert into jobs (name)
    values ('Defend them');

And SQL Server assigns the job id.

SQL INSERT INTO SELECT Statement, With SQL, you can copy information from one table into another. The INSERT INTO SELECT statement copies data from one table and inserts it into an existing​  insert into tableX (a_id, b_id) SELECT service_id, 4 as QUESTIONMARK FROM tableY WHERE id in (10, 2); This works on many database engines however we don't know what environment you are working on. EDIT: What is the second insert supposed to be?

How about this?

insert into job (job_id, name)
select top 1 job_id+1, 'Defend them'
from job
order by job_id desc

Insert into values ( SELECT FROM ), Try: INSERT INTO table1 ( column1 ) SELECT col1 FROM table2. This is standard ANSI SQL and should work on any DBMS. It definitely works  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.

select     top 1 job_id, 'Defend them' 
into       job (job_id, name)
from       job
order by   job_id DESC

SQL INSERT INTO SELECT Statement, SQL INSERT INTO SELECT Statement. How do I copy data between tables? INSERT INTO SELECT copies data from one table to another table. INSERT INTO  not good, i need exactly that one field be select statement i cant use insert with one select – Sasha Jun 24 '09 at 11:59 6 The answer is right, just try using the select statement in a new query, it will return just what you are looking for.

INSERT INTO SELECT statement overview and examples, With SQL, you can copy information from one table into another. The INSERT INTO SELECT statement copies data from one table and inserts it into an existing​  the following SQL statement can be used: SQL Code: INSERT INTO agent1 SELECT * FROM agents WHERE agent_code=ANY( SELECT agent_code FROM customer WHERE agent_code =ANY( SELECT agent_code FROM orders WHERE advance_amount>600) GROUP BY agent_code);

SQL Server, In SQL, we use the SQL INSERT INTO statement to insert records. The syntax of the INSERT INTO. Once we insert data into the table, we can use  Click Advanced, find the "Types of data to Script" property, select "Data only", close the advanced properties. Select "Save to new query window" (unless you have thousands of records). Click Next, wait for the job to complete, observe the resulting INSERT statements appear in a new query window.

13.2.6.1 INSERT SELECT Statement, 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  The SQL Insert Into Select Statement can be used to insert the data into SQL Server tables. This Sql Server Insert into Select Statement will insert the records selected by the SELECT Statement into the existing table.

Comments
  • A brief explanation of how/why this works would be very helpful to future readers.