Insert Multiple Rows in One Table Based on Number in Another Table

Insert multiple rows in one table based on number in another table

Answer to original question

Postgres allows set-returning functions (SRF) to multiply rows. generate_series() is your friend:

INSERT INTO b (all_names, birthday)
SELECT names, current_date -- AS birthday ??
FROM  (SELECT names, generate_series(1, number) FROM a);

Since the introduction of LATERAL in Postgres 9.3 you can do stick to standard SQL: the SRF moves from the SELECT to the FROM list:

INSERT INTO b (all_names, birthday)
SELECT a.names, current_date -- AS birthday ??
FROM   a, generate_series(1, a.number) AS rn

LATERAL is implicit here, as explained in the manual:

LATERAL can also precede a function-call FROM item, but in this case
it is a noise word, because the function expression can refer to
earlier FROM items in any case.

Reverse operation

The above is the reverse operation (approximately) of a simple aggregate count():

INSERT INTO a (name, number)
SELECT all_names, count(*)
FROM   b
GROUP  BY 1;

... which fits your updated question.

Note a subtle difference between count(*) and count(all_names). The former counts all rows, no matter what, while the latter only counts rows where all_names IS NOT NULL. If your column all_names is defined as NOT NULL, both return the same, but count(*) is a bit shorter and faster.

About GROUP BY 1:

• GROUP BY + CASE statement

Insert multiple rows for each row in another table

What do you mean with "getting it in a set"? Would you like to insert it in a ordered way? Then you can do something like this:

chain the queries together with a union and sort it

insert into tableB ([Type], BillNo, AMount)
select 'sales' as [Type], BillNo, amount as AMount from TableA union
select 'cash' as [Type], BillNo, TotalAmount as AMount from TableA union
select 'tax' as [Type], BillNo, tax as AMount from TableA
order by BillNo, [Type]

Insert multiple rows in a table based on ranges between multiple columns in another table

Lots of good options already mentioned. I would also lean towards a periodics table and a join. One approach might be the following one:

USE TEMPDB

CREATE TABLE #Y (YY INT)
INSERT INTO #Y VALUES (2000), (2001), (2002), (2003), (2004),
                  (2005), (2006), (2007), (2008), (2009),
                  (2010), (2011), (2012), (2013), (2014),
                  (2015), (2016), (2017), (2018), (2019)

CREATE Table #M (Mnth INT)
INSERT INTO #M VALUES (1), (2), (3), (4), (5), (6), 
                  (7), (8), (9), (10), (11), (12)

SELECT *, CASE WHEN Mnth < 10 THEN CAST (YY AS CHAR (4)) + '0' + CAST (Mnth AS CHAR (2)) 
        ELSE CAST (YY AS CHAR (4)) + CAST (Mnth AS CHAR (2)) END AS Combined   
INTO #Period
FROM #Y
CROSS JOIN #M

SELECT * FROM #Period 

CREATE TABLE TABLE1 (ID INT, YEAR1 INT, MONTH1 INT, Combined1 CHAR (6), YEAR2 INT, MONTH2 INT, Combined2 CHAR (6))
INSERT INTO TABLE1 VALUES (1, 2010, 11, '201011', 2011, 2, '201102'), (2, 2012, 10, '201210', 2012, 12, '201212')

SELECT * FROM TABLE1

SELECT T.ID, P.YY, P.Mnth
FROM #Period AS P
INNER JOIN TABLE1 AS T ON P.Combined BETWEEN Combined1 AND Combined2
ORDER BY 1, 2

--DROP TABLE #M, #Period, #Y, TABLE1

Insert multiple rows from select into another table

If I understand correctly, you would use insert . . .select:

insert into table1(col1, col2, col3)
    select 1, col2, 0
    from <your other query here>;

Insert Multiple Rows from Table A to Table B on Multiple Different Dates MYSQL

Use a cross join:

SELECT a.DATE, b.LOCATION, b.VARIABLE
FROM TableA a
CROSS JOIN TableB b;

If you actually want to insert this data into another table, then use an INSERT INTO ... SELECT, using the above select query.

Insert multiple rows with one column from another table

Use an insert based on a select:

insert into table2 (id,name,table1_id)
select nextval('table2_seq'), 'new entry', t1.id
from table1;

Is it possible to insert multiple rows in a table based on a select returning more than one row

You can use a const in the select list

INSERT INTO A(colA, colB)
SELECT id, 'Hardcoded-Value'
FROM B
WHERE status = 'APPROVED'

Insert multiple rows from another table

You can insert using select.

Something like this:

insert into history (id, created_date, updatedBy)
select id, getdate(), 'add person here??'
from #temptable;

The above require the updatedBy to be supplied in the select query.

If you want current user, you could use SUSER_NAME()

sql insert multiple rows from another table

VALUES table value constructor specifies a set of row value expressions to be constructed into a table, so you need a different statement:

INSERT INTO WorkAreaContainerSubTypes (WorkAreaId, ContainerSubTypeId)
SELECT 1, Id 
FROM ContainerSubTypes;

As an additional note, you may use VALUES only if SELECT Id FROM ContainerSubTypes statement returns a single row or no rows (perhaps with an optional WHERE clause):

INSERT INTO WorkAreaContainerSubTypes (WorkAreaId, ContainerSubTypeId)
VALUES
   (1, (SELECT Id FROM ContainerSubTypes WHERE Name = 'Some name'));

---

Related Topics