How to Do Insert into a Table Records Extracted from Another Table

How to do INSERT into a table records extracted from another table

No "VALUES", no parenthesis:

INSERT INTO Table2(LongIntColumn2, CurrencyColumn2)
SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1;

Select data from another table during insert

Use insert . . . select instead of insert . . . values:

insert into table2 
    select null, 12, max(odate), 0
    from table1;

As a note: you should get in the habit of including the column names in the insert() statement.

mySQL :: insert into table, data from another table?

INSERT INTO action_2_members (campaign_id, mobile, vote, vote_date)  
SELECT campaign_id, from_number, received_msg, date_received
  FROM `received_txts`
 WHERE `campaign_id` = '8'

insert into table values: from another table and one value is identity

It depends if you need the identity value to be the same from the other table, in which case use:

-- SET IDENTITY_INSERT to ON.  
SET IDENTITY_INSERT TABLE_NAME ON;  
GO

remember to put it off after.

Extract column values from one table and insert with modifications into another

-- DROP FUNCTION alt_edger(_s text, _v text, _relation text, _tbl text, _tbl_src text)
CREATE OR REPLACE FUNCTION alt_edger(_s text, _v text, _relation text, _tbl text, _tbl_src text, OUT row_count int)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sql text := format(
       'INSERT INTO pg_temp.%3$I (label, source, target)
        SELECT DISTINCT $1, %1$I, %2$I FROM pg_temp.%4$I
        WHERE (%1$I, %2$I) IS NOT NULL'
      , _s, _v, _tbl, _tbl_src);
BEGIN
   -- RAISE NOTICE '%', _sql;  -- debug
   EXECUTE _sql USING _relation;
   GET DIAGNOSTICS row_count = ROW_COUNT;  -- return number of inserted rows
END
$func$;

db<>fiddle here

Most importantly, use format() to concatenate your dynamic SQL commands safely. And use the format specifier %I for identifiers. This way, SQL injection is not possible and identifiers are double-quoted properly - preserving non-standard names like Document Number. That's where your original failed.

We could concatenate _relation as string to be inserted into label, too. But the preferable way to pass values to EXECUTE is with the USING clause. $1 inside the SQL string passed to EXECUTE is a placeholder for the first USING argument. Not to be confused with $1 referencing function parameters in the context of the function body outside EXECUTE! (You can pass any string, leading colon (:) does not matter, the string is not interpreted when done right.)
See:

• Format specifier for integer variables in format() for EXECUTE?
• Table name as a PostgreSQL function parameter

I replaced the DELETE in your original with a WHERE clause to the SELECT of the INSERT. Don't insert rows in the first place, instead of deleting them again later.

(%1$I, %2$I) IS NOT NULL only qualifies when both values are NOT NULL.
About that:

• Check if a Postgres composite field is null/empty

Don't use the prefix "pg_" for your table names. That's what Postgres uses for system tables. Don't mess with those.

I schema-qualify known temporary tables with pg_temp. That's typically optional as the temporary schema comes first in the search_path by default. But that can be changed (maliciously), and then the table name would resolve to any existing regular table of the same name in the search_path. So better safe than sorry. See:

• How does the search_path influence identifier resolution and the "current schema"

I made the function return the number of inserted rows. That's totally optional!

Since I do that with an OUT parameter, I am allowed to skip the RETURNS clause. See:

• Can I make a plpgsql function return an integer without using a variable?

How to insert into a table new records based on info from another table?

You could write a AFTER INSERT TRIGGER something like bellow:

CREATE TRIGGER trigger_insert_table_1
   ON table_1 
   AFTER INSERT AS
BEGIN

   INSERT INTO table_2
   ( DeviceIPAddr,
     Status,
     TimeStamp)
   VALUES
   ( NEW.DeviceIPAddr,
     0,
     getdate() );

END;

Insert query with a column that is the total number of records from another table

One way is to use windowed COUNT:

INSERT INTO new 
       (website, 
       company, 
       address, 
       city, 
       state, 
       zip,
       [count])
SELECT f.website, 
       f.business, 
       f.[address line 1], 
       f.city, 
       f.state, 
       f.[zip code],
       [count] = COUNT(*) OVER()  -- count of rows returned by `SELECT`
FROM   [finaltable] f;

db<>fiddle demo

mysql - insert into tbl (select from another table) and some default values

You simply have to do:

INSERT INTO def (catid, title, page, publish) 
SELECT catid, title, 'page','yes' from `abc`

---

Related Topics