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
Hello
Is there a way to do this in SQL Server 2005?
Insert into Table1 (a,b,c,d,e)
values
(select fruit from Table2, ‘apple’, ‘pear’, select vegetable from Table3, ‘banana’)
If there is only one row in the tables,use
Insert into Table1 (a,b,c,d,e)
select (select fruit from Table2), 'apple', 'pear', (select vegetable from Table3), 'banana'
I’m attempting to pull distinct records from Access. When I use:
SELECT DISTINCT MyField1 INTO MyUnion2 FROM MyUnion1
I get back 131M rows, which is what I want. But, when I use:
SELECT DISTINCT MyUnion1.Field1, MyUnion1.Field2, …… INTO MyUnion2 FROM MyUnion1
I get back 190M rows, which is not what I want. How do I get Access to see the “DISTINCT MyUnion1.Field1” in the second example?
While trying to check for the bulk import operation, I am getting error message.
Please help to fix this problem –
steps:
1. First I created a table named City and inserted records –
create table city(cityid int identity(1,1) primary key,cityname nvarchar(10) unique)
insert into city(cityname)
select ‘Kakinada’
union all
select ‘Guntur’
union all
select ‘hyd’
2. Exported the records of the table to a new txt file.
2. now I created a new table city1 which the same table structure as City.
BULK INSERT city1 FROM ‘C:\city_1.txt’ WITH (ROWTERMINATOR = ‘\n’)
Getting the error message –
Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.
Bulk load: DataFileType was incorrectly specified as char. DataFileType will be assumed to be widechar because the data file has a Unicode signature.
Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
Thanks
Sourav
hi pinal
how r u . Hope ur fine by the grace of god.
i’ve query that suppose i want to insert a complete row between the rows . how should i insert . can u plz help me.
like this
EMPID EMPNAME
——– —————
1. 111 IMRAN
2. 112 PINAL
4. 114 SHAIK
So now i want to insert between 112 and 114 i.e (113,Rohit)
o/p should be like this
111 imran
112 pinal
113 Rohit
114 shaik
Thanking u very much.
shaik .
Hi,
I was getting the following problem when i used union all”
“Maximum number of user tables under all sides of a UNION statement can not exceed 256. Please simplify the query and retry”. But this went away when i used Union instead of Union all. I will be inserting huge number of rows around a lakh or even more. Will it be safe for me to use it?
Any help will be very much appreciated.
Thanks
Pavan
Hi Pinal,
I am currently working on MySQL and SQL Server 2005 databases.
I have created stored procedure in SQL Server 2005 what it does is..It calls the stored procedure in MySQL and try to insert 5 milions of records from one table to another in MySQL and once it is done, It will try to fetch all data from second table and insert into SQL Server table….
but this whole process takes lot of time to process so can you show me more efficient and best way to do it….
Repsonse will be very much appreciated…
Gr8 way, thanks, it worked fine, good job
I am quite impressed by this site learned many new things. I hav query i guess i will get help from here for sure.
In my table i have 4 column 1st column is PK with Auto numbers, and in third column i have some value where i have to merge this PK col value.
Id name price merge_col
1 ABC 11 xyz_1
2 sdf 12 god_2
to achive this i am inserting a record and then retrieving the Id filed value and again using update query to change merge_field.
So is there any way to get the value of Id field as soon as the value is inserted in that field and i can use it in the same insert query for next column
Hi Priya,
What you can do is
INSERT INTO Test([NAME]) SELECT ‘XYZ_’ + CAST(MAX(id) + 1 AS VARCHAR) FROM test;
Try this out…Chage table name and column according to yours.
Hello Sir,
I am nilesh chaudhari. I have to store a gujarati text
value in sql server 2005.
And also in textbox i have to write a text in gujarati in asp.net 2.0 window applicatiion.
Plz, help me.
Hi,
I am Durai.How to retrive 7th record in sql server 2005 with out using field condition like( where Empid=7). Is there possible to retrive using rownum=7 in sql server 2005
Plz Tell me
Hi Sir,
insert into table1( col1, col2, col3, col4, col5, col5)
( SELECT * from table2)
Suppose table2 contains 1000 records, while insertion it throw error on 251 row, it’s all rollback. But i dont want to rollback all. I want keep as it is the inserted record, it generate a log for 251 and the insertion is continuing from 252 row onwards.
For this i need to use a cursor.
But i’m not dont no the pros and cons for the cursor.
Is there any other approach to solve this issue?
Let me know ASAP
Hi,
Pls suggest an SQL query to get a list of all tables that are neither referencing nor referenced by any other table.
Thanks for your help
Try this
select T.table_name from INFORMATION_SCHEMA.TABLES T
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
on T.table_name=TC.table_name where (TC.constraint_Type =’Foreign Key’or TC.constraint_Type is NULL) and
T.table_name not in (‘dtproperties’,’sysconstraints’,’syssegments’)and Table_type=’BASE TABLE’
Dear all
I am using visual basics 6 and I am trying to insert records from a select statement into the table. The count for the select statement shows that there are 1 136 098 records. The insert statement makes the system to hang as it is trying to insert 1 record at a time.Can you provide me with the altenative code to insert records in bulk rather?
Use setbased approach
insert into target_table(column_list)
select column_list from source_table
Hi,
How to create a array object in sql Server 2005
I want to Multiple insert into Mapping table so i don’t
want to exe every time sp from front end only pass the array
object from front end and insert value in mapping table
“I already use split function for string data type”
Thanks & Regard
Vishal Jain
name,id,age,address,addresstype,hiredate ..how to normalize this…
Hi,
I have two tables and i want to insert multiple values in FirstTable for all primary keys from secon table and some fixed values.
METACODE example
insert into TableOne (value1, value2, value3)
values (12, primary keys from tableTwo,2009)
something like this
12 Primary_key_from TableTwo1 2009
12 Primary_key_from TableTwo2 2009
12 Primary_key_from TableTwo3 2009
…
12 Primary_key_from TableTwon 2009
SQL2005
Hi Sir,
Today I did browse your website, It’s really a nice work carried out by you for the programmers either very new or old but in dialemma at some point.
Thanking Again
BEst Regards
Kundan Kumar
Sub : Regarding duplicate value…..
Hai sir,
I have two tables that are..
1. tbl-a
2. tbl-b
tbl-a contains fields
1.btblc_no
2.btblc_value
and tbl-b contains fields
1.btblc_id
2.btblc_payment
3.btblc_date
now my query is join two tables and remove duplicate value if there is any from tbl-a as it btblc_payment is more time but btblc_value is one.
my qyery is coming like that way is as below
btblc_no btblc_value btblc_id btblc_payment btblc_date
——— ————– ——— —————– ————–
101 5000 1 3000 10/10/2008
101 5000 1 2000 12/10/2008
102 2000 2 Null Null
103 1000 3 Null Null
—————————————————————————-
But I want this result like this way is bellow..
btblc_no btblc_value btblc_id btblc_payment btblc_date
——— ————– ——— —————– ————–
101 5000 1 3000 10/10/2008
101 Null 1 2000 12/10/2008
102 2000 2 Null Null
103 1000 3 Null Null
—————————————————————————-
OR..
btblc_no btblc_value btblc_id btblc_payment btblc_date
——— ————– ——— —————– ————–
101 5000 1 3000 10/10/2008
Null Null Null 2000 12/10/2008
102 2000 2 Null Null
103 1000 3 Null Null
—————————————————————————-
Pls Pls Pls help me out of this problem..
Best Regards
Atul
Hi,
i have about 2000 records in an excel sheet. the records need to be written to ms sql server 2005. I have to insert them in batches. Please help me. i have to take up first 100 records from the excel sheet, write it to a batch file. at the end, i need to execute these batch files in one go……. so that the time consumed is less…….. please help me.