I previously wrote article about SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL. I am glad that in SQL Server 2008 we have new feature which will make our life much more easier. We will be able to insert multiple rows in SQL with using only one SELECT statement.
Previous method 1:
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
Previous method 2:
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
SQL Server 2008 Method of Row Construction:
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES ('First',1),
('Second',2),
('Third',3),
('Fourth',4),
('Fifth',5)
Reference : Pinal Dave (https://blog.sqlauthority.com)
120 Comments. Leave new
hi,
how to insert another rows in table that contain primary and foreign key?
because when i insert some rows, i am getting wrong.. i need your answer please…
thanks
What did you mean by wrong? Did you get any error?
I have a table CPY10100 with a missing column. So I made a temp file of the table and dropped/created a new table CPY10100 with the correct columns.
So how do I use the INSERT INTO SELECT command in SQL2008 to bring the data from the CPY10100temp to the CPY10100?
I’ve tried:
INSERT INTO CPY10100
(
list of all columns, including new column
)
SELECT
(
list of all columns,including new column
)
FROM CPY10100temp
but get an error on the newly created column.
Your help would be much appreciated.
Thomas
My enivironment is Sql Server 2008 R2, but still i’m getting error: Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘,’.
here is my code:
CREATE TABLE #SalesDates
(id INT, saledate DATEtime
);
go
INSERT INTO #SalesDates (id,saledate)
VALUES
(1, ‘2012/1/1’),
(2, ‘2012/1/2’),
(3, ‘2012/1/3’),
(4, ‘2012/1/4’),
(5, ‘2012/1/5’),
(6, ‘2012/1/6’)
go
SELECT id
FROM #SalesDates s
WHERE s.saledate BETWEEN ‘2012/1/1’ AND ‘2012/1/6’;
go
DROP TABLE #SalesDates;
please help me.
This doesn’t make sense at all. I keep gettting the Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘´’. error message when I try. Please help.
Hi
how to insert multiple rows into table using one procedure call
can anyone provide me the syntax for it
Thankx,
sruthi
Thanks! Was helpful.
Thanks. I find that your site usually has the best and easiest to understand instructions.
I am using this method to insert multiple recoreds, but when one of the rows has an error it does not tell me which one. How to find out which of the 1000 rows has the error??
Hi,
need ur help, how i want to create query to insert value in others server?which have sql instance name. ex: server1\sql01
Sql query if insert value in same server, this ok;
insert into dbo.udtFGPALLET (palletID) values (@PackageSN)
Sql query if insert value in other server with sql instance;
insert into server1\sql01.rpt_db.dbo.udtFGPALLET (palletID) values (@PackageSN)
result error bcoz of \ … pls help. thanks
There should be a way to like this with Windows Live Credentials. This post saved me some work :-)
If we have 1 lakh records to update then it is difficult to add all those in row construction scenario.is there any other solution please reply me
Is it possible to insert multiple records in multiple tables using single select query ?
Like,
CREATE TABLE Relation
(
ProductId INT,
SubCategoryId INT
)
CREATE TABLE Category
(
MainCatId INT,
SubCategoryId INT
)
I want something like this, Is it possible ?
INSERT INTO Relation
(ProductId,MainCatId)
INSERT INTO Category
(MainCatId,SubCategoryId)
SELECT ProductId,MainCatId,SubCategoryId
FROM ProductDetails
Thanks.
getting error in 2nd time same query, first time same query worked properly in sql server 2012:
PLEASE TELL ME THE SOLUTION
error query:
Update [churn_trial_F] set Status_by_site=’Customer Present In 2012/2013’
where [PT_Fiscal_Year] = ‘BN\2011’ and [$(getting error : ” incorrect syntax near ‘\’ “)]
[Internal_External]!= ‘Internal MCS (DEPT)’ [$(getting 2 error : ‘ incorrect syntax near ‘internal ” and “incorrect syntax near ‘DEPT'” EXPECTING ‘(‘ OR SELECT.)]
and [Total_Revenue] >0
and [Status_by_site] is null
and [Customer_by_site] in
(Select a.[Customer_by_site] from
Trial_d2 a
inner join Trial_d2 b on a.[Customer_by_site] =b.[Customer_by_site]
where a.year=’2011′
and b.year=’2012’ [$(getting error :UNCLOSE QUOTATION MARK AFTER THE CHARACTER STRING ” ) ‘ .]
)
#1ST QUERY SUCESSFULLY WORKED:
Update [churn_trial_F] set [Status_by_Site] =’Loyal Customer’
where [PT_Fiscal_Year] in(‘BN/2011′,’BN/2012′,’BN/2013′) and
[Internal_External]!=’Internal MCS (DEPT)’
and [Total_Revenue] >0
and [Status_by_Site] is null
and [Customer_by_site] in
(Select a.[Customer_by_site] from
Trial_d2 a
inner join Trial_d2 b on a.[Customer_by_site]=b.[Customer_by_site]
inner join Trial_d2 c on b.[Customer_by_site]=c.[Customer_by_site]
where a.year=’2011′
and b.year=’2012′
and c.year=’2013′
)
How to insert 10000 records at a time using insert statement ?
Insert into table values (‘a’)
go 10000
Thank you for reply……i want insert records at a time morethan 100 using single statement ?Is it possiable ?
I am inserting 5 in one statement.
use tempdb
go
Create table x (i int, j varchar(100))
go
Insert into x values (1,’One’), (2, ‘Two’), (3, ‘Three’), (4, ‘Four’), (5, ‘Five’)
go
Select * from x
go
drop table x
Need to fetch the column value and error message of insertion fails while inserting multiple records using insert into select * query
@Natarajan – Please share the sample query and exact error message..
Hi Pinal Dave
I have .sql file which contains millions of Insert commands.,and they are having insert statements to be inserted into different tables When I am executing by opening in SQL SERVER MANAGEMENT it says
Insufficient memory to continue the execution of program
Can anyone please help me its very urgent
Thanks in advance for your help
Hi ,
I need to select 2 coulmns from one table and it should be inserted into newly cretaed variable not table in single query how can i write query fro this