Update: In SQL Server 2008 there is an even better method of Row Construction for inserting multiple records, please read it here: SQL SERVER Insert Multiple Records Using One Insert Statement – Use of Row Constructor
This is a very interesting question I have received from new development. How can I insert multiple values in a table using only one insert? Now this is an interesting question. When there are multiple records are to be inserted in the table following is the common way using T-SQL.
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Second',2);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Third',3);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fourth',4);
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('Fifth',5);
GO
The clause INSERT INTO is repeated multiple times. Many times DBA copy and paste it to save time. There is another alternative to this, which I use frequently. I use UNION ALL and INSERT INTO … SELECT… Clauses. Regarding performance there is not much difference. If there is performance difference it does not matter as I use this for one time insert script. I enjoy writing this way, as it keeps my focus on the task, instead of copy paste. I have explained following script to new developers. He was quite pleased.
USE YourDB GO INSERT INTO MyTable (FirstCol, SecondCol) SELECT 'First' ,1 UNION ALL SELECT 'Second' ,2 UNION ALL SELECT 'Third' ,3 UNION ALL SELECT 'Fourth' ,4 UNION ALL SELECT 'Fifth' ,5 GO
The effective result is same.
Reference : Pinal Dave (https://blog.sqlauthority.com) , SQL SERVER – Union vs. Union All – Which is better for performance?
876 Comments. Leave new
Insert into Company(Name,Role_Of_Person)
Select Name, Role_Of_Person
From Company inner join Company_Person on Company.Name = Company_Person.Role_Of_Person
This is my query..
As i want to insert “Name,Role_Of_Person” values in 2 dif tables as..
Company & Company_Person respectivelly…
while i m executing this query in SQL 2005…getting error as
Invalid column name ‘Role_Of_Person’.
so i think it is due to..that it is column of 2nd table n i mentiond
INSERT INTO Company(As a 1st table)…
plz tell me how to resolve this…
thank u!
Hi Pinal,
I need to duplicate the rows in same table , simply changing one field value. (Only 5 columns)
Sql is like this
INSERT SETTINGS (CATEGORY, NAME, OWNER,VALUE, SYSTEM)
SELECT CATEGORY, NAME, VALUE, SYSTEM
FROM SETTINGS
WHERE OWNER = ‘XXXXX’
Owner field will not accept ‘NULL’ values
I need to add Owner ‘YYYYYY’ and other column values taking from ‘XXXX’
Thanks in advance.
Ravi
I think this should work.
select @yyyyy = xxxxx from yourtable
INSERT SETTINGS (CATEGORY, NAME, OWNER,VALUE, SYSTEM)
SELECT CATEGORY, NAME, @yyyyy, VALUE, SYSTEM
FROM SETTINGS
WHERE OWNER = ‘XXXXX’
Regards
Paraminder
Is there a way to use the same ‘INSERT INTO’ command without specifying the column names? Cuz i have a huge set of columns in my table, and i wat to insert a row into tat table, by querying from another table, which again has hundreds of columns!:(
Yes, of course. For example:
CREATE TABLE a (foo INT)
CREATE TABLE b (foo INT, bar INT)
INSERT INTO a
SELECT foo FROM b
Just make sure you insert right amount of columns with correct data types to the target table.
Hi,
I have to bulk insert into a table from a text file.
The following code is working fine
BULK INSERT Table_1 FROM ‘D:Testt2.txt’ WITH (ROWTERMINATOR = ‘n’)
But if i want to set the textfile name form a variable then it is not working. Pls look at the code
DECLARE
@fname varchar(500);
BEGIN
SET @fname = ‘D:Testt2.txt’ ;
BULK INSERT Table_1 FROM @fname WITH (ROWTERMINATOR = ‘n’)
END
Can you please help me in this regards.
hi,
the below piece of code will work for the condition.
DECLARE @fname varchar(50)
declare @name nvarchar(500)
SET @fname = ‘C:\Test.txt’
set @name = ‘BULK INSERT tempdate2 FROM ”’ + @fname + ”’ WITH (ROWTERMINATOR = ”\n”)’
exec sp_executesql @name
note: all quotes are single quotes.
INSERT SETTINGS (CATEGORY, NAME, OWNER,VALUE, SYSTEM)
SELECT CATEGORY, NAME, ‘YYYYY’, VALUE, SYSTEM
FROM SETTINGS
WHERE OWNER = ‘XXXXX’
This will work
Regards
Bijoy C.
Hi,
I was very excited about this piece of code but it did not compile at all.
I kept getting that From is expected in the expression.
I used the code as is from above.
I had to copy and paste.
Can you explain why this may have happened?
>> INSERT statements way: about 5 seconds
>>INSERT SELECT UNION ALL way: 40 miliseconds!!!
the reason this happened is the data was already in memory for the second statement
Hi :
I have a dilema and that is ; I have createda page that shows repeated records from one table for one Invoice ID
and I want to insert the itemized records to another table at once .
the problem is that some times these itemized records are 2 sometime are 10 or 14 records ( Repeated nest from a record set )
How I make sure that alll these records can be inserted to another table with one click of submit from one form ?
Thanks in advance
SEan
is there any way of doing a sql insert where you inserting multiple values into 1 table and selecting 1 value from another table, i.e.
INSERT INTO table(field1,field2, field3) VALUES
(id,1) (SELECT id FROM table2 where field1= ‘hello)
?
thanks, most appreciated
The correct method is
INSERT INTO table(field1,field2, field3)
SELECT id,1 FROM table2 where field1= 'hello'
Hi ,
Is it possible to insert different values in multiple tables unconditionally by one insert query command?
eg: INSERT into table1(field1,field2,field3) VALUES (value1,value2,value3);
INSERT into table2(field1,field2,field3) VALUES (value4,value5,value6);
INSERT into table3(field1,field2,field3) VALUES (value8,value7,value9);
INSERT into table4(field1,field2,field3) VALUES (value18,value28,value38);
can you help me in this regard…
thanks in advance
Hi pinaldave,
I tried your below query in SQL Plus it is not working
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5
;
could pls help me on this
In SQLPLUS you need to use FROM DUAL
ex
SELECT ‘First’ ,1 FROM DUAL
UNION ALL
SELECT ‘Second’ ,2 FROM DUAL
Also note that this site is for MS SQL Server
Hi Dhayanethi,
I think u r missing “GO” in the script.
Hi Sumeet Bhasker,
The example which you given is obsolete.I tried the same query in SQl Sever 2005 but it was not working.
Regards
satya
is there any way of doing a sql insert where you inserting multiple values into 1 table and selecting 1 value from another table, i.e. or inserting data from externally
INSERT INTO table(field1,field2, field3) VALUES
(id,1) (SELECT id FROM table2 where field1= ‘hello)
?
Thanks in advance
insert into dbtrelation (town_code, dscr,vo,b) values
select 23,relation_code,
count(case when status_code=’V’ then status_code end)as Victim,
count(case when status_code=’O’ then status_code end) as offender,
count(case when status_code=’B’ then status_code end) as Both
from name_file, offense_file, pdid_code p
where
name_file.offense_id=offense_file.offense_id and
offense_file.pdid=p.pdid
and date_offense>=’1/1/2006′ and date_offense=’1/1/2006′ and date_offense=’1/1/2006′
and date_offense<=’12/31/2006′ and pdid=11)
order by relation_code
This give me error like Incorrect syntax near the keyword ‘select’. Please tell me how to resolve it.
hi pinaldev,
i have a doubtregarding a query.
select * from table where name like ‘a%’ and group=’g’.i am getting an error saying incorrect syntax near keyword group. wherein name and group are column names of table. can you please suggesr an answer for it.is it that error becoz of group keyword
Hi All,
I am inserting some 7 lakhs records into a custom table. I am using UNION in place of UNION ALL. Will it effect the performance ? When I am running this Script as Program from Front End it is taking hours to Complete…!!
Please help on this….
Thanks In Advance
Neeti
Hi Dave,
Is it possible to Insert/update a data in Multiple tables with single query?
Awesome !
What an increase in performance.Thank you pinaldave.Thank you Very Much.
Your site is pretty much helpful.
I have a problem and need a solution for it….
I have Multiple select statements from different tables in my stored procedure .Can i have a name for each select statement.
No. It is not possible
Why do you want to do this?