sql server insert multiple records


CREATE TABLE #CALCULATE
(
COL1 VARCHAR(20),

COL2 VARCHAR(20))

Using Insert INTO commands for each row

INSERT INTO #CALCULATE
(COL1,
COL2)
VALUES ('one',
1);
INSERT INTO #CALCULATE
(COL1,
COL2)
VALUES ('two',
2);
INSERT INTO #CALCULATE
(COL1,
COL2)
VALUES ('three',
3);
INSERT INTO #CALCULATE
(COL1,
COL2)
VALUES ('four',
4);
INSERT INTO #CALCULATE
(COL1,
COL2)
VALUES ('five',
5);

SELECT *
FROM #CALCULATE

DELETE FROM #CALCULATE

Using Union All command

INSERT INTO #CALCULATE
(COL1,
COL2)
SELECT 'one',
1
UNION ALL
SELECT 'two',
2
UNION ALL
SELECT 'three',
3
UNION ALL
SELECT 'four',
4
UNION ALL
SELECT 'five',
5

SELECT *
FROM #CALCULATE

CREATE TABLE #CALCULATETEMP
(
COL1 VARCHAR(20),

COL2 VARCHAR(20))

Insert from another table

INSERT INTO #CALCULATETEMP
(COL1,
COL2)

SELECT COL1,
COL2
FROM #CALCULATE

Create table and insert the data from another table

SELECT *
FROM #CALCULATETEMP

SELECT COL1,
COL2
INTO #CALCULATETEMP1
FROM #CALCULATE

SELECT *
FROM #CALCULATETEMP1

DROP TABLE #CALCULATE

DROP TABLE #CALCULATETEMP

DROP TABLE #CALCULATETEMP1

Reference : www.sqlauthority.com