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



sql server execute all files in a folder using sqlcmd

Step 1. Create a sql file (proc.sql) and palace it here C:\SqlFiles\01_database\proc.sql

create procedure procName
as
begin
select * from DimProduct
end

Step2. Create a sql file(proc1.sql) and place it here C :\SqlFiles\proc1.sql

create procedure procName1
as
begin
select * from DimProduct
end

step3.Create a BatchCommand file(ExecuteSqlFiles.bat) with following text in it and place it here C:\SqlFiles\ExecuteSqlFiles.bat

for /R %%X in (*.SQL) do SQLCMD -S Server -d Database -U UserID -P Password -I -i "%%X" >> ResultScript.txt

Step4. Run the ExecuteSqlFiles.bat (i.e by clicking on it)

A file (C:\SqlFiles\ResultScript.txt) if not exist is created and the errors encounterd while executing the sql files is written to the file.