SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

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?

About these ads

869 thoughts on “SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

  1. 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…. :)

  2. 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

    • 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

    • 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)

  3. Hello pinaldave,
    Myself Gurmeet singh working as Soft. Engg in Mohali.
    I have read ur blog its really helpful.
    Wish u very good luck.

  4. 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

  5. Hi,
    Thanx, it was very use full for me.
    You have solved my big problem.
    Thnx again.

    RandhirSingh
    Data Base Developer
    Haryana(India)

  6. 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?

  7. 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)

  8. 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.

  9. 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).

  10. 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

  11. 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

  12. 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 :)

  13. 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

  14. 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

  15. 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.

  16. 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

  17. 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.

  18. 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!

  19. 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

  20. 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.

  21. INSERT SETTINGS (CATEGORY, NAME, OWNER,VALUE, SYSTEM)
    SELECT CATEGORY, NAME, ‘YYYYY’, VALUE, SYSTEM
    FROM SETTINGS
    WHERE OWNER = ‘XXXXX’

    This will work

    Regards

    Bijoy C.

  22. 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.

  23. 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?

  24. Pingback: SQL SERVER - Insert Data From One Table to Another Table - INSERT INTO SELECT - SELECT INTO TABLE Journey to SQL Authority with Pinal Dave

  25. >> 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

  26. 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

  27. 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

  28. 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

  29. 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

  30. 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

  31. 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

  32. 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.

  33. 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

  34. 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

  35. 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.

  36. 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!

  37. 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

  38. 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

  39. 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

  40. 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 ?????

  41. 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

  42. 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

  43. Hi Shreyas,

    First check the indexes status whether the indexes are fragmented or not and what is the scan density?

    rgds

  44. 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…. :(

  45. 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

  46. 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

  47. 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.

  48. 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??………..

  49. 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

  50. 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

  51. The method using …. UNION ALL … brought me a slowdown of factor five. Seems it highly depends on the amount of data.

  52. 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

  53. 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

  54. 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?

  55. 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.

  56. 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

  57. 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

  58. 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)

  59. 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

  60. 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

    • Since it is now a single INSERT statement, it is atomic (it will all succeed or fail). You could check the potential INSERTs ahead of this, but that may be as much trouble as performing each INSERT individually.

  61. 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′

  62. 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

  63. 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

  64. 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

  65. Pingback: SQL SERVER - Execute Same Query and Statement Multiple Times Using Command GO Journey to SQL Authority with Pinal Dave

  66. Hello pinaldave,
    Myself Faisal Qureshi working as Soft. Developer in Mumbai.
    I have read ur blog its really helpful.
    Wish u very good luck.

  67. 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.

  68. 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

  69. 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

  70. 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

  71. 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.

  72. 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

  73. 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

  74. 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

  75. Hi Pinal Dave,
    I am very glad to see your talent hear,
    Its really good and intresting for all software engineer.
    Thanks again dear.

  76. 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.

  77. 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

  78. 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.

  79. 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.

  80. 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

  81. 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.

  82. 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

  83. 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.

  84. 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

  85. 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)

  86. 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

  87. Can I perform the “Insert multiple records using one insert statement” task using a stored procedure ? If so, how do achieve this ?

  88. 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.

  89. 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

  90. 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

  91. 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.

  92. 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.

  93. 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

  94. 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

  95. 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

  96. rani, I believe your error is occuring because of your last semi-coln ‘;’

    no ‘;’ is required after the ‘go’ statement

    Cheers.
    Simmo

  97. 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

  98. 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

  99. 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 .

  100. 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

  101. 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

  102. 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

  103. 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

  104. 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

  105. 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!

  106. 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;

  107. 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.

  108. 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
    ;

  109. 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.

  110. 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

  111. 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

  112. 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

  113. 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.

  114. 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)

  115. 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

  116. 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…

  117. 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…

  118. 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

  119. 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 & “‘)”;

  120. 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

  121. 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

  122. 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

  123. 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.

  124. 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.

  125. 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.

  126. 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

  127. 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!!

  128. Pingback: SQL SERVER - 2008 - Insert Multiple Records Using One Insert Statement - Use of Row Constructor Journey to SQL Authority with Pinal Dave

  129. 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

  130. 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.

  131. 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!

  132. 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.,

  133. 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

  134. why are we using UNION when INSERT INTO is doing the same task.Is there any signigicant difference as regards query optimization?

  135. 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.

  136. @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.

  137. 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

      .
      .
      .

  138. 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?

  139. @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.

  140. 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

  141. 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

  142. 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.

  143. @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.

  144. 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.

  145. 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

  146. 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

  147. 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.

  148. 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

  149. 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.

  150. 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

  151. 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
    ————–

  152. 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.

  153. 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

  154. 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'

  155. 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?

  156. 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

  157. 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 .

  158. 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

  159. 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…

  160. 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

  161. 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.

  162. 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.

  163. 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

  164. 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

  165. 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’

  166. 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?

  167. 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

  168. 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

  169. 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

  170. 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

  171. 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.

  172. 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

  173. 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

  174. 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

  175. 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.

  176. 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..

  177. 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

  178. 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

  179. @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

  180. 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

  181. 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

  182. 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

  183. 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

  184. 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.

  185. 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.

  186. 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

  187. 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.

  188. 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.

  189. 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′.

  190. 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

  191. The same procedure gives error :

    Must declare the scalar variable “@SalesOrderID”.

    I have declared this variable……
    pls help
    thanks

  192. 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.

  193. 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!

  194. 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

  195. 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.

  196. 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’)

  197. @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.

  198. 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.

  199. 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..

  200. 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

  201. 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

  202. 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.

  203. Pingback: SQL SERVER - Three Rules to Use UNION Journey to SQL Authority with Pinal Dave

  204. Pingback: SQL SERVER - Top Five Articles of Year 2008 Journey to SQL Authority with Pinal Dave

  205. Better way:
    INSERT INTO MyTable (FirstCol, SecondCol)
    VALUES (‘First’,1), (‘Second’,2), (‘Third’,3), (‘Fourth’,4), (‘Fifth’,5);

  206. 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

  207. Thanq for the above solution which was very useful to me.(About insertion of multiple values at single instance through query).

  208. Hi All,
    I am happy to get the code for inserting multiple records using one INSERT statement.

    Special thanks goes to Pinal Dave.

    cheers.

  209. 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.

  210. 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???

  211. 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

  212. 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……….

  213. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  214. 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. :)

  215. 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

  216. 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

  217. 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

  218. 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

  219. 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..

  220. 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

  221. 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.

  222. 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

  223. @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)

  224. 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!!

  225. 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!

  226. @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.

  227. 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

  228. 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!

  229. 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

  230. 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!

  231. 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.

  232. How about…

    INSERT INTO ”table_dance”
    (”id”, “name”, “brasize”)
    VALUES
    (”0”, “striperella”, “36dd”),
    (”1”, “demi moore”, “32d”), …

  233. 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.

  234. 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!!!

  235. 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

  236. 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???

  237. 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

  238. 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.

  239. 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!

  240. 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.

  241. 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

  242. 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

  243. 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

  244. 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

  245. 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.

  246. 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.

  247. @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.

  248. 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);

  249. 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

  250. 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!

  251. 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

  252. 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

  253. 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.

  254. 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!

  255. 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'

  256. 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’)

  257. 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

  258. @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′)

  259. 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’)

  260. 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

  261. 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

  262. @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.

  263. 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!

  264. 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

  265. 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

  266. 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………

  267. 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

    • @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.

  268. 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

  269. @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

  270. 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.

  271. 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.

  272. 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.

  273. 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.

  274. 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..

  275. 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.

  276. Pingback: SQLAuthority News – 1200th Post – An Important Milestone Journey to SQL Authority with Pinal Dave

  277. 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.

  278. 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

  279. 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

  280. 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

  281. 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.

  282. 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 );

  283. 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

  284. 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

  285. 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

  286. 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.

  287. 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

    • 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

  288. 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

  289. 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

  290. 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.

  291. 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

  292. 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

  293. Select Colname,count(ColName) From TableName
    Group by ColName Having Count(ColName)>1

    It will show all the duplicate records with number of occurrences.

  294. 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.

  295. 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.

  296. 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

  297. 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..

  298. 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

  299. 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

  300. 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

    • 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

  301. 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

  302. @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

  303. 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

  304. 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.

  305. 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

  306. 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

  307. 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

  308. 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

  309. 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

  310. 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

  311. 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

  312. 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

  313. 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

  314. 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

  315. 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.

  316. 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.

  317. 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

  318. 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’

  319. 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.

  320. 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’) ;

  321. 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

  322. 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.

  323. 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

  324. 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

  325. 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

  326. 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

  327. 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]

  328. 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?

  329. 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?

  330. 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.

  331. 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

  332. 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.

  333. 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

  334. 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

  335. 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

  336. 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

  337. 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.]

  338. 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…

  339. 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

  340. 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

  341. 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?

  342. 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”

        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.

  343. 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?

  344. 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

  345. 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.

  346. 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..?

  347. 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?

  348. @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.

  349. 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

  350. 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….

  351. 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’

  352. 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)

  353. 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?

  354. 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!

  355. 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

    • 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

  356. 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.

  357. ohhhh (smile)
    i mean
    select convert(varchar ,MONTH(GETDATE()),111) +’-‘ + convert(varchar ,year(getdate()),111)
    anyway thanx madhivanan i got my soln

  358. 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

  359. 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