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,
There are other alternatives and better approaches than multiple statements shown here.
For example, a single INSERT query will not let you process the parameters before insertion, which makes this method weak. Using triggers will work, but will degrade your server performance.
So if your data needs no processing, you can use multiple insert statements. However, if you want to process them then I’d recommend using one of the following two new features since SQL Server 2005:
1. Using OPENXML
2. CLR User Defined Types
Just google them and you will find many examples and tutorials.
Thank you for the brief article. It was brief and to the point.
I have used this syntax with other tools. It is ideal for loading initial definition data, and test data. Its simplicity is its strength.
how do i insert the same record i a table by a number of times
@fred
Insert into table2 select * from table1
GO 1000
above statement will insert everything from table1 into table2 1000 times. Just change the number beside GO and and you are good.
This will work in SQL Server 2005/2008.
~ IM.
So can that list of values be passed in via parameter(s) of a stored procedure?
With sql server CE this query:
INSERT INTO Scontistica
(Prodotto,Sconto)
values (‘Sky’,0.5),
(‘Skywarm’,0.5)
give me this error:
[ Token line number = 2,Token line offset = 19,Token in error = , ]
why??
Note that this is supported from version 2008 onwards
how we can insert a new row between two rows?
Dont worry about it. It is a display purpose use the Order by clause
Hello Prashant,
As MS Excel, SQL Server doesn’t allow us to define the position of new row. But it can be done indirectly by creating clustered index. In a heap table we have no control to the physical storage location of a row.
Kind Regards,
Pinal Dave
hi,
You are doing great job, I really appreciate it. :)
I want to insert unique records in one insert statement. Is it possible? Thanks
Well, of course you can.
There was an article about setting IGNORE_DUP_KEY ON. Thats one way to do it. Distinct values are added to the table and duplicate values are ignored. Just remember to set IGNORE_DUP_KEY OFF when you’re done or you can get yourself into all kinds of troubles afterwards.
Other way, which I prefer more, is simply just to select only distinct values into the other table. For example, and this is very narrow example:
INSERT INTO TargetTable
SELECT DISTINCT * FROM SourceTable
Hello,
I am very new to SQL Server. I hope someone can help me. I have a large fixed width flat file that I need to import into an existing table in SQL Server 2008. I’ve tried using the wizard, but it either imports everything into the first column as one long string, or it defines the first column as 1 digit and imports everything into that column in multple rows. I was told that if I import the file as one long string, then I could write a statement that would allow me to import it into the table I’ve built in the proper (correct columns) format. I am not sure exactly which statement would be best for this. I will need to do this once or twice a week, so I only want to have to write the query once. I know this has to be possible, but I’m at a total loss.
Would it be better to use an update statement? For example
This is where I get confused. I am not sure of the correct syntax to use.
Use my_database
Update (my_table)
Set first_column varchar(4) not null = long_string_table.column_1;
Set second_column varchar(1) not null = long_string_table (do I need to say where each column begins and ends? That seems to make more sense.)
Any help is appreciated. Thank you.
thanks for the help
Thanks a lot sir.
when i use the query the
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1),
(‘Second’,2),
(‘Third’,3),
(‘Fourth’,4),
(‘Fifth’,5)
in sql server 2005 it is giving error
Incorrect syntax near ‘,’. ca just tell me it will work with 2005 also ? or it will work with only 2008
@Sabir
It won’t work in SQL Server 2005 and below version.
It only works in SQL Server 2008 version.
~ IM.
Incorrect syntax near ‘,’.
iam same errore sabir
Have you read previous reply?
This feature works from version 2008 onwards
I have inserted the following into MS Access:
INSERT INTO ms_Material (MUID, Manning, EqRough, HWCoef)
VALUES
(‘CCP’, 0.0115, 0.0082, 120)
(‘CFL PCCP’ , 0.0115 , 0.0082 , 120)
(‘PCCP’ , 0.0115 , 0.0082 , 120)
(‘RCP’ , 0.0115 , 0.0082 , 120)
(‘Steel RGJ’ , 0.0115 , 0.0082 , 120)
(‘Steel Relined PCCP’ , 0.0115 , 0.0082 , 120)
(‘WSP’ , 0.0115 , 0.0082 , 120)
(‘WSP CML’ , 0.0115 , 0.0082 , 120)
(‘WSP CML&DC’ , 0.0115 , 0.0082 , 120);
Can someone please give me a reason why I am getting syntax error messages? Thank you.
This syntax is not supported in MS Access
You need to use multiple insert statements
INSERT INTO ms_Material (MUID, Manning, EqRough, HWCoef)
VALUES
(‘CCP’, 0.0115, 0.0082, 120)
INSERT INTO ms_Material (MUID, Manning, EqRough, HWCoef)
VALUES
(‘CFL PCCP’ , 0.0115 , 0.0082 , 120)
.
.
.
how to insert multiple rows using single query in sql 2000 and 2005 ?
when i use the query the
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1),
(‘Second’,2),
(‘Third’,3),
(‘Fourth’,4),
(‘Fifth’,5)
in sql server 2005 it is giving error
Incorrect syntax near ‘,’ .
is ther any other format similar to the above one for inserting multiple rows ins sql 2000 and 2005 ?
You need to use multiple insert statements
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1)
INSERT INTO MyTable (FirstCol, SecondCol)
(‘Second’,2)
etc
i am working on sql 2000, now i want to transfer my entire data on sql 2005. can u pls provide me the steps that how i have to take the backup and how to implement it in 2005.
Read about backup database and restore database in SQL Server help file
For User Using SQL 2000/2005
You can use the following statement to have same effect as of 2008 feature …
Insert Into Table1 (col1,col2) Values (‘A’, ‘B’ ) Insert Into Table1 (col1,col2) Values (‘C’, ‘D’ )
Multiple inserts requried.
Hope it helps
or
Insert Into Table1 (col1,col2)
select ‘A’, ‘B’ union all
select ‘C’,’D’
Hi,
[Sql 2005]
how i copy one table data (above 8 lakhs rows) into another one table within the server using T-SQL (except insert statement) ?
Can you provide its solutions…? It must helpful for me
Thank you
One option is to move data to text file from where you can import to another table. Refer this post for more details
hi,
this question might be naive but im trying to insert two values in a single column
there are two columns
tablename:exmaple
t1—–id
t2—parentid
can i insert two parentid for a single cell?
insert into example values(‘1′,’23,24’);
im getting a error message Incorrect syntax near ‘,’.
thanks for your time and would appreciate any help
What is the datatype of parentid?
If parentid is a foreign key, then you can’t insert multiple items in that column. This is one to many relation, you can make t1,t2 a composite key so that it is treated differently and becomes:
insert into example (1, 23)
insert into example (1, 24)