Insert All Values of a Table into Another Table in SQL

Insert all values of a table into another table in SQL

The insert statement actually has a syntax for doing just that. It's a lot easier if you specify the column names rather than selecting "*" though:

INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
SELECT Foo, Bar, Fizz, Buzz
FROM initial_table
-- optionally WHERE ...

I'd better clarify this because for some reason this post is getting a few down-votes.

The INSERT INTO ... SELECT FROM syntax is for when the table you're inserting into ("new_table" in my example above) already exists. As others have said, the SELECT ... INTO syntax is for when you want to create the new table as part of the command.

You didn't specify whether the new table needs to be created as part of the command, so INSERT INTO ... SELECT FROM should be fine if your destination table already exists.

Inserting values from one table into another table in SQL Server

declare @fields table(id int, fieldid int, fieldname varchar(20), fieldtype varchar(100), tableid int)

insert into @fields  
values (1, 1001, 'empno', 'int', 101),
       (2, 1002, 'empname', 'varchar(50)', 101),
       (3, 1003, 'deptno', 'int', 102),
       (4, 1004, 'dname', 'varchar(50)', 102);




declare  @tablecontents table (id int, fieldid int, entityid int, value varchar(max));

insert into @tablecontents
values (1, 1001, 10001, '501'), (2, 1002, 10001, 'PAUL'),
       (3, 1001, 10002, '502'), (4, 1002, 10002, 'RAJ'),
       (5, 1003, 10003, '10'), (6, 1004, 10003, 'computer');

with data as
(
select f.fieldname, c.value, c.entityid 
from @tablecontents c
     join @fields f
        on c.fieldid = f.fieldid
where f.fieldname in ('empno', 'empname')
)

select [empno], [empname]
from data d pivot (max(value) for fieldname in ([empno], [empname]))p;

insert into a table from another table with different columns

This should work I think

INSERT INTO A (load, Roll, dest)
SELECT load, Roll, dest
FROM B

How to insert from one table into another with extra values in SQL?

Just add the values onto the select line along with the columns in the column list:

INSERT INTO Table2 ([Id], [Item Description], [Item Cost], [Order Date])
    SELECT NEWID(), Description, Cost, '2014-12-13'
    FROM  Table1
    WHERE Id = '1';

SQL INSERT INTO from multiple tables

You only need one INSERT:

INSERT INTO table4 ( name, age, sex, city, id, number, nationality)
SELECT name, age, sex, city, p.id, number, n.nationality
FROM table1 p
INNER JOIN table2 c ON c.Id = p.Id
INNER JOIN table3 n ON p.Id = n.Id

How can I insert values from a nested table into another table?

You don't need a procedure for this. You can do this with an INSERT ... SELECT from the countries cross joining the nested tables.

INSERT INTO prod_country_ai (year, amount, country_fk)
    SELECT 
        p.year, p.amount, c.id
    FROM 
        country c
    CROSS JOIN TABLE(c.prod_an) p;

How do I insert values from one table into another table?

Just replace LOB with Group in the SELECT

INSERT INTO TableA (Key, ID,Name,LOB,a,b,c ) 
SELECT Key, ID,Name,Group,a,b,c 
FROM Table B 
WHERE Key = "blah"

How to insert new records from one table into another table

SELECT the fields from only one table (tbl_Daily.*) instead of all the fields returned in the SELECT * result set.

INSERT INTO tbl_Cumulative
SELECT tbl_Daily.*
FROM tbl_Daily
LEFT JOIN tbl_Cumulative
ON tbl_Cumulative.ID= tbl_Daily.ID
WHERE tbl_Cumulative.ID IS NULL

---

Related Topics