Menu
For example, if I have string 'sunday', then I want to insert same value in 1000 rows using SQL only; without using loops.
861
First query USE CustomerDB; IF OBJECT_ID('Customer', 'U') IS NOT NULL DROP TABLE Customer; CREATE TABLE Customer ( CustomerID int PRIMARY KEY IDENTITY, CustomerName nvarchar(16), ...about 130 more columns... ); INSERT INTO Customer VALUES ('FirstCustomerName', ...), ... 1500 more rows...
INSERT-SELECT-UNION query to insert multiple records Thus, we can use INSERT-SELECT-UNION query to insert data into multiple rows of the table. The SQL UNION query helps to select all the data that has been enclosed by the SELECT query through the INSERT statement.
insert into table my_table(col1, col2) VALUES (val1_1, val2_1), (val1_2, val2_2); Storing records to a file and using load data infile yields even better results (best in my case), but it requires more effort. Show activity on this post. It's okay to insert 1000 rows.
A table can store upto 1000 rows in one insert statement. If a user want to insert multiple rows at a time, the following syntax has to written. If a user wants to insert more than 1000 rows, multiple insert statements, bulk insert or derived table must be used.
If you don't want to use another table you can use:
INSERT INTO some_table (some_column)
SELECT 'Sunday'
FROM (
SELECT 1
FROM (SELECT 1 UNION SELECT 2) as d1
JOIN (SELECT 1 UNION SELECT 2) as d2
JOIN (SELECT 1 UNION SELECT 2) as d3
JOIN (SELECT 1 UNION SELECT 2) as d4
JOIN (SELECT 1 UNION SELECT 2) as d5
JOIN (SELECT 1 UNION SELECT 2) as d6
JOIN (SELECT 1 UNION SELECT 2) as d7
JOIN (SELECT 1 UNION SELECT 2) as d8
JOIN (SELECT 1 UNION SELECT 2) as d9
JOIN (SELECT 1 UNION SELECT 2) as d10
) AS t
LIMIT 1000
You can adjust the amount of JOIN's depending on the limit you want.
113
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
