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://blog.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.
INSERT using UNION is not a way to improve the performance. I had tried inserting 35706 records to a table (int values).
The normal insert way (35706 insert statements) took just 9 seconds to complete the execution whereas the union all way takes more than 3 minutes.
I did not continue after 3 minutes…. :)
union ALL
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
everything is fine even i did the same thing but getting result…
Hi,
You miss go command at the end of insert.
Try u will not get error.
Thanks,
siva
remove the last union all
I am sorry i forgot to say i have not inlcluded last UNION ALL
Remove the last Union all.
Hi Ruba,
There is not a big problem with your code .
You just had an extra UNIONALL after your last Select statement.
Thanks,
Vipin Teotia
Remove the last “UNION ALL”.
If you look at the example, there’s no “UNION ALL” after the final entry. I haven’t tried it but this is my guess as to why you might be getting errors.
You do not need last UNION ALL; as shown in example above.
Regards,
Pinal Dave (SQLAuthority.com)
another way to insert or update multiple rows in a table to use XML :
declare @UserInfo table (InfoName nvarchar(100), InfoValue nvarchar(100))
declare @xml xml
set @xml = ‘
‘
insert @UserInfo (InfoName, InfoValue)
select UserData.value(‘@Name’, ‘nvarchar(100)’), UserData.value(‘@Value’, ‘nvarchar(100)’)
from @xml.nodes(‘/root/user’) as Data(UserData)
select * from @UserInfo
Yes sir there is no need to add UNION ALL in the last,the thing we should not use brackets in the select clause
When I try to run this I get the error “FROM keyword not found where expected” do you know how to fix that?
Sir;
I need your help in coding.
I work as a Lab assistant and warden in a school in Mumbai. I know ASP a bit.School has asked me to program a module for daily muster for students. I am new to ASP. I do not know ASP.NET
The module is like this :Say a teacher gets logged in with his employee id and password and after authentication goes to other page where he inputs his subject and grade in a form. This will fetch some number of records from studentmaster table depending on the number of students in that grade with that particular subject. ( sometimes 19 or sometimes 30 or whatever ). This data should appear in text boxes of a form and not as a text on the next page mainly showing student id ( hidden) and name. There should appear a drop down box or two radio buttons at the end of EACH row showing “present and absent”. Also the form should have drop down boxes for date, day and year. Teacher then will select date, day ,year only once ( not per student )and Present or Absent per row (per student )as per the case. Once submitted, ALL rows should get added simultaneously to SQL database where other table called studentmuster would be already created. Inserted data should show fields like this…… Teacher ID, Student ID, Student Name,Grade, Date , Day,Year, Present or Absent.
I can insert one data at a time. Updating a record is OK since in case of a mistake by teacher that particular single data could be fetched and corrected. I do not know how to insert multiple rows in a table at one go.
I need code in ASP and not in ASP.NET. I do not know any other programming language and this too I am doing after a long time.
I have read “UNION ALL” above and will try this, but since I do not know the exact number of rows to be inserted, I think I will have to use loop. I do not know how to do this. Can you help me? Give me a call if you feel like.
Thanks in advance.
Damodar
Mumbai ( Phone number removed)
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.
This syntax is supported from version 2008 onwards
Also if you have data in a text file, you can use BULK INSERT
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
Thank you sir.
I have used this query to insert multiple record.
There is still a question.
If there are lac of record insert in same query. will there any problem with the query.
or we have to split the query with thousands.
Thanks in advance.
In that case, have data in a text file and use bcp or bulk insert
INSERT INTO checkmultipleinsert(id , name)
VALUES (1 , ‘A’) , (2 , ‘B’) , (3 ,’C');
this query genrate an error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘,’.
I have sql2008 pls reply
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
i wanna insert more number of column records using select sub query.
Post some sample data and explain how you want to import the data to a table
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)
Is there any way to store multiple textbox (say 1000 textbox ) values into database on button click with better perfomance, ways other than normal sp and insert query
Hello Sonny,
If you are using .NET 3.5 as your Front End, Then read topic LINQ TO SQL. This latest technology does not needs any stored procedure to insert data into tables and it does not even needs any select statement to be written to retrieve data from sql tables.
Ofcourse it prepares insert and select statements by itself, You as a developer, need not worry how LINQ TO SQL prepares these statements.
~ IM
hello i am a student and i think that it is possible
Thanks
Say if i don’t have a common field to join then what can be done?Coukd you pls tell me?
Post some sample data and expected result
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
You may need to use
update m
set amt=amt+d.amt_dipo
from master as m inner join daily_account as d
on m.id=d.id
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
You need to use two insert statements
u can do two insert statements or
use trigger ok
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.
there is one cause for it is connection break up. you can set the connection time greater. Try it..
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 sir!
please you can send me the setup of sql server on my emil adress. Thanks
[email removed]
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
Is there a way to use the same ‘INSERT INTO’ command without specifying the column names? Cuz i have a huge set of columns in my table, and i wat to insert a row into tat table, by querying from another table, which again has hundreds of columns!:(
Yes, of course. For example:
CREATE TABLE a (foo INT)
CREATE TABLE b (foo INT, bar INT)
INSERT INTO a
SELECT foo FROM b
Just make sure you insert right amount of columns with correct data types to the target table.
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,
the below piece of code will work for the condition.
DECLARE @fname varchar(50)
declare @name nvarchar(500)
SET @fname = ‘C:\Test.txt’
set @name = ‘BULK INSERT tempdate2 FROM ”’ + @fname + ”’ WITH (ROWTERMINATOR = ”\n”)’
exec sp_executesql @name
note: all quotes are single quotes.
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
The correct method is
INSERT INTO table(field1,field2, field3)
SELECT id,1 FROM table2 where field1= 'hello'
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
In SQLPLUS you need to use FROM DUAL
ex
SELECT ‘First’ ,1 FROM DUAL
UNION ALL
SELECT ‘Second’ ,2 FROM DUAL
Also note that this site is for MS SQL Server
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 )
That method is now available in verstion 2008
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.
No. It is not possible
Why do you want to do this?
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.
Why do you need this?
Can you give us more informations on what you want to do?
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
Becuase you teied this code at ORACLE where you need to use FROM DUAL like
SELECT 5 FROM DUAL
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
Yes it will work
Have you tried it?
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
Immediately after running the query, run this
SELECT @@ROWCOUNT
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
Use
ORDER BY cast(eventdt +’ 2000 as datetime)
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!
This is possible only from the version 2008 of SQL Server
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
Try
begin transaction
insert into table1 ( c1,c2) values ( ’1′,’2′)
insert into table2 ( c1,c2, c4) values ( ’1′,’2′,’4′)
If @@error0
rollback
else
commit
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
Most likely it should be
Delete from child_table
where col not in
(select col from parent_table)
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?
It should be something like
Insert inot target_table(column_list)
select col, your_value1,your_value2,….
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)
Where do you have the source data?
If you want to insert 10000 rows in one shot, better have it in a text file and import to table using BULK INSERT
http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx
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
Example
Insert into target_table(column_list)
select column_list,static_value from another_table
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
You need pagination
Refer point 4
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx
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
into sal_history values(…..)
select col1,caol2,…. from tableX
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.
Yes you can use set based approach
Provide more informations on what you are trying to do
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.
This is kind of cross-tab report
Read this and make use of it
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
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
Yes it is possible as long as the datatypes of the columns match correctly
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.
You can simply use
Update employee_table
set manager_id=0
where manager_id is null
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
Yes it is possible from SQL Server 2008 version onwards
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
You need a dynamic SQL
Make sure you read this article fully
http://www.sommarskog.sq/dynamic_sql.html
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
Here you go
declare @sql varchar(max)
set @sql=”
select @sql=@sql+’ update ‘+table_name+’ set first_name=”peter” where first_name=”john”’
from information_schema.columns
where column_name=’first_name’
exec(@sql)
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.
It is because you were trying to insert data to two columns from three values. You need to include third column in the INSERT Statement
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.
select columns from your_table
where username like ‘[a-z][a-z][a-z]%’
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
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
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
You need to double each single quotes
Refer this to understand how single quotes work in SQL Server
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
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 .
You dont need braces in SELECT statement. Remove them and try
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..
It should be
Insert into dbo.facthurricane (DIM1, DIM2,Nameid,WIND,PR)
select dim1 from dbo.locationD
union all
SELECT DIM2 from dbo.DIMTIME
union all
SELECT NameID from dbo.NameD
union all
SELECT WIND from dbo.StagingHurrincane
union all
SELECT PR from dbo.StagingHurrincane
I realise that you want to insert to different columns by taking values from different tables. My previous solution is not correct
You need to join all the tables based on some common columns and take correspoding columns like
Insert into dbo.facthurricane (DIM1, DIM2,Nameid,WIND,PR)
select t1.dim1,t2.DIM2 , t3.NameID , t4.WIND ,t4.PR from dbo.locationD as t1
inner join dbo.DIMTIME as t2 on t1.key_col=t2.key_col
inner join dbo.NameD as t3 on t2.key_col=t3.key_col
inner join dbo.StagingHurrincane as t4 on t3.key_col=t4.key_col
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
Try
select name, age, ID = IDENTITY (int)
Into #tempTable
from (
select name, age from Table1
where …….
UNION
Select name, age from Table2
) as t
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.
In ORACLE you need to use DUAL
INSERT INTO mytable(id,name)
SELECT 1, 'pizza' FROM DUAL
UNION all
SELECT 2, 'donuts' FROM DUAL
UNION all
SELECT 3, 'milk' FROM DUAL;
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 on my email id
i have a one solution that is Primary Key but i can’t keep Primary key For Item Code . it can be dublicate saling same item.
now a am going to do ID Of Record No and ItemCode (ID + ItemCode) as a Primary key but
still question is hungup why it is happening like that
i have a dought some time network slow that time happning
but i am not sure about that
Pls. any budy know reply me.
Well i liked ur site . i will soon post my probs regarding sql server 2005
For those using ORACLE:
Oracle NEEDS a FROM in the SELECT statement, so just use a FROM DUAL to make it work with ORACLE.
Example:
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT ‘First’ ,1 FROM DUAL
UNION ALL
SELECT ‘Second’ ,2 FROM DUAL
UNION ALL
SELECT ‘Third’ ,3 FROM DUAL
UNION ALL
SELECT ‘Fourth’ ,4 FROM DUAL
UNION ALL
SELECT ‘Fifth’ ,5 FROM DUAL
;
If i use update command for particular table my condition statisfy for example three rows. I dont want update three rows, so i want update Row wise if one row updated means i want break.
Hello,
I need help please send to my mail
Table
year,Sname,FName,RollNo,Class,paidDate,paidAmount
here i want to get grand total with all columns in the table
it should be displayed under Paidamount
please reply as soon as possible
thanking you
Hello,
I need help please send to my mail
Table
year,Sname,FName,RollNo,Class,paidDate,paidAmount
here i want to get grand total with all columns in the table
it should be displayed under Paidamount
please reply as soon as possible
thanking you
Try
select year,Sname,FName,RollNo,Class,paidDate,sum(paidAmount) as grand_total from your_table
group by year,Sname,FName,RollNo,Class,paidDate
Hi Pinal,
The solution was very Useful….
Thanx
USE master
GO
INSERT INTO emp (ename, eid)
SELECT ‘First’ ,4
UNION ALL
SELECT ‘Second’ ,6
UNION ALL
SELECT ‘Third’ ,7
UNION ALL
SELECT ‘Fourth’ ,8
UNION ALL
SELECT ‘Fifth’ ,9
GO
Hi. Is there a way to do multiple insert statements while ignoring duplicates (varchar type) if the id is auto_increment?
Nice blog btw – thanks for sharing your knowledge.
Thanks!… this is an interesting solution
Hi mate.
First off, this is a fine solution to reduce network load if you have a lot to insert.
But suppose you never know how much needs to be inserted, eg somewhere between 1k and 25k records. You write a method to make a statement that inserts all the records at once (using your neat trick). Will it still work if you insert 25k records in one statement (assuming records of considerable size)? Does SQL Server impose a limit in this context? This should be documented, should it not?
Also, will SQL Server choke on making a ‘temporary table’ before inserting it into the target table if you have a lot of records to insert? I wonder what the slow down is in terms of records… Maybe SQL Server optimizes this and inserts the records into the target table at once?
Regards,
TT (BE)
sir,,,i have two different values but column name is same and where clause column name is same…so how can i update record with it….
ex…
update tblName set Same_clmnName=’var1′, same_clmnName=’var2′ where differ_clmnName = ’001′ . while variable value ill be one at a time….means either var1 having any value or var2 having any other value Plz help me….
zaved warsi
query 1-
update tablename set clumName = ‘var1′ where id = ’001′
query 2-
update tablename set clumName = ‘var2′ where id = ’001′
i want to update record depending upon variable value if exits then update column name otherwise update var2 value…
like this…
UPDATE feedbacktbl SET `fullname` = CASE WHEN `sn` =2 THEN “za” WHEN `sn` =3 THEN “ma” END ..
but here is different sn number……i want same sn number and same coulumname ‘fullname’ but different variable name…i hope u got it…
haha, Good Blog for me, Fresh Uesers.
thank you!
SQL Integration Services package is the best way to import multiple rows (Provided the number is significant, otherwise the overhead of package execution can be an overkill) into a Sql Database.
Happy Coding
Nash Vyas
sir
i want to insert values entered by user into the textbox to the table into database sir please tell me the command.
plz check this code
string insertSql;
insertSql = “INSERT INTO dcfuture.FIRST(username,firstname,secondname,age,rollno,email)”;
insertSql +=”VALUES(‘” & lastnametextbox.Text& “‘,’” & agetextbox.Text & “‘,’” & rollnotextbox.Text & “‘,”;
insertSql +=”‘” & emailtextbox.Text & “‘)”;
You have missed to pass values for the column names username and firstname
Hi All,
I want match where condition record in select statement,
suppose where condion have 3 record and i want to match 1
record and find result,and vice versa
my query is below
Select max(groupid) as Groupid,title from incidenthistory where title in (select Title from incidenthistory where GroupId=’2′ Group by Title) and assigneeid is not null
group by title
In above query if title come in groupid=4 then
it should not come when using query groupid=2
Any Help Appreciated to me……..
Thanks In Advance
Hi,
I want to insert values in a table from 2 different tables which don’t have any common field
say all columns of table 1 and then a single date field from table2
can u help plzzzzz..
If you use versions starting from 2005,
Insert into target_talbe(column_list)
select t1.col,t2.col1 from
(
select *, row_number() over (order by (select 0)) as sno from table1
)
as t1
left join
(
select *, row_number() over (order by (select 0)) as sno from table2
)
as t2
on t1.sno=t2.sno
union all works great with up to 2500 records ..
if i use more the query returns -1 as number
of rows affected.
I am using sql 2005
Please help
Great web page:
I’m trying to display rows of data that have duplicate records side by side with the miles increasing.
SELECT “CapDer”.”cder_capcode”, “basetableversions”.”TV_PubDate”, “FutureResidual”.”fr_ID”, “FutureResidual”.”fr_mileage”, “FutureResidual”.”fr_6″, “FutureResidual”.”fr_12″, “FutureResidual”.”fr_18″, “FutureResidual”.”fr_24″, “FutureResidual”.”fr_30″, “FutureResidual”.”fr_36″, “FutureResidual”.”fr_42″, “FutureResidual”.”fr_48″, “FutureResidual”.”fr_54″, “FutureResidual”.”fr_60″, “FutureResidual”.”fr_66″, “FutureResidual”.”fr_72″, “FutureResidual”.”fr_78″, “FutureResidual”.”fr_84″, “NVDPrices”.”PR_Basic”, “NVDPrices”.”PR_ModifiedDate”, “basetableversions”.”TV_PubSeq”
FROM ((“PUB_CAR”.”dbo”.”CapDer” “CapDer” INNER JOIN “PUB_CAR”.”dbo”.”FutureResidual” “FutureResidual” ON “CapDer”.”cder_ID”=”FutureResidual”.”fr_ID”) INNER JOIN “PUB_CAR”.”dbo”.”NVDPrices” “NVDPrices” ON “FutureResidual”.”fr_ID”=”NVDPrices”.”PR_Id”) INNER JOIN “PUB_CAR”.”dbo”.”basetableversions” “basetableversions” ON (“FutureResidual”.”fr_pubdate”=”basetableversions”.”TV_PubDate”) AND (“FutureResidual”.”fr_pubseq”=”basetableversions”.”TV_PubSeq”)
WHERE (“basetableversions”.”TV_PubSeq”=1051) AND (“FutureResidual”.”fr_mileage”>5 AND “FutureResidual”.”fr_mileage”15 AND “FutureResidual”.”fr_mileage”<21)
ORDER BY “FutureResidual”.”fr_mileage”
is it possible,
Any help appreciated.
Steve.
What i’m asking is how do i display a set of records for miles = 10, then on the same row the same set of records with values for miles = 20 etc.
Another way to explain:
How do I:
Select “a”,”b”,”c” ,”d”,”e”
From “Table a”
Where “miles”=10
AND
“c”,”d”, “Price”,”date”
Where “Miles”=20
Order BY “a”
But in columns: “a”,”b”,”c” ,”d”,”e”,”c”,”d”, “Price”,”date”
I’m new to sql and need it this format to export to excel.
UNION ALL just puts the records in a vertical format and requires an equal SELECT expression.
Any help at all please.
You need PIVOT
refer this
For version 2005
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx
Fro version 2000
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx
Hi Pinaldave,
i have 2 lines data in the same field which means after the end user typed the first line he press enter and continue typing in the second line of the same field (cell).
How can i delete just the second line
Thanks
thanks a lot….
GREAT TIP — But I felt that I had to contribute a little piece about efficiency.
We had a basic insert statement where we were inserting an ID and a Date. Our problem is that we had approximately 25,000 IDs/dates that we were trying to insert.
Using the standard INSERT INTO convention took about 8 minutes.
I converted to the convention described in this article and it actually took longer than the INSERT INTO method. I gave up and did not look at the code again for several weeks.
HOWEVER, by accident, I tried this format again with 1,000 records and it worked in 3 seconds. Like other people have said, the # of columns and the # of records have a HUGE effect on how efficient this statement is.
Long story short, using the code above I broke up my 25,000 record set into groups of 500. The net result was a script that executed in 21 seconds, a HUGE improvement over the original 8 minutes.
Thanks again!!
[...] 2, 2008 by pinaldave 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. [...]
Hello,
Need Help in SQL Server 2005.
I need to update few column in a table. So i have putted 2 table joins (All join are putted correctly) and used update lock also. There are nearly 7 lac records are there. Its takeing nearly 20-25 min.
Can you please suggest me what can be done to reduce time and quick update.
Thanks
Jitu
Can you post the code you used?
Dear Sir;
Congratulation for your fantastic web site.
My problem is, I use a FlexGrid to collect the user input records(1-500 Records.) and then when user finished, I read the flex in a Array and with a Loop insert them in two tables(SQL Server 2000).
Is there any way to insert to the tables with one sql query?
I mean I don’t want to use FOR loop to insert them.
Thanks a lot.
Farid.
Pefromance degrades massively over large datasets. For 10000 inserts over 2 columns a standard multiple insert takes about 4 seconds or so whereas this technique takes around 1 minute 27. Not so good!
1. How to insert bulk data in sql table from text file.
if text file is delimited, and only insert third or fourth number delimited value want to insert in table.
2. How to get lacs(more then 8 lacs) of record from table.
when i am executing select query, my pc raise an error of virtual paging error.,
can you tell what is the error in the following statements.
insert into emp_new (name,id,salary)
values (‘Jony’, 215, 20000);
Your blog is very helpful to us.
Thanx
The syntax is correct
Did you get any error?
why are we using UNION when INSERT INTO is doing the same task.Is there any signigicant difference as regards query optimization?
Hai Pinal Dave,
I am Nirmal Singh Working as SE in Madurai.
Your query trick helps me in many situations.
Thank you much.
All the Best.
@Payal,
When you use Union all , you still need to use INSERT INTO.
If you carefully see the code, you will recognize that, if you want to insert 5 records in a table then you will write 5 insert into statements, but when you use union, you can insert 5 records in single insert statements.
Thing to notice is, it is executed only once and 5 records are inserted in the table, It is doing the same work as 5 insert statements.
Hope this helps,
Thanks
Imran.
Hi,
I need to insert data into multiple tables based on one single select on one table only.
Its like i have a requirement wherein some of the column data needs to go in one table and the other columns data into a separate table.
Please do let me know what to do with respect to this ?
Try the code like this
Insert into target_table1(column_list)
select required_columns from source_table
Insert into target_table2(column_list)
select required_columns from source_table
Insert into target_table3(column_list)
select required_columns from source_table
.
.
.
For one of my websites I need to simply copy a set of records or rows from one table to another table. The tables are part of one database only. How can I achieve it. Should we use a datareader to do it?
Insert into target_table(column_list)
select column_list from source_table
where some_condition
@webdesign,
There are many atleast two ways that I know to copy tables.
1(a) Create a new table which is similar to your original tables, and then execute this statement, this will copy all your records from one table to other table.
insert into new_table select * from old_table
1(b) If the table is very big and you want all the constraints, indexes, primary keys… foriegn keys… and everything related to table, then I suggest script the original table,
To srcipt original table – In object explorer, click databasename, click table name and then right click on table , click script table as and select Create to.
Run the script on the same database, but change the name of the table and change the name of foriegn and change keys etc…. and your new table is now ready,
execute the same statement to copy all the data in this new table.
2. If you dont want any constraints, keys, indexes , then simple execute this statement.
this will create a create table and also insert all data, but there will be no constraints , keys ….
Select * into new_table from old_table
You can also do this through DTS package …
Hope this helps.
Thanks,
Imran.
webdesign specified that tables are part of database which meant they already existed and you need sime
INSERT INTO…. SELECT syntax
this statement is new to me, good to know that we can insert multiple record using select and unionall ,
Dear sir
How can i update & insert the records in two join fields in sql 2005. whereas i m updating the records from MS-Access Forms
pls. help
rahul
9910839188
Wonderful Information. I really appreciate your work towards this site.
Though I have potential experience in Oracle, this site helped a lot as I am working on SQL Server now.
Keep going and Rock.
Thanks,
Srikanth Kondeti
INSERT INTO dbo.MOTO_MGPS_STG_PRICE_LIST_DATA
(SEQ_id, Price_List_id, Price_List_Name, Currency_Code,
SKU_Name, Price, Start_Date, End_Date, Updated_By_On, Approved_By_On,
Source_Syatem, ERP_EXTRACT_DATE, WM_LAST_UPDATE_DATE, WM_PROCESS_FLAG, WM_ERROR_MESSAGE)
VALUES
(1, 100, ‘moto’, ‘dollar’, ‘Asia’, 500.00, 23-JUL-2008, 24-JUL-2008,
‘Ravikiran’, ‘Murali’, ‘Ebiz’, 25-JUL-2008, 21-JUL-2008, ‘Y’, ‘INITIAL PHASE’)
please tel me what did i missed in this.trying to insert but failed.
Note that date values should be passed within single quotes
Use single quotes around the date values
@Ravikiran,
You mentioned one column name as “Source_Syatem” , is it correct, I think it should be Source System.
Second thing, if everything is correct, then may be the datetime values you are passing are incorrect, check the format how table is configured.
a) check if the date column has datetime datatype or character datatype.
b) if it is character then you need to change the values you are passing.
c) if it is datetime then check the formatt ( style of date).
Also check the datatype of “price” field, it is suppose to be decimal ( X.2) – where X can be any value.
Hope this helps
Thanks.
I am trying to insert above 5000 records in a database using the “select” and “union all” statements. i am getting error “Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
It really helps to insert multiple values in a single table.
Following is the one way by which we can insert the value:-
DECLARE @Authors TABLE(lastname VARCHAR(20), firstname VARCHAR(20))
INSERT Authors VALUES (‘asd’,'asd)
INSERT Authors VALUES (‘gfh’,'fgh’)
……
…..
…..
SELECT * FROM @Authors
GO
Hello pinaldave,
Myself Madhusudan Pawar working as Soft. Engg in Mumbai.
I have read ur blog its really helpful.
Wish u very good luck.
Thanks
hi Pinal,
I read this articles. very nice. Can u tell me some more.
Actually i used one stored procedure for inserting multiple values from asp.net. The number of rows inserting is according to the user requirement. So i create a command object with the stored procedure name with parameters and put one loop statement for inserting the values. here u provide one method for multiple insertion but i think this method is static ie if i put 5 rows i can use 4 union all like that. But i cant assure the number of rows. so how i can do with one insert query or stored procedure.
Dear Pinal Dave
I have a field named reminder as datetime in database.
I need to check whether today’s date and month are equal to the reminder date and month. don’t consider year.
So how this can be compared.
Please Help
Regards
Deepak Biradar
One method is
select columns from your_table
where day(date_col)=day(getdate()) and month(date_col)=month(getdate()
how can i isert more than one rows in a same field, one of the field is set as primary key. inserting each row is not possible . please give a solution.
Hi Everybody….i want to write sql script which will insert 1000 data in a table..and i need to complete the script by 2morow morning..i need to auto increment the number i.e..422000001199,the last 4 number needs to be changed..any idea how to proceed plz..thanks in advance
Why don’t you use newid() which will generate auto ids for you.
select newid(), contactname
from Customers
I have used northwind database.
i want the syntax of inserting multiple rows with
insert into tablename values(‘&col name”);
Hi!
Good work guys …
I tried to migrate a table from MySQL into MSSQL 2000 using the same technique mentioned in this website using multiple SELECT and Union Alls .. but the trouble is it works fine on SQL Server 2005 Express till about 2000 records … but in SQL Server 2000 I have been facing:
“The query processor ran out of stack space during query optimization” error …
So, be carefull if you are running this query on SQL Server 2000… Due to 8k limit of Row Data in SQL Server 2000 and SQL Server 7.0.
Cheers
Aleem Latif
————–
Sir,
i want to insert records in a table but in ascending order by name or ID.
how to do that.
regards,
Anirudh Sood
Programmer
Storing Ordered data doesn’t matter
You should explicitely use Order by clause in the SELECT statement
Oddly, this approach was way slower for me.
hi Thanks the solution. but i need to insert 500 records at a time and inserting for different values every time is difficult.. plz can anyone help me on this.
this is very useful to new developers such as me, very thanks to the author.
thank you! you saved me :)
i have a problem
all of my data is stored in a text file, each column seperated by a comma, and each record in a new line
how can i insert this data in an oracle sql table
This insert into -select works great, thanks very much
Hello sir,
how to insert the records for multiple tables using one form
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.
hai i using stored procedure to insert records in to the database table taking only 99999 records not morethan that please tell me where i have to do changes
Thanks in advance
Hi Pinal
This was realy an informative forum for me. I want to ask one question if u can answer that what is the replacement of [Sum(column) over(partition by column order by column) as Alias]
in sql server 2000. This function is supported in sql server 2005,2008 and in oracle.
Regards
Ijaz, Pakistan
SELECT column , sum(some_col) from your_table
group by column
order by column
hi,
i am new to sql server 2000
I have a table W_ORDER_F with 64 coloumns and 532 rows . i need to create a backup table W_ORDER_FBKUP.
can you healp me to get the syntax?
with regards
Rani
This is one method
SELECT * INTO W_ORDER_FBKUP FROM W_ORDER_F
Hi !!
I created Unicode DB using sql 2005 and insert value using this statement,
Insert into Test1(Name,Address ) values(‘vasana’ ,N ‘Ήεàĺћ & §àεý’ );
This work fine,
The problem is with my select statement,
select Name,Address from Test1;
it give result like this
vasana , ???????
Can u please tell me how to write proper select statement to get Unicode value .
Thanks.
hi,
java and sql query for getting primary key of recently inserted row
Hi,
which one better ‘OR’ or ‘IN’ to filter a result set
Hi Pinal,
I have 2 tables as below :
1. mst_Customer
2. mst_Installment
mst_Customer contains customer infor with unique id which is tagged with mst_Installment.
I would like to retrive only 1 record from mst_installment on the basis certain criteria like date etc. and would like to show against mst_customer table record. Query I am ready with return proper record if I use top 1 but when it comes to more than that result is not proper at all..
Hi Pinal,
I accidentally browsed ur blog.Its very informative.keep up the good work..when i followed ur same techinque and got this error,can u pl help. iam new to this sql concepts
SQL> insert into employee_usa(e_id,e_name)select 100,’pranu’ union all select 101,’satyam’;
insert into employee_usa(e_id,e_name)select 100,’pranu’ union all select 101,’satyam’
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
For ORACLE you need to use FROM DUAL
insert into employee_usa(e_id,e_name)
select 100,'pranu' FROM DUAL
union all
select 101,'satyam' FROM DUAL;
union all
select 100,'pranu' FROM DUAL
union all
select 101,'satyam' FROM DUAL
Hi Pinal,
I am trying to insert data from one table to another using a procedure,but its erroring out.
Insert into table2 select * from table1 where col1=@var
Error:Incorrect syntax near Insert statement
Regards
Babu
@Babu,
Script what you posted seems to be incomplete, and may be error is in the script which you did not posted here… I dont see anything wrong in your script you posted. Just check the line above insert statement, make sure you declared @var variable…
here is an example…
CREATE TABLE table1 ( STATUS VARCHAR(50))
go
INSERT INTO table1 (STATUS) SELECT ‘GREEN’
go
CREATE TABLE table2 ( STATUS2 VARCHAR(100))
go
DECLARE @var varchar(100)
SET @var = ‘GREEN’
INSERT INTO table2 SELECT * FROM table1 WHERE STATUS = @VAR
go
select * from table2
go
drop table table1, table2
works perfectly fine with me…
Regards,
IM
Hi Pinal,
Good to see your website which gave a lot of solutions.
And I have a question..
Why we go for table variables rather than cursors and views?
Can you please clearify?
Thanks
Regards Prakash
Hi Praveena,
insert into employee_usa(e_id,e_name)
select 100,’pranu’
union all select 101,’satyam’
Its working in sql server 2005..
Which DataBase are you using?
Regards Prakash
Hi Durai..
I just went to this site today..and cheked your comment..
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
so u can do like this
select * from Table where ID=
(SELECT MAX (ID) FROM Table WHERE ID in
(SELECT top 7 ID FROM Table ORDER BY ID ))
This is not only for ID ,from any column name u can get the corresponding row..
Regards Prakash
Hi Durai,
Even better way for that…
select top 1 * from Table where ID not in (select top 6 ID from Table)
Methods 5 and 6 may be better
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/find-nth-maximum-value.aspx
Hi All,
I want to get max id from that table where i am inserting data by ‘Select’ statement.
I have follwing T-SQL and Function:-
1. T-SQL:::::
INSERT INTO Risk([Description],RiskCode)
SELECT distinct(Risk),dbo.ReturnUniqueRiskID(Risk)
from ImportRiskControlErrorLog
2. Function:::::::
ALTER FUNCTION [dbo].[ReturnUniqueRiskID]
(
@Risk VARCHAR(50)
)
RETURNS VARCHAR(50)
BEGIN
Declare @UniqueRiskID VARCHAR(50);
SELECT @UniqueRiskID=’R-’ + CAST(MAX(CAST(SUBSTRING(riskCode,3,9) AS INT)) + 1 AS VARCHAR(50)) FROM Risk
WHERE RISKCODE LIKE ‘R-%’
IF @UniqueRiskID IS NULL
Set @UniqueRiskID=NULL;
Return(@UniqueRiskID);
END
Regard
Kris
You are really greate Dave. The article was posted nearly 1 and half year back. Still there are comments running on this article. This is the first time I have ever seen. Keep it up Dave.
Hi Pinal,
Can you please suggest your favorite method from above article when you have 50 insert statements(4 fields to update) with each field’s value not exceeding 100 char – there may be 50 concurrent users ?
Many Thanks,
Rumba.
Hi all,
can you please send me coding for the insert the values from different forms. my coding capture the 2nd form values only. this is not capture the first form. so please help me
thanks
This is a good suggestion it does not perform well when inserting lots of records (20K in my case).
I did a comparison between the Method above (using Union All) and using a stored procedure.
I’m trying to insert:
total columns in table: 2
columns to insert: 2
rows sample: 20,000 rows
data types: int
table status: empty before insertion
Method one:
—————
using stored procedure:
basically the sp takes two parameters, one for each column, and it will do a straight insert into the table as in:
————————————————–
Create PROCEDURE [dbo].[sp_record_Insert]
@id1 int,
@id2 int
AS
SET NOCOUNT ON
insert into myTable(id1,id2) values(@id1,@id2)
————————————————–
and the calling code is like:
————————————————–
exec sp_record_Insert 140319,47857539
————————————————–
this is executed 20,000 times to insert 20,000 records
TOTAL Execution time: 3 seconds
Method 2:
————
Using the Union All method took: 5 minutes and 9 seconds.
So I beleive you can use the union all method on smaller number of records, but once you go beyond that, you are better off trying something else.
Hi Folks, wanted to report my real-world test on this one.
We have a database operation doing 2.5MM row data loads on a daily basis – so performance is of interest.
We ran a test with the 3 different syntaxes from above:
1) many individual inserts, one per row
2) one multiple-row Insert, one total for all rows
3) Union All
Loading into a 9 column table with a couple of indexes on it, we found the following:
1) 00:03 – Even with the extra overhead of the command, this is by far the fastest for us.
2) 00:10 – A little slower.
3) 01:30 – The slowest of the bunch. Maybe better for smaller sets?
We ran on Windows Server 2008, with SQL 2008. This is a quad-core with Intel 2.83GHZ cpus, 8GB RAM, and a 7200 RPM data-drive.
oh, um the test was run on 5,000 rows.
Hi Pinal,
iam Really very happy to see your website.It is really Healpful for me.
Best of luck
Hi Pinal,
i am trying to do multiple inserts from a table to the same table by changing a field value and i am also have the increment the id ( primary key ) by 1 with each insertion.
How can i increment the recordid with each insertion?
( i should not use identity auto increment )
i tried …
insert into table (id,…..)
select (max(id) + 1), …. from table
ERROR: Violation of PRIMARY KEY constraint ‘PK_IDtable1′. Cannot insert duplicate key in object ‘dbo.table1′.
Hi,
I have written following procedure for inser, where I want to pass table name from application program. But its not working. Same format works for create table.
This procedure giving me error as
Error converting data type varchar to datetime.
my procedure is
Create PROCEDURE [dbo].[InsertSalesOrderDetail]
(
@tablename varchar(20),
@SalesOrderID int,
@SalesOrderDetailID int,
@CarrierTrackingNumber nvarchar(25),
@OrderQty smallint,
@ProductID int,
@SpecialOfferID int,
@UnitPrice money,
@UnitPriceDiscount money,
@LineTotal money,
@rowguid nvarchar (50),
@ModifiedDate datetime)
AS
BEGIN
SET NOCOUNT ON;
exec(‘ INSERT INTO [AdventureWorks].[dbo].['+ @tablename +' ]
values( @SalesOrderID,@SalesOrderDetailID,@CarrierTrackingNumber,@OrderQty,@ProductID,
@SpecialOfferID,@UnitPrice,@UnitPriceDiscount,@LineTotal,@rowguid ,@ModifiedDate ) ‘)
END;
pls help
tell me how to pass table name, if we specify the table name in query, then it works.
Thanks
The same procedure gives error :
Must declare the scalar variable “@SalesOrderID”.
I have declared this variable……
pls help
thanks
I want to know how to insert a multiple rows to a table using ‘&’ symbol in SQL query….Please do help me…
Thanks for sharing valuable tips here. Really it helps lot.
You indeed get a big performance increase, but if you try to use this method where it really counts (like inserting 100k records) it will crash your query compiler and fail to execute. A query simply can’t be that big and complex. Optimizing inserts like this is possible only on mysql.
I have a stored procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_InsertIMEIDetails]
@XMLDOC varchar(8000)
AS
Declare @xml_hndl int
exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC
BEGIN
INSERT INTO IMEI_IMEIDetails (SerialNumber)
SELECT SerialFrom
FROM OPENXML(@xml_hndl,’//SerialNumbers/SerialFrom’,1)
With (SerialFrom varchar ‘@id’)
END
I want to insert a series that the user will enter from the form the series will start from “000000″ and will end on “999999″ so we are talking about an insertion of 10 Lakh records in one go and the second problem is I want to store the numbers in the same format as they are getting entered.
Hope I am making sense here, can some one help me on this ASAP.
Thanks!
hi,
i have a little doubt related to insert query.
i have a form in which i have 7 text boxes.
2 text box values stores in table customer.
5 text box values stores in table address.
and i have relate these 2 tables with a foreign key i.e, customer_id is added as new column in address table.
Question?
i want to insert the customer_id into table address as when click the submit button of the form.
and i am inserting all the values from a single Stored procedure first inserting table customer values then tables address values.
so plz tell me how can i do this.
thanks in advance
Thanks a lot! This was very useful and saved me tons of time.
Hi,
Thanks for this tutorials, it’s cool and really helps me a lot in solving my problem.
Hope to know more from you.
Thanks!
Hi
I created below store procedure to update the flag of status to 4 from 2.This will invoke when user hit my web site because written in global.cs file under application_beginrequest event in asp.net.Already so many records will got expired and in status 4 but i need the data of last updated records.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Create PROCEDURE adStatusUpdate AS
UPDATE [addetails] set ad_status = 4
WHERE (ad_status = 2) AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() ))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Actually i want to expire an ad after validty period get over and also notify user about expiry of their ad using mail.
when i update a query it will display 12 records updated instead Can i get the last updated records like select query so that it will be useful to get their mailids from respective tables or can i store the updated records in another table using insert query
insert into tablename
values (UPDATE [addetails] set ad_status = 4
WHERE (ad_status = 2) AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() )) )
or
select * from aduser (UPDATE [addetails] set ad_status = 4 WHERE (ad_status = 2) AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() )) )
select userdetails.user_email,addetails.ad_title,
addetails.auth_code,addetails.ad_status,
addetails.ad_publishing_date
from userdetails inner join addetails
on addetails.auth_code=userdetails.auth_code
and addetails.ad_status = 2 AND ( ( DATEADD(dd, ad_validity, ad_publishing_date) <= getdate() ))
Kindly sort out my problem else provide me alternate solution to match my scenario.
Thanks in advance.
Hi all,
I tried to run the following query an obviouly i didn’t execute. I know this is terribly wrong. Please help me out. The select statement is to get values from another table and it will return ultiple values which I have to insert into reasonforchange table.
insert into reasonforchange(reasonforchange_id,reasonforchange) values
(newid(),select distinct displayvalue from oc_picklistvalues pv inner join oc_picklistlanguages pl
on pv.picklistid=pl.picklistid where pl.shortdescription=’Reason for Change’and pv.displayvalue=’Promotion’)
@Arvind,
The query is failing because the select statement is returning more than one value.
Test if the query works, using select top 1 ……. something like this,
insert into reasonforchange(reasonforchange_id,reasonforchange) values
(newid(),select top 1 displayvalue from oc_picklistvalues pv inner join oc_picklistlanguages pl
on pv.picklistid=pl.picklistid where pl.shortdescription=’Reason for Change’and pv.displayvalue=’Promotion’)
Work Around : Why don’t you create a default on ReasonForChange column , keep the default as newid(), then insert statement will be,
insert into reasonforchange (reasonforchange) select distinct displayvalue from oc_picklistvalues pv inner join oc_picklistlanguages pl
on pv.picklistid=pl.picklistid where pl.shortdescription=’Reason for Change’and pv.displayvalue=’Promotion’
Hope this helps,
Regards,
IM.
how can i set the logging options through the database management interface in sql server 2005
Actually i want, Record logging shall be handled within the database, and not within the application. The user
shall set the logging options through the database management interface in SQL Server 2005.
Very good concept
Hi ,
I want to insert value into particular column,there is any statement like using “where” condition…”insert table table_name values value1 where id=111″ etc.
Please let us know if any statements like..
Thanks and Regards,
Phani..
Hi Pinal:
I am stack in a piculiar situation.
I have a stored proc (SQL 2005) which is compilled and executed successfully. I used cursor in my stored proc.
Stored proc executes on a table and match data with another table dada. So it will either update existing data or if new data come will insert new data into second table.
My updating command is working ok. But inserting commend is not working. I know in my first table I habe 7 new data which are waiting to insetr into second table.
I’m trying to find it out where is the problem but couldn’t.
Would you please help me. I have to delivery my project by the end of the second week but I’m stack here.
Here is my Stored proc:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[AddMonth_Event_TotalInfo]
–@fiscalYear int
as
Declare
C1 CURSOR READ_ONLY
FOR
SELECT CONVERT(varchar(7), IdsData.EarliestEvent, 111) AS [Yr-Mon], TagType.TagTypeID AS EventID, ISNULL(SUM(IdsData.EventCount), 0) AS Total
FROM TagType INNER JOIN
TagDetails ON TagType.TagTypeID = TagDetails.TagTypeID INNER JOIN
IdsData ON TagDetails.[TagName (Unique)] = IdsData.TagName INNER JOIN
SensorCategories ON IdsData.AgentIP = SensorCategories.SensorIP
WHERE (SensorCategories.Monthly = ‘Y’)
GROUP BY CONVERT(varchar(7), IdsData.EarliestEvent, 111), TagType.TagTypeID
ORDER BY [Yr-Mon], EventID
Declare
@MonthID int,
@EventMonth varchar(7),
–@EventName varchar(30),
@EventID int,
@Total int,
@EventTotal int
–delete from Month_Event_TotalInfo
OPEN C1
FETCH NEXT FROM C1
INTO @EventMonth,@EventID,@EventTotal
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @MonthID=isnull(MAX(Month_ID),0)+1 from Month_Event_TotalInfo
select @Total=Event_Total from Month_Event_TotalInfo where Event_Month=@EventMonth
and Event_ID=@EventID
if (@Total>=0)
update Month_Event_TotalInfo
set Event_Total=@EventTotal where Event_Month=@EventMonth and Event_ID=@EventID
else
insert into Month_Event_TotalInfo(Month_Id,Event_Month,Event_ID,Event_Total)
values (@MonthID,@EventMonth,@EventID,@EventTotal)
FETCH NEXT FROM C1
INTO @EventMonth,@EventID,@EventTotal
END
CLOSE C1
DEALLOCATE C1
what is the max rows can we insert into one table
What is SESQL
HI Pinal,
please help me out…i have a multiple insert query in a file which is about 125 MB (they are around 5 lac inserts). I tried to fire the osql command but it says that it cannot process more than 1000 inserts at a time…i cannot put GO lines after every 1000 inserts in that file…please help how to overcome this issue
Hi guys,
I like pinal site very much, but somebody posting waste answers, please don’t do like that. So please post only correct answers.
[...] SQL SERVER – Union vs. Union All – Which is better for performance? SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL [...]
[...] SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL Insert multiple records using UNION clause. [...]
Better way:
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1), (‘Second’,2), (‘Third’,3), (‘Fourth’,4), (‘Fifth’,5);
Hi,
Every one of u r experts. Pls help me with sp syntax that will insert values into a table Named “Reference”
which has fields
ReferenceID int, ReferenceNo bignt, ReferencedBy varchar(50)
the sp will be called like
exec sp1(1,100)
it needs to insert 100 rows starting from 1 to 100 into the Reference field.
Waiting for your help
The basic code is
SELECT number FROM master..spt_values
where type=’p’ and number between 1 and 100
Also refer
http://beyondrelational.com/blogs/madhivanan/archive/2007/10/10/generating-random-numbers-part-ii.aspx
Thanq for the above solution which was very useful to me.(About insertion of multiple values at single instance through query).
Hi All,
I am happy to get the code for inserting multiple records using one INSERT statement.
Special thanks goes to Pinal Dave.
cheers.
Hii,
Nice solution, thanks for sharing.
With Many Regards
hai,
i need insert multiple records table with clear example like table name as customer or employee etc for clarity im in starting stage
thanking you,
sreetheja.
Hi,
I am inserting multiple records in to a table through .Net application. With each insertion i have to increment the recid field with 1.
I tried,
insert into table2 (recid,…..)
select (max(recid) + 1), …. from table2
this is working for only 1record insertion. If I insert more than 1 record then the recid value is not getting incremented for each record I insert.
So, Can anyone give a solution for this???
create table product_master
(
product_no varchar2(6) primary key check(product_no like ‘P%’),
description varchar2(5) not null,
profit_percent number(2,2) not null,
unit_measure varchar2(10) not null,
qty_on_hand number(8) not null,
reorder_lvl number(8) not null,
sell_price number(8,2) not null check(sell_price>0),
cost_price number(8,2) not null check(cost_price>0));
insert into product_master values(‘&product_no’,'&description’,&profit_percent,’&unit_measure’,&qty_on_hand,&reorder_lvl,&sell_price,&cost_price);
ORA-01008: not all variables bound
can u plz explain why this error came and how to solve it
HI,
I just want to insert a new row in between 2 rows & also 2 news row in one single query
Eg.
Table A has 2 columns(no,name) & values are present in table as
1,’a’
2,’b’
3,’c’
4,’c’
so i want to insert a 2 new rows in one Statement
1. I want to insert a new row (5,’e') in between 2 & 3.
2. I want to insert a 2 new rows (6,’f’ )& (7,’g’ ) at a time into a table at position above 1 & below 4.
also
Can i use where condition in Insert statement
can we do like this……….
[...] SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL [...]
Hi!
I am new to this and i need to create a table with 1,000,000 entries wherein it starts at 000000001 and so on. It has to be sequential. The second column must be composed of 3 random numbers from 000-999.
Does any of you have an idea on how i can achieve this?
Thanks a lot in advance. :)
i want to insert one table completely to another table
destination table is nk_np_pst
the inserting table is nk_np_unp
so wat command i want to give
Insert into nk_np_pst(column_list)
select column_list from nk_np_unp
Hi Shibu,
Assuming that both the tables have got same number of columns, you can write an sql like:
(a table to be inserted in b table)
SQL: Select a.* into b from a
Let me know if your number of columns vary (Table a number of columns is different to table b number of columns)
Hope it helps
Shreyas
Nice dude…that helped me save a lot of memory issues I was having with multiple inserts!!
i am extracting 15 columns from many tables which contains many sub queries and i am inserting into a table (using this select statements),but it takes 45 mins…
how to do this…
pls
This works too…
DECLARE @Target TABLE
(
[Id] int,
[Value] varchar(25),
[Size] int
);
DECLARE @Source TABLE
(
[Id] int,
[Value] varchar(25),
[Size] int
);
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (0, ‘Blue’, 1)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (1, ‘Orange’, 3)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (2, ‘Purple’, 2)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (3, ‘Red’, 2)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (4, ‘Yellow’, 1)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (5, ‘Black’, 1)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (6, ‘Green’, 4)
INSERT INTO @Source ([Id], [Value], [Size]) VALUES (7, ‘Brown’, 1)
INSERT INTO @Target ([Id], [Value], [Size])
SELECT [Id], [Value], [Size]
FROM @Source
WHERE [Size] = 1
SELECT * FROM @Target
I want to know is dere any method to insert multiple rows
By executing Insert command once and after that only entering values.
Thanks in advance
i want to know on how i can insert data to sql row from a textarea??the text area contains multiple names and i would like to insert it in each row with the details of it…help me..thank you..
Mr. Dave,
This is related to using Distinct clause in a Subquery.
I am converting over 100 attributes from Access Database table to SQL Server 2000, using Asp.Net 1.1 and Vb.Net.
The Access table has duplicates and I am trying to eliminate duplicates. The key is Alien_Number.
The query looks like this:
SELECT *
FROM tblClients T1
WHERE (ALIEN_NUMBER IN
(SELECT DISTINCT RTrim(Alien_Number)
FROM tblClients T2
)
ORDER BY ALIEN_NUMBER
I am still getting duplicate Alien Numbers.
I would appreciate if you have any suggestions.
Thank you for your time.
Regards
Lakshman
hi lakshman,
it is very appropriate if you remove the duplicate data from the source and then run the insert into statement.the same problem is resolved using the above said statements.
regards,
Zaim Raza.
Hi sir,
I have Two tables one is temprory table #temp .
and i want to update those records which are in both table and insert those from # temp table which are not in main table
i have tryed but .it only upades
if exists (select PId from payoutdetails where PayoutNo=@PayoutNo and PId in(select SponserId from #p1 ))
update payoutdetails set ClubIncome=@CalAmountForPlan1 where PId in ( select SponserId from #p1 )
else
insert into payoutdetails (PId, SmallClubIncome) select SponserId,@CalAmountForPlan1 from #p1
but it doesnot insert any record
thanks
any help
@Santosh
If you are using 2008, you might want to look at the MERGE statement.
Anyway, no reason to use IF:
update payoutdetails set ClubIncome=@CalAmountForPlan1 where exists(select PId from payoutdetails where PayoutNo=@PayoutNo)
and PId in(select SponserId from #p1)
insert into payoutdetails (PId, SmallClubIncome) select SponserId,@CalAmountForPlan1 from #p1
where NOT exists(select PId from payoutdetails where PayoutNo=@PayoutNo)
and PId NOT in(select SponserId from #p1)
Hi,
I have used the above query to insert multiple rows in single statement. But now the question is how do it get @@identity of each row inserted back. the OUTPUT clause is for SQL 2005. I am using SQL 2000.
Please help me.
Thanks!!
Perfect example thanks, my SQL INSERT disease got remedied.
Hi,
Can anyone please tell me when we use ‘ (single quotes) in an insert query to add the new values to the table and when we should not use any quotes (like inserting NULL)?
What does quotes do exactly? pls explain..
Thanks!
@Deb
single-quotes are used for literals. If there are to single-quotes, it want to insert a zero-length string. NULL means there is no value there at all.
Hi Pinal Dave
I am using access database but i am try to search a table where i should pick the firs t record and insert it into another table and be able to update the record with the next 4 records in the first table.
I can insert but my update is giving an error. this a a bit of my code. Pls help i am using a for loop to get the next 4 records, but i also though of rowcount but do not know how to use it .
gcon.OpenConnection()
cmd.CommandText = “select * from english where centcode like ‘” & centre & “%’ and subjcode like ‘” & Subjcode & “%’”
cmd.Connection = gcon.con
dr = cmd.ExecuteReader
Dim Subjcode1 As String
While dr.Read()
i += 1
For i = 1 To CandCount
CentCode = “”
If i = 1 Then
CentCode = (dr(“centcode”))
CandNo = (dr(“candno”))
Subjcode1 = (dr(“subjcode”))
‘Abs = (dr(“abs”))
Yr2 = (dr(“y2mrk”))
Yr3 = (dr(“y3mrk”))
‘mQuery = “Insert into casstbl(centcode,subjcode,candno1,y2mrk1,y3mrk1,candno2,y2mrk2,y3mrk2,candno3,y2mrk3,y3mrk3,candno4,y2mrk4,y3mrk4,candno5,y2mrk5,y3mrk5)” _
‘ & ” values” _
‘ & “(‘” & CentCode & “‘,’” & Subjcode1 & “‘,’” & CandNo & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘,’” & Subjcode1 & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘,’” & Subjcode1 & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘,’” & Subjcode1 & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘,’” & Subjcode1 & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘)”
mQuery = “Insert into casstbl(centcode,subjcode,candno1,y2mrk1,y3mrk1)” _
& ” values” _
& “(‘” & CentCode & “‘,’” & Subjcode1 & “‘,’” & CandNo & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘)”
cmd = New OleDb.OleDbCommand(mQuery, gcon.con)
cmd.ExecuteNonQuery()
End If
If i = 2 Then
CentCode = (dr(“centcode”))
CandNo = (dr(“candno”))
‘Subjcode1 = (dr(“subjcode”))
‘Abs = (dr(“abs”))
Yr2 = (dr(“y2mrk”))
Yr3 = (dr(“y3mrk”))
mQuery = “Update casstbl set=(candno2,y2mrk2,y3mrk2)where centcode like ‘” & centre & “‘” _
& ” values” _
& “(‘” & CandNo & “‘,’” & Yr2 & “‘,’” & Yr3 & “‘ )”
cmd = New OleDb.OleDbCommand(mQuery, gcon.con)
cmd.ExecuteNonQuery()
End If
gilsygirl
Hi Pinal,
First of all, Excellent work!!!
My query is related to which is based for performance:
i am tryign to insert NEW rows into a table- ard 500 rows per sec
AA) using a dataset appproach- call a ds.update/insert
or
BB)directly giving values as abv proc of insert using union all.
or
CC) passing an array to a stored proc with values i wish to insert.
thnx!
Hi sir,
i am facing one proble plz give me solution
I am using mysql database sir,
i have one table that table one column is there delevey_email in that column i am insert one row like subbu@gmail.com,madhu@gmail.com
but these mail ids are insert two rows another table
this is my problem give me solution sir
This is very nice solution for inserting more than on row in a table …………………….
this is quite amusing! congrats!
i’m wondering if you can use a counter into the sentence cause i have a field that’s not auto_increment and i need it to be inserted in a fashion autoincremental.
I know I can just edit the column and put auto_increment on the atributes but I could use this trick also to insert products names in varchar unique fields. (product1,product2…product9999)…
hope I was clear on my question!!!
Thanks a lot, amazing contribution!
Uhm, if I’m just about to get it right, if the query you provided would be equal to $sql, you’re telling me to copy and paste that as many times i want it to be executed?
That’s not cool. I don’t know if there’s another way, maybe writting even a little iterator in any language would be better in terms of redundance.
How about…
INSERT INTO ”table_dance”
(”id”, “name”, “brasize”)
VALUES
(”0”, “striperella”, “36dd”),
(”1”, “demi moore”, “32d”), …
This will work from version 2008 onwards
hi all ,
i was lacking in syntax , n i got that from here, thank u so much for all
Hi All
I’m programmer from and about to insert 10Lac Records at a time depending on some condition but 2,000 to 4,000 records are getting skipped even though the condition is getting satisfied what could be the reason.
I stored 200 values in an array and that 200 values represent one record.
It is easy to insert ‘n’ record, that i did N no. of times with MAX of 20 values but this is first time im supposed to insert this large no. of values.
So donno how to do…
HELP!!!
how to get the date between two date through a query.
For Example: i need the date between 1/3/2009 and 30/3/2009. Please mail the answer if anybody knows to
SELECT columns FROM your_table
WHERE date_col>=’2009-03-01′ and date_col<dateadd(day,0,'2009-03-30')
hi,
i have table with columns uniqueid, datetime, float, smallint. In my test code i’m creating collection of dummy objects (with related properties) and then i’m inserting them to my table.
1. one by one by calling stored procedure
2. insert with multiple select
3. multi insert
results are following :
for 1200 records are time like 0.9s, 5.46s, 0.6s
for 2400 recs : 1.65s, 13.0s, 1.12s
…
for 4800 recs 2nd fails saying “insufficient memory”…
why is it so slow???
Hi Majo George,
You can get dates between periods by these two ways:
1.
select CAST(’2009-03-01′ AS DATETIME )+ a.Number
from master..spt_values a
WHERE Type = ‘p’
AND number<30
2.
;with cte as(
select CAST('2009-03-01' AS DATETIME) AS Dt
UNION ALL
select Dt + 1
from cte
WHERE dt <= '2009-03-30'
)
select *
from cte
Let me know if it helps you.
Thanks,
Tejas Shah
I need to insert a journal entry transaction from GP GL20000 open table into GP GL30000 history table. The columns are the same. It is a multiple line journal entry. Once I get the Journal Entry inserted into the historical table I can easily delete the multiple line record in the open table.
Thank you for your assistance.
I need a insert to enter 368 values into a table:
a column CRT must have a incremental value starting with 1. an example : i need 1,2,3 etc…or something 36801, 36802 etc…can you help me with this?
thanks in advance!
select number from master..spt_values
where type='p' and number between 1 and 368
If you do something like
insert into table (autoincrement, name) values (”, ‘eliezer cazares’);
your autoincrement field will be filled by itself.
About inserting 368 rows, either way you come up with an iteration in some programming language or you copy and paste 368 times the query just like the “MySQL (so-called)-Authority” says.
Can i insert records into cursor from multiple cursors at a time?
I have a form with 2 input text boxes where i want to enter a number value in each. One input box in the start number (example: 5) and the second is the end number (example: 26). I want to be able to print each number from beginning to end incrementing by 1 example 5 6 7 8 until 26, each number on each page of paper, in total 22 pages.
Another example start number 48 end number 52; 48 49 50 51 52 total of 5 pages.
Can anyone help me or give me a tip.
Thanks in advance
select number from master..spt_values
where type=’p’ and number between your_start_number and your_end_number
Hello Sir,
I want to Import or can say convert a MS-Exess
File into the SQL Sever 2005 EXPRESS.
so plz tell me how it is possible?
And can i need to put the same fields in both?
Thanks
Hello Sir,
I want to Import or can say convert a MS-Exess
File into the SQL Sever 2005 EXPRESS.
so plz tell me how it is possible?
And can i need to put the same fields in both?
And i want to know the automatic Primary key allotment process.
Thanks
Hi
I have a Insert Trigger on Table A but when i inserts multiple rows in a single insert statement the trigger fires only once.
Is there any solutions to this, plz help me to solve this problem.
I want to invoke the trigger for every row insert from the single insert statement.
Thx
Karthik
Hi!
i m not getting how to insert records into ms-access in ascending order using java.i hav tried order by clause.it takes data in resultset in ascending order but does not insert that data in ascending manner in access.
please give me solution.
Hi Pinal,
Could you please answer my query?
I have a python script where in I have an insert statement in a loop. That means the insert statement executes same number of times as the loop executes.
To improve the performance I used your way of “UNION ALL” to insert 1000 rows at a time.
But when I execute this new script, for inserting 2000 rows, the performance has become lower than it was in the original script.
I insert 34 columns per row.
Please help.
Thanks in Advance.
@Neelam, try less records. Like 100. I’m just guessing here though.
@pradeep.
To ensure order, INSERT them separately.
@Karthik
The documentations says: An AFTER trigger is executed only after the triggering SQL statement has executed successfully. This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted.
So, it will only execute once. If you want it for every record, perhaps you can identify which records were INSERTed.
Hi Pinal,
I have error with code “ORA-00936: missing expression”. What am I missed? Thanks.
MERGE into cm_adm_usergrants c
using (select id, name from smart.user$ where id=223) o
on (c.userid=o.id)
when not matched then
insert (userid, pluginid) values
(select userid, pluginid from cm_adm_usergrants where userid=117)
when matched then
update set c.pluginid=(SELECT pluginid
FROM cm_adm_usergrants where userid=117);
@Mikail
ORA is an Oracle error, not SQL Server.
hi this is sravan.
i want insert rows from one table to another with out duplicating the rows.
then how can i write a sql command? plz tell me
thnx
sravan
I’ve had some really interesting findings, with adding multiple rows at a time.
I’ve found that when adding around 30,000 rows of data (three columns), 20 rows at a time is optimal! 100 rows at a time is a bit slower and the more you add, the slower it goes!
I can’t explain WHY, just from my observations.
BTW, I’m also in the position of wanting to add data from a file where some of the rows need to be inserted and some updated (i.e. an overlap).
I’m creating a temporary table (based on the old table), inserting the data from the file in to the new table (using the UNION ALL technique), wiping data from the table I want to update (where there is data in the temporary table, one SQL command to do that) and inserting the data from the temporary table to the table I want to update (again, one SQL command to do that).
I really wish SQLServer had MySql’s ON DUPLICATE KEY feature!
@sravan
If you want to restrict what goes in, use DISTINCT.
If you want to restrict if it is already there, use WHERE NOT EXISTS()
Thank you, it didn’t improve my performance very much, because i only need to insert 5-10 rows, but it was exactly what i wanted to do
Hi every one ,can any one help me in this regard ,i want to do this in SQL Server2000,presently iam doing this in Access but i am giving example in Excel.
Policy no RISK STATRT DATE RISK END DATE DOB ACTIVE/LAPSE
111 1-Feb-03 9-Aug-09 19/09/1982 ACTIVE
112 9-Dec-03 9-Jun-09 01/01/85 LAPSE
now I want the following records in this format
Pno Policy year NO of Days exposed AGE ACTIVE/LAPSE
111 0 365 20 0
111 1 366 21 0 IF A POLICY IS IN ACTIVE STATUS WE NEED TO GIVE 0 OTHERWISE 1
111 2 365 22 0
111 3 365 23 0
111 4 365 24 0
111 5 366 25 0
111 6 188 26 0
FOR A LAPSE POLICY WE NEED TO CALCULATE EXPOSURE UPTO THE NEXT POLICY ANNIVERSARY
Pno Policy year NO of Days exposed AGE ACTIVE/LAPSE
112 0 366 18 0
112 1 365 19 0
112 2 365 20 0
112 3 365 21 0
112 4 366 22 0
112 5 365 23 1
WE NEED TO GIVE 1 IN THE COLUMN ACTIVE/LAPSE IN THE POLICY YEAR WHERE IT GOT LAPSED
@Kumara Datta
Are all those results from the two sample records?
Hello,
my name is dipti.
i read ur bolg it’s really helpful.
but i have one query ;
i want to insert values in one table (consider as table1) from another table(table2). also consider there are 4 fields in table1 and table2 has more fields including fields in table1.
now problem is i want to insert first field into table1 from another global variable and rest three fields are from table2.
can you please help me in this.
@dipti
INSERT INTO table1(col1, col2, col3, col4)
SELECT col1, col2, col3, @global_var FROM table2;
@ brian tkatch
i tried it. it works. Thanx!!
Thanks for this. I had a MySql table with 3 columns and 4,500 rows. I used your syntax to convert to Sql Server and the insert took 23 seconds. Not bad!
hi i want a query 2 retrive data from 2 database & add it in another 3rd database
please suggest me the query.. m fed up.. i’m searching since 2 days & found the following:
Insert into table_name(col_name1, col_name2)
values ((select column_name from table_name1 where id = ’29′) union (select column_name from table_name2 where id = ’3′))
please give me a solution…
Insert into db3..table_name(col_name1, col_name2)
select column_name from db1..table_name1 where id = '29'
union
select column_name from db2..table_name2 where id = '3'
sirr,
how to insert more data(for ex 10 mb) in sql serverr
how insert the dynamic data in mysql using c pogram.
ie
a=5;
b=10;
c=15;
INSERT INTO per(a,b,c) VALUES(‘a’,'b’,'c’)
@Ankit
I don’t understand the question. What exactly is the problem?
@karthi
Which part is dynamic? Is this a T-SQL script?
how can append/insert records in excel format, at once, to Ms access database table using the insert sql query
I AM TRYING TO INSERT MULITPLE ROW IN ONE STATEMENT ,BUT ITS DOES NOT WORK
I WROTE
insert into DEPT(DNO) VALUES (1), (2) , (3);
AND ERROR SHOW:-
Msg 102, Level 15, State 1, Line 80
Incorrect syntax near ‘,’.
PLEASE TELL ME WHAT CAN I DO ,SYNTEX IS RIGHT BT DOES NOT WORK
@VIJAY
In SQL Server 2008, that syntax should work. Prior to that version, a VALUES statement can only INSERT one record at a time.
So, there are two options:
insert into DEPT(DNO) VALUES (1);
insert into DEPT(DNO) VALUES (2);
insert into DEPT(DNO) VALUES (3);
Or:
insert into DEPT(DNO)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3;
Brian, I’m getting same error as Vijay on SQL Server 2008. Can you tell me how to fix this?
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ‘,’.
When I run:
INSERT CT_ProductFailures (Old_Modes, New_Modes)
values
(‘AAA’, ‘BBB’),
(‘AAA2′, ‘BBB2′),
(‘AAA3′, ‘BBB3′)
Make sure the compatibility of the database is 100
How can i get all newly inserted identiy id. I used following query but it returns only the last record id. Can u help me?
INSERT INTO TableNew(
column1,
column2,
column3
)
SELECT
1,
xmlcontent.value(‘ column1[1]‘,’VARCHAR(200)’) column1,
xmlcontent.value(‘ column2[1]‘,’BIT’) column2
FROM @xmlData.nodes(‘//Table1′) AS R ( xmlcontent )
SELECT IDENT_CURRENT(‘TableNew’)
@Damodharan
Pinal has an article on three emthods here: http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/
The OUTPUT clause is mentioned here: http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/
Personally, i use the OUTPUT clause.
How can i load a text file in sqlserver 2005 by using .net
How can i insertthe values of a text file in sqlserver 2005 by using .net
HI,
I have a jsp Page where User Add Text Box’s as per is requriment for that am using JavaScript,
Now i want to insert those textbox values in my database table when the user click submit button,
Regards
Vanishree
Hi,
I wanted to know about multiuser scenario…
While many users are inserting values to the DB at a time how to get the last id being inserted..
And i want information about record locking. Please help.
Thanks & Regards
Kavya
@Kavya,
One solution if you are still in designing phase, Add Last_updated_Time column to your table and assign a default value of getdate().
By this way, you can know what is the last value that was inserted into the table based on the datetime column.
Or you can also an Identity column that will give you lastest inserted record.
Regarding Second Question.
Locking a record in a table.
This is just a summary for you to start, please refer books online for in depth knowledge…
SQL Server applies Shared / Exclusive locks when dealing with records in a table.
Exclusive lock: SQL Server puts a Exclusive lock on the table when a user updates / inserts a record into a table.
Shared Lock: SQL Server puts a Exclusive lock on the table when a user reads data from a table.
Shared lock is compatible with other shared Locks, meaning multiple reads can happen simultaneously on a table.
Exclusive lock is not compatible with Shared Lock, meaning if SQL Server puts a Exclusive Lock on a table, No other connection can read data from that table.
But still, there could be scenarios, where it is possible to read dirty data, lose updates, phantom data…. To overcome all these cases, You need to apply proper Isolation level before you initiate any transaction.
I strongly Suggest you read the topic, ISOLATION LEVELS in SQL SERVER 2005.
This topic has been explained very well in simple words with example in below weblink:
http://www.sql-server-performance.com/articles/dba/isolation_levels_2005_p1.aspx
~ IM.
Very good performance increase. THANK YOU, Pinal Dave!
I have to insert muliple records in two scenarios:
I have coverages and Rates, each location has muliple coverages and each coverage has muliple rates
coverageId 1
rateId 1,2, 3, 4,
coverageId 2,
rateId 1, 2, 3
Insert above into a table LocationRate that has
LovCovRateId, identity field,
LocationId
coverageId
rateId
I have to first select coverages for each location from another table
(select coverageId from locationInfo table)
Then refer to a rate table to get the rates for each coverageid (Select rateId from rates where coverageId = (select coverageId from LocInformation table where locationId = @locationId)
then get those rateId’s for each coverage Id’s and insert all the relevant coverageId AND their RateId’s into the LocationRate table.
So here there is a case of two scenarios of muliple inserts, muliple coverages and for each coverage muliple rateId’s
Pinal, I always look for solutions given by you, I feel you can help me on this, Any help will be much appreciated, thanks in advance!
i am enter value from one table to other,but in second table their is a primary key and it shows the error of primary key contraint.Solve this problem
Hi
I am new to sql server 2005. i’ve one table (Raw Material)if i updated any data in this table that data will be updated in all 5 tables.
Plz give me the solution
Any one can help me……
i want to insert multiple records into my table through SELECT statement,
and some times i may give different feilds I.e
insted of col1,col2,col3 ,
i may give only col1,col3..
like this……so
is it posiible in Sqlserver2005
plz rep me………
HI,
am write a query which retrieve from view and in that loop am passing set of condition and i have to retrieve some value from table2 by passing where condition ,
My Processing time gets slower when am passing my Second Query in First Loop.is there any alternative Way.
EG:-
Sql=”Select * from View1 Where Month=’Feb’ And Year=’2009′ ”
rs = st.executeQuery(sql);
While(rs.next())
{
String emp=rs.getString(“Empcode”);
String Name=rs.getString(“Name”);
float sal=rs.getFloat(“Salary”);
float Broughtforward=rs.getString(“Broughtforward”);
Sql1=”Select * from Table1 Where Month=’Jan’ and Year=’2009′
rs1 = st.executeQuery(sql1); // When
While(rs1.next())
{
.
. When am running this query my proccesing time gets slower
.
}
Int i =injsert into Table1 values (‘Empcode’,'Name’,'Salary’…)
Thanks in advance
how to display the result of joining of two tables , one returning more than one row and other having only one…
@shree
Check Ur Joining both the table should have one or more comman field thn u ill not have this problem Say for exampple
Employee: Empid,tokeno,name,Depid
Dept:Depid,dept..
now u can join Employee.Depid=Dept.Dept thn it ill return only one row.
hi sir,
i want’s to insert multiple data with entry of single data, for example, if i m inserting patient details, i want’s to insert his personal data as single entry and two medical tests and four medicines, then how do i insert all this data in a single table with no duplication of multiple entry(means obviously sigle entry data(patient’s personal details and unique ID) will be repeated, but two tests should not be repeated four times with four medicines )
plz, give reply as early as possible
@Sushant
You would need to normalize your database structure to one more level. From your post, I can definitely tell you that database structure is not designed properly.
you need to have one table that stores personal details, lets say TableA
Another table that stores Test results, lets say TableB
Another table that stores Medicines, lets Say TableC
And one common key that joins these tables, meaning implementing one to many relation ship, by creating foreign key constraints on tables.
Primary Key on Table A, Table A would be considered as Parent table and Table B and Table C will have a foreign key referencing to Table A.
The above explanation holds good for what you have mentioned in your post.
If you need more clarification, please post your table structure and some sample data.
~ IM
Thank you sir
I performed all database normalisations as mentioned by you, and i used patient_id as primary key in tableA and foreign key for remaining two tables(tableB & tableC). Now, i have to get a single crystal report from these 3 tables, but when i fire a query it again gives unwanted repeatations on crystal report. i might be wrong in applying a query, if possible give query for these three tables. i m posting table structure of all 3 tables.
TABLE-A
hospitalId varchar(50)(Primary key)
patientname varchar(50)
gender varchar(50)
age numeric(18, 0)
weight numeric(18, 0)
TABLE-B
hospitalId varchar(50)(foreign key)
ward_name varchar(50)
patientname varchar(50)
date datetime
test varchar(50)
TABLE-C
hospitalId varchar(50)(foreign key)
ward_name varchar(50)
patientname varchar(50)
date datetime
med_name varchar(50)
doses varchar(50)
duration varchar(50)
Now, on save click i insert two tests and four medicines at a time with all other data.
plz give query for retrieving these data in a single report
Hi,
m using excelsheet as a backend n inserting data thru’ oledb jet4.0 as follows
INSERT INTO [Sheet2$] VALUES(‘” + label1.Text.ToString() + “‘,’” + DateTime.Now.ToShortDateString().ToString() + “‘,’” + DateTime.Now.ToShortTimeString().ToString() + “‘)”;
but problem occurs whn it reaches at last record.it insert 4 records like
F02SD39999 12/15/2009 3:16 PM
F02SD40000 12/15/2009 3:16 PM
F02SD40000 12/15/2009 3:16 PM
F02SD40000 12/15/2009 3:16 PM
F02SD40000 12/15/2009 3:16 PM
I dont want duplicate record,so help me on that?
thanking you.
HOW TO COPY INTO SPECIFIC COLUM DATA INTO A TABLE USING “BCP ” PROGRAME IN SQL SERVER 2000?
EX:-
—————————
COL1 COL2 COL3
—————————
– 1 -
– 2 -
– 3 -
– 4 -
– 5 -
– 6 -
—————————-
Pls revert bak.
Hi,
I have to pass an string to stored procedure but varcgar(8000) may not afford its size since the string is toooooo long.Is ther any data type which can hold such long strings?(m using MS SQL Server 2005)
Thanks.
Hi,
I have to pass an string to stored procedure but varchar(8000) may not afford its size since the string is toooooo long.Is there any data type which can hold such long strings?(m using MS SQL Server 2005)
Thanks.
Use varchar(max)
hi all,plz hlp me out in dis….
m using oracle 9i sql*plus.
it is known dat d size of date datatype is 7….
but when i write- select vsize(sysdate) from dual;
output is 8…
or select vsize(to_date(’31-MAR-09)) from dual:
d output is also 8…bt if i write-select vsize(hiredate) from emp where deptno=30:
d output is 7…
can anyone explain please..
i want to know that how we can insert a marathi font inside the database using sql server 2005. please reply soon. i really need it urgently..
REGARDS
TRUNAL.
[...] – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE 3) SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL 4) SQL SERVER – 2005 – Create Script to Copy Database Schema and All The Objects – Stored [...]
Hi Pinal,
When we use Select * into table1 from table2….
It will not create indexes of table 2 in table1…
How i can acheive this.
Hello Kishor,
SELECT * INTO clause is just to copy the data into a new table. It does not copy the table structure. To create index you will have to write CREATE INDEX statements after loading the data.
You can first create the table2 same as table1 with script that includes indexes and other constraints and then insert data using INSERT INTO statement.
Regards,
Pinal Dave
Thanks Pinal,
Can u help me…I hve read abt the global variables..so can we create global variables in sql server ? if yes than How
and how we can call them out of the script…
Thanks & Regards
Kishor
Hello Pinal Dave,
i need your help sir, actually..i try to making a query for fatching a record in different tables in different server Databases using sql server 2008.
so please help me out…
thanks
Atul
Hello Atul,
To fetch record from tables in different server database, use OPENQUERY function or 4 part naming. For that you must have linkedservers for different servers.
Regards,
Pinal Dave
Dear all,
in my case, the clients are inserting the data at time into the table.
i.e. multiple users are insert the data at a time .
that time i am facing problem,. each query taking one connection and it is not relasing quickly. so some users are lost their data.
kindly give some suggestion.
Hi,
i need to insert one row for each day of the year. i.e 365 rows. it is so frustrating to insert one by one. I followed the above given format, still i am unable to insert records.
Below is the query used. Please let me know if there is any correction required in the query
INSERT INTO TST_ADM_TST_PKG (TST_ADM_TST_PKG_ID,TST_ADM_NO,TST_PKG_ID,TST_PKG_TYP_CDE,TST_ADM_TST_PKG_DESC,UPDT_USR_LGN_CDE, UPDT_DTE_TM, PROD_ID )
SELECT (TST_ADM_TST_PKG_SEQ.NEXTVAL,’57067′,’PKG001′,’000′,’STANDARD 2008′,’GUEST’,SYSDATE , NULL )
UNION ALL
SELECT (ngt01.TST_ADM_TST_PKG_SEQ.NEXTVAL,’57068′,’PKG001′,’000′,’STANDARD 2008′,’GUEST’,SYSDATE , NULL )
UNION ALL
SELECT (ngt01.TST_ADM_TST_PKG_SEQ.NEXTVAL,’57069′,’PKG001′,’000′,’STANDARD 2008′,’GUEST’,SYSDATE , NULL );
Hello Sathish,
Use the below script:
DECLARE @Year AS INT,
@FirstDateOfYear DATETIME,
@LastDateOfYear DATETIME
– You can change @year to any year you desire
SELECT @year = 2010
SELECT @FirstDateOfYear = DATEADD(yyyy, @Year – 1900, 0)
SELECT @LastDateOfYear = DATEADD(yyyy, @Year – 1900 + 1, 0)
– Creating Query to Prepare Year Data
;WITH cte AS (
SELECT 1 AS DayID,
@FirstDateOfYear AS FromDate,
DATENAME(dw, @FirstDateOfYear) AS Dayname
UNION ALL
SELECT cte.DayID + 1 AS DayID,
DATEADD(d, 1 ,cte.FromDate),
DATENAME(dw, DATEADD(d, 1 ,cte.FromDate)) AS Dayname
FROM cte
WHERE DATEADD(d,1,cte.FromDate) < @LastDateOfYear
)
SELECT FromDate AS Date, Dayname
FROM CTE
OPTION (MaxRecursion 370)
Regards,
Pinal Dave
Thanks Pinal :)
will work on your suggestion.
Sathish
Hi Pinal,
Thanks for wonderful suggestions and tips.
I have two questions.
1) I need to know that why unique key allows only one NULL value, why not more than one ?
2) Can we use transactions and commit or rollback transaction inside instead of Triggers ? If NO then Why ?
Thanks in advance for your kind suggestions and answers.
Thanks,
Sanjay
Hello Sanjay,
Unique column allow any value for once even it is null. If null comes again then its duplicate and unique constraint not allow that.
You can use transaction inside trigger. But if you rollback a transaction:
1. All data modifications made to that point in the current transaction are rolled back, including any made by the trigger.
2. The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.
3. The statements in the batch after the statement that fired the trigger are not execu
Regards,
Pinal Dave
Hi ,
Q: How to update more than one records with using single update statement with different where conditions
MyRequirement: I have requirement to update almost 150 records with different values based on primary key id.
for this , I have formated a query with multy update statements
like
MyAns:
update my_table set col1=val1,col2=val2,…. where id=valID1
update my_table set col1=val4,col2=val5,…. where id=valID2
update my_table set col1=val6,col2=val7,…. where id=valID3
update my_table set col1=val8,col2=val9,…. where id=valID4
but Is It possible to do with single update statement
and Is their any better solutions for my Requirement, If so Let me know
It is possible with one statement with CASE:
update my_table set col1= CASE id WHEN ‘valID1′ THEN ‘val1′ WHEN ‘valID2′ THEN ‘val4′ END, col2= CASE id WHEN ‘valID1′ THEN val9,…
It is also possible with a join.
WITH Data AS (SELECT new data)
UPDATE my_table FROM my_table, Data …
Though, 150 separate statements shouldn;t be much of a problem, especially if they are wrapped in one transaction.
can any1 tell me what could be the problem .
when i am inserting data in mysql server it insert the same row for two times where as i want it to be entered for single time
Hi Nagaraju,
Single statement is not possible this way but you can rewrite the query if all values (val1, val2, valID1…) are in another table.
Regards,
Pinal Dave
actually
I created an tblemployee table
as
create table tblemployee(
eid primarykey ,
ename varchar(50),
email varchar(50),
date varchar(10),
zipcode numeric(6),
basicsal double(10),
total double(10)
);
eid is set to automatic increase
now on using isert query on it
as
insert into tblemployee(‘ename’,'email’,'date’,'zipcode’,
‘basicsal’,'total ‘)values (“rajni”,”abc@bbc.com”,”12/20/2010″,”400089″,”10000″,”10000″);
now this is inserted properly into table but for 2 times with 1 entry as eid 1 and next entry with eid 2 rest all data is same just primary key is increasing for second entry.
i mean 1 entire row is inserted for 2 times that to with increase in id which is a primary key for the table
Hi,
I found a space between my database fields, it is possible to remove? also my data is huge like 30Million……..Please can give any solution for this………
Regards,
Imran Saiyad
Hello Imran,
Are you talking about unused space in database file or space in table columns?
If you mean unused space in database file then use DBCC SHRINKDATABASE or DBCC SHRINKFILE.
To remove space in table records use REPLACE function.
Regards,
Pinal Dave
i had created a table like this
eg –
CREATE TABLE #Employee
(EmployeeID INT IDENTITY(1,1),
EmployeeName VARCHAR(20))
now i want to insert mulitple values into the this table
i had tried it in this way -
WHILE SCOPE_IDENTITY()<5 OR SCOPE_IDENTITY() IS NULL
INSERT INTO #Employee (EmployeeID,EmployeeName)
VALUES (SCOPE_IDENTITY(),'Mahesh' + SCOPE_IDENTITY())
it doesn't gives me error nor does it inserts the values in the table.
can u explain me what could be the problem
Can you tell me the best possible way to achieve this (multiple row insert into a table with mulitple columns)
as this can be achieved with a single table with single identity column
eg –
CREATE TABLE #InnerJoinTest1( InnerJoinTest1_PK INT IDENTITY(1,1) )
WHILE SCOPE_IDENTITY()<5 OR SCOPE_IDENTITY() IS NULL INSERT INTO #InnerJoinTest1 DEFAULT VALUES
I want to achieve this with the above table structure(#Employee) so how can i achieve it is there any way
need ur help
pls suggest
I’m confused as to what you are trying to do.
INSERT INTO Employee (EmployeeName)
SELECT ‘Mahesh’ UNION ALL
SELECT ‘Nitin’ UNION ALL
SELECT ‘Manisha’ UNION ALL
SELECT ‘Dipthi’
What happens is that…
First you try to insert values (NULL, ‘Mahesh’ + NULL) into the #Employee table. SCOPE_IDENTITY() returs NULL because you haven’t actually inserted any IDENTITY value yet. You can check this by calling the following right after the CREATE TABLE statement:
SELECT SCOPE_IDENTITY();
Now, you can’t insert value into the IDENTITY field (well, you can, but you need to explicitly enable it first). This gives an error:
Cannot insert explicit value for identity column in table ‘#Employee’ when IDENTITY_INSERT is set to OFF.
Now the back to the WHILE loop. Take notice that you have “OR SCOPE_IDENTITY() IS NULL” in there. That’s why you entered the loop in the first place. SCOPE_IDENTITY() returns NULL again because last insert failed and keeps returning NULL every time because all the inserts fails.
Now you got yourself a nice endless loop which, I think, is consuming A LOT of server’s resources, mainly CPU :)
Pinal’s fix is good but I would add COALESCE in there and for some reason I like to use CAST over CONVERT:
WHILE COALESCE(SCOPE_IDENTITY(), 0) < 5
INSERT INTO #Employee (EmployeeName)
VALUES ('Mahesh' + CAST(COALESCE(SCOPE_IDENTITY(), 1) AS VARCHAR))
Hello Marko Parkkola,
Thanks for giving a explanation .
I tried the query and it worked greatly.
Its my first post now will enjoy this arena.
Thanks and Regards,
D.Mahesh.
Hello Mahesh,
I do not know how the first insert statement is not returning error.
Anyway the first statement has two issues: 1. you are trying to insert a value in IDENTITY column. 2. you are concatinating varchar and integer type values.
Replace the first insert statement with following and check.
WHILE SCOPE_IDENTITY()<5 OR SCOPE_IDENTITY() IS NULL
INSERT INTO #Employee (EmployeeName)
VALUES ('Mahesh' + convert(varchar(2),SCOPE_IDENTITY()))
Regards,
Pinal Dave
Hello Pinal,
Thanks that worked.
Its my first post now will enjoy this arena.
Thanks and Regards,
D.Mahesh.
Hi , how to find out value for a particular column for which duplicate records are inserting in to the table which is causing vialation of primarykey
Select Colname,count(ColName) From TableName
Group by ColName Having Count(ColName)>1
It will show all the duplicate records with number of occurrences.
The below qquery will show all the duplicate records with number of occurrences.
Select Colname,count(ColName) From TableName
Group by ColName Having Count(ColName)>1
where ColName = Primary key in your table.
hi pinal,
in stored procedure i am inserting data in master in master 1 identity column and getting that using scope_identity value insert into child table. if same time multipla user inserting then what will happen and how resolve this problem. Here I have to insert all the data.
can u plz resolve this.
Thanks.
How do i remove from the mailing list of this great post? it’s been over a year and i still get replies… thank you
Hello Masih,
No need to worry about simultaneous execution of your code. Because you are using SCOPE_IDENTITY function and SQL server provide you the identity that is generated by one current user’s session.
Regards,
Pinal Dave
set @newrecord =’select 1,’09:00′,’17:00′,1,1 union all select 2,’10:00′,’18:00′,1,1′
set @finalsql = ‘select’ + ‘ insert into HR_ShiftBreak (BreakID, BrekStTime, BreakEnTime, ShiftID, company_id) ‘ + @newrecord
exec @finalsql
i want to insert 1000 thousand record in my table or may be more (depend upon user request) ..
i m using concate method to make my Query string like Pinal…
@final=’select insert into Shreak (BreakID, BrekStTime, BreakEnTime, ShiftID, cany_id) select 1,’09:00′,’17:00′,1,1 union all select 2,’10:00′,’18:00′,1,1′
i m giving you an example of two data ….
kindly tell me the syntax to enter more that 1000 records…
using union all..
hi
i want my query like this
declare @stateQuery as nvarchar(200)
if @stateid is null
set @stateQuery=”
if @stateid is not null
set @stateQuery=’and tbl_city.stateid =’+cast(@stateid as nvarchar(10))
select top 1 len(dbo.Tbl_city.cityName) FROM dbo.tbl_city INNER JOIN
tbl_cityinfonew ON dbo.tbl_city.cityId = tbl_cityinfonew.cityId
where tbl_cityinfonew.language =@language
+@stateQuery
order by tbl_cityinfonew.createdate desc
Thanks in advance
Siva
Hi Siva,
You should use sp_ExecuteSQL for this purpose.
Here we just need to write dynamic query and pass parameters.
Let me give you an example:
DECLARE @qry NVARCHAR(MAX)
DECLARE @params NVARCHAR(MAX)
SELECT @params = ‘
@Language VARCHAR(MAX),
@StateID INT’
declare @stateid int, @language VARCHAR(MAX)
select @stateid =10
if @stateid is null
set @qry=”
SELECT @qry = ‘
SELECT TOP 1 LEN(dbo.Tbl_city.cityName)
FROM dbo.tbl_city
INNER JOIN tbl_cityinfonew ON dbo.tbl_city.cityId = tbl_cityinfonew.cityId
WHERE tbl_cityinfonew.language = @language ‘
IF @stateid IS NOT NULL
SELECT @qry = @qry + ‘ and tbl_city.stateid = @StateID ‘
SELECT @qry = @qry + ‘ORDER BY tbl_cityinfonew.createdate DESC’
PRINT @qry
EXEC sp_executesql @qry, @params,
@language,
@StateID
Let me know if you have any question.
Thanks,
Tejas
hi Pinn,
I need a single sp taht perfrom a single functionality fro difeerent forms, depending on which from is been used the record needs to be updated in its table
thanks
I need a single storedprocedure tht perfrom a single functionality like insert for diferent forms, depending on which from is been used ,the record needs to be inserted in its respective table.
Please help me in this issue
thanks
Do let me know solution for my query .
Hello Rajni,
You explained that when same row is inserted more than one time that identity values increses and that is the only difference between two rows. But what is your query about it?
Regards,
Pinal Dave
i am not able to get why the entire row is inserted for 2 times .whereas there should be single row entry.
Run a profiler and see if you get an idea on why it is inserted twice
I have query with union statements but it is comparitively very slow. Is there any alternative method instead of union or union all that can be used to increase the performance. If so can you give me some sample query.
Hello Rathy,
To append two tables, union all is an optimized clause but you can test insert into another table as an alternative. Like
SELECT * INTO temp FROM Table1
INSERT INTO temp SELECT * FROM Table2
SELECT DISTINCT * FROM temp –alternative of union
SELECT * FROM temp –alternative of union all
In SQL Server 2008 a new alternative is MERGE clause but that would work faster only if these table have identity fields.
Regards,
Pinal Dave
hi,i want a stored procedure to insert multiple rows at a time. can any one help me
@Rathy
Are you sure it is the SELECT statement(s) that is slow and not the INSERT statement? Run SELECT independently to see if that’s the problem.
What is the problem with this besides it’s slow? Do you need to run it frequently for some reason?
@chinni
Just create a new procedure like you create any other procedure:
CREATE PROCEDURE InsertMultipleRowsAtOnce
AS
BEGIN
SET NOCOUNT ON;
– Your INSERT .. SELECT statements goes here
END
Hello Chinni,
There is a lot of information about stored procedure in BOL and on msdn.
If you are facing some issue in writing stored procedure for some specific case then let us know the details.
Regards,
Pinal Dave
I am fetching some columns from some specific tables using a select statement. One or two conditions are distinct for fetching the columns all the other conditions are same. So I am using union all statement. Is there any alternative way for doing the same instead of UNION or UNION ALL.
Thanks,
Rathy.
Hello Rathy,
If you can provide the queries we can help you in writing a single query instead of multiple queries.
Regards,
Pinal Dave
This WORKS on Access!!!!
INSERT INTO LC__TaskAssociation (lngCheckID,lngTaskNumber)
SELECT Table1.lngCheckID, Table1.lngTaskNumber FROM (SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID
UNION ALL SELECT 1 As lngCheckID,2 As lngTaskNumber FROM LC__DummyID) As Table1
hi pinal
i have to match two tables and insert the matched data in a separate table and unmatched in another table what should i do . please help
aruj
Hi Aruj,
To get matched records use INTERSECT clause and to get non-matched records use EXCEPT clause. Write two SQL statements to perform these two operations and insert the results in target tables.
Regards,
Pinal Dave
Hi pinal,
I attended a interview on Saturday(6-2-10) .He asked a question that
Can we use function to insert values into Database?
I answered no … because i think function are used to return values….
Does my answer was right ??? I am beginner to mssql
Thanks in advance
Chethan.K.V
Hello Chetan,
Your answer is correct. Any type of changes can not be performed through function.
Best of luck!!!
Regards,
Pinal Dave
to diffrant database but the table are same one table to onther table record are append(1598 record ) how to append the data onther table
By append, to you mean INSERT from one TABLE to another, or to get the results from both TABLEs?
hai if i am leaving the values for some fields in the insertion query it should not display the error it should take the value and store in the datbase as ‘zero’.Thanks in advance .Plz reply mesoon
hai if i am leaving the values for some fields in the insertion query it should not display the error it should take the value and store in the datbase as ‘zero’.Thanks in advance .Plz reply mesoon
You should make those columns to have default value 0
Do these COLUMNs have a DEFAULT? A DEFAULT value is supplied when the statement doesn’t INSERT it. That DEFAULT value is NULL, which will cause an error if the COLUMN is set as NOT NULL.
hi dev,
this is babu.i am new to diz blog.
here i have a problem .regarding insertion of multiple recards at a time in temp table.
as you see below i declared one temp table
then i execute my query and inserted into temp table.
but only last record in inserting here when i did like diz.
plz any help is appriciatable .
(i have to take whole records in diz temp table so further i can use like operater on it to filter)
create table #temp(post varchar(100),[user_id] int,[image] varchar(100),city_id int)
declare @a varchar(100),@b int,@c varchar(100),@d int
insert #temp values(
select distinct a.post,a.user_Id,a.Image,a.city_Id from ads a,cities c,items i
where a.item_id=i.item_id and a.city_id=1)
select * from #temp
Your code should be
create table #temp(post varchar(100),[user_id] int,[image] varchar(100),city_id int)
declare @a varchar(100),@b int,@c varchar(100),@d int
insert #temp
select distinct a.post,a.user_Id,a.Image,a.city_Id from ads a,cities c,items i
where a.item_id=i.item_id and a.city_id=1
select * from #temp
How many records does the SELECT actually return?
Hello Babu,
Rewrite your query as below:
insert into #temp
select distinct a.post,a.user_Id,a.Image,a.city_Id
from ads a,cities c,items i
where a.item_id=i.item_id and a.city_id=1
Regards,
Pinal Dave
Hi,
I have 2 queries which retreive same column with different values. I want to add the values of these two columns(corresponding values) using these 2 queries. Any kind of help will be highly appreciated.
Regards,
Neha
Hi,
I have a customers table, orders table and a products table
A customer orders more than one product at once
i would like to insert those product values associated with the customer with one insert statement into the orders table
How do i do that?
insert into orders (customer_name, product_name_1, product_name_2, product_name_3)
select (select c.customer_name from customer c where c.customer_id = your_customer_id), (select p.product_name_1 from product p where p.customer_id = your_customer_id), (select p.product_name_2 from product p where p.customer_id = your_customer_id), (select p.product_name_3 from product p where p.customer_id = your_customer_id)
this will resolve your problem hoping that there is some customer id in the all three tables for referencing.
This is a classical example of many-to-many relationship.
You need 4 tables with (at least) following columns:
Customer (ID INT PRIMARY KEY, Name NVARCHAR(..), Address NVARCHAR(..), ..)
Order (ID INT PRIMARY KEY, CustomerId INT REFERENCES Customer(ID), OrderDate DATETIME, ..)
Product (ID INT PRIMARY KEY, Name NVARCHAR(..), ..)
OrderLine (ID INT PRIMARY KEY, OrderId INT REFERENCES Order(ID), ProductId REFERENCES Product(ID), AmountOfItems INT, ..)
Customer, Order and Product tables probably explaines themselves. OrderLine table forms a m:m relation between Order and Product tables. It tells how many Product-items belongs to the Order.
Thanks,
But what if one customer orders five products at once, and i want to update those products and associate it with the customer at once.
for instance CustID =1
orders = ProdID =5,8,9,10,15
orderdate = 2010/01/02
How do i write the query to insert the custID with the ProdID’s into the database?
@Whitaker
INSERT INTO OrderLine(OrderId, ProductId)
SELECT @CustId, 5 UNION ALL
SELECT @CustId, 8 UNION ALL
SELECT @CustId, 9 UNION ALL
SELECT @CustId, 10 UNION ALL
SELECT @CustId, 15
Hi Pinal
Mistakenly i have delete all the data in a table.How can i rollback the transaction.?But i am not using any begin trans statement.
directly i have typed
‘delete from tablename’
@ravikumar
It cannot be ROLL BACK.
Do you have a backup?
hi @ pinal n all,
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
i tried the above code to insert multiple records into single table ‘my_dept’ , but m getting ORA-00923 error:”FROM keyword not found where expected”…..
can u point out where the problem
thanx in advance…!!!
@karan
SQL Server allows the FROM clause to be omitted, Oracle does not. Instead, use FROM Dual
select 1,’research’,’blore’ FROM Dual
UNION ALL
select 2,’ad’,’mysore’ FROM Dual
UNION ALL
select 3,’marketing’,’hyderabad’ FROM Dual
UNION ALL
select 4,’hr’,’mumbai’ FROM Dual
go
Note though, this is a SQL Server forum, not an Oracle forum.
I’m sorry but this article is entirely misleading. Why are you creating UNIONS where you can use a STANDARD ANSI SQL INSERT statement for such a task???
Smeet Bhasker correctly pointed this out. I strongly suggest you update your article so that you stop propagating incorrect information.
To insert multiple rows in a single statement can be easily acheived by using ANSI SQL syntax as follows:
Sumeet Bhasker wrote:
INSERT INTO YourTable (FirstCol, SecondCol)
VALUES (‘First’ , 1) , (‘Second’ , 2) , (‘Third’ , ‘3′), (‘Fourth’ , ‘4′) (‘and so on’) ;
Hello Gavin,
This article was written in 2007 at that time ANSI SQL INSERT was not implemented in SQL Server.
This feature is introduced in SQL Server 2008 and I have blogged about it as well suggested to use that afterwords.
Kind Regards,
Pinal
This doesn’t work in SQL 2005
Hi Sudhi,
It does work with SQL Server 2005, please post your code.
Kind Regards,
Pinal
Multiple row insertion method works from version 2008 onwards
how to combine more one fields into one field using sql2005
example:
colA col2
1 a
1 b
1 c
2 d
2 e
2 f
i need this
colA colB
1 a b c
2 d e f
thanks
This was a tricky one. I even had to use my good old friend Google a bit! But this is how you can do it in one query. Personally I would do it using APPLY and UDF though.
WITH CTE1(col1, r, col2)
AS
(
SELECT
col1,
ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col1),
CAST(col2 AS VARCHAR(8000))
FROM
MyTable
),
CTE2(col1, r, col2)
AS
(
SELECT
col1,
r,
col2
FROM
CTE1
WHERE
r = 1
),
CTE3(col1, r, col2)
AS
(
SELECT
col1,
r,
col2
FROM
CTE2
UNION ALL
SELECT
CTE1.col1,
CTE1.r,
CTE3.col2 + ‘,’ + CTE1.col2
FROM
CTE1 INNER JOIN
CTE3 ON CTE1.col1 = CTE3.col1 AND CTE1.r = CTE3.r + 1
)
SELECT
col1,
MAX(col2)
FROM
CTE3
GROUP BY
col1
thank u very very much for the stuff provided here….
really u have solved a big problem…
thanks a lot…
Hi,
i have one table which contain one of the column data type is xml. so how can i insert data into this table with single quotes? if i just insert as below
select * from emp where empId=”
while retriving i am getting like below
select * from emp where empId=’
please give me solution.
Tnx.
You need to use four single quotes
Refer this to understand how single quotes work in SQL Server
http://beyondrelational.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx
sir,
can you send me the ‘UPDATE’ query for SQL server 2005 to my E mail address?
I am using UNION ALL to insert 1000 records at a time for realtime data with Sql server 2000 Std Edition service pack 4 it take 1 sec but with Sql Server 2005 Enterprise Edition it takes 5 sec.
Please suggest what can be done to resolve the problem.
Many Thanks
Gunjan
Awating for reply.
On the same DB I m running another insert query using Union all to insert data in 11 columns this is taking 1 sec for 1000 records but other query which is inserting data in 20 columns taking 5 sec. Please reply.
Many thx in advance
Gunjan
INSERT INTO TABEL (test, test1, test2) VALUES (), (), ()
use it like this …
Note that this method will work from version 2008 onwards only
i am nikunj and develope hospital managemet
so i have 78 filed in 1 table how insert data in to database using for loop
please
Where are source data coming from?
Hi Pinal,
I am having 20 variables which are to be inserted into a table all are new rows.
But it is conditional, i.e. if a variable is blank i dont want it to be updated…..
Thnks & Regards,
Kapil Desai
Got the Solution
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
put where clause in Select statement to chk if empty
insert into table(col)
select col from
(
select @var1 as col
union all
select @var2
union all
.
.
) as t
where col is not null
Hello Kapil,
If you are updating the columns with the value of variables then assign the variable value as below:
column1 = ISNULL(@variable1,column1)
And if you want to check for blank space also then write as below:
column1 = CASE WHEN ISNULL(@variable1,”) = ” THEN column1 ELSE @variable1 END
Regards,
Pinal Dave
while we are inserting more then 100 record in a table then we are getting error
ORA-01438: value larger than specified precision allows for this column
Note that this is for MS SQL Server
Post your issues at http://www.orafaq.com
Hey Pinal Dave…I’m working with SQL Server and need to make an operation of inserting values into a new table…but these values come from another table that is in the same database….each field in the new table comes from an operation like this:
INSERT INTO [FT].[dbo].[NewData]
([Name]
)
SELECT [Value]
FROM [FactoryTalk].[dbo].[FloatTable]
WHERE Index=0
the other operations are the same…just changing the column name of the table and the index…the second operation is this:
INSERT INTO [FT].[dbo].[NewData]
([Data]
)
SELECT [Value]
FROM [FactoryTalk].[dbo].[FloatTable]
WHERE Index=1
I tried to do all the operations together but wasn’t sucessfull… the only thing I coud do was to do them separately….but then each operation just completes one column and fill the others with NULL elements…
I tried to use ‘UNION’ and ‘UNION ALL’ but they didn’t work… please help me…
thanks for your attention…
Matt
Try something like
INSERT INTO [FT].[dbo].[NewData]
([Name],[Data]
)
SELECT
case when Index=0 then [Value] else 0 end,
case when Index=1 then [Value] else 0 end
FROM [FactoryTalk].[dbo].[FloatTable]
I need to insert 50 rows in a MsSQL table in a new column, which has been newly added, but all the other columns should have the same data.
How can i do that with a single insert query?
Should i need to delete all the records prior that?
How will you map each of new data to existing values?
we’ve been experiencing problems in retrieval and storing data in sql server 2005. this past few days storing data entered by multiple end-users causes our sql server to slows down in its processing, end-users access one table at the same time which resulted to hanging of computers and many of them their data did not store in the table. how can we resolve this problems? is this one of the weaknesses of sql database server 2005?
How much data does the table have?
Post the code used to insert data to the table
sir, how can i insert value in one field only in mysql?
for example, i have:
1. “dog”
2. “cat”
3. “mouse”
i need to insert that all in one field name Animal. how do i do that?
is it possible to insert value in a field where there is existing value to that field even its not a primary key that will add another value to that field?
sorry for my english..
i need it for my thesis. thank you again.
Are you using mysql?
Use
select group_concat(animal) from your_table
Also note that this site is for MS SQL Server
For mysql related questions post at http://www.mysql.com
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
Hi Pinal Dave,
I want to know, if we ahve 1000′ of records and we have to insert records into table in a batch of say..50 records.
How can we do in SQL?
In PL/SQL we can write block for that but how can we achieve it on SQL command prompt.
Start with this
declare @i int
set @i=50
while @i>0
begin
insert into target_table(columns)
select top (@i) columns from source as s
where not exists(select * from target_table where unique_col=s.unique_col)
if @@rowcount=0
break
end
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 ……..
Sir,
I am in a situation where i have to fill multiple records against a single number for example if you purchase many things on a single bill in a mall. i am using two table for that first one is taking the master table where we store bill specific details like bill no., customer name,address , mobile number etc. in second table i am storing all the material purchased against that bill no. Now, i am taking all the records in array and then sending it to the table. Is there any other way to send it to the table?
@Smit,
If you give some sample input and expected output, that would help us understand what your requirement is.
Data speaks better than explanation.
~IM.
Dear All,
Hi,
i want to ask u alls please tell me how i make insert and update query whit inner join so please tell me
thanx
pop show
Update t1
set col1=t2.col1,
.
.
.
from table1 as t1 inner join table2 as t2
on t1.keycol=t2.keycol
i have 3 tables:
Measurement (MeasureID,Name)
Customer(CustID,Name)
CustMeasureLink(CustID,MeasureID,Value)
I want that whenever new measurement is added..
then in CustMeasureLink new records with that MeasureID should be added paired with existing CustID’s..
eg. if there are 3 records in customer table…now if MeasurementID 2 is added in measurement table than….
CustMeasureLink would be…
1,2,SomeValue
2,2,SomeValue
3,2,SomeValue
Please help me how to do that in single insert statement…..
Thanx….
i have 3 tables:
Measurement (MeasureID,Name)
Customer(CustID,Name)
CustMeasureLink(CustID,MeasureID,Value)
I want that whenever new measurement is added..
then in CustMeasureLink new records with that MeasureID should be added paired with existing CustID’s..
eg. if there are 3 records in customer table…now if MeasurementID 2 is added in measurement table than….
CustMeasureLink would be…
1,2,SomeValue
2,2,SomeValue
3,2,SomeValue
Please help me how to do that in single insert statement…..
Thanx….
insert into CustMeasureLink(CustID,MeasureID,Value)
select CustId,2,somevalue from Customer
table1 contains one column that is date
i want to alter the table and add column month
& extract the month for the date column & update the table
date is in this format : 02/18/2010
the new column month must contains 02
or if possible ‘FEB’
You dont need a seperate column
Use derived column in the SELECT statement
SELECT date_col, month(cast(date_col as datetime)) as [month] from your_table
Also, you should always use proper DATETIME datatype to store dates. Also make sure to read this blogpost
http://beyondrelational.com/blogs/madhivanan/archive/2010/05/25/understanding-datetime-column-part-i.aspx
but i need another column for further requirement…
i had return below query but the inner query retuens multiple values….
update test set test.month1 =(select MONTH(b.osdate) from test b where test.osdate=b.osdate)
update test set month1 =MONTH(cast(b.osdate as datetime))
I want to create a record from existing record but want to change the two filed value.
insert into table(columns)
select col1,col2,2,’test’,…. from table
I have a query,
I try your query with both UNION ALL and UNION and data inserted properly. Is there any difference between them with insert clause?
There are lot of differences between the two. Try with duplicate values and see the result. Also make sure to read this blog post
http://beyondrelational.com/blogs/madhivanan/archive/2008/04/03/union-vs-union-all.aspx
Thanks for the tip – and to think, only almost EVERY OTHER platform has been doing it ALL WRONG.
aka -
INSERT INTO TABLE (COL1, COL2, COL3) VALUES
(1, 2, 3),(4, 5, 6)…etc
Thanks Micro$oft for providing a longer more tedious method!
Note that this will work from version 2008 only
Hi.. Pinal,
I need to insert data in two tables with 1 to many relations ship from front end. What I am doing is In Table1 single row is inserting but in Table2 multiple records are inserting with single Stored procedure.
Here is the code for stored procedure:
BEGIN TRAN
BEGIN try
Insert into Table1 ()
values ()
END try
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Declare @Identity numeric
Select @Identity =@@IDENTITY
BEGIN try
Insert into Table2 ()
values ()
END try
BEGIN CATCH
ROLLBACK TRAN
END CATCH
Commit Tran
Kindly suggest your reviews on above procedure. as I am new to SQL
hi am goutham
i want insert per day only 10 values in a table by using
stored procedure in
sql sererver
Ony method I can think of is to use a trigger
Have a datetime column in the table with the defualt value of getdate(). In the after trigger write this
If exists(select * from table where datecol>=dateadd(day,datediff(day,0,getdate()),0) and datecol<dateadd(day,datediff(day,0,getdate())+1,0) )
rollback
Ony method I can think of is to use a trigger
Have a datetime column in the table with the defualt value of getdate(). In the after trigger write this
If (select count(*) from table where datecol>=dateadd(day,datediff(day,0,getdate()),0) and datecol10
rollback
The code should be
If
exists(select count(*) from table where datecol>=dateadd(day,datediff(day,0,getdate()),0) and datecol10
rollback
Here is my code currently:
Select c.CaseNo,
ci.indIncomeSrc1 as indIncomeSrc1,
ci.indIncomeSrc2 as indIncomeSrc2,
ci.indIncomeSrc3 as indIncomeSrc3
from XAKTcomSupServ.dbo.Clients c
–Pulls in Client Income Source
Left Join Clients.dbo.ConsumerIncome ci
on c.CaseNo = ci.cssID_fk
Here is the Output:
1001652
NULL NULL NULL 1802630
13 16 16 1802766
NULL NULL NULL 1804297
14 16 16 1804706
NULL NULL NULL 1805008
10 16 16 1805427
14 16 16 1806114
NULL NULL NULL 1806171
NULL NULL NULL 1806200
14 16 16 1806262
14 15 16 1806421
What I need is if any of the columns match not to be included in final output.. ie
As in column 2 I only want a final output of 13, 16 not
13, 16, 16.
Thank for the help.
hi
can any help me getting date in month and year in one column i.e 07-2010 (mm-yyyy)
This is the formation issue which should be done in your front end application. Refer this http://beyondrelational.com/blogs/madhivanan/archive/2010/06/29/understanding-datetime-column-part-iv.aspx
If you dont use any front ends, use
select right(convert(varchar,getdate(),103),7)
is there any other option than concat()
What did you mean by concat?
ohhhh (smile)
i mean
select convert(varchar ,MONTH(GETDATE()),111) +’-’ + convert(varchar ,year(getdate()),111)
anyway thanx madhivanan i got my soln
Ok. You should do formation at front end application.
Otherwise this is another simple method
select right(convert(varchar(10),getdate(),105),7)
from u ofcourse
It’s not pretty but here how I finally worked: (I am using SQL 2000 so 2008 option are not possible)
SELECT ins.CaseNo,
ins.indSrc1
Into #tmp
FROM #IncomeSource ins
SELECT ins1.CaseNo,
ins1.indSrc2
Into #tmp2
FROM #IncomeSource ins1
Where ins1.indSrc2 not in (ins1.indSrc3)
SELECT ins2.CaseNo,
ins2.indSrc3
Into #tmp3
FROM #IncomeSource ins2
Where ins2.indSrc1 not in (ins2.indSrc3)
–then in the next select statment
select tp.indsrc1+ ‘, ‘
+ t2.indsrc2+ ‘, ‘
+ t3.indsrc3 AS IncomeSource
HOW UPDATE ONE TABLE IN SAME DATA IN EVERY ROWS AND NO USE ANY KEYS THIS TABLE . WHAT WILL BE UPDATED ONE ROW DATA . AND NO ANY CHANGES ANY ROWS ONLY CHANGES PARTICULAR ONE UPDATED ROW HOW IT’S POSSIBLE DO THIS. PLEASE SEND QUERY
Can you give us more informations with sample data with expected result?
A much simpler solution is to do like this:
INSERT INTO table_name (col1, col2, col3, …) VALUES
(val1, val2, val3, …),
(val1b, val2b, val3b, …),
(val1c, val2c, val3c, …),
…
(val1n, val2n, val3n, …);
Each set of parentheses is another record to add.
Sir,
I have an issue in SQL 2000.
i HAVE 5 fields in a table.
Have ot insert 5 rows at a time with same id.
For e.g.
If I have Empid = 5 ,
at a time 5 rows with 5 columns should be inserted.
What would be the single insert query for generating 5 rows with same empid???
Can you post some sample data with expected result?
hello
pinal
i want to insert records with where clause ..i.e
insert into table1(column1,column2)values(‘abc’,'xyz’) where name=’cccc’
can i??
I think you need to use an update statement. If not, give more informations
does this statement works with SQL 2005?
Which statement are you talking about?
sorry this sttement..
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
…
when i try for the 1st time, it didn’t work, but now it’s working for sql 2005 because the statement thant i wrote got error..
anyway tq pinal…
I want to get onefield like “subject” its data a,b,c,d,e
display different columns
a b c d e
below of a its information ,below b its information
give solution in crystal report2.0 with vb.net 2005
In Crystal reports, use a formula containing this code
replace({col},”,”,crlf)
and use this field in the report
hi,
i have table temp1
CREATE TABLE temp1(id int,name varchar(50),add varchar(50))
and another table student
CREATE TABLE student(name varchar(50),add varchar(50))
insert temp1(name,add) select(name,add) from student
but i want id of table temp1 automatic contain value start from 0, 1,2,3 and so on
after delete table temp1 when insert value through command
insert temp1(name,add) select(name,add) from student
then again id valu start from 0,1,3 and so on
how can solve this problem
Thank & Regards
Atul Prajapati
Make use of identity column for temp1 table
Hi,
I have a table of 30000 rows, and it made it work slower than separated insert statements.
Thanks anyway,
Chen
actually iu have a problem with sql ig auto gen
i used the following syntaxes
this one is working
CREATE TABLE new_employees(id_num int IDENTITY(1,1),fname varchar (20),minit varchar(20),name varchar(30))
but this is not working while inserting the values
INSERT INTO new_employees(id_num,fname,minit,name)values(‘hkdfgf’,’4535′,’kjlfkhg’)
and it is showing the following errors
Msg 109, Level 15, State 1, Line 1
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
It should be
INSERT INTO new_employees(fname,minit,name)values(‘hkdfgf’,’4535′,’kjlfkhg’)
I am using Java J2ee (struts framework) and MS SQL server 2005, My functionality (business logic) will be in the stored procedure.
The problem is how to send set of records (output of a query) to java. Likewise i need to send set of entered records to Sql Server, to be inserted in a table.
Please help on the same
Thanks
Rasheed
You can loop thru a Java code to send data to a table
HI Friends,
I need another urgent help with example.
Task:-
1.Create ssis package with import multi format file into same table like excel and xml and db resources.
2.And create loop for checking this data and import into correct table and error table
3. And create meta data and read this data path from meta table to import
pls any one my friends help me with small examples
Your query is very much help to work but can u help me
how can i generate this from asp.net page
i want to print next 100 books barcodeand i am creating it run time in a panel , but if there is any command to save all the record using single query run time then it save my executing time
plz help me
You need to use pagination. Search for the same in this site
hi madhivanan,
it could be consume more time as we are passing insert statement from the Java. i will increase IO operation from DB to Java.
Please advice
Thanks
Rasheed
How many row are you going to add at a time?
Dear Pinal,
I am new in to fields of Databases and i am interested in term DBA, but one thing really makes me confussing most of the time, when i am ask about the memory structure for SQL Server as you know every thing that is with the scopr of databases is mainly maintained and managed within the prescribed structre. so please can you elaborate the Instance and Datafile involvement in depth.
Thanks and Regards.
Ziyad Mehmood(DBA)……
May all u live long.this site is pretty much informative, appreciated.
i have a proble any one can solve my problem:
the problem is
i have a client side form having fields refno, company, amount, paid, through which a franchise can pay bills. i want that after 5 pm the form is automatically disabled and no bill payment can a franchise make after 5 pm. will u plz help me. its urgent
i need this in php. tell me about it a php query
ur admirer
web developer
sami
Which application are you using?
When saving data you can check for the time and display an alert if the time is aboe 5 PM
very nice site it realy helpful.
i have a problem in php, that is a form having fields refno, company, amount, paid which is used to make a bill payment for franchise. my problem is if a reference no is, say 111 of a particular bill, is paid in a particular month, if a franchise used to pay that bill in that month again,they should receive a message u can not pay this bill again it paid once in a month.
but in next month they are allowed to pay that bill.
plz plz solve my problem. i will be very thank ful to all of u fine people.
web developer
sami
You need to check for the payment date. If there exists data for this month for that bill, give an alert message
i am using SQL 2000 plz tell me insertion of records(i used above but there is coming eror)
What was the error you got?
good morning sir
i have employee table
some columns sal_id, sal_salary,sal_address etc
sal_salary
14000
12000
3000
12000
2000
4500
9800
12000
6000
2300
1200
1800
means toal column no 100
if i want to sum use select sum(sal_salary) as col name from employee
if i want to sum only for 10 to 50 column then i don’t know about answer this question
plz sir reply me answer this question
Post the exected result
hello sir
means sir , sir plz give me query for this question plz
thankyou
You need to post the exepcted result for the sample data
This is sourse table
eno,sal
1,10
2,20
3,30
destination table
eno,sal
1,10
2,30
3,60
how to write quaries?
select eno, sal from source_table
union all
select eno, sal from target_table
how can i insert bulk values at a time
ex:- i had a table custid and the table consists of single column i,e custid 1 to 100
table
i want to insert all the values at time how can it be done
insert into target_table(custid)
select custid from custid
tnks it helps me alot
i have table data in col1 and col2 like
28 10
25 25
28 58
30 42
i need result like
28 10
25 25
30 42
means no repeatation of col1 Plz advise query of sql 2005
select col1,min(col2) as col2 from table
group by col1
i am getting a error while inserting using a union all
drop table #t
create table #t (sno int,num varchar(10))
– Create a comma delimited string to test with
declare @str varchar(500),@sno int
select @str = ‘ABC|DEF|GHI’
select @sno = 123
–select @str = ’4,2,7,7834,45,24,45,77′
——————————————————–
—- Code to load the delimited string into a table —-
——————————————————–
– Create insert for comma delimited values
declare @sql varchar(8000)
select @sql = ‘insert into #t select (@sno, ”’+
replace(@str,’|',”’) union all select (@sno, ”’)
select @sql=@sql+”’)’
print @sql
– Load values from comma delimited string into a table
exec ( @SQL )
——————————————————–
——————————————————–
– Select values from temp table to show results
select * from #t
Error is:
Must declare the variable ‘@sno’.
But i have already declare that variable. What may be the error
select @sql = ‘insert into #t select (@sno, ”’+
replace(@str,’|’,”’) union all select (@sno, ”’)
should be
select @sql = ‘insert into #t select (‘+@sno+’, ”’+
replace(@str,’|’,”’) union all select (‘+@sno+’, ”’)
i inserted multiple items in our SQL database but it is somewhat not appearing or even if I invoke it in our web service it is not appearing. can someone help me with this??
Hi,
I also inserted 66,000 items in our SQL database and it was inserted succesfully when I check the count of the items in the database, but when i am accessing it in our website it is not displaying. Can someone help me with this?
Did you get any errors during insertion?
Did you get any errors when you insert data to the table?
Did you get any error duing insertion of data?
how can i insert all rows one time into table in sql server2005
INSERT INTO tab_treeview1 values
(186, ‘vedioui.gif’, 151, ‘Linear Algebra Coordinates with Respect to a Basis.mp4′, ‘Linear Algebra Coordinates with Respect to a Basis.mp4′, ‘Coordinates wit’),
(187, ‘vedioui.gif’, 151, ‘Linear Algebra Coordinates with respect to orthonormal bases.mp4′, ‘Linear Algebra Coordinates with respect to orthonormal bases.mp4′, ‘Coordinates wit’),
(188, ‘vedioui.gif’, 151, ‘Linear Algebra Cross Product Introduction.mp4′, ‘Linear Algebra Cross Product Introduction.mp4′, ‘Cross Product ‘),
(189, ‘vedioui.gif’, 151, ‘Linear Algebra Deriving a method for determining inverses.mp4′, ‘Linear Algebra Deriving a method for determining inverses.mp4′, ‘Determining Inv’)
(190, ‘vedioui.gif’, 151, ‘Linear Algebra Determinant after row operations.mp4′, ‘Linear Algebra Determinant after row operations.mp4′, ‘Row operations’)
(191, ‘vedioui.gif’, 151, ‘Linear Algebra Determinant as Scaling Factor.mp4′, ‘Linear Algebra Determinant as Scaling Factor.mp4′, ‘Scaling Factor’),
(192, ‘vedioui.gif’, 151, ‘Linear Algebra Determinant when row is added.mp4′, ‘Linear Algebra Determinant when row is added.mp4′, ‘ when row is ad’),
(193, ‘vedioui.gif’, 151, ‘Linear Algebra Determinant when row multiplied by scalar.mp4′, ‘Linear Algebra Determinant when row multiplied by scalar.mp4′, ‘Multiplied by s’),
(194, ‘vedioui.gif’, 151, ‘Linear Algebra Determinants along other rowscols.mp4′, ‘Linear Algebra Determinants along other rowscols.mp4′, ‘ Rowscols’),
(195, ‘vedioui.gif’, 151, ‘Linear Algebra dim(V) + dim(orthogonoal complelent of V)=n.mp4′, ‘Linear Algebra dim(V) + dim(orthogonoal complelent of V)=n.mp4′, ‘Orthogonoal’),
(196, ‘vedioui.gif’, 151, ‘Linear Algebra Duplicate Row Determinant.mp4′, ‘Linear Algebra Duplicate Row Determinant.mp4′, ‘Duplicate Row D’),
(197, ‘vedioui.gif’, 151, ‘Linear Algebra Eigenvalues of a 3×3 matrix.mp4′, ‘Linear Algebra Eigenvalues of a 3×3 matrix.mp4′, ‘Eigenvalues of ‘)
Hello Mr. Martin,
First, I want to know what type of data you are trying to retrive, whether it is just retrieving 66000 rows with all the columns or is it based on some condition.
Please execute the same query that you are trying to execute from the front end in the T-SQL query analyzer and check whether it is retrieving the required data.
Kind Regards,
Pinal Dave
it is a search suggestions, some sort of intellisense, appears only when letters are typed in the search box.
There are no errors. actually insertion is succesful.
Hello Mr. Marlon,
Please let me know whether you are trying to fetch all the columns of the data or based on some condition.
As a first step, open the table in the SQL database and check whether all the data has been inserted into the table properly.
If so, execute the same query that you are trying to execute from the front end in T-SQL query analyzer and check whether the query fetches the desired data.
Kind Regards,
Pinal Dave
Hi Sir,
I’m new to SQL and I was browsing the web and happen to strolled to your site, I may say you have a very interesting site. I need your assistance regarding the below information because I’m not sure if I’m building this table correct with the included information. I ‘m using SQL Managing Studios 2008 for building/creating a table in DDL (I’m a first-time user). The table has 6 columns (FName, LName, SSN, Salary, SuperSsn…) and 6 rows contain the person information such as fname, lname, ssn…. Anyway is this the proper syntax and procedures for creating a table with the below information? Any assistance would be greatly appreciated, Thank you.
CREATE TABLE [EMPLOYEE]
([id][int]IDENTITY(1,1) NOT NULL,
[FName][varchar] (25) NOT NULL,
[LNAME][varchar] (25) NOT NULL,
[SSN][varchar] (11) NOT NULL,
[Salary][varchar](10) NOT NULL,
[SuperSsn][varchar](11) NOT NULL,
[DNo][varchar](6) NOT NULL;
INSERT INTO [EMPLOYEE]
([FName],[LName],[SSN],[Salary],[SuperSsn],[DNo])
VALUES(‘Eilen”MarcAdoo”12345987”65000”12345987”1004′),
(‘Nora”Watkins”45123987”35500”12345987”1001′),
(‘Mary Anne”Lazarro”32145878”60000”12345987”1003′),
(‘Clara”Thomson”03412344”53000”12345987”1003′),
(‘Raymond”Thomson”02932455”22200”12345987”1002′),
(‘Ziggy”Gravellese”45698755”35000”12345987”1002′);
Why did you use varchar datatype to store salary? Use proper datatypes such as numeric
varchar datatype accept both integer & String..If U r using Varchar Datatype in Data You may to store String Values & integer Values….Then Why are Suggested To Rick “You are Must Use to store the salary using Integer Datatype”….Sir Explain This Confusion.
When you store numbers in varchar datatype, you cannot do any arithmetic calculations until you convert to number
This is Pradeep, working as a Application Developer in Chennai, Your Blog is Nice….
hello mr.Pinal Dave
I have a program with some client(win app) and I want to integrate their data into one database. I mean I have a central databse. how can do that?
You need to import data to the respective tables
Hi,
I want to copy records from one database to another database which already contains some records.
So while inserting records if record already exists then update the existing to new one else insert it.
How to do this in SQL server 2008 express/professional.
General logic
update t
set col=s.col
from source as s inner join target as t
on s.keycol=t.keycol
insert into target(col_list)
select col_list from source as s
where not exists(select * from target where keycol=s.keycol)
I think this is even easier ;-)
INSERT INTO MyTable
(FirstCol, SecondCol)
values
(‘First’ ,1)
,(‘Second’ ,2)
,(‘Third’ ,3)
,(‘Fourth’ ,4)
,(‘Fifth’ ,5)
GO
Yes provided that the version is 2008 or more
Hi Dave,
I would like to insert the following query around 50,000 times with different MDN, how to write the PL/SQL query, please?
Here MDNs starting value is – ’8000000000′ and ends with ’8000050000′.
INSERT INTO “ABC”.”MDN_MAP” (PART_KEY, VERSION, MDN, CARRIER_ID, EFFECTIVE_DATE) VALUES (’20101116′, TO_DATE(’16-NOV-10′, ‘DD-MON-RR’), ’8000000000′, ’322222′, TO_DATE(’16-NOV-10′, ‘DD-MON-RR’));
Please do the needful asap.
Thanks,
-Vishwa
This is for MS SQL server. For Oracle questions, post at oracle sites
I wanted to insert n number of data in table
please could any one let me know the query/script
Where is the source? If it is in a text file use bcp or bulk insert
Thanksssssssssssssss!!!!
Hi Pinal,
i have got a list say list skills to be entered into a table say ‘skills’. Each skill should be added as a new record in the table.
The number of skills can vary.
I want to use stored procedure for this task. The list will be passed as parameter in the Stored Procedure and the Stored Procedure should retrieve each skill and insert a new record.
I repeat again the number of skills can vary and thus number of records to be inserted can vary.
Please can you help me out with this.
hi every one thanx to all who helped :-)
Hi Pinal,
When i Write this query in SQL server 2005 am getting error like “incorrect syntax near ‘ALL’
Please help .
Post the exact code you used
One little contribution do Dave’s post about efficiency (July 1, 2008).
(I am using VB .NET 2008, Excel 2007, MSSQL 2005)
My program has to insert data from excel (23271 rows x 29 columns) into database. I was experimenting with both of UNION ALL and INSERT INTO with different fragment size and here’s what I found out:
INSERT INTO statement:
size of fragments time(min)
1 3:55
25 3:20
50 3:10
100 3:05
200 3:05
400 3:05
800 3:05
UNION ALL statement:
size of fragments time(min)
5 2:40
25 2:30
50 2:15
100 2:15
150 2:15
200 2:20
300 2:40
500 3:15
1000 >5
First statement experiment was expected(?) because there’s n INSERT INTO statements group in one so as fragment size increases timing tends to about 3 minutes (3:05).
Second statement experiment shows that most efficient fragment size is about 100+/-50 for the same data size as in experiment.
So, for most data sizes UNION ALL statement with specific fragment size should be more efficient than INSERT INTO.
Anyone?
Hello Pinal Dave,
I am a s/w engg in Mohali.. and this site is really helpful.
thanks for writing
Regatds.
This post is very useful. I’m wondering though how can I use a CASE statement to determine which records will be inserted in my one INSERT statement? I’m trying to insert multiple values into a table variable using a CASE statement and then want to be able to select all the values that were inserted. I included a snippet:
DECLARE @YrTbl TABLE
(Yr VARCHAR(7))
SET @TodayMn = MONTH(getdate())
SET @TodayYr = YEAR(getdate())
SET @2Years = YEAR(getdate())-2
SET @LastYr = YEAR(getdate())-1
SET @Yr1 = @2Years + ‘-’ + RIGHT(@LastYr,2) — ’2009-10′
SET @Yr2 = @LastYr + ‘-’ + RIGHT(@TodayYr,2) — ’2010-11′
– I know the syntax is not correct, but this is what I want to be able to do:
INSERT INTO @YrTbl SELECT CASE WHEN @TodayMn in (1) THEN SELECT @Yr1 UNION ALL SELECT @Yr2 end
SELECT Yr from @YrTbl
What I’m trying to get from my SELECT:
2009-10
2010-11
How can I do this?
INSERT INTO @YrTbl SELECT CASE WHEN @TodayMn in (1) THEN @Yr1 END UNION ALL SELECT @Yr2 end
SELECT Yr from @YrTbl
Thanks madhivanan. Sorry I didn’t mention it earlier, but the problem is, I need to keep it within the CASE so that I can express what to do if the @TodayMn is not in 1. So in addition to the
WHEN @TodayMn IN (1) THEN…
I also need
WHEN @TodayMn IN (2) THEN…
(3), (4), etc
You cannot have multile values in case expression. However you can have it inside a single quote
Ended up using IF THEN ELSE instead of CASE and it worked like a charm.
Great help Pinal Dave.
Thanks
hi!
how can i copy multi field data from one table to other table in one field in new new column
please help!
post some sample data with expected result
Hello,
I have some problem . i want change My Sql Data base in MS Sql data base . i am trying that but Both Syntax are different.
plz help me
You need to change the queries manually
Hello,
Insert into myTable
(col1, col2, col3)
select 1, 2, 3
union all
select 4, 5, 6
works really fine for me.
But I want it to be a stored procedure, and I want to pass all the data as a parameter. Is there a way to do that?
Thanks in advance..
I’m having a problem with a Java code Sir,I find it hard to spot where the exception might be,during compilation,it runs properly,but when I execute it,it displays some certain exception (Error :java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘Schedule’.) please help,below is the code:
import java.sql.*;
public class Test
{
public static void main(String args[])
{
try
{
/* Initialize and load the JDBC-ODBC Bridge driver */
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverManager.getConnection(“jdbc:odbc:air_dsn”,”",”");
Statement stat = con.createStatement();
ResultSet rs = stat.executeQuery(“SELECT Fl_no,Fl_route,Dp_timings from Schedule”);
System.out.println(“******* Flight Schedule *******”);
while (rs.next())
System.out.print(rs.getString(1) + ” : ” + rs.getString(2)+ ” : “+rs.getString(3) + “\n”);
con.close();
con = DriverManager.getConnection(“jdbc:odbc:air_dsn”,”",”");
rs = stat.executeQuery(“Select Fl_no,Aircraft_type,Seating_capacity from Flights”);
System.out.println(“******* Flight Details *******”);
while (rs.next())
System.out.print(rs.getString(1) + ” : ” + rs.getString(2)+ ” : “+rs.getInt(3) + “\n”);
con.close();
}
catch (Exception e)
{
System.out.println(“Error :”+e);
}
}
}
Hi Pinal,
I am very new to SQL server programming and I am learning everything online. Your advice would be very valuable.
Is it possible to connect to a URL , pass parameters and load all the data (about 500 records)?
We have a webservice set up and is running using SAP CRM.
The automated webservice never loada all the records. So I end up having to run the web service ( passing 3 different sets of paramaters) emanually (using SAP) about 5 times a day and it really does take up my time! I have to pause my reports schedule until the data loads complete and rerun them.
Is there a way to avoid this slow loading? When i manually run it, I have to keep repeating many times before the table is fully loaded. I have tried to trace for any locks using SQL profiler but this doesn’t help. The web service quits with no error, after loading partially.
Any advice on how to make this run to completion, very much appreciated.
Regards,
Uv
I want to take user input to insert values into a table. the table exhists in he db already. I want the satement to loop for x amount of times, where x is the number of rows that need to be inserted… for instance I can run the following:
insert into Table
VALUES (‘NH’, ‘&ID’, ‘&ZONEID’, ‘&AREAID’);
this will prompt the user to inser values for ID, ZONEID and AREAID.
how to I loop and make it work if there are say 5 rows to insert with this information?
Thanks,
Doug
Hey really nice explanations…
Thanks very much..
I have question. I have two tables and I am comparing it using query listed below
SELECT MIN(TableName) as TableName, ID, employee,unit FROM (
SELECT ‘Table A’ as TableName, A.ID, A.employee, A.unit FROM Table1 A
UNION ALL
SELECT ‘Table B’ as TableName, B.ID, if (A.ID>B.ID,’Y',’N')as Flag , B.employee, B.unit FROM Table2 B
) tmp
GROUP BY ID, employee,unit
HAVING COUNT(*) = 1
ORDER BY ID
getting result
Table A 15001 s1sangare ppm1
Table B 15948 d1wilson ppm1
Table A 15948 d1wilson sgi1
Table A 18548 d1mschroe ppm1
Table B 19546 n1mpeshma ppm1
my question : in above query it compars data if any changes are made to any of columns data is displyed like d1wilson.
I want to add flag if any one of column is change say N if both columns have same data than flag should be Y
Please help to redefine query
INSERT permission denied on object ‘emp’, database ‘Test1′, schema ‘dbo’.
how to insert many thousands of records in a table using single insert that has four columns the first column is an auto number the second one takes value from another table column and the third one takes a static string value
insert into target_table(cols_list)
select col2,’value’ from source_table
Note that you need to exclude identity column in Cols_list
hello,
pinaldevo ,
i read your blog that is very effective , i am frsher working in vc++ developer as a fresher in small company,i have MCA from2010 in Jss engineering college,noida;
This makes your work more complicate. as a simple solution just use this.
USE DATABASENAME;
INSERT INTO tableName(col1,col2,col3….) VALUES(‘Record 1 val1′,’Record 1 val2′,’Record 1 val3′), (‘Record 2 val1′,’Record 2 val2′,’Record 2 val3′),(‘Record 3 val1′,’Record 3 val2′,’Record 3 val3′);
if you wanna use this with C# just put that into a string variable like this.
SqlConnection connectionVariable = new SqlConnection(“connection string blabla bla…”);
string cmdStr= “INSERT INTO tableName(col1,col2,col3….) VALUES(‘Record 1 val1′,’Record 1 val2′,’Record 1 val3′), (‘Record 2 val1′,’Record 2 val2′,’Record 2 val3′),(‘Record 3 val1′,’Record 3 val2′,’Record 3 val3′)”;
SqlCommand cmd = new SqlCommand(cmdStr,connectionVariable);
connectionVariable.Open();
cmd.ExecuteNonQuery();
connectionVariable.Close();
————————
Greeting from Rez.!
how to insert 1000 row in a table at a time?
insert into table(cols) select values…
GO 1000
Hi there!
Is it possible to insert the DEFAULT value in multiple insert statement.?
Thanks in Advance
Default values can be automatically added if the column is omitted in the insert statement
I have two separate table on my DB, they have the same column ttitles though with different rows length, so I will like to combine both into a single table.
I wrote the syntax below for that purpose, bit it doesn’t work
create table newtable
(col1,col2,col3)
go
insert into newtable
(col1,col2,col3)
select col1,col2,col3 from tab1
union all
select col1,col2,col3 from tab2
union all
;
It returned with tis error
“Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ‘;’.”
Please help
Great tip……going to get many uses out of this one!
Hi,
In table1 “4 fields” and table2 “3 fields” are there.
In table1 “4th filed” not null, here can i use use insert method with “SELECT” .
Regards,
Kumar. A
Hello Sir
I want to insert data in final table from test table in a single query… it is showing error in last line.. what is wrong there??
Please help me how can we insert data from different table of same schema into a single table?
select * into nitm_final
from ((select * from test_nitm_1)
union all
(select * from test_nitm_11)
union all
(select * from test_nitm_12)
union all
(select * from test_nitm_13)
union all
(select * from test_nitm_14)
union all
(select * from test_nitm_15)
union all
(select * from test_nitm_16)
union all
(select * from test_nitm_17)
union all
(select * from test_nitm_18)
union all
(select * from test_nitm_19)
union all
(select * from test_nitm_20)
union all
(select * from test_nitm_21)
union all
(select * from test_nitm_22)
union all
(select * from test_nitm_23)
union all
(select * from test_nitm_24)
union all
(select * from test_nitm_25)
union all
(select * from test_nitm_26)
union all
select * from test_nitm_27)
go
Got it..
I was missing alias name..
Add alias name at the last
select * from test_nitm_27) as t
go
sir,,
how to insert and select a image in sqlserver database … please help me
Simple method is to store path in the table’s column and store actual image in the server’s directory
Hi,
I am using sql server 2000, Once in 2-5 months, it stops insertion records from application;
and after that I have to manually insert one record from query analyzer and then it starts smooths for next 2-5 months; there is no error in sql server log nor in event viewer nor, space issue on HDD, nor log file is over sized;
your expert advice is required, what do i check to fix this issue;
hope to listen from you soon
thanks
sani
You need to run a profiler to understand what is going on
Hi
Thanks for your reply; you means when I database is stuck and not inserting rows; at that time I run profiler ?
as right now it is working and don’t have idea when this problem arise again
hope to listen from you again
thanks and best wishes
sani
You need to run it until you find something wrong in the process
hi everyone !!!!
I’ve six columns in new table….i ve to insert the data in it….all six columns are coming from different table…..consider there is no pk….how can i insert the data in it ?
plz reply !!!!!!
You may need to join all those xiz tables and insert to new table
thnkx !!!!!!
now plz help me in new query…plz find it below
Hi, pinal great website very helpful…great palace to learn new things…thanks
hi everyone
structure of tables
Table- Answer
[Connid] [varchar] (50) ,
[DNIS] [varchar] (50),
[ANI] [varchar] (50) ,
[Date] [varchar] (50),
[Time] [varchar] (50),
[DT] [datetime] NULL ,
[CampName] [varchar] (50).
Table abandoned
[Connid] [varchar] (50) ,
[DNIS] [varchar] (50),
[ANI] [varchar] (50) ,
[Date] [varchar] (50),
[Time] [varchar] (50),
[DT] [datetime] NULL ,
[CampName] [varchar] (50).
Table Abandonedq
[Connid] [varchar] (50) ,
[DNIS] [varchar] (50),
[ANI] [varchar] (50) ,
[Date] [varchar] (50),
[Time] [varchar] (50),
[DT] [datetime] NULL ,
[CampName] [varchar] (50)
there are four table………. one table ( table CDR —> as soon call enter in call center connid is provided) has column called DT….which is start time of call….now for end time data can be come any of the table……connid is uique for all four.
scenario-
in a call center a call comes it gets a unique connid………then the call process…..it could be abandoned or abandoned in queue or answer by agent…….i need to calculate total process time against each connid……may it be answered may it abandoned by user or abandoned in queue or answered by agent.
example–
connid-12312hqwjqheqkeq is answered by agent
connid-121wejqwheheqe is abandoned in queue
connid-121bddaskjdabsdk is abandoned by user
@varun,
hello, you can try following
insert into destination_table (col1,col2)
SELECT
(select col1 from source_table1),
(select col2 from source_table2)
how to store one column in multiple rows in one insert
hi
i want to insert records from table to another table
where record is exists then replace else add
can help me
thanks
Generic method
update t1
set t1.col=t2.col,…
from table1 as t1 inner join table2 as t2 on t1.keycol=t2.keycol
insert into table1(col_list)
select col_list from table2 as t2 where not exists(select * from table1 where keycol=t2.keycol)
Hello,
Can a datatable be passed as a parameter to a stored procedure? I want to insert data from a datatable into a database table. Can you please help me with it?
thanks
Hi Amee,
Are get the Solution for below Request . please help me about this query. i have same requirement. Thanks in Advance …
“Can a datatable be passed as a parameter to a stored procedure? I want to insert data from a datatable into a database table. Can you please help me with it?”
You need to use a dynamic sql. But in this case it is not recommended. Why do you want to do this? Also beware of sql injection
exec(‘use ‘+@db_name+’ your query here’)
sir,
I am uday
I am inserting multiple rows in two tables with relationship using xml.
I trying to inserting compny id(company table) in email table using @@IDENTITY
how i can write stored procedure for that
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
This one help me Great!
Thanku
Excellent job by Pinal…!!! Best Wishes
SELECT City, ModifiedDate
FROM Person.Address
WHERE StateProvinceID DATEADD(yyyy, -5, GETDATE());
GO
SELECT City, StateProvinceID, PostalCode
FROM Person.Address
WHERE StateProvinceID = 15733;
GO
– get the missing indexes that would be beneficial for speeding up above queries
SELECT D.index_handle, [statement] AS full_object_name, unique_compiles, avg_user_impact, user_scans, user_seeks, column_name, column_usage
FROM sys.dm_db_missing_index_groups G
JOIN sys.dm_db_missing_index_group_stats GS ON G.index_group_handle = GS.group_handle
JOIN sys.dm_db_missing_index_details D ON G.index_handle = D.index_handle
CROSS APPLY sys.dm_db_missing_index_columns (D.index_handle) DC
ORDER BY D.index_handle, [statement];
Hi Sir,
your blog is good and so much helpful to me . i have query on the above article insert Multiple records using single insert statement using Stored Procedure please give the solution to my query .
Post your query so that we will be able to help you
Hi Madhivanan,
Thank You for Responding quickly,
Am is the begginer , i have Dynamic Datatable in C# (ADO.net) with 50 records, i want to insert in Db using Stored Procedures with unique insert statement . Please help me ,
Thanks in Advance .
You need to loop thru the data in C# and insert into the database
Hi Madhivanan,
Thank You for Responding quickly,
am doing like that only but to insert every record we call storedprocedure for every record. i want to call storedprocedure only once . Is it Possible . Thanks in Advance.
You have concatenate all values and pass it as parameter to the procedure and split inside it. But it is better to call it everytime from your C#
try this
insert into toy values
(1,’Kitchen set’,200);
(2,’Racer jet’,500);
(3,’Milk Toy’,350)
Note that this will work from versions 2008 only
Hi. When I need to insert multiple values at once it’s ususaly from another table, so I just use the select-statement with the insert-statement like this:
insert into [MyTable] select ProjectID, GroupsID = 131119 from [MySourceTable] where [KeyField] = 1
Note that what I select must have the same columnnames as the table I’m trying to insert into.
Did you try this?
insert into [MyTable] select ProjectID, 131119 from [MySourceTable] where [KeyField] = 1
the syntax for multiple row insertion
INSERT INTO [Age]
([Name]
,[Age]
,[Profession])
VALUES
(‘n’,9,’j'),(‘t’,39,’yj’)
INSERT INTO .[Age]
([Name]
,[Age]
,[Profession])
VALUES
(‘n’,9,’j'),(‘t’,39,’yj’)
Note that this will work from version 2008 onwards
can you tell me how to insert Multiple Records in multiple table Using One Insert Statement –
INSERT ALL
INTO suppliers (supplier_id, supplier_name) VALUES (1000, ‘IBM’)
INTO suppliers (supplier_id, supplier_name) VALUES (2000, ‘Microsoft’)
INTO customers (customer_id, customer_name, city) VALUES (999999, ‘Anderson Construction’, ‘New York’)
Here ‘ALL’ keyword is not supported, but why?
What did you mean by ALL in this content? Read the blog and use as stated
hi dave i have a problem.i have 1 textbox control,1 label,1 button.in my database i have create a table and thge column name is date datatype is datetime.so how to convert datetime format to dd/mm/yyyy in my database and how to retrieve the table values into a label when i click the button in vb.net.
You dont need to worry about the formation. Make sure to input dates in YYYYMMDD HH:MM:SS format. Refer this for more informations
http://beyondrelational.com/blogs/madhivanan/archive/2010/06/03/understanding-datetime-column-part-ii.aspx
Hi,
I need to insert multiple rows in to a table with one column is same, which i ll get through sub query(select).
Thanks in advance for your help.
Thanks
Rukmangada
insert into table(column) select column from table
I have a table control UI in my form. I want to post the cell data to the sql server DB.
I have done insert data from text box control, to a valid table field in the database. Note: the asp table control, n the table of DB use the word “table”. Table is populated with data from one table, and some of the cells’ data has to be written to another table in the same DB. Would some one help me with taking my cell data to the server? Thanx.
What if i have an error row? The whole dataset gets rejected. How can i find the error row when inserting/updating data in batch?
It will not execute and no rows will be added to the table. If you double click the error message it will point it to the line where problamatic data are there
Hi,
I have a tabel ItemTransactions (of invoice module).
If I insert 5 rows for the first time (during temporary Save), I have no problem.
Now the situation is like this.
If I open the invoice again and do any of the following
- add more rows
- delete some rows
- change the value in some rows
Currently what I do is:- delete all currrent rows from the table then insert all rows again
Is there any other better option?
Please advise
Delete all rows and insert new rows again. Thats the good method
I have a code:
insert into absence (absence_id, people_id, startdate, starttime, enddate, endtime, absencetype, calcduration)
(
select newid(), p.people_id, t.[start date], t.[start time], t.[end date], t.[end time], ‘SICK’, ‘T’
from tongasickness t
inner join people p on p.fullname = t.[Employee Name]
)
that gives the error:
Msg 512, Level 16, State 1, Procedure OC_AUDITTRIGGER_ABSENCE_INSERT, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
The statement has been terminated.
Why am I getting this error?
Try this
insert into absence (absence_id, people_id, startdate, starttime, enddate, endtime, absencetype, calcduration)
select newid(), p.people_id, t.[start date], t.[start time], t.[end date], t.[end time], ‘SICK’, ‘T’
from tongasickness t
inner join people p on p.fullname = t.[Employee Name]
How to insert 25000 record in inserted at one time.
i have ready query to insert in sql server 2008.
but give Error
Msg 10738, Level 15, State 1, Line 24999
The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.
Note that it is restricted to 1000 rows. If you want to insert more than 1000 rows, have data in text file and use bcp or bulk insert
Hello Folks,
Great Resource – Thank you all. I need your help with the following.
Have two tables:
1. materialleaf, with columns materialsid (numeric (10,), PK) and description (varchar(100))
2. materialkeyword, with columns materialsid (numeric (10,0), PK, FK to table 1) and keyword (varchar(50), PK) [you guessed it, materialsid in table 2 is FK to its corrsponding column in table 1)
Problem/Requirement:
I need to parse the description field in table 1 based on a ‘ ‘ (blank space) or a ‘,’ delimiter and enter the various bits as keywords in separate rows in table 2 ALONG with the materialsid from table 1.
Example:
Table 1: MaterialSID = 1000, Description = “5 Inch Philips Wrench”
Material SID = 2000, Description = “Firex Fire Extinguisher”
Table 2 should look like: 1000 5
1000 Inch
1000 Philips
1000 Wrench
2000 Firex
2000 Fire
2000 Extinguisher
Can someone please help with a script/function/SP for SQL Server 2005?
Thanks much!
Dear All,
How to Update Multiple Records Using One Update Statement.
Regards,
Jayaram.
What is wrong with update statement? Did you use it?
Thanks for this post! Not much of a difference using this method (only 1 second faster but regardless, still faster) though this makes the code much cleaner in my eyes!
Cheers!
I have to admit I am disapointed by the UNION performance I saw a couple of place telling that it’s faster. In my case it is MUCH SLOWER. I have tried using it for 50k records and the query just times out.
I can do it in 2 min which i found slow but with the UNION 5 minute is not enough.
I have tested a few methods and indeed UNION is faster but you have to limit the number of UNION to optimise it. Instead of executing 1 big SQL statement with 50K UNIONS, I do 1000 SQL querys with 50 unions in each query. I have been able to make it 4 time faster than making each of the 50k inserts individually.
50K unions is way too slow after the 50 minutes the query was still running… From my testing in my environement 50 union is my optimal .
Thats informative. Thanks
I notice the original posting is all about insert-by-union-select, but as several commenters have asked about how to improve insert performance in general, here are some more recommendations:
- Increase ADO.NET BatchSize
- Choose the target table’s clustered index wisely, so that inserts won’t lead to clustered index node splits (e.g. autoinc column)
- Insert into a temporary heap table first, then issue one big “insert-by-select” statement to push all that staging table data into the actual target table
- Apply SqlBulkCopy
- Choose “Bulk Logged” recovery model instad of “Full” recovery model
- Place a table lock before inserting (if your business scenario allows for it)
Taken from http://arnosoftwaredev.blogspot.com/2011/10/tips-for-lightning-fast-insert.html
THANK YOU SO MUCH. IT REALLY HELPED ME A LOT
Dear Pinal,
I am very thanks full to you, Because you have made it very easy for me to learn SQl. I have developed confidence in me just because of your tutorial.
Regard:
Ashish Jain
Thanks for the solution! Really helpfull.
Usual set of insert queries was running for about 5 seconds in my case.
Now it is less than 1 second.
I have a question about using UNION to create tables. In the case:
SELECT * INTO TableName
FROM (
SELECT x
FROM Table1
WHERE x = ’1′
UNION
SELECT y
FROM Table2
WHERE y = ’2′
) as X
ORDER BY Z
In the above case, when does the ORDER BY get applied? Is it applied to each statement, and the result appended together?, or, is the result set created in a memory table, then, the table created ordered by Z from the UNION’ed result?
First result is created and last it is ordered. But there is no gurantee that the order by work correctly. You should always use order by clause when using select statement
thanks dude… nice work
INSERT INTO books(thec_code, room_number, status)
SELECT ‘C01′,’R115′,’Confirmed’
UNION ALL
SELECT ‘C02′,’R210′,’Reserved’
UNION ALL
SELECT ‘C04′,’R375′,’Cancelled’
UNION ALL
SELECT ‘C04′,’R455′,’Reserved’
GO
It’s not working…
What’s wrong??
:(
ahhh… i got it!!!
it should be GO;
and not simply GO
:)
this is actually a MUCH faster method:
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1),
(‘Second’,2),
(‘Third’,3),
(‘Fourth’,4),
(‘Fifth’,5);
GO
This is SQL2008 only
To the last poster, here’s the MSDN link: http://msdn.microsoft.com/en-us/library/dd776382.aspx
INSERT INTO dbo.State(StateName)
VALUES (‘Alaska’), (‘Arkansas’), (‘Alabama’);
Hi Pinal,
I got a task to do.I need to insert 400,000 rows into Adventure Works DB table inorder to see how Proactive caching works in different scenarios.
My question is how to insert 400,000 rows at a time in a table.
Can you let me know the script to generate it.
Please do help me out.
Thanks in Advance.
Hi Pinal,
I have tried to use you way of inserting multiple records.
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
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint ‘PK_tableName’. Cannot insert duplicate key in object ‘tableName’.
The statement has been terminated.
I usually use excel formula to create insert into statement. It helps me to upload more than a millions of records in a minute. Those who do insertion in the database quite frequently, they can use my technique that use excel formula.
Zahid
Sorry, I should work, I have check my error msg and it is a primary key violation.
Dear Reader,
you can use either Pinal’s process ro my process as you like.
I don’t have any records to insert.I need to insert new rows.How do I enter millions of rows into a table at a time.
Please let me know the Script?
Can we use While loop or something like that.
Please help me out.
Samyuktha
Where is the source data coming from? If it is a table, just use this
insert into target_table(cols)
select cols from source_table where
how write table of 2 with cursor in MS SQL ?
Can you give more information on what you want to do?
I’m having 2 tables namely customer,product.The customer table consist 2 field namely custid and custname and product consist 3 fields namely prodid,prodname,date. i need to insert data from both table with single insert command and here when i select custname this particular id will be inserted into product id. Pls help…..
Select AcType, MainCode, Balance from Master where AcType between ’01′ and ’07′ and Balance>100000
Union
Select AcType, “~”, SUM(Balance) from Master where AcType between ’01′ and ’07′ and Balance>100000
Group by AcType
Order by AcType
Result will be
Head Office
Wednesday, December 7, 2011
07/12/2011 3:51:32 PM User : RAJEEV Station : GFL Page : 1
—————————————————-
AcType MainCode Balance
—————————————————-
01 00101086GP 193,104.78
01 ~ 193,104.78
04 00100427Y5 276,045.42
04 00100620Y1 255,202.44
04 00100751IA 128,244.86
04 00101157IA 462,052.60
04 ~ 1,121,545.32
05 00100865GP 190,150.05
05 00101298GP 166,621.25
05 ~ 356,771.30
07 001000000107 167,489.78
07 001000000307 155,108.62
07 001000000407 431,436.82
07 001000000507 316,170.47
07 001000000907 239,352.41
07 001000001107 154,044.20
07 001000001307 267,387.29
07 001000001407 990,851.57
07 001000001607 263,150.63
07 ~ 2,984,991.79
==================== (End of Report)
—- > Now I want to do as follows
Head Office
Wednesday, December 7, 2011
07/12/2011 3:51:32 PM User : RAJEEV Station : GFL Page : 1
—————————————————-
AcType MainCode Balance
—————————————————-
01 00101086GP 193,104.78
01 ~ 193,104.78
04 00100427Y5 276,045.42
04 00100620Y1 255,202.44
04 00100751IA 128,244.86
04 00101157IA 462,052.60
04 ~ 1,121,545.32
05 00100865GP 190,150.05
05 00101298GP 166,621.25
05 ~ 356,771.30
07 001000000107 167,489.78
07 001000000307 155,108.62
07 001000000407 431,436.82
07 001000000507 316,170.47
07 001000000907 239,352.41
07 001000001107 154,044.20
07 001000001307 267,387.29
07 001000001407 990,851.57
07 001000001607 263,150.63
07 ~ 2,984,991.79
Grand Total 4,656,413.19 <—- "GRAND TOTAL"
==================== (End of Report)
Is This Works for Delete?
No, Union all cannot be used to delete rows from a table
FYI – I have done extensive research regarding this original post, and according to my research the fastest way to insert bulk data via SQL is to use a Table Variable as follows…
DECLARE @TblVar table (FirstCol nvarchar(50), SecondCol int)
INSERT INTO @TblVar
VALUES (‘First’,1);
INSERT INTO @TblVar
VALUES (‘Second’,2);
INSERT INTO MyTable (FirstCol, SecondCol)
SELECT * from @TblVar
Sure it is not a single statement. But… it was about 4 times faster than the UNION ALL method.
For large numbers of records I got better performance by grouping 1000 records at a time (ie repeating the SQL above for every 1000 records). I ran each group of 1000 records as a separate script.
I welcome any feedback…
Pinal,
Hmmm I just noticed the update you wrote at the top.
(ie 2008 method of Row Construction).
I wonder how my method compares in performance.
I might give it some testing.
Wow, in my tests the 2008 method is actually about 2 x slower than the Table Variable method. AND it is limited to 1000 rows.
This page really great…
Please give me a script that once i entered four digit numbers i can insert it to the database as many as i can instead of inserting it one by one.. i have here the script but i dont know how to insert it even 100 times in one click.
$sqllucky = “INSERT INTO lucky (userid,lucky1,lucky2,lucky3,lucky4,ldate) VALUES (‘$userid’,'$lucky1′,’$lucky2′,’$lucky3′,’$lucky4′,now())”;
$luckyresult = connect($sqllucky);
that snippet can inserted one by one what if i want to insert 10 times…please help me…
insert into #MangeshTemp
values
( ‘Test1′, ‘Test2′ ),
( ‘Test3′, ‘Test4′ );
.
.
.
so on and so forth…
you can use a advanced text editor with a macro to produce your insert statement.
forgot to specify columns, but you get the idea…
SQL 2008
Insert into DCGRPMBR (CAMPAIGNYEAR, GROUPCONTRACTACCOUNT, GROUPCONTRACTSORTFIELD, MEMBERACCOUNT, MEMBERACCOUNTTYPE,
MEMBERSORTFIELD, NOTESET, LASTCHGDATE, LASTCHGTIME, LASTCHGUSER, CREATEDATE, CREATETIME, CREATEUSER)
Values (2010, 2527927, ‘GOLUB/PRICE CHO’,
(Select DWACCOUNTS.DWORGACCOUNT
from DWACCOUNTS where DWACCOUNTS.DWNAME in (‘SCA_PCGC’)),
‘O’, PCS’, 0, 20120216, 105610, ‘SALBERTIN’, 20120216, 105611, ‘SALBERTIN’)
I have a table (SCA_PCGC) that contains 126 rows with one column, an ID. I would like that data from each row to be inserted into an existing table (DCGRPMBR) along with values that I manually enter in the code. The code above works fine when there is only one record in the SCA_PCGC table, however it fails with the error:
“Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
The statement has been terminated.”
Whenever I have more than one record in the SCA_PCGC table, I get the above error.
Ideally I would like the code to run through each of the 126 records in the SCA_PCGC table and insert them into the DCGRPMBR table with each of the values. So I would end up with 126 new records in DCGRPMBR:
One Row with the following information for each of the 126 records
2010, 2527927, ‘Golub/Price Cho’, ID FROM SCA_PCGP, ‘O’, ‘PCS’, 0, 20120216, 105610, ‘SALBERTIN’, 20120216, 105611, ‘SALBERTIN
I am new to this and have tried different variations of what I have found for multiple row inserts. Any help is greatly appreciated.
if you examine the inserted tables after two operations
first one results multiple times 1 row(s) effected rows
but second one says one time but multiple row(s) effected
select sum(salary) from EMPloyee limit 10 to 50
Hi all ,
I want to use this same technique in MySQL and MsAccess databases.
Anyone knows how to implement in that two databases as SQL Server.
Thanks,
Prabhakaran.K
hi all
I have created a table using phpmymin with 3 fields(id, name contactno) . Now my data is in csv file. Please tell me a command to insert the data presented in the excel sheet into the table using phpmyadmin
how to insert data into multiple table through the use of one insert query.
why is unionall faster than multiple insert …is it true for all conditions or in some particular conditions
how in one insert commend ican insert multiples rows ?
hi sir,
this blig is good and helpful for me….
but how can i insert N no’s of records when we are not Knowing exactly records
plz give me this kind of query solution to improve my query….
Hello Sir,
This blog is very good and very help full to me to become a bda
Thks
thank you!
Hi Sir,
I am struggle to make this work, I receive ‘Subquery returned more than 1 value.’ error, everytime when I try to run this:
INSERT INTO TASKS_DATA_HEADER (ASSET_ID, CLIENT_ID, CREATED, TASK_DEF_ID, MODIFIED, TASK_ID, USER_ID, PROJ_ID, LAST_MODIFIED, LATITUDE, LONGITUDE)
SELECT ASSET_ID, CLIENT_ID, CREATED, TASK_DEF_ID, MODIFIED, TASK_ID, USER_ID, PROJ_ID, LAST_MODIFIED, LATITUDE, LONGITUDE
FROM UNDO_TASKS_DATA_HEADER_DELETE
WHERE CHANGE_ID = ’19802a81-a42a-4b2e-9c68-ef22a36ef610′
The frustrating part is the the column matches and INSERT suppose to work for multiple rows, and the query without INSERT INTO work just fine, which return 11 rows, any ideas?
Much Thanks in advance!
sir tell me one thing..can i set limit of no of rows in table in sql server 2005 ?..e.g i want insert only 5 rows in table not more than 5.
If the maximum rows is5, then write a insert trigger,see if the count(*)>5, then rollback
thnk u very much
Hi
I am having an issue in running prepared statement.
There are three tables Tab1 contains 2 columns (ID,name) ID is a primary key & Foreign key
Tab2 has (Name,ID) here also ID is a primary key.& Foreign key Tab3 has (ID1,ID2) both have not null contraint and refer to forgein key ID s from tab1 and tab2
The data for tab1 and tab 2 are are inserted now for inserting the data for tab3
i get data as (integer,string)
When i create the SQL statement i create it like this
Insert Into tab3 (ID ,ID ) where values((select ID from tab1 where ID=?),(Select ID from tab2 where name=?);
when i set the prepared statement i set it as
pst.setInt(1,ID);/// over here id is an integer
pst.setString(2,Name);///here name is a string
when i run the prepared statement it tells me that no value found for ID2 ??
When i run the individual SELECT statements from the insert statements in command prompt of sql then it displays the values and if i give the values in the above insert statement also then the value get inserted but same this the prepared statement does not do…
What can be done in this situation????
thanks a lot…
sir ,
how will the ” insert using UNION ALL ” affects in ” after insert Trigger ” ???
[...] SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL [...]
INSERT ALL
INTO mytable (column1, column2, column3) VALUES (‘val1.1′, ‘val1.2′, ‘val1.3′)
INTO mytable (column1, column2, column3) VALUES (‘val2.1′, ‘val2.2′, ‘val2.3′)
INTO mytable (column1, column2, column3) VALUES (‘val3.1′, ‘val3.2′, ‘val3.3′)
SELECT * FROM dual;
INSERT ALL
INTO mytable (column1, column2, column3) VALUES (‘val1.1′, ‘val1.2′, ‘val1.3′)
INTO mytable (column1, column2, column3) VALUES (‘val2.1′, ‘val2.2′, ‘val2.3′)
INTO mytable (column1, column2, column3) VALUES (‘val3.1′, ‘val3.2′, ‘val3.3′)
SELECT * FROM dual;
This is not valid in SQL Server. Is this for ORACLE?
This is only possible in Oracle
hello sir,
pls tell me how to insert the values at end of the record? can we add values at end of the record and how does it affect
TRIGGER CREATING
create trigger trigger_name on table_name for insert,update
as
update table_name set column_name=column_name+500
Hi,
how to insert 100 values into a table(in which there are 7 fields in a table entry format) in a single querry.Am a new user.kindly help me on this
thanks in advancce!!
The solution you have given is excellent
“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”
I have two questions after going through this .
A) Is there any limit to number of rows for insertion ?
B) If I insert 2000-3000 rows with this method then would it be a feasible solution or should go for stored procedure insted ?
Use the following
USE YourDB
GO
INSERT INTO MyTable (FirstCol, SecondCol)
VALUES (‘First’,1), (‘Second’,2), (‘Third’,3), (‘Fourth’,4), (‘Fifth’,5)
GO
[...] Insert Multiple Records Using One Insert Statement – Use of UNION ALL [...]