How to Insert Data in SQL Server

The 1000-row limit applies to the VALUES form specifically, and exceeding it is a hard error, so batch large loads or switch to INSERT ... SELECT. Naming the target columns protects you from breakage when someone adds a column later, and it is required when the table has an IDENTITY column you are not supplying.

INSERT INTO orders (customer_id, amount)
VALUES (101, 42.50), (102, 17.00), (103, 99.95);

Insert from a query

INSERT ... SELECT runs entirely on the server, has no row limit, and is the right tool for archiving or backfilling. Listing columns on both sides makes the mapping explicit rather than positional. For very large copies, batch with a TOP (n) loop so the transaction log does not grow unbounded.

INSERT INTO orders_archive (id, customer_id, amount)
SELECT id, customer_id, amount
FROM orders
WHERE created_at < '2025-01-01';

Return the inserted rows with OUTPUT

OUTPUT returns the rows as they are written, including server-generated values such as IDENTITY and defaults, which is the clean alternative to SCOPE_IDENTITY() when inserting more than one row. It can also write into a table variable with OUTPUT ... INTO, which is how you capture new IDs for a follow-up insert.

INSERT INTO orders (customer_id, amount)
OUTPUT INSERTED.id, INSERTED.customer_id
VALUES (104, 25.00);