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?

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.

    Like

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

      Like

  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

    Like

    • 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

      Like

      • Yes sir there is no need to add UNION ALL in the last,the thing we should not use brackets in the select clause

        Like

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

      Like

  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.

    Like

  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

    Like

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

    RandhirSingh
    Data Base Developer
    Haryana(India)

    Like

  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?

    Like

  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)

    Like

  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.

    Like

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

    Like

  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

    Like

  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)

    Like

    • 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

      Like

      • 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

        Like

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

    Like

  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

    Like

  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

    Like

  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.

    Like

  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

    Like

  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.

    Like

  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!

    Like

  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

    Like

  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

    Like

    • 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!:(

      Like

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

        Like

  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.

    Like

  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.

    Like

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

      Like

  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?

    Like

  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

    Like

  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

    Like

  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

    Like

  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

    Like

  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

    Like

  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

    Like

  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

    Like

  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.

    Like

  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

    Like

  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

    Like

  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.

    Like

  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!

    Like

  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

    Like

  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

    Like

  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

    Like

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

    Like

  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

    Like

  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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

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

    Cheers.
    Simmo

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  113. query 1-
    update tablename set clumName = ‘var1′ where id = ‘001’

    query 2-
    update tablename set clumName = ‘var2′ where id = ‘001’

    i want to update record depending upon variable value if exits then update column name otherwise update var2 value…

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      .
      .
      .

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

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

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

    Like

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

    Like

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

    Special thanks goes to Pinal Dave.

    cheers.

    Like

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

    Like

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

    Like

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

    Like

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

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  226. How about…

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

  250. @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;

    Like

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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

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

    Like

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

    Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

    Like

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

      Like

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

    Like

    • 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

      Like

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

    Like

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

    Like

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

    Like