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
This my sql statement:
create table newStudentMark(Matric_No varchar(6),course_code varchar(10),kump varchar(5),chap_no varchar(5),num varchar(5),studentAns varchar(5),correctAns varchar(5),mark integer);
st.executeUpdate(“insert into newStudentMark values(‘”+session.getAttribute(“matric”)+”‘,'”+session.getAttribute(“course_code”)+”‘,'”+session.getAttribute(“kump”)+”‘,'”+session.getAttribute(“chap_no”)+”‘,'”+num+”‘,'”+studentAns+”‘,'”+correctAns+”‘,'”+mark+”‘)”);
ITS SHOW (javax.servlet.ServletException: java.sql.SQLException: Columns of type ‘INTEGER’ cannot hold values of type ‘CHAR’. )
PLS HELP ME..I HAV 1 WEK ONI TO SOLVE IT
Remove single quotes from Mark column
Look in have this sum insert
example:
INSERT INTO dbo.Manpower(Minority_Employees, Female_Employees, Total_Employees, Cleveland_Residents)
SELECT SUM(Narrative.MIM),Sum(Narrative.MIF), SUM(Narrative.MAF + Narrative.MAM + Narrative.MIF + Narrative.MIM), SUM(PersonnelSummary.Cleveland_Resident)
FROM Narrative, PersonnelSummary
I would like for it to insert the unique id of the Narrative table into the Manpower table. I have a column name NID (fk, int, not, null) in the manpower table. so when I run the user go to the manpower report page it loads all manpower totals for each ID that show up.
You need to join the two tables to get proper results
I just want to drop a thanks post. This small example solved quite a problem for me.
Regards,
Kriviq
Hello i’am learning SQL Server
there is something like insert into SET Col1=x, Col2=Y, etc. at Sqlserver?
or the only method tested, for insertion is to use:
INSERT INTO table () values (), ()…
I have to change a lot of SQL to migrate my scripts.
Thank you
Gustav
You need to give us more informations on what you want to do
Hi, Iam new to programming,
I have to perform a where clause and retrieve the records.
table name: table1
want to perform where clause on 2 columns
Column1 : header_Id
Cloumn2 : previous_Msg_Header
so,
header_id previous_Msg_Header
1621 0
1622 0
1623 1621
1624 0
1625 1624
So my output Should be:
header_id previous_Msg_Header
1622 0
1623 1621
1625 1624
I short, I need the records whose header_Id is not present in previous_Msg_Header
select columns from your_table
where header_id not in (
select previous_Msg_Header from your_table)
i tried to insert multiple rows in sql 2000 using follwing syntax, it displays an error message
INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (‘First’ , 1) , (‘Second’ , 2) , (‘Third’ , ’3′), (‘Fourth’ , ’4′) (‘and so on’) ;
i want to know whether this format is applicable in sql 2000 ?
You need to use multiple inserts
INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (‘First’ , 1)
INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (‘Second’ , 2)
etc
Hi,
I am stuck in batch insert statement (insert into select) when i run only Select statement it’s execute in 7 min & yield 17.5 million records. But when I try this select with insert statement it’s hang the system after some time.
Following is the key info for above problem:
1. SQL server 2008
2. No null value in select statement.
3. Insert table populating foreign keys from master table(it’s like a FACT table).
Waiting for ur expert suggestion
Hi Sir,
I want answer must for this question becoz it is the greate bug in my project. the question is
HOW CAN I RESTRICT ONE INSERTION AT A TIME IN TO A TABLE ?
with the use of triggers r any …., pls send me to my mail id
[email deleted.]
Create a after insert trigger having this code
if (select count(*) from inserted)>1
rollback
I am trying to create a trigger on a database table that when a record is inserted into the table and a certain field is populated with a value i want to take that record and insert multiple instances of the data for that record. So if lets say one field is yes and another field is 6 then i want to take that record and make 6 instances of that in the database…
declare @val varchar(10), @i int
select @val='yes',@i =5
select @val from master..spt_values
where type='p' and number between 1 and @i
I am trying to INSERT an additional address on all my students record, with address type = ‘PERM’. But I only want to do this if they do not have a ‘PERM’ address type already.
I will need to join my address table with another table because I only need to do this for students whose ‘admit year’ is, for example “2010” and I can only do this with a join.
See my statement below:
INSERT INTO [Campus6_test].[dbo].[ADDRESSSCHEDULE],[Campus6_test].[dbo].[ACADEMIC] (address_type,email_address)
values (‘PERM’, ‘slorenh@yahoo.com’)
SELECT people_code_id FROM [Campus6_test].[dbo].[ACADEMIC]
where academic_year = ‘2006’
and academic_term = ‘SUMMER1’
and admit_year = ‘2006’
and admit_term = ‘SUMMER1’
and address_type ‘PERM’
Help…What am I doing wrong?
Thanks
Thanks a lot this query is a good one…
HI
this is good
its like bein in DB
using “not in” is better or having a “left join where is null” is better
and if there is diff between them?
if anybody can explain the difference between the two?
It depends on the size of the table. In many cases “Left join where is null” will outperform “not in”
Thanx madhivanan
I am creating job portal in asp.net I want to save resume (*.doc) file in sql database. Can Please help me in This?? This is my first porject.
The better approach is to store the file path in the table and store the file in the Server’s directory
Which version of SQL Server are you using?
“The better approach is to store the file path in the table and store the file in the Server’s directory”
Then you have to remember handle concurrency and atomicity of the operation too. Otherwise you could end up with broken links and missing documents.
Add FileUpload and Button controls to you web page. On Button.Click event check if FileUpload.HasFile is true and read file from FileUpload.FileBytes property. You can also read FileUpload.ContentType to get the MIME type of the file.
Next open up SqlConnection. Create SqlCommand, set appropriate SQL clause to SqlCommand.CommandText property and add parameters to SqlCommand.Parameters collection. Finally call SqlCommand.ExecuteNonQuery and close connection.
my question is regarding index :
__________________________
we were using sql 2000 in which i need to improve the query performance, already there are some indexes which were created and maintained but when i execute DBCC SHOWCONTIG(table_name,index_name), o/p delievered by this command is
DBCC SHOWCONTIG scanning ‘TW_TRANS’ table…
Table: ‘TW_TRANS’ (154027880); index ID: 13, database ID: 14
LEAF level scan performed.
– Pages Scanned…………………………..: 16183
– Extents Scanned…………………………: 2063
– Extent Switches…………………………: 15722
– Avg. Pages per Extent……………………: 7.8
– Scan Density [Best Count:Actual Count]…….: 12.87% [2023:15723]
– Logical Scan Fragmentation ………………: 42.06%
– Extent Scan Fragmentation ……………….: 99.95%
– Avg. Bytes Free per Page…………………: 2554.6
– Avg. Page Density (full)…………………: 68.44%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
now i want to drop this and to create new index.
whether this may yield any problem to our production database.
if it cause any problem then what action i need to perform?
Hi
I am facing problem in SQL server 2005 taking long time to insertion, while same application is running on another server work just perfect;
Server A:
Edition Enterprise Edition (64-bit):
Product Level SP2
Version 9.00.3042.00
@@Version Microsoft SQL Server 2005 – 9.00.3042.00
(X64) Feb 10 2007 00:59:02 Copyright
(c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT
6.0 (Build 6001: Service Pack 1)
Server B:
Edition Enterprise Edition (64-bit)
Product Level SP3
Version 9.00.4035.00
@@Version Microsoft SQL Server 2005 – 9.00.4035.00
(X64) Nov 24 2008 16:17:31 Copyright
(c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT
5.2 (Build 3790: Service Pack 2)
Server A is working fine but Server B is in trouble,
Your advice to fix the problem will be highly appreciated.
Thanks and Regards
sani
Sir,
i have a gridview from which i have to select the no. of employees and then fire the insert query which includes multiple insertion of records simultaneously.
Please i m in a mess help me out.
I want to create a insert procedure for the table call_Header where it consists of columns like clrequest id,clfullname,clphone,clcategoryid,clpriorityid,clupdated,clloggged,clcomments etc..where i have to insert only few of them from front end like..ids,phone,name of caller.but not comments,clupdated.how to write insert query for this..?
You need to include the column lists in the INSERT statement
INSERT INTO your_table(col_list)
select ……..