Update: For SQL Server 2008 there is even better method of Row Construction, please read it here : SQL SERVER - 2008 - Insert Multiple Records Using One Insert Statement - Use of Row Constructor
This is very interesting question I have received from new developer. How can I insert multiple values in table using only one insert? Now this is 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 me focus on task, instead of copy paste. I have explained following script to new developer. 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 (http://www.SQLAuthority.com), SQL SERVER - Union vs. Union All - Which is better for performance?
I try to execute 400 insert statements to table about 20 columns (int or float). Regarding performance there is MUCH difference. ;)
INSERT statements way: about 5 seconds
INSERT SELECT UNION ALL way: 40 miliseconds!!!
thats funny isn’t it?
thanks a lot for this trick.
I have a 200 rows to insert and I tried to use your way because it seemed much faster, but since I didn’t know how to end it properly, it generated an error.
that’s what I wrote:
INSERT INTO FACULTY (faculty_id, fac_name)
SELECT ‘F123′, ‘Jim Taylor’
UNION ALL
SELECT ‘F124′, ‘Amy Johnson’
UNION ALL;
Can you tell me what did I miss?
Thanks
You do not need last UNION ALL; as shown in example above.
Regards,
Pinal Dave (SQLAuthority.com)
Hello pinaldave,
Myself Gurmeet singh working as Soft. Engg in Mohali.
I have read ur blog its really helpful.
Wish u very good luck.
Hello pinaldave,
Thanks a lot to provide such hidden info about sql server
I never thought that we can do this with a single statement.
it’s really helpful us.
wish u all the best
Hi,
Thanx, it was very use full for me.
You have solved my big problem.
Thnx again.
RandhirSingh
Data Base Developer
Haryana(India)
This solution is useful, but it does not appear to scale well. For 1000 records things went swimmingly. I then tested with 5000 records and an error was returned:
“Internal Query Processor Error: The query processor ran out of stack space during query optimization.”
Just thought I would provide a bit of warning. This does appear to perform better than a single command with multiple INSERT statements, but the cost in server resources may be a problem. Has anyone else experienced this?
Thanks David for sharing information here.
It also depends on the size of the table and row to be inserted. We are successfully inserting nearly 10K row using this union.
Regards,
Pinal Dave (SQLAuthority.com)
If you need to insert multiple records at a time then you may use a simpler syntax. Here is how it goes:
INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (’First’ , 1) , (’Second’ , 2) , (’Third’ , ‘3′), (’Fourth’ , ‘4′) (’and so on’) ;
This is what I use. It is simple and effective.
However, I wanted to know if there is any way to upload values in a batch from text file with values to a SQL database.
Sumeet, the syntax you provide is only valid on mysql, not sqlserver2005. i believe pinal’s method is the only one that really works (barring the aforementioned caveats, of course).
Hi It’s helpful
But is thr someway where i can use a single insert to insert values for some columns from one table and the remaining columns from another table?
Sri
Sure,
you can do something similar to following example
Insert into yourtable (table1col, table2col)
Select table1col, table2col
From table1 inner join table2 on table1.table1col = table2.table2col
Regards,
Pinal Dave (SQLAuthority.com)
Thanks
Say if i don’t have a common field to join then what can be done?Coukd you pls tell me?
Pinal,
Good to see your site & blog.
You could generate a script which does that, if the values are stored in some tabular format
e.g. if you have an excel worksheet containing rows with data - you could write a formula in an excel cell (for each row)
OR
you could write sql statement do generate insert statements
e.g. select “insert into mytable (field1, field2, field3) values” + field1 + “,’ + field2 + “,” + field3 “)” from mysampletable
No need to do copy-paste :)
Sir,
I want to know that how i can update one by taking data from other table.
For Exmp. There is one master table having col name ID, Amt.
There is one second table daily account( Id, amt_dipo). How i can update the master table in the evening
With Best Regards,
Somesh Vashisht
Hi,
This blog is very good and helpful. I need to insert records into 2 tables at a time. Can you please tell me the procedure for it.
Thanks & Regards
Hari
In response to question 9:
Try ‘Bulk Insert’ to load data from a file.
Hello. Thank you for creating a website like this. It is very helpful.
I have a question. Is it possible for an INSERT command to fail?
In what situations would this happen?
I insert about 1000 records in a table and only the first 200 get inserted. The rest of the other 800 do not. I split the process by inserting in 5 batches, 200 records each and all are successfully inserted? What could be the reasons for an incident like this?
Thank you for any help you can provide.
This blog is very helpful. I got a lots of things new here. keep it up.
Thanks.
can you please suggest an answer for my question
how can we insert the values into different tables at a time
Hi Pavanich,
We can not insert values into different tables at a time. We need to use multiple Insert statement.
Kind Regards,
Pinal Dave (http://www.SQLAuthority.com)
Hi,
thank you for your suggestion,can you give me an idea of how to insert data which is in the form of xml into a table.kindly explain with an example.
ragards,pavanich
Hi,
you have given suggetion for inserting multiple rows at a time in a table using query.but whats the difference between inserting the data using insert into query and the one you have given.even the one you have suggested is very big.i mean to say here also we use many statements.
Regards,
pavanich.
Hi Pavanich,
The advantage of using the query I have suggested is performance and single insert statement (reduction in network traffic).
Regards,
Pinal Dave (http://www.SQLAuthority.com)
Hello ,
Really this is very useful site for me too…
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
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 have to bulk insert into a table from a text file.
The following code is working fine
BULK INSERT Table_1 FROM ‘D:\Test\t2.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:\Test\t2.txt’ ;
BULK INSERT Table_1 FROM @fname WITH (ROWTERMINATOR = ‘\n’)
END
Can you please help me in this regards.
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?
Hi,
Did you change yourDB to “your databasename” example like adventureworks?
Regards,
Pinal
[...] Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL. [...]
>> 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
Hi Sean,
Answer to your question is here : http://blog.sqlauthority.com/2007/08/15/sql-server-insert-data-from-one-table-to-another-table-insert-into-select-select-into-table/
Regards,
Pinal Dave
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
Hi Habs,
I think you can do it in SQL Server 2008 constructing rows. I am not sure how you can do it in other versions.
There are alternate ways of creating temp table or using sub-routines.
Regards,
Pinal Dave (http://www.sqlauthority.com)
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
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
The example provided by Bhasker is for MySQL. SQL Server user do not try that.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
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
You can do something like this
INSERT INTO table(field1,field2, field3)
SELECT id,1,id FROM table2 where field1= ‘hello’
Regards,
Pinal
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 Hetal,
You have following code
insert into dbtrelation (town_code, dscr,vo,b) values
select 23,relation_code,
Replace it with following code (remove the keyword value)
insert into dbtrelation (town_code, dscr,vo,b)
select 23,relation_code,
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
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 Neeti,
If your data is distinct you should use union all otherwise union.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Hi Dave,
Is it possible to Insert/update a data in Multiple tables with single query?
Hi Sathish,
It is not possible.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
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.
Hi pavanich,
Surround group with []s, like so:
select * from table where name like ‘a%’ and [group]=’g’
i want to insert in one table and update in another table with one query how
Hello,
Please I need advice.
SQL Server 2005. I have a table consisting of 200 fields with a primary key(auto-increment). I need to be able to duplicate via a stored procedure a specific row in the table identified by primary key value i.e. insert a record form that table into the SAME table …. the primary key of the inserted record so be incremented and I would need to change to of the field values in the inserted record (i.e. duplicating a row in a table with a primary key(auto increment). What is the best way to do this… one suggested writing to temp table then re-insert to original table. I have had some issues with this is there another method? Please not that other users may try and access the orginal record during this insert process. Can you give me example syntax and comments.
Thank You!
hi all,
i wanted to know whether a insert statement can contain where condition in it.
Hi Pinaldave,
This site is awesome. I am a beginner and got stuck at some point.
My query is i have 2 tables (tblbuilding & tbltype) with columns as building, buildingid, type, typeid.
I have one main table tbltechen2 with columns group,seats,room,buildingid, typeid.
My question is when i submit the survey page with building and type specified, i should get the buildingid and typeid to be entered into tbltechen2 getting the buildingid and typeid from tblbuilding and tbltype respectively..
Can you tel me the query for inserting into tbltechen2.
Thanks a tonnnnn,
karthik
hi i am trying to insert data for 5 lines its giving following error,pls suggest me
INSERT INTO c2 (type, invtype)
SELECT ‘First’ ,’a’
UNION ALL
SELECT ‘Second’,'b’
UNION ALL
SELECT ‘Third’,'c’
UNION ALL
SELECT ‘Fourth’,'d’
UNION ALL
SELECT ‘Fifth’,'e’
ERROR: FROM Keyword not found where expected
please suggest me for this response
Chiranjeevi, did you try giving semicolon (;) at the end?
Hi Dave,
I have sql server 2000. It runs very slow. I checked in SQL Server - Current Log and it shows that database is backed up after every 2/3/4 hours. However, I checked the Database maintenance plan. There is no single plan wherein backup is scheduled on hourly basis. Is there any place else where backup could have been scheduled and I’m not able to track? If not then could you please let me know why am I able to see the messages in SQL Current Log?
Regards
Shreyas
Sir
I have the following procedure
————————————————-
declare @xmlData xml
set @xmlData = ‘
select …..
from FROM @xmlData.nodes (’/NewDataSet/Table’) AS p(nref)
————————————————-
it takes 6 seconds
but when I write
————————————————-
declare @xmlData xml
set @xmlData = ‘
Insert into tempTable
select …..
from FROM @xmlData.nodes (’/NewDataSet/Table’) AS p(nref)
————————————————-
it takes 10 minutes ?????
this is a pretty neat trick. espcially in my java program where executing each insert equals one call to the executeUpdate method.
Is Union All works with Oracle
I need to find out ,No of row inserted into the table like
SQL:> insert into table where some condition
Supppose if i fire this Query i need to know no of records inserted into table. Using java
Hi All,
Using following code I am getting recods month wise
but the months are not coming in ascending order.
Select DATENAME(month, sportendt) as eventdt from sportsnews where sport_type=’Cricket’ and sportendt>=getdate()-1
GROUP BY DATENAME(month, sportendt)
HAVING count(*) >= 1
Help me
Dnyanesh
Hi Shreyas,
First check the indexes status whether the indexes are fragmented or not and what is the scan density?
rgds
hi Pinal…I am trying this UNION ALL trick to insert more than one row in the table on informix…But it does not seem to be working…. :(
Hi Rama Krishna,
Thanks for your reply.
Did you mean that the scanning density or index fragmentation could be the cause of why it is showing as “Database backed up” in sql current log?
Thanks again.
Awaiting reply
Thanks rama krisha.
Is that relevant to displaying “database backed up” every 3 hrs. in sql current log?
shreyas
Is there anyway to do multiple inserts as in mysql?
e.g.
INSERT INTO sometable VALUES (
(a,b,c),
(c,d,e),
(e,f,g)
)
thanks!
i want sum thing different
insert into table1 ( c1,c2) values ( ‘1′,’2′)
insert into table2 ( c1,c2, c4) values ( ‘1′,’2′,’4′)
tell me i want to to excute abouve in same query and also once ne query fail they both roll back/
plz tell me how to do dat
I am trying to insert multiple rows in a database using the “select” and “union all” statements. This is working great.
I am also using the @@identity + (variable - 1.toString) as my primary key.
I am getting a “primary key constraint error” in SQL Server. “Duplicate key not allowed”
I have tried to set Identit_Insert = On and then Off at the end of the query but am still getting this error. Does anyone know how I can fix this problem?
(I am doing this in order to migrate from mySQL to SQLServer) and was using last_Identity() instead of @@identity previously. I thought if I changed to the sql server naming convention to get the last id entered and set the Identity_insert to on/off it would work but it’s not working.
Hi all,
How to load bulk data in db2 database??
The database resides on OS/390 machine.
i want to load data in tableA(say) from other tableB(say0 which is in other DB2.. ..
Both the tables are same??? but resides on different DB2..
can anyone help on this???
I tried using export and import commands. but it takes a hell lot of time….
is there a way to load the data faster than this??………..
Hi Dave,
Presently I’m using in sql server below mentioned query for insert in a stored procedure. I have declared the variables @a, @b and @c respectively. Is this the correct way?
If I do it without variables I’m getting correct output whereas with the variables I’m getting duplicates. Can anyone please help?
Also the stored procedure returs me correct records as well as null records without variables. Is there any way of getting rid of NULL? I’m using cursor.
insert into
(select @a = a, @b = b, @c = c from a,b,c where a = b and b = c group by… order by…)
Thanks
Shreyas
I have a similar query to one of the above. Perhaps you could help.
I have 2 tables Login and Salt
I want to insert UserName,Password into the Login Table and
instert LoginID from the Login Table and Salt into the salt table.
Please help
Regards
Simon
The method using …. UNION ALL … brought me a slowdown of factor five. Seems it highly depends on the amount of data.
How do you remove orphan records from a table which was not referenced by a foreign key?
Thanks,
Liz
I have two database with same tables. Depending on a column value(takes binary value 0 or 1) in one table I have to copy those into another table if that column is 1.
how can i do this?
thanks in advance
Ravi D
very good article i wanted to know that can we practice
administration part articles sitting on the local machine
because i tried to apply permissions to the user but was unable to do it
Thanks and Regards,
Satnam Singh
Database Developer
MUMBAI
very good article.
i tried , its working fine.
Thanks and Regards,
shishma
S/w engg
Blore
Hello,
I need help to insert multiple rows from one table to another.I have to do something like this.Select the one colum from the table and rest all i have to insert by myself.How do i do i?
I USE SQLSERVER.IN MY PROJECT I IMPORT A DATABASE WITH MAXIMUM 20000 RECORD IN A DATA BASE WITH 200000 RECORD.IN THIS PROJECT WE COMPARE THIS IMPORTED RECORD WITH SECOND DATA BASE THAT NEED SOME INSERTING AND SOME UPDATING IN LARG SCALE DATABASE.I WORK WITH DATA SET AND I MUST COMPARE AND MANUPLIATE DATA IN FIELDS !! BUT THIS WORK GET ALOT OF TIME FOR UPDATE AND INSERT !! HOW CAN I SAVE TIME AND DO WORK FASTER.
Hi..
I need to insert a clob type data into a table.
I break this clobe into few chunks and added them into a temporary table. Now i want to add those chunks into a one column of some other table.
it is not allowed to use text type data locally with in a stored procedure? how iam suppose to do this?
Thanks
Sandevni
Hi Sql junkies,
I have a problem with scheduled backups. Any backups run through database maintenance plan or called by procedure xp_sqlmaint hang. They just show status as executing but nothing happens. I ran a profiler too and checked log messages, no clue. Nothing has been recorded in log, etc.
Please can somebody help me urgently?
Many thanks
Shreyas
Pinal,
I m Hitesh Agja and I have prolem with multiple insertion. My table has 10 fields including one identity field. and i want to insert more then 10,000 rows in just one shot…how can i do this…union all gives me “Insufficient system memory” kind of error. Plz help me out budy….i m trying with bulk insertion what are the other options for me pinal..
Hitesh Agja
(Ahmedabad)
I got the solution…faster then ever you have seen….bye guys….
hi…
pinal if for multiple record insertion i do not have fix number of records then how i can insert multiple insert using your union all type query………..actually i have tried and find error…bcoz …i have not fix record insertion
Hi Pinal,
I am using your way to insert bulk data. But i am facing a problem in this command.
if there are 100 records to insert if any of statement raises error, no record is insert into database table.
can i ignore these type of error and insert error free data.
Actaully i am read data from excel sheet and insert into sql database. there is problem of primary key constraints. i have made user define id and user id composite key.
so how could i achieve this thing
Super reference site - thank you for having this - very helpful!
I need to update many similar db’s with a similar record in one table - and most of the record is populated with my known text, but a few fields need to be carried over from another row (easily identified) in that same table (the value is obviously different in each db) - but in the same table. Can this be done with one statement or do I need to run an Insert followed by an Update statement? For example…
===
INSERT INTO TableXYZ
(Col1, Col2, Col3)
VALUES (’Value 1′, ‘Value 2′, ‘Value 3′)
===
UPDATE TableXYZ
SET Col4=’Value 4′, Col5=’Value 5′
Super reference site - thank you for having this - very helpful!
I need to update many similar db’s with a similar record in one table - and most of the record is populated with my known text, but a few fields need to be carried over from another row (easily identified) in that same table (the value is obviously different in each db) - but in the same table. I want to run it in one statement (repeat it for each db), but will use two statements if needed. I can’t seem to get the Update statement to work though. Can you help???
For example…
===
INSERT INTO TableXYZ
(Col1, Col2, Col3)
VALUES (’Value 1′, ‘Value 2′, ‘Value 3′)
===
UPDATE TableXYZ
SET Col4= a, Col5 = b, Col6 = c
WHERE (Col1 = ‘Value 1′) AND EXISTS
(SELECT Col4 AS a, Col5 AS b, Col6 AS c
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’)
Hoping you can assist - thank you!
Tim
Here’s what I figured out works if I use two steps:
(Step 1: Create a new record with known data in TableXYZ)
INSERT INTO TableXYZ (Col1, Col2, Col3)
VALUES (’Value 1′, ‘Value 2′, ‘Value 3′)
(Step 2: Update that new record by referencing data already existing in the table (ie: Col1 = ‘Value 1′)
UPDATE TableXYZ SET
Col4 = (SELECT Col4
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’), Col5= (SELECT Col5
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’), Col6 = (SELECT Col6
FROM TableXYZ
WHERE Col1 = ‘Old Known Value’)
WHERE (Col1 = ‘Value 1′)
- Thanks again, Tim
hi
can we insert multiple record at the same time in sql + ?
please help me..
can we insert records from excel or text in sql server2000?
how to use massage command in sp
Hai ,,,,
I have to insert the values based on three conditions .first i have to insert based on the id values and secondly based on the names and so ..what kind of stored procedure i have to write using sqlserver
Hi,
Its a nice site dave. How to insert values into a table, where 1st column in static value and other columns are from select Query which will come from another table..
Thanks in Advance
Hi Dave,
This query really helped me a lot both in time and performance.
Keep the spirit going on
Thank alot! This really helped…
Perfect! Saved me lots of time. I just inserted 4,358 records with one statement! “UNION ALL”
G
[...] by one of long time reader who really liked trick of SQL SERVER - Explanation SQL Command GO and SQL SERVER - Insert Multiple Records Using One Insert Statement - Use of UNION ALL. She asked how can I execute same code multiple times without Copy and Paste multiple times in [...]
Hello pinaldave,
Myself Faisal Qureshi working as Soft. Developer in Mumbai.
I have read ur blog its really helpful.
Wish u very good luck.
Hi Dave,
This z a very good site and a beautiful and useful article. It really helped.. Wish u all the best and expecting much more from you.
Hi there…If there are say 10,000 records in a table..How do I select the second 500 records from it…i.e. the records from number 500 to 1000
Hey srikant,
Assuming you are using SQL Server 2005, you can do that using the ROW_NUMBER function with the OVER clause.
Here is an example from msdn:
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS ‘RowNumber’
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
you can find more info at: http://msdn2.microsoft.com/en-us/library/ms186734.aspx
Can we apply the same for multiple update statements
How to build a unconditional “INSERT ALL” with can statement as Oracle
ej
insert all
into sal_history values(…..)
into sal_history2 values(…..)
select col1,caol2,…. from tableX
hi there,
plz help me to find the solution
I have a table with 3 fields name,price and department.
now i want to
Insert the table with exactly 100,000 records. name field is to be made up of random length A-Z characters but never the letter M. However there MUST always be atleast 1 record of all possible field lengths i.e. 0 to 20.
The price number is a random between 0-10000
The departments are random from the following list
sales,computers,hr.
hi ,u can easily move the excel sheet data to the Sql server using DTS if using SQL 2000 and using SSIS for SQL 2005
I wonder how to do insert query with more than one MS Access database in ADO?
eg:
database1, field1 (password=111)
database2, field2 (password=222)
How do insert field1 from database1 into database2 field2 with one ADO connection?
Thanks in advance
regards,
Martin
I have a source table, T1, with three columns col1, col2 and col3.
I have a destination table, T2, with the same three columns.
I want to move all the data from T1 to T2, deleting all those records in T2 where there exists in T1 a record with the same col1 AND col2 values.
In other words, a record is uniquely defined by the combination of col1 and col2.
What’s the best way to achieve the above?
Thanks
Its Really good and Interesting stuff.
gud stuff
Hi Pinal Dave
i am facing problem in updating more than one nodes in a single xml document.
is there any way to replace more than one node in a sinle xml document
If not then what is the best way to achieve this?
Many thanks…..
Tejal
Hi thanks a lot It is very much useful.
I want to know is there any thing to replace cursor in sql server.
Hi Pinal Dave,
I am very glad to see your talent hear,
Its really good and intresting for all software engineer.
Thanks again dear.
select name from sys.objects where type = ‘c’ –CHECK Constraint
select name from sys.objects where type = ‘D’ –DEFAULT Constratint
select name from sys.objects where type = ‘f’ –FOREIGN KEY Constraint
select name from sys.objects where type = ‘l’ –Log
select name from sys.objects where type =’fn’ –Scalar Function
select name from sys.objects where type =’if’ –Inline table function
select name from sys.objects where type = ‘p’ –Stored Procedure
Select name from sys.objects where type = ‘K’–PRIMARY KEY & UNIQUE Constraint
select name from sys.objects where type = ‘RF’–Replication filter stored procedure
select name from sys.objects where type = ‘S’ –System Table
Select name from sys.objects where type = ‘TF’–Table Function
select name from sys.objects where type = ‘TR’–Trigger
select name from sys.objects where type = ‘U’ –User Table
select name from sys.objects where type = ‘V’ –View
select name from sys.objects where type = ‘x’ –Extended stored procedure
I want to store the result of every single query in one column in a table. Say First qery gives 40 records then those 40 records should be in first column, and if second query gives 23 records then those 23 records should be in second column, the way so on….So could any say how to do ? Which would be highly appreciated.
Howdy -
Can I insert multiple rows into one table by doing a select on another table. For example:
INSERT INTO table1(col1,col2, col3)
SELECT col1,col2, col3 FROM table2 WHERE col1 > 5
Thanks, Matt
I browse and saw you website and I found it very interesting.Thank you for the good work, greetings:
Hi Pinal,
gone thru ur blog…ur blog z highly useful… thx a lot man…
Hi Pinal,
Fantastic work. Can make alot of people happy to see.
One question:
Employee table contains emp_id, other employee details and a column manager_id. Some of the manager_ids contains NULL. How can we update those nulls to zero USING self join.
Hi to find last modfiy table and stored procedure in sql server 2000 And 2005
In My Table does not have creation date And Modify date field.
Question:
Can you do something like this in MSSQL?
INSERT IF NOT EXISTS
INTO YourTable (FirstCol, SecondCol)
VALUES (’First’ , 1) , (’Second’ , 2) , (’Third’ , ‘3′), (’Fourth’ , ‘4′) (’and so on’) ;
Thanks for any insight into this
Very helpfull and performance improving article.
Thanks
Hi,
we are using SQLserver2000 in our application.In that we are
handling Chineese characters. Collation is “SQL_Latin1_General_CP1_CI_AS”. datatype is nvarchar.
But in most of the situation chineese characters displays as ??.
sometimes there is nothing displays. Pls. advice me to resolve this case.
Hi Dave I’m using Access how could I insert multiple records using SQL let’s say I got a Destination Database and a Source Database and the user could choose what table he/she would use and from the Source Database choose Table1 and in the Destination Database also Table1 and everything in that Source table would be copy or append to the Destination Table.
Insert into (Destination Table) in (Destination Database) Select
(Source Table.*) from (Source Database)
Please need help on this, Thanks in advance
Hi Pinal,
Thanks a lot for sharing this great information with us.
I need to insert mulitiple value in a table whose value will be decided at runtime. Also no. of value will be decided at runtime.
Is there any way to resolve it at backend.
Thanks and Regards.
Gyan.
I WANT TO CREATE A PROCEDURE IN SQL SERVER 2000 JUST LIKE THIS PLZ HELP ME BY GIVE PROPER SOLUTION
IF(SET @IN_SERVICE=’DEVOTIONAL’)
INSERT INTO TBL_DEVOTIONAL
IF(SET @IN_SERVICE=’ASTRO’)
INSERT INTO TBL_ASTRO
IF(SET @IN_SERVICE=’CRICKET’)
INSERT INTO TBL_CRICKET
I’m trying to write a script that updates multiples tables at the same time.
For example, I’m trying to update an Employee Database where Name = John, i need to change the name John to Peter. (Database contains over 30,000 records )
I need the script to search all tables and update firstName where necessary
Hi
I have 500 customers in a table call PM_CUST_CURR.
THe Base currency is Riyals.
There are 40 currencys that are still to be uploaded for same 500 customers.
I am uploading all by benthic. but this a kinda donkey biz.
is there ne way to upload the customer with nase currency and then copy 40 currency for every customer though some script rather then uploading 1 by 1
Do reply
This is really useful
It will save a lot of time
Thanks
Ankur
Can I perform the “Insert multiple records using one insert statement” task using a stored procedure ? If so, how do achieve this ?
Very useful Query for Developers and DBA’s.
Hi,
I want just thank you pinal dave for solution good very. It help problem solve sql server insert records lots. You best sql dev person and this site much nice. thank much, for problem my you solve.
Amand.
I need help. i have 2 tables with 40 columns each and i need to insert the data from one table to the other. destination table is normalized but the source table isnt. so i will need to check for description to ascertain the relevent ‘id’ from the code tables. i need to generate a reusable script to perform this.
Regards
Sbosh
Dear all,
Which is best performance (INSERT,DELETE and UPDATE) in ms sql
Regards
Adhi
Hi Pinal,
Is it possible to insert multiple records by passing parameters?
if so, I requesting you to write me the example and also post mail to my email id ASAP.
eg. same above example to pass parameter values through a function. is it possible? please write in detail,
To understand in detail:
let’s say i’m reading inputs values from a column and saving into a DB table.(VBA perspective) i want to utilize effectively the SQL Server Database rather frontend functionality code.
Regards & thanks
Kameswararao
I get the foll error :
INSERT INTO [mxmc_db].[dbo].[AuditTrail] (Refid,jobNumber)
Select 01,01,01
Union ALL
Select 01,01,01,01,01,01,01,01,01,01
Union ALL
Select 01,01,01
Union ALL
Select 01,01,01,01,01,01,01,01,01,01,01,01,01
go
Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
hi,
how can I find the username that starts with minimum 3 chars.Actually,I tried with like,but like is showing the rows with one character only,but how can I get username that starts with minimum charcters.
I just want to thank pinaldave for the original tip above, then thank Sumeet for his improvement, and I will also thank MrSnipey for his suggestion regarding how to insert data from a text file via the bulk method, although I haven’t tried that one out yet.
Anything that can reduce the tedium of copy & paste is highly welcome. I was also positive that there must be a better way, as I could not imagine that experienced coders would suffer the infinite repetitiveness of the alternative of copy & paste. Most sites I found when I Googled the problem dealt with other issues than the solution here, which is what I was of course looking for. Thanks again all around!!!
Yours,
makaroo
Hi Guys,
I want to combine something like 6O excel templates of the same structure into one template. I ‘ve tried ”SELECT…UNION ALL SELECT… ” with 3 templates which worked fine. But with 60 templates do I need to write 60 or so SELECT…UNION ALL SELECT… statements? What is the easy way out?
Help out please.
Regards,
Felix
hi ,
I get error
insert into my_dept (dept_no , dname , location)
select( 1,’research’ , ‘blore’)
union all
select ( 2 , ‘ad’ , ‘mysore’)
union all
select ( 3 ,’marketing’ , ‘hyderabad’)
union all
select ( 4 , ‘ hr’ , ‘mumbai’)
go
;
please help me , i want to know this command
Advance thx ,
rani
rani, I believe your error is occuring because of your last semi-coln ‘;’
no ‘;’ is required after the ‘go’ statement
Cheers.
Simmo
Hello,
I need send all SELECT clause for parameter, because my INSERT is into store procedure. But i have problem with ” ‘ ” in varchar values. How to send this sentece as parameter?
Or
how to fix this code? if i want use variable:
declare @teste varchar(255);
declare @teste2 varchar(255);
set @teste = ‘INSERT INTO MyTable (FirstCol, SecondCol) ‘;
set @teste2 = ‘SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3
UNION ALL
SELECT ‘Fourth’ ,4
UNION ALL
SELECT ‘Fifth’ ,5 “;
exec (@teste + @teste2);
thanks
please help
how do i insert e records records at a time suppose if i dont know no of records to be inserted. it depends on the selections of records.i mean inserting no of records changes each time.how do i insert by passing the variables.if the values is not hard coded.
thank you,
Spoorthi
insert into company (’EMPNO’, ‘ENAME’, ‘JOBS’, ‘MGR’, ‘HIREDATE’, ‘SAL’, ‘COMM’, ‘DEPTNO’)
SELECT ( 4587, ‘STANLEY’, ‘TEAM MEMBER’, 8745, ‘28-JAN-08′, 5000, 2100, 10)
UNION ALL
SELECT ( 4787, ‘THOMPSON’, ‘TEAM MEMBER’, 5825, ‘18-JULY-05′, 2500, 1100, 30)
UNION ALL
SELECT ( 2584, ‘ROGER T’, ‘TEAM MEMBER’, 8745, ‘28-JAN-08′, 5000, 2100, 10)
GO
sir i have made this statement the issue is that it says that the select statement is missing . it is unable to insert the muliple rows.pls tell me how to work on this statement .
Hello
I have created three dimension table and one staging table want to load data into fact table .Getting an error
My Insert statement is
Insert into dbo.facthurricane (DIM1, DIM2,Nameid,WIND,PR)
select dim1 from dbo.locationD
SELECT DIM2 from dbo.DIMTIME
SELECT NameID from dbo.NameD
SELECT WIND from dbo.StagingHurrincane
SELECT PR from dbo.StagingHurrincane
MY select statements are fine but some INSERT is not working
Any help
-Praveen
I am very new to SQL world..
hai dave how r u ……
i visit your web site it s really i a good enclopedia of SQL server.
i am facing one problem in sql 2000 server actually in my database iwant to see Roll
No and Degree No in order by Rollno .
it is not showing my query is like
select * from student where Uid=’A’ order by RollNo.
select * from student where Uid=’A’ order by DegreeNo.
some time it is working in sql query analyzer .but not working in jsp Script result.
Waiting for your Reply
ASAP
Manoj
HI , i have a problem
could ‘u help me please :)
y have 2 “select” one of those with IDENTITY and the SLQ7 do not allowed the UNION… for example
select name, age, ID = IDENTITY (int)
Into #tempTable
from Table1
where …….
UNION
Select name, age, 0
from Table2
why i want do that? … becouse …i have to UPDATE th TempTable but i don’t know whay don’t bellow to me do that
Hi Pinal,
Is there a way that you can insert values into multiple tables at one go. I am a beginner and I was writing a procedure, so got this question. Your input is greatly appreciated.
Thanks
Is there any Examples to INSERT or (UPDATE) values in two different tables by writing only one statement in SQL Server 2000.
Case example: Say I have a THREE values. I want to INSERT two of the values in TABLE_ONE and the one value on TABLE_TWO.
INSERT is more important to me but as well UPDATE
Hello Can you please tell me how I can INSERT multiple rows using checkboxes?
Your blog is very usefull!
Can u explain the difference between Union and Union All?
Sumeet Bhasker stated that:
INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (’First’ , 1) , (’Second’ , 2) , (’Third’ , ‘3′), (’Fourth’ , ‘4′) (’and so on’) ;
this is used when number of row is defined. What if the number of row is dynamic? Can someone help me on this? Thank You!
hi pinal,
i have tried the option u have provided as below-
INSERT INTO mytable(id,name)
SELECT 1, ‘pizza’
UNION all
SELECT 2, ‘donuts’
UNION all
SELECT 3, ‘milk’;
but every time i get the error like-
ORA-00923: FROM keyword not found where expected
i also tried a another option-
INSERT INTO mytable(id, name) values(1, ‘pizza’),(2, ‘donuts’),(3, ‘milk’);
but i get the error like-
ORA-00933: SQL command not properly ended
Please, help me, tell me what wrong i m doing.
Hi, i have a one proble it is make me made and it’s happen so many time
i am using TDBGRID 7. OLDB and XArrayDB
i am using loop like that
For M = 0 To Myarray.UpperBound(1)
If Myarray(M, 1) “” Then
‘Insert Command
End If
Next
it is inserting twise a same record, Why ? I don’t have any idea about that if any budy know exact solution for that pls. reply