CODEWITHSUNDEEP
sql
Jui Test
Jui Test

Reputation: 2409

How to select the record from table and insert into another table?

I wanted to select the last record from the table1 and insert into another table .Here is my query.

Insert into table2 values(select top 1 col1,col2 from table1 order by id desc).

I know for adding the value into table,need to be in cotation.But where to add?

Upvotes: 3

Views: 8951

Answers (2)

Bohemian
Bohemian

Reputation: 425358

You can select literals to fill in the other columns that table1 can't provide, something like this:

insert into table2 (col_a, col_b, col_c, col_d)
select top 1 col1, col2, 'foo', 'bar'
from table1
order by id desc

Any columns you do not name in the column list will get the default value, or null if no default is defined.

The number and type of columns selected must match the number and type of columns in the insert column list.

Upvotes: 2

aF.
aF.

Reputation: 66747

In SQL, there are essentially basically two ways to INSERT data into a table: One is to insert it one row at a time, the other is to insert multiple rows at a time. Let's take a look at each of them individually:

INSERT INTO table_name (column1, column2, ...)
VALUES ('value1', 'value2', ...)

The second type of INSERT INTO allows us to insert multiple rows into a table. Unlike the previous example, where we insert a single row by specifying its values for all columns, we now use a SELECT statement to specify the data that we want to insert into the table. If you are thinking whether this means that you are using information from another table, you are correct. The syntax is as follows:

INSERT INTO table1 (column1, column2, ...)
SELECT t2.column3, t2.column4, ...
FROM table2 t2

So, in you case, you can do it like this:

Insert into table2
select top 1 t1.col1,t1.col2 from table1 t1 order by id desc

Or you can use your syntax like this:

declare @col1 type_of_col1, @col2 type_of_col2
select top 1 @col1 = t1.col1, @col2 = t1.col2 from table1 t1 order by id desc

Insert into table2 values(@col1, @col2)

Offcourse, this all works assuming that the column datatypes are matched.

Upvotes: 2

Related Questions