SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

Following three questions are many time asked on this blog.

How to insert data from one table to another table efficiently?
How to insert data from one table using where condition to anther table?
How can I stop using cursor to move data from one table to another table?

There are two different ways to implement inserting data from one table to another table. I strongly suggest to use either of the method over cursor. Performance of following two methods is far superior over cursor. I prefer to use Method 1 always as I works in all the case.

This method is used when table is already created in the database earlier and data is to be inserted into this table from another table. If columns listed in insert clause and select clause are same, they are are not required to list them. I always list them for readability and scalability purpose.
USE AdventureWorks
----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database

Method 2 : SELECT INTO
This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
USE AdventureWorks
----Create new table and insert into table using SELECT INSERT
SELECT FirstName, LastName
INTO TestTable
FROM Person.Contact
WHERE EmailPromotion = 2
----Verify that Data in TestTable
SELECT FirstName, LastName
FROM TestTable
----Clean Up Database

Both of the above method works with database temporary tables (global, local). If you want to insert multiple rows using only one insert statement refer article SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL Server Downloads

About these ads

661 thoughts on “SQL SERVER – Insert Data From One Table to Another Table – INSERT INTO SELECT – SELECT INTO TABLE

  1. Can you tell me how to use the INSERT INTO IN clause to transfer data from a table in MS SQLServer DB into a table in a MS Access DB?

  2. thanks for your posting its really helpful do u know how to transfer a db table (with data) from one database to another in different server?

  3. Hi Dave,

    I’m trying to move data between tables of 2 different dB’s on same machine (SQL Server2005).

    Exactly trying to move Col1, col2 of Table1 of db1 to Col3 and Col4 of Table2 of db2.

    Is it possible only by writing SQL scripts, if so can suggests me some links regarding this?


        create trigger railways_result_trigger on railways_result
        after insert
        if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, getdate (), 103)))
        delete from current_railways_result
        delete from railways_result
        delete from current_railways_result
        insert into current_railways_result select *,dense_rank()OVER (PARTITION BY stu_batch_time,exam_date ORDER BY stu_total_marks DESC)stu_rank
        FROM railways_result
        insert into railways_result_backup select *,dense_rank()OVER (PARTITION BY stu_batch_time,exam_date ORDER BY stu_total_marks DESC)stu_rank
        FROM railways_result
        ;with DelDup as (select row_number() over (partition by
        stu_name, stu_roll,exam_date,stu_rank,stu_batch_time order by stu_name,stu_roll,exam_date,stu_batch_time) as RowNo from railways_result_backup)
        Delete from DelDup where RowNo> 1

        first time data not inserted into first table plz help me

        • This may be the culprit if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, getdate (), 103)))

          Dont use convert function

          • my requirement is delete both table data before insert plz help me i need in project sir.

            i m creating trigger

      • wat would be the query if i want to transfer data from one to another n also add some independent values in my other columns…

        i.e if i have two tables A and B , say A wid 4 columns and B with 6 columns, transferrin values from 4 columns in A to B… and i want to insert some values into other 3 columns in B…

        • where is the problem adding the independent values to the select-part?

          INSERT INTO database1.dbo.table (column1, column2, column3, column4)
          SELECT column1, column2, 1, ‘abc’ FROM database2.dbo.table

  4. Hi dave,

    thanks for posting such wonderful posts.

    I’m trying to do a recursive loop searching for a particular parent in a tree, and retrieve all children under it.

    Problem: While Exists(Select deptid from global_Master_Directory Where parentid IN (@ParentID) ) not being looped.

    Please kindly guide me on this. thanks again.

    Stored Procedure:

    CREATE PROCEDURE procRetrieveDivUsers
    ( @divisionId varchar(10))
    — Create a variable table. A temporary table starts with #
    declare @TableVar table
    tempPK int identity(1,1),
    costcenter varchar(6) NOT NULL ,
    deptid varchar(6)
    — declare and Initalize the variables

    Declare @ParentID VARCHAR(1024), @deptID varchar(1024), @costcenter varchar(1024)

    SET @ParentID = @divisionId

    –Loop through – problem occurs here.. no looping occured. I tried printing out @parentID, it does return –value but when not doing looping. the while loop does work if i do a hard code value replacing @parentID
    While Exists(Select deptid from global_Master_Directory Where parentid IN (@ParentID) )

    INSERT INTO @TableVar (costcenter, deptid)
    ( SELECT costcenter,deptid FROM global_Master_Directory Where ParentID IN (@ParentID) )

    SELECT @deptID =COALESCE(@deptID + ‘,’, ”) + deptid FROM global_Master_Directory Where ParentID IN (@ParentID)

    SELECT @ParentID = @deptID –SELECT


    SELECT distinct COALESCE(@costcenter + ‘,’, ”) + costcenter FROM @TableVar


    • Sir i have problem in mysql ………. I have 2 tables socialnet (firstname, lastname) and other is socialreg (age, status) ………. now i want to copy whole Firstname coulmn from socialnet to socialreg………………………PLZ tell me the query

    • How To Transfer The Data from One Table To Another Table…………

      Insert Into TableName1(columnname1,columnname2…)
      Select columnname1,columnname2… from TableName2

  5. hi Dave,
    How to retrieve data from a table by row number of that table ?
    (addittional information: suppose there is a table, and you don’t know any information except the attribute of that particular table, you get a request that you have to retrieve data of 31st no. row.)

  6. Dave,
    What is the easiest way to insert data from one table where the data in the first table doesn’t exist in the second?

    Table1 Table2
    a a
    b b

    Example: In the above table I want to insert ONLY the row containing the value c into table2.

    Thanks in advance for your help!

  7. Paul (9): I did it like this:

    FROM P350..T_AH_MBR

    ‘MRA’ is the destination DB, and I only want to insert new records that entered the P350 DB as a result of the previous day’s INSERTS. MBR_ID is the primary key in both. MBR_ACTV_IND is a WHERE clause that’s only pertinent to my code – yours will assuredly be different.

  8. hello

    i want to insert only the time into my table. or else how can i differentiate date and time. here i have field logout_time, its data type is date time. here i have to check if the column is empty i have to raise a exception. because while inserting first into the table the logout_time field inserting as empty string, later it will be updated. but its taking the default system date. i don’t want that i want the entire column should be empty. please do help me

  9. Hi man

    I need help, I’ve been working on this for a weeks now. I have created a Job in SQL Server Agent. The job is is executing fine but the my SQL Statement in the Command block is not correct.

    I’m using SQL Server 2005, I need to import an Access Database to SQL server automatically every morning, and Delete the old data before Inserting new data.

    Can you please help me with the SQL statement to do this job.


  10. Hi, i have code of following:

    drop database if exists company;
    drop table if exists location;
    drop table if exists country;
    drop table if exists region;

    create database if not exists company;
    use company;

    create the table named region
    create table if not exists region
    region_id varchar(20) not null ,

    region_name varchar(32) not null,

    constraint region_PK primary key (region_id)

    ) ;

    create the table named country

    create table if not exists country

    country_id int(20) not null,
    country_name varchar(32) not null,

    region_id varchar(20) not null ,

    constraint country_id_PK primary key (country_id),

    constraint region_id_fk foreign key (region_id)
    references region(region_id)
    ) ;

    Now i want to use command insert into to add values into tables. But I dont know how to do to add values foreign key region_id in country table which can link wich data from table region. Anyone can help me? Thanks you so much.

    note: If you can show me your work clearly. Thanks

  11. on November 19, 2007 at 2:33 pm16 surender
    i want to import the data into a table from other table which is in different Database please help me out

    SELECT * INTO rejeesh.dbo.authors FROM pubs.dbo.authors

  12. Hi ,
    You can use the Data Transformation services(DTS) to import the data from one table to another table!
    use import/export wizard and create a package to do so.

  13. hi deven,

    i have same issue as u have disscussed here.

    but i already have the SP for the selection of data.

    now i have to insert that same data in to table which i m getting through tht SP.

    so can i pass this sp to the insert query as you have passed select query to the insert query.

    i m getting cofused in it.

    Please reply me.
    its urgent.

    Waiting for your reply.
    Thanking You.


  14. if (user != null)
    if (user.IsApproved == false || user.IsLockedOut == true)
    SecurityEvent evt = new SecurityEvent(“The user (” + Login1.UserName + “) is locked out, but has tried to authenticate.”,
    Login1.FailureText = “Your account is locked. Please contact administrator.”;

  15. hi dave ,

    I want to insert data in a table located in one database ,
    by taking the data from another database —– In DB2.
    both database are on different machine.

    Please respond ASAP.

  16. Hi Dave,

    I have two differne tables and Table A has around 1000 records.
    Table B has around 1500 records

    Both tables have a common field (Customer_code).
    And few Customer_Codes which are there in the Table A are not there in the Table B and few Custotomer Codes which are there in the Table B are not there in the Table A.
    Both the Table have another Column called Quantity.

    Now i want to bring Customer_code from any one of the table, Quantity of the Table A and Quantity of table B to a temporary table.

    Can u help me with the query where the sum of quantity should not change even after bringing to the temp table.

  17. Hi,
    I want to insert using SELECT statement but select statement will be only for one column for rest of the coulmns fixed data will be innserted.


    Insert into table EmployeeInfo(Id,firstname,lastname, Salary) values(1,’aaa’,xyz’, Select salary from Payroll_Info where EmpId = 1)

    It is giving me error like
    Incorrect syntax near the keyword ‘select’.

    Can you please help me in this regard.


  18. Hi Suresh(23),

    change table names it should work for you.

    select t1,sum(qty)
    (select t1,sum(qty) qty from t1
    group by t1
    union all
    select t1,sum(qty) qty from t2
    group by t1
    ) A
    group by t1


  19. Hi suresh,

    more details if you are expecting below result:

    select t1,sum(t1_qty1) t1_qty,sum(t2_qty2) t2_qty
    (select t1,sum(qty) t1_qty1,0 as t2_qty2 from t1
    group by t1
    union all
    select t1,0 as t1_qty1, sum(qty) t2_qty2 from t2
    group by t1
    ) A
    group by t1


  20. Hi manvendra Singh(22),

    we can use Openrowset and insert data into table using

    Insert into
    select from OPENROWSET(‘SQLNCLI’, ‘Server=xxxx;Trusted_Connection=yes;’,
    ‘SELECT GroupName, Name, DepartmentID
    FROM AdventureWorks.HumanResources.Department
    ORDER BY GroupName, Name’) AS a;

    see sql help for more details.


  21. Dear Vishakha(24),

    we can put data like that. i’m not clear about your requirement but generally anthing to be picked by like that then we use Constaraints/Deafults in defining values.

    give more details about problem to help you better.


  22. When the question of optimisation comes we use stored procedures instead of long queries.
    Now suppose a stored procedure’s name is 101 character long then what to do?

  23. hi, i tried my codes like this but it’s not working…
    please help..?

    Insert into magpatoc.dbo.RSOTransfer
    Select * FROM (‘Provider=Microsoft.Jet.OLEDB.4.0;’,
    ‘Data Source=c:\CopyOfRSODB.mdb;User Id=admin;Password=;’,
    ‘SELECT * FROM FinalCustItemRSO’)

  24. hi
    Iwant to generate a script for insertion of data to a table.

    I have to repeatedly insert specific data to a table for each database,i want to automate it by genarating the insertion script.with the scripts of creation and all i have to generate some hundred records for a table to each database.

    Please help me if any one got this situation

    Thanks and regards
    Ravishanker Maduri

  25. Hi!

    Thanks for a great article
    Im using ODBC in c++ in a applications,
    but all i want is to know how in the program, import large data directly to SQL Server but from the application from an external datasource (csv,txt), without have to use BULK INTO or OPENROWSET.

    Heard somewhere that the bcp_init would do the job.

    Lambda Swahili

  26. Hi……
    consider i hav three tables
    i need to enter t1col1 and t2col2 in t3col1 and t3col2 respectively….

    plz guide me to implement this using insert select statement


  27. Have a user define data type for a phone number field. Defined as (###)###-####. Problem is have a hard time checking the constraint when inserting data from a stored procedure SQL 2005 Thank you for the help.

  28. Hi Dave
    I have two tables, one with 10 columns and the other with 20 columns
    10 columns are common in both the tables
    i want to update the table with 20 columns on a daily basis using a DTS Package

    i need to insert if a record doesnot exist ,if exists i need to update the 10 fields
    kind of new to sql sp’s
    thot of using cursors
    can anyone suggest me a way of doing it ?
    thanks in advance

  29. you can transfer data from one server to another by the
    following commands

    Insert into finalxlsitems1
    select * from
    ‘SELECT *
    FROM server.reportserver.dbo.finalxlsitems1′

    • Can you tell me the naming conversion and the format of data passing in OPENROWSET …what is ‘sa’ stand for?

  30. salam.sir

    My Problem

    i have 5 tables and one main table.the main table cantain all the foreign keys of another 4 table.so if i enter values in main table.it will not enter it.because it contain foreign keys of 4 tables.so please send me vb code to insert a record.


    VB code for insertion of foreign keys values in table without mentioning the forighn key attribute.


  31. pease help me.
    i have imported text file file into SQl db, i wan to change the impoted tbale to exixting table. how i can do that

  32. Hi

    How can i export data from one table to another table in different database using SQL query. I nead to use the query in VB6.0


  33. hi everyone
    in seq server 2005 there is a feature of link
    server by which u can communicate two diffrent server
    and their database and more, ur querying one another server database using the same qury window

  34. Helowww,
    I want to insert from one table to another, create an EDM table, which is denormalized fact table, ETL from OLTP…
    However this source table consists 30millions rows and still have to join with 6 other tables (also thousand to millions)…
    Today this query is done in 10 hours, which is highly unacceptable, how can I solve this???
    I’m thinking about devide-n-conquer where each record given an identity, and insert records in 10.000 records/iteration???
    Any idea???

  35. hi,
    I have to copy 2 colums,which are from 2 different tables, in to another table .when I am trying insert into,it is giving error.
    Msg 1038, Level 15, State 5, Line 1
    An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as “” or [] are not allowed. Add a name or single space as the alias name.
    can anyone help me in this.

    My query is

    select registration.customerid,vendoroffer.offerid,getdate() Into offer_assignment From Registration,vendoroffer where (select dbo.f_AgeCalc(dateofbirth, GetDate()) as age)Between 0 and 6 And zipcode=98052 And gender=’Female’ And vendoroffer.customerid=registration.customerid

  36. Am trying to insert data from one table to another, but query is giving error :

    (SELECT 1

    Help !!!!

  37. hi,

    I have to copy the same data to two different tables. Is it possible to do this with only 1 query? or does it have to be 2?
    The thing is the database will have to function afterwards for someone who barely uses pc’s. So the messages that pop up must stay at a minimum. I can’t program with VBA so cancelling the messages out by those means is out of the question for me.

    P.S: I am using access 2007

  38. Hi,

    I have used INSERT INTO TABLE1….SELECT COLA, COLB.. FROM TABLE2 , in a procedure. The Problem is it doesn’t insert all the selected records in TABLE1. eg. If it selects 20 records it may insert all 20 sometimes, sometimes may insert 11 records or 9 records (less no. of records). Pls. suggest.


  39. Dave,

    I am really struggling and my last foray into SQL was 9 years ago. I am trying to run the following on SQL Server 2005

    INSERT INTO dbo.sim_control_table (customer_name, sage_customer_number, date_of_sale,
    customer_buy_price, customer_commission_due)

    SELECT customer_name, sage_customer_number, date_of_sale,
    customer_buy_price, customer_commission_due

    FROM dbo.excalibur_20080131

    WHERE dbo.excalibur_20080131.sim_number = dbo.sim_control_table.sim_number

    In order to keep it simple I have used the same column names in both tables and I am looking to update from multiple rows to multiple rows. When I then Execute the query I get the following error message:

    Msg 4104, Level 16, State 1, Line 1
    The multi-part identifier “dbo.sim_control_test_table.sim_number” could not be bound.

  40. Nice forum. thanks.
    I am inserting data from one table to another new table using Select into. Can you tell me, how can i copy over the defaults and indexez and constraints along with this.

  41. Can you tell me how to use the INSERT INTO IN clause to transfer data from a table in MS SQL Server database into a table in a oracle database?

  42. @ JEWEL

    This is what I know…. please correct me if I am wrong.

    1. You cannot work on only one column in case of insert and delete. When ever you try to insert a row will be effected .. in no case you can work on only one column. and copy is a insert statement. So that is not possible.

    2. You can UPDATE one column, other columns will not be effected when you try to update one particular column. Since copy is not a update statement its a insert statement you cannot work on only one column.

    If you want to have the same data in two columns in a table, for sure you can do that with out manually copying it.

    This is what I did in the example:


    -> I created a table named Uexample and then I create a column EID which is int Datatype and I created one more computed column .. its not an alias because I did not give any datatype for second column, for aliases you still have to give the data type. so my second column is computed column which is exactly the fist column. I used computed column because you cannot have two columns with same name in one table.


    -> Simple insert statement.


    -> Simple select statement.

    hope this works.

  43. @ Joji

    The best way to copy a table with its indexes, defaults, constraints, triggers and all other stuff related to a table is …

    Script the table :

    1. In object explorer, right click on the table and then select SCRIPT TABLE AS : CREATE TO

    REMEMBER: WHEN YOU TRY TO SCRIPT IN SQL SERVER 2000, make sure you uncheck the option DROP if exists. IF THIS IS CHECKED then you will loose your original table. ( If you run the script in the QA in the same database )

    When you get the script, then copy the script and run in Query Analyzer (2000) or SSMS connecting to the database in which you want to create the table.

    once you run the script then you will have your table ready with all your constraints, indexes, defaults, triggers….

    BUT after table is created, you will NOT use SELECT * INTO statement, now you will use

    INSERT INTO new_table_name SELECT * FROM old_table_name

    Hope this helps.

  44. @ Mark

    This is what I understood, please correct me if I am wrong …

    Your explanation gives answer for your question : You said you are trying to update the data in table dbo.sim_control_table, but you know what, you are trying to insert the data when you want to update it.

    Solution would be : Dont use Insert statement, use update statement, your problem will be solved.

    This is a small example:


    -> I created two tables which are almost same, This is what you did, two tables with different names but they have same column names and that too in the same order :)


    -> I inserted three rows in Uexample2


    -> checking values using select statement.


    -> Now insert three rows in Uexample1, I wantedly inserted different data in this table, just to distuinguish… but you can see my first column in the two tables its exactly same. This is what you did :)


    -> Checking the values in Uexample1.


    -> This is your T-SQL statement, I did the same thing, I am trying to update the value in Uexample1 from Uexample2 using my first column as macthing, this is what you did in your case.

    Know what : I got the same error :)

    Error: Msg 4104, Level 16, State 1, Line 2
    The multi-part identifier “Uexample1.eid” could not be bound.

    SOLUTION : Now let us try update for the same table.
    T-SQL code will be changed slightly,


    -> Instead of INSERT we will use UPDATE and instead of WHERE CLAUSE we will use JOIN CLAUSE.

    Check the output.

  45. @ J.Marchena

    I tried working on your problem… I dont know how to implement this in Access 2007. I tried in SQL Server 2005.. same applies to SQL Server 2000 too.

    I will explain my logic and I will ask the question I have .. because I got some errors while executing quiries…

    This is what I did in this example :

    1. I create three tables, by name: Uexample1, Uexample2, Uexample3


    Same tables with different names.

    2. Now I created a trigger on the table UEXAMPLE1. This triggers inserts data in UEXAMPLE2 and UEXAMPLE3 tables when ever you try to insert the data in UEXAMPLE1 table.


    This could be one solution when you insert data in UEXAMPLE1 and you want that data in UEXAMPLE2 and UEXAMPLE3.

    But that is not your concern, you said you are trying to copy the data, which means you will not insert data in UEXAMPLE1.

    For that we will try the same logic, but this time we will create the trigger on UEXAMPLE2, when ever a row is inserted ( while copying fromUEXAMple1) into UEXAMPLE2 this trigger will insert the same row in UEXAMPLE3


    So you dont have to write two quiries to copy the data into two tables.

    There is one more way you can do this … you can create a view on either UEXAMPLE1 or UEXAMPLE2 and create a INSTEAD OF trigger on any one of the view, will do the same thing.

    ISSUES: I am not genius… no where near .. while doing the above examples… I got many errors…

    1. like when I use INSTEAD OF trigger on Uexample2, the data doesn;t go in the table UEXAMPLE2 … but it is directly goes to UEXAMPLE3. This is strange.

    2. When I execute :


    and I have trigger on UEXAMPLE2 which inserts records in UEXAMPLE3… I saw that only one row is copied in UEXAMPLE3 but all the rows have been copied in UEXAMPLE2… This is also strange to me … ?

    please clarify … I spent enough amount of time on this !

  46. @ viswa (38)

    This could be one solution … like you said I used cursor in this … but not sure if there is still any better solution for this …

    This is the example :


    -> created two tables, UExample1 and UExample2 , they have 2 columns same between them, UExample2 has two extra columns.


    -> I inserted three rows in UExample1. Three differenrt rows.

    INSERT INTO UEXAMPLE2 VALUES ( 1, 244 , 333 , 444)
    INSERT INTO UEXAMPLE2 VALUES ( 2, 244 , 333 , 444)
    INSERT INTO UEXAMPLE2 VALUES ( 3, 344 , 333 , 444)
    INSERT INTO UEXAMPLE2 VALUES ( 4, 444 , 333 , 444)

    -> Then I inserted four Different rows in UExample2, first column in both the tables is common and has three different values. This is to create your condition… if it exisits then update if not Dont…. So there are three rows in Uexample1 which exists in UExample2.


    ELSE PRINT convert(varchar(10), @col )+ ‘ DOES NOT EXISITS IN UEXAMPLE2′

    -> I create a stored Procedure which is checking if there is any row in cola in Uexample2 which exists in UExample1.

    If yes then It is updating that colmn with the value of UExample1.

    If not then it is printing … it does not exists in UEXAMPLE2.

    Hope this helps … I am not sure if this is a good solution, since I used Cursor in this …

  47. hello, friends,
    I want to insert data into one table(save_documen_v) from another table(document_v).

    i have 3 more columns r there in save_document_v then document_v.other columns r same.

    i want to insert record of that different column from outside n in the same row i want to insert selected record from document_v table.

    if i pass the query–
    insert into save_document_v
    (member_code,class_no) values (1102,(select
    class_no from document_v where (acc_no=’D 1173′)))

    then it works.
    but when i m trying to insert into more columns like-

    insert into save_document_v
    (member_code,class_no,acc_no) values (1102,(select
    class_no,acc_no from document_v where (acc_no=’D 1173′)))

    it not work.

    i have 16 columns same in both table.
    n 3 more columns in save_document_v(member_code,m_type,display)

    if any one has solution plz give me.

  48. hi,
    related to my previous post..
    if in that problem i write this query-

    insert into save_document_v (member_code,m_type,display,class_no,acc_no,
    values (1101,’document’,’yes’,(select class_no,acc_no,author,author_2,title,yr,imprint,
    remarks,abstract from document_v where acc_no=’D 1173′))

    its give error–
    ERROR at line 4:
    ORA-00947: not enough values

  49. You know what I would love to figure out is how to write an “execute select” statement. In other words,

    execute dbo.doSomething @param1, @param2, @param3
    select column1, column2, column3
    from table

    such that the value of column1 is passed to @param1.

    Right now, I write something like the following:
    for scripts
    select ‘execute dbo.doSomething ”’ + column1 + ”’, ”’ + column2 + ”’, ”’ + column3 + ””
    from table

    for stored procedures
    select @value1 = column1, @value2 = column2, @value3 = column3
    from table
    execute dbo.doSomething @value1, @value2, @value3

    It gets the job done, but I’m *convinced* that there’s a better way I am overlooking. Any suggestions on where to start reading?


  50. @ Kruti

    This is what I understood, please correct me if I am wrong,

    You have two tables, lets say UEXAMPLE1 and UEXAMPLE2, one of the two tables has 3 extra columns, 16 columns are same in both the tables.

    Here is the example …


    –> I created two examples UEXAMPLE1 and UEXAMPLE2. In these UEXAMPLE1 has only 3 columns and UEXAMPLE2 has 5 columns out of these 5 columns in second table, three columns are common for both the tables.

    INSERT INTO UEXAMPLE2 VALUES ( 1, 2, 3, 4, 5 )
    INSERT INTO UEXAMPLE2 VALUES ( 11, 12, 13, 14, 15 )
    INSERT INTO UEXAMPLE2 VALUES ( 21, 22, 23, 24, 25 )
    INSERT INTO UEXAMPLE2 VALUES ( 31, 32, 33, 34, 35 )
    INSERT INTO UEXAMPLE2 VALUES ( 41, 42, 43, 44, 45 )

    –> Inserted values in the second table.

    Problem : Now when you said you are trying to insert the data from second table into the first table. Your query works fine when you select one column from second table but when you select two columns your query does not works,

    Solution : The problem is you cannot use one select statement in that way for more than one column. I am not sure but if you write select query for every column seperated by a comma then hope that will work

    like this ( I did not execute this query, I assume this will work ) :


    My solution :

    For your first query this could be a replacement :

    SELECT ’10’, EID2
    EID5 = ’15’

    Explanation : Here you are using a select statement and you are passing the value which you want to insert in the first column and in the second column you are copying the value from second table by giving a where clause.

    For your second query this could be repalcement :

    SELECT ’20’, EID2, EID3
    EID5 = ’25’

    Explanation : Here you are using a select statement and you are passing the value which you want to insert in the first column and in the second column and third column you are copying the value from second table by giving a where clause.

    Hope this helps…

  51. Hi

    I wondered if anyone could help me?

    Pinal’s blogs have proved very useful so far but I have been struggling to do something in sql server 2005 now for over a week. I’m positive it is something quite simple yet I am a novice at sqlserver and it is making me lose the will to live.


    My overall aim is to have two versions of the same database (AdventureWorks) on the same server so that I can run a series of tests using various sql comparison tools and compile a report based on my findings.

    Using Pinal’s blog I have so far managed to copy what appears to be a skeleton of the DB. I.e. all of the dbo’s have been successfully copied but I cannot seem to get any data to copy into these tables.

    As I said above, I am a novice at SQL and SQL Server so if the answer is obvious please show a little patience.

    If anyone can help with getting the data into the tables or suggest a whole new approach which will enable me to have two instances of the same database on the same server I would be much obliged.

    The renaming of databases was a whole other kettle of fish over which I effectively wasted 2 days work.

    I’m sure this is a simple thing to do but I feel I have exhausted all obvious avenues.



  52. khub saras kaam chhe. mane khub gamyu….!!! tame brahman bhai lago chho…!!!! aatlu saras parinam aapva badal khub khub abhinandan…!

  53. @Priya :

    This is what I know, please correct me if I am wrong

    There are basically two types of triggers in sql server 2005 (DML & DDL) and only one type (DML) in sql server 2000.

    Sql Server 2000.

    1. DML Triggers: Again in this we have two more types of triggers,

    b) ” FOR ” or ” AFTER ” TRIGGERS

    a) INSTEAD OF : Again in this we have three types of triggers,

    1. DELETE
    2. INSERT
    3. UPDATE

    b) ” FOR ” OR “AFTER” TRIGGERS: Again we have three types of triggers just like “INSTEAD OF”

    1. DELETE
    2. INSERT
    3. UPDATE

    These are total six types of triggers available in Sql server 2000. These triggers gets executed when ever you insert, update or delete.

    Now in sql server 2005, they have created one more Trigger named DML, these triggers gets executed when you execute any DML : create , alter, drop statements.

    DDL Triggers: We have many triggers under this category available for all kind of DDL statements, to get more information you can refer to books online… the list is very big…..

    Please refer to this example to know more abotut DDL triggers,



    I didn;t know anything about Triggers but after reading this article, I know something about triggers, now I write triggers just based on this knowledge… This article is really really good… very easy to understand : to know more about DML Triggers please follow this example,



    Hope this helps


  54. In the previous post, there are some mistakes.

    By mistake I wrote DML triggers in sqlserver 2005 , it is DDL ( Data Definition Language) – Create , Alter Drop.

    2000 – only DML ( Data manupulation Languae) – insert , update , delete.

    2005 – DDL and DML.

  55. Hi, I have a query
    i have 2 databases ‘A’ and ‘B’ both SQL SERVER 2005,

    and i want at such a thing that at some specefied time everyday say (22:00 hours) data to be selected from few columns from table in databse ‘A’ and the to be inserted into specified the table in database ‘B’.
    and this process should run self that is self executing.
    is this possible

  56. i have one prob is that

    i want to select the particualr row from sql database & insert into ms access database how it is possible

    how can i write the query in c# appliaction

    plz help

  57. i have created student table where student_id is the primary key for that table. another table is logbook_entries where student_id is a primary key and it also a foreign key where it refers student_id in student table. i do not how to retrieve the student_id to logbook_entries from student table.
    how can i write the query.
    plz help me out.

  58. hi,
    i want to write trigger on table of one database for insert which insert that record into table of other database……..

  59. No help for my query?

    It really is quite important that I get this to work. If anyone can help it would be aprreciated.

    Post #69

  60. Hi ,

    I need following output. code should be in store procedure

    column1 column2 column3
    a a1 1
    a a2 Yes
    a a3 12
    b b1 Null
    b b2 65
    b b3 67.7

  61. There are two ways to specify the data values:

    – Use a VALUES clause to specify the data values for one row:
    INSERT INTO MyTable (PriKey, Description)
    VALUES (123, ‘A description of part 123.’)

    – Use a SELECT subquery to specify the data values for one or more rows.
    INSERT INTO MyTable (PriKey, Description)
    SELECT ForeignKey, Description
    FROM SomeView

    How to insert the old purge data from AA database to additional the BB archive database?


  62. Hi,

    I want to copy tables with structure, keys and indexes from one server to another server in sqlserver2005. I can’t use Generate script as it copies all tables. I just need to copy selected tables from source server to destination server which doesn’t exist in the destination server.

    Eventhought i tried with the
    SELECT *
    INTO server1.db1. tblName
    FROM server2.db1.othertblName

    but it doesn’t copy the indexes and keys.

    Please send me query .


  63. Hi,
    I have SQL Server table with TimeStamp column. I canno insert values into this field. When I try to
    Insert values into this field it says the followin error.

    “Server: Msg 273, Level 16, State 1, Line 1
    Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a
    default of NULL for the timestamp column.”

    Kindly let me know how to insert values into this field.

    Thanks in Advance,

  64. Help please..?

    i tried to code that enable to transfer from the data in my local drive database to the network database to the network database updated but i got error i used this statement below..

    please help me please..?

    here is my codes….

    Dim conn As ADODB.Connection
    Dim SQL As String
    conn = New ADODB.Connection
    conn.ConnectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\pc1\TSmobileData\CopyTSMobile.mdb;Persist Security Info=False;Jet OLEDB:Database Password=h1lt1″

    SQL = “Select into CopyTSMobile.dbo.TTMtransaction” & _
    “Select FROM (’Provider=Microsoft.Jet.OLEDB.4.0;’,’Data Source=c:\TSMobile.mdb;’,’User Id=;’,’Password=h1lt1;’,’SELECT FROM TTMtransaction where sync=0’)”

    conn = Nothing

    please help please how can i fix this error..

    The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

  65. your posting its very helpful but still i am not getting solution for my problem is that “to transfer a db table (with data) from one database to another in different server?”

  66. I have a Table A and Table B if i insert a record in Table A, a message box will shown in Table B that “In table A a new records is inserted”. i need a query for this.. thanks in advance..

  67. I have one table say T1 with 5000000 records. I need to insert in this record in another table say T2. I have written

    Insert into T2
    Select * from T1

    But it is taking very long time. also after executing for 10 min its giving Disk full error as there is hugh disk space.

  68. help me out…

    i need to copy a table from one server to another server.

    both seevers reside on different different machines.

    i think “select * into” will work but i dont know the actual syntex of it…

  69. Anand
    You can do this using this by connect to one server and Right click on the databasen->select Task->Import/Eport option.
    try this.

  70. dear sir
    i have a question
    can i copy one table to another table in same database and before inserting in second table i want to fire one trigger is it possible if yes plz give me code

    one table is employee
    and second table is company
    and one trigger
    i want to copy employee in company before copying in company trigger will fire and if any error then it will give u in the error message table
    plz send me thanks in advance

  71. How to insert data(only one column) from source table to another table

    its very urgent need please send me the query.

  72. hello dev,
    i have two tables ticket and account.
    In ticket table i have two columns ticketid, balance
    In account table i have three columns acid,balance,ticketid

    when i insert data in ticket table, automatically the balance in account table should be inserted.

    how to write a trigger for this in sql 2005

  73. @pavani.


    Description:I am creating two tables ticket and account as you described in your question.

    FOR INSERT — Remember this trigger is only for insert

    then I created a trigger which will insert same data in accounts table in columns ticketid and balance, when ever you try to insert data in ticket table.

    here is an example.



    Hope this helps, I am not sure if this what you were looking at, Your decsription was not enough, so what ever I undertsood I tried to implement.


  74. Hello sir,
    I want a I want update a recored from another table at once using a select query. So please tell me query

  75. I am writing a SP to copy existing data into the same tables. This is similar to the actual COPY command. The sequence of copy is as follows

    1) Table1(Parent table)
    id Column1
    11 Test1
    12 Test2

    2) Table2(Child 1 Table)
    id FK Column1
    13 11 Test4
    14 11 Test5

    3) Table3(Child 2 Table)
    id FK Column
    15 13 Test6
    16 13 Test7
    17 14 Test8

    I am copying only say data for ID = 11 into the table1 and all child tables. The new rows inserted into the ChildTables (Table2, Table3) should carryover the Primary key and insert them as foreign keys into the child tables.

    For eg: output looks like this, If user Copied 11 from Table 1 then

    Table1 will look as

    id column1
    11 Test1
    12 Test2
    100 Test1 (new copied data)

    Table2 will look as

    id FK column1
    13 11 Test4
    14 11 Test5
    15 100 Test4 (new copied data)
    16 100 Test5 (new copied data)

    Table3 will look as
    id FK Column
    15 13 Test6
    16 13 Test7
    17 14 Test8
    18 15 Test6 (new copied data)
    19 15 Test7 (new copied data)
    20 16 Test8 (new copied data)

    Appreciate any ideas or help on this

  76. Hi,

    I am trying to write a query that will copy select data from table A to Table B. However Table A gets updated, so I need a way to copy that selected data from Table A over to Table B without recopying data I have already copied from Table A into Table B. Any help would be greatly appreciated.

  77. @ Josh,

    You can create an insert trigger on table A which will insert data in table B automatically.

    Here is an example,

    1. create two tables , exampl and example.
    2. create trigger on table exampl.
    3. insert a record in table exampl, and check table example, you should be able to see the same inserted record.



    Hope this helps.

  78. i need some help guys,

    suppose table a has ID as a column
    and you want to insert data from table b to table a where table a ID = @id (where @id is passed into a sproc)

    lets say
    insert into table a
    (column a, column b, column c)
    SELECT column a, column b, column c
    (FROM a series of joins and table b and WHERE conditions)

    — and this is where the problem is…
    AND table a.ID = table b.ID??? wont even let me alias table a…any clues?

  79. @DON,

    I am not sure if I understood your questions.

    1. Reason why you cannot use alias a fot table would be, generally we give aliases for table after FROM statement thats where we define all aliases and in your insert into statement your table is coming before FROM statement. This is just observence … I dont know what is the correct explanation.

    2. When you try to insert into a table, you can insert all the column, similarly when you try to delete some row, you cannot delete one column in that row, whole will be deleted, BUT when it is for UPDATE, you can update one column of one row.

    You are writing an insert statement which means you are inserting whole row and the same time you are using a join to compare the same table using a.ID = b.ID , This is not possible to insert a record in table a when you are using the same table in comparision, only situation when this possible is UPDATE. when you can compare the values of same column in that row.

    I know its very confusing, hope you understood, If I am wrong please correct more than happy to learn :)

  80. Hi, CAn Anybody tell me that , i Have 10 records in Table A , and 2o record in Table B now i want to add these 20 records in table A using DATA TRANSFORMATION WIZARD in SQL 2000,
    Can anybody help me plssssssssssssssss. It s Very Very Urgent.

    Thanks in Advance,
    Software Developer

  81. hello sir

    i am manoj

    i am trying to fetch images form folder and image name in data base.
    i want to all images in a table in 2 rows and 4 column.

    plz help me

  82. Hi

    1.I want data to be inserted in T2 table from T1 table on
    schedule basis.
    2.Both tables are in different server and differe DB.
    3.I have 19 clumn in T1 where as 20 columns in T2 the 20th column is addition in T2 which accepts NULL .
    4 I dont want to alter T1 design.
    5.Please can any body help me in transfering the data . I need to pass NULL value to the 20th column in T2 table while moving the data from T1

    Thanks in advance………………:-)

  83. Hello!

    First off I want to say great example. Worked perfectly for my needs with little customization. How I’m wonder if something more is possible.

    My working current code is (generalized for simplicity)

    INSERT INTO db1.dbo.CommonTable
    SELECT * FROM db2.dbo.CommonTable
    WHERE my_column = 4

    Is it possible that the new rows, inserted into db1, could have the ‘my_column’ value changed? Something like:

    INSERT INTO db1.dbo.CommonTable
    SELECT * FROM db2.dbo.CommonTable
    WHERE my_column = 4
    SET my_column = 99

    Could anyone lend a hand?

  84. @Mathew

    This can be done like this… I am sure there must be a good way to do this…

    Example :

    create table example1 ( cola int, colb int, colc int)
    create table example2 ( cola int, colb int, colc int)

    insert into example1 values ( 2, 3, 45)
    insert into example1 values ( 12, 13, 45)
    insert into example1 values ( 112, 113, 45)

    insert into example2 (cola , colb, colc ) select cola , colb , 99 from example1 where example1.colc = 45

    select * from example1
    select * from example2

    Result :

    2 3 45
    12 13 45
    112 113 45

    Example2 ( output)
    2 3 99
    12 13 99
    112 113 99

    Hope this helps.

  85. @ Imran Mohammed

    That’s great, thank you very much. I realize the solution must include the specific column names along with the value I wish to substitute but is there a way to generalize the statement and only specify the column I want change?

    The reason I’m hesitant about listing all columns is I’m trying to write a generic algorithm that works on 40 databases containing 60 tables with a varying number of columns.

    In the meantime, I’ll try and incorporate the column names and that should work with the above code you gave me.

  86. hai dev
    i have a problem in right outer join in below query

    Select a.contact_id,a.contact_name as ‘Contact Name’,
    SUBSTRING(a.contact_number,3,12 )as ‘Contact Number’,
    Gender= case a.gender WHEN ‘m’ THEN ‘male’ WHEN ‘f’ THen ‘fem’ ELSE ‘who’ End,
    From phone_book a with(nolock) ,User_Group_Members_Dets b with(nolock)
    Full outer join User_Group_Dets c with (nolock)
    on b.gid=c.gid
    where a.user_id=922124
    and a.active = 1
    and b.status=1 and a.contact_id=b.contact_id and
    b.gid = 67754 order by a.contact_name

    here if anyrows of a,b are not maching with where clause
    the right tale (c) are not getting

    plz give me a suggesition

  87. Hi… please help
    how i can copy data from table wich different database where every database have same table name into one database where have same table

  88. Hi,

    Actually, there is a table called ‘Workgroup’ in Oracle Database in which the users will change their workgroups rarely.
    There is a same table in ‘MS SQL Server’ Database.
    I would like to bring the table from Oracle to MS SQL Server and whenever there is a data change (Workgroup change) in the Workgroup table in Oracle, data of the same table in MS SQL Server should also change.

    Is there a way to do this?
    Please help me. It’s urgent..

  89. Hi murry,

    murry you can use Import/export data to copy a data from one database to other.


    use this query

    insert into databasename.dbo.tablename
    from databasename.dbo.tablename
    where (condition)


    select lastname,firstname into pubs.dbo.test
    from northwind.dbo.Employees

    This query will create a test table and copy the data.

    Vinit Satam

  90. hello sir,

    i am new to sql server

    so my problem is also little for you but i am confusing please answer

    problem is,

    i have one table with 10 records in it

    when i insert or update records there is no problem,

    but when i delete lets say row no 4 and..

    then when i insert a new record it is inserted at 4th position and then the record are continued to be added from that position to downwards

    and not at the end of table(means, not from last record onwards)

    what happens and what is the solution???

  91. A table named ‘employees’ have the columns Emp_name, emp_no,age and the second table named ‘User’ have columns Emp_name,age. How to insert the values Emp_name, emp_no from the ‘employees’ table into ‘User’ table using subqueries

  92. @shameer

    How can you insert data in the column when you dont have the column in the destination table.


  93. ya, nice and thanks

    but when i entered a value in particular column in one table the next column will automatically inserts the value from another table which has the value at particular column

    I tried that code in query manually but i need this code to be generate automatically when i enter a value in beside the column

  94. @Zulfi Asdani

    You can do it in two ways,

    1. Use bulk copy command from command prompt or sql server and import that data from text into sql server.

    2. create a dts package, select source as text file and destination as SQL Server and you can import all data into sql server from text file ( very easy).

    Try looking on internet you can find many examples.

    Hope this helps,

  95. how to run a insert query so as to insert a column from remote server to local DB, this column contains some sql queries so these queries should also get executed while inserting the column.

  96. Hi,

    have 3 tables in one server database and want to copy that data in to my Local database (b’cos I want to work with that local data with out effect that original data)
    Please help me.

  97. @Anu
    You can use SSIS or DTS to do that.

    IF you are using SQL server 2005, right click the database–>Task–>Export. Then everything is straigth forward from there on wards.

    hope this helps

  98. Hello

    I am very new to write SP,so need guidance in doing the following.

    I need to write a stored procedure,which will insert data into 3 tables.

    These are the 3 table



    -TOCHSCounterNumber—-this is autogenerated

    Basically these tables are changing the numbering of elements.

    like for example.

    this is the numbering of a document named Transportation TP 1.1.1
    so this will be changed to 1.A.1 or 1.a.1 depending upon the choice.

    so the table TOCHSCounterStyle is like
    TOCHSCounterStyleID —1

    TOCHSCounterStyleID —2

    TOCHSCounterStyleID —3

    Thank You

  99. @ blue star

    Need more information ….

    Its really confusing !!! Please provide more information with input and how you want your output to be.


  100. Hello Pinal Dave,

    i have a problem regarding data transfer.
    The scenario is there are 2 database DB1 and DB2.
    Both the database having a table TAB1 with same structure.

    Now when data is inserting into DB..TAB1 we are firing a Trigger TGR1 to insert the same data into DB2..TAB1. But the concern is on performance…

    So is there any other way to insert the data in DB2..TAB1 after inserting into DB1.TAB1?????

    If is there any error while inserting into DB2..TAB1, trasaction under Trigger will be ROLLBACKED.

    plz help soon its urgent.

    Thanks in advance

  101. Hi , thank for the script in your document, very useful.
    My friend and i tried it, and did work but the next thing we want to do was to add a new column to the table having received the data, and update that column the same way as in the insert into , but it happens that the data are appended from the last row of the table , leaving the two initials table blank.
    That the script:

    //Create a new Table
    create table Info
    (FirstName VARCHAR(100), LastName VARCHAR(100))

    //Inserting the data
    INSERT INTO Youssef(FirstName, LastName)
    SELECT FirstName, LastName
    FROM AdventureWorks.Person.Contact

    //Altering the Table
    alter table Info
    add Email VARCHAR(100)

    Here is where i get stack, it update from the last row leaving the Firstname and Lastname blank

    update Info
    set Email = (SELECT EmailAddress
    FROM AdventureWorks.Person.Contact)

    i don’t want to use a procedure and looping construct.


  102. @@Imran

    How Can I copy my local sql server 2005 table’s data to my host database?

    It would be helpfull if you can provide me the code.

    Currently im doing this by using Import/Export Wizard and its a very long process.

    Thanks in Advance!!

  103. Hello Dave,

    I’m having two tables

    Table1: Table2:

    ID Date Month Jan Feb Mar Remarks

    1 1 Jan xx xx xx xxxxx

    2 14 Jan xx xx xx xxxxx

    3 26 Jan xx xx xx xxxxx

    4 14 Feb xx xx xx xxxxx

    5 13 Mar

    I want distinct values of Month in Table1 to be the Column Name of Table2 as above said:
    can you help me out

    Thanks & Regards
    Surendar K

  104. Hi,

    I need a help
    I had a Query that when we delete the data from any table
    from a particular database in SQL Server 2005, the deleted data has to insert in the DeleteLogTable, using triggers

    DeleteLogtable Columns

    TableName ColumnName DeletedValue

    Plz solve my problem.

  105. Hi Dave,
    I have 2 tables as @temp1 and @temp2 such as
    id name
    1 pavan
    2 raj
    2 kumar
    2 pav
    1 tamma
    and @temp1
    id city
    1 nagpoor
    2 poona

    and want new table @result as
    id city name
    1 nagpoor pavan,tamma
    2 poona raj,kumar,pav

    please tell me how to do this with stored procedure or UDF

  106. Hi,

    pavan mainde

    I hope this query may help u to get ur output.

    Select T2.Id, T2.Name, T1.City from @Temp1 T1, @Temp2 T2
    where T1.Id= T2.ID

  107. Hi,

    I need a help
    I had a Query that when we delete the data from any table
    from a particular database in SQL Server 2005, the deleted data has to insert in the DeleteLogTable, using triggers

    DeleteLogtable Columns

    TableName ColumnName DeletedValue

    Plz solve my problem.

  108. Hi Dave

    I have to move some tables from one databes to another on the same SQL server 2005.
    Actually I have to do this a few times, move it from DB1 to DB2, from DB2 to DB3, from DB3 to DB4…, all of them on the same server.
    The respective databases and tables have different names.
    In fact only the beginings or the endings of the names are different.
    I was thinking to this with INSERT INTO – SELECT FROM
    statements. I have moved data from DB1 to DB2, that was no problem. To move it again I have to write new script with the new names.
    Can I somehow use one script, where I can do something about the table and database names. The manes of the columns are all the same in all databases.
    Can you please help me. I have about 50 tables. How can I automate this procces?
    Should I use BCP instead?
    I would really appriciate your help.
    Grateful M

  109. Hi Dave,

    i am asking this query from 29 Sep 2008.
    Plz help me to solve the problem.
    I need a help
    I had a Query that when we delete the data from any table
    from a particular database in SQL Server 2005, the deleted data has to insert in the DeleteLogTable, using triggers

    DeleteLogtable Columns

    TableName ! ColumnName ! DeletedValue

    Plz solve my problem.
    If guys any one has any idea on this plz give me the reply.

  110. Hi bubu,
    I hope this will help u to solve ur problem.

    Suppose Test1 is a table in that there are 3 columns(CL1,Cl2,Cl3) so now we are adding the CL1 & CL2 into CL3
    So below procedure ma help u.

    Create Proc Insert2RecordsInTo1Col
    Insert into Test1(Cl3)
    Select Cl3= CL1+Cl2 from Test2

  111. Hi Punter,

    I hope the below query will help u.

    There are 3 tables(Test1, Test2, Test3) and the Test1&Test2 are named as T1, T2
    In the Test1 we have First_Name(Column)
    In the Test2 we have Last_Name(Column)
    In the Test3 we have Full Name(Column)
    So now i am inserting Test3 table in the Full_Name(Column)
    from Test1&Test2 tables “First_Name+Last_Name”
    but i have a comon column ‘ID’ in both the tables(i.e,T1, T2)
    so i am comparing the ‘ID’ column from both tables and inserting it in the Test3 table in the Full_Name Column.

    Insert into Test3(Full_Name)
    Select T1.First_Name+’ ‘+T2.Last_Name from
    Test1 T1, Test2 T2
    where T1.ID = T2.Id

  112. hi,

    I need a help .. need to insert a purticular field value into another table.(need trigger)


    T1 = table one

    t2=table two

    then have to insert using trigger

    update t1 set f1=t2.f2

    please help me

  113. hi.please help me.. i want to insert date to my table in sqldeveloper,but i cannot.. what i must do can anyone write this code whole!!!!

  114. HI,
    i have a probelem in sqlserver 2005.
    i want to integrate data into a single intergration table from many other tables.
    all tables have some column names. and i want to bring the data such that if a entry in row is changed it should be updated , and if row doesnot exist is should be inserted.

    But probelem here is i have to compare 7-8 columns in a single row and then decide to update or not is there any solution.

    there are a lot of solution available but they compare 1 or 2 columns in a row only.

    PINAL please help out.

  115. Please can someone help me…

    I am fairly new in the DB domain and I need to create a scripte that will CUT data from a table and PASTE it to a new table. The objective is to segragate historical data per month to improve the performance of a reporting tool.
    Also how can I implement variables into my script to let the user specify from what date till what date he want to “transfer” the data into a new table that will have a variable name as well?

    Is this possible?

    Thank you in advance for any help!

  116. Hi

    Looks the situation you have best suits for table partitioning.
    The Data movement will also be very fast when partioning is in place . the requirement will be just to have a column in the table with the representing time or date which will help us to segregate data very easily.


  117. So I am having a problem. I have set up my database tables in phpMyAdmin. Therefore, in my php code I am having a hard time writing the code to insert a primary key from one table into another. For example,
    StudentInfo (stdID, FName, etc)

    LookUpInfo(stdID, ProductID, etc)

    If anyone understand please HHEEELLPPP.

    $query= “INSERT INTO LookUpInfo(stdID) VALUES (‘stdID’)”;
    $result= $result = mysql_query($query)
    or die (“Query failed: ” . mysql_error());


  118. The following query helps to create the archival of table and dump the data from source table into test table based on criteria.
    Step one: Run the following query

    SELECT * INTO tbltest FROM tblsorce WHERE year(fieldyear) >= 2008

    step Two: Refresh the table list

    You will see a new table tbltest

    Then use this table and test your query.

  119. sql for two different table from two different server

    like server1 & server2 — Two Different Server Not Database
    tables table1, table2

    select a.sn, b.name from server1.table1 left a join server2.table2 b on a.sn=b.sn

  120. This is the query for inserting data form one table to another table in different DATABASE


    insert into DocTypeGroup
    Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType
    from Opendatasource( ‘SQLOLEDB’,’Data Source=;User ID=sa;Password=gchaturthi’).dbIPFMCI.dbo.DocTypeGroup

  121. This is the query for inserting data form one table to another table in different DATABASE


    insert into DocTypeGroup
    Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType
    from Opendatasource( ‘SQLOLEDB’,’Data Source=;User ID=sa;Password=gchaturthi’).dbIPFMCI.dbo.DocTypeGroup

  122. Hi,

    I am connected to a network server through sql server management studio express.

    I need to restore a copy of the backup which resides in my pc into the db connected through the network.

    When I click on the restore db thru files option, I am able to browse thru only the drives/files of the server. How do I restore the db from my pc?



  123. Hi frnds,
    i m try to do one query.
    i have two tables
    M_Payment (machine no, total amt, reference no)

    Mode_of_payment (reference no, cash amt, cheq amt, DD amd)
    data are likn:-

    machine no total amt reference no consumer No

    1 1000 122 C1
    1 200 123 C2
    1 300 124 C3
    2 500 125 C4
    2 500 125 C5


    reference no cash amt cheq amt DD amt

    122 500
    122 500
    123 200
    124 100
    124 200
    125 500
    125 250
    125 250

    i want machine no wise total amt and sum of total cash amt, total chq amt and total DD amt

    my require o/ p is…
    machine no totla amt cash amt cheq amt DD amt
    1 1500 600 700 200
    2 1000 500 250 250



  124. Hi Pinal,

    Wish you and your Family a Very Happy New Year 2009 :-)

    Pinal i have my Group on Yahoo for MSSqlServer which is “ASKSQLEXPERTS”, its my Pleasure if you will Join this Group so that all the members of this Group will get more help…

    Thanks & Regards
    Dharmendra Dixit

  125. @devan

    Hi Bro,

    You can solve Your problem by writing this Script, think it will work successfully..

    SELECT * INTO MYTable FROM Pubs.dbo.TableName

    Dharmendra Dixit

  126. Hi,

    Could anybody help or guide me on how to automate the backups of our tables from all the databases in Sql server 2000 and storing that backup file as *.mdb(MS Access) file on daily basis?

    Thank you!

  127. Very nice blog. I appreciate your efforts. It works for me.

    Can you please let me is it possible with one query?

    Kunal Mehta

  128. How to insert data from one SQL server table to another SQL server table?
    For example, I want to recreate the table on production server from test server. Both servers are SQL server 2005 express, table structure are same.

  129. Hi

    I’m trying to transfer records from one table in one database to a parallel table in a second database, using code as described in responses above.

    I’m using the code :

    “INSERT INTO [Students] IN ‘C:\VB9\Future Indicative\fiarchive2007.mdb’ SELECT * FROM [Students] IN ‘C:\VB9\Future Indicative\fi.mdb where [ID] in (2626,3548,2627,3549)”

    and I get an error ‘Syntax error in from clause’. Can you spot a fault????

    I have an open connection to the first database, but have NOT iopened a connection to the second database. Is this required????

    Many thanks


  130. Further to that… I note that I have a connection open to the database FROM which I’m copying, but do NOT have an open connection to the destination database. This is clearly wrong… I’ll experiment.

  131. Hi,

    I retrive data from table using mysql database with the c++ program under linux , but when i insert data using variable then table receive null value

  132. can you help me…
    this is a simple problem that i can’t figure it out.


    strql=”INSERT INTO tablename (field, field) VALUES (” & text1.text & “, ” & text2.text & “)”

    unable to save in the database..

  133. Automate sql Query(stored procedure) using Jobs

    Just create a new job through sql server agent
    and in description write down

    EXEC Stored procedure name

  134. Automate sql Query(stored procedure) using Jobs

    Just create a new job through sql server agent
    and in description write down

    EXEC Stored procedure name



    EXEC sp_makewebtask
    @outputfile = ‘D:\Pritesh\Media WIP Balances as of 31 Jan 2009.xls’,
    @query =’Select ad_PCCode, Ad_Loc, Cl_Name, Brand_Name, Brand_Code, ad_pjid, ad_pjno,
    case ad_reftype when ”VINV” then ”VINV” when ”MINV” then ”MINV” when ”TRO” then mis_name when ”RRO” then mis_name when ”PRO” then mis_name end as Stat_Name,
    sum(ad_dramt) as WIP_DR,
    sum(ad_cramt) as WIP_Cr,
    sum(ad_dramt – ad_cramt) as WIP_Bal
    From WIPMediaById
    left outer join qryBrands on ad_BrandCode = Brand_Code
    left outer join mismasterids on ad_statusid = mis_misid and mis_group = ”MSTAT”
    Where Ads_AccDate <= ”2009-01-31” and ad_accode = ”1320000500”
    Group by ad_PCCOde, Ad_Loc, Cl_Name, Brand_Name, Brand_Code, ad_pjid, ad_pjno,
    case ad_reftype when ”VINV” then ”VINV” when ”MINV” then ”MINV” when ”TRO” then mis_name when ”RRO” then mis_name when ”PRO” then mis_name end’,
    @colheaders =1,
    @FixedFont=0,@lastupdated=0,@resultstitle=’Media WIP Balances as of 31 Jan 2009′

  136. I am trying to insert data in a table table1 of data base db1 from table 2 of database db2 . But I find error message 208 invalid object name

    insert into tb1(f1,f2) select f1,f2 from ma.tb1 where ma.tb1.f1=1

  137. Mustaque Ahemed

    you have databaseA , TableA

    you have databaseB, TableB

    say, you are trying to insert data from TableA of DatabaseA into TableB of DatabaseB

    This is script,

    use DatabaseB
    insert into TableB (f1,f2) select f1,f2 from databaseA..TableA as Tab1 where Tab1.f1 = 1


  138. based on this example i used the following code:

    insert into entrydata
    (StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate)
    (StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate)
    from holdingtable where StaffID = ‘AMBRES003′ and WeekBeginningDate = ‘2009-01-26′;

    i recieved the following error message:
    Msg 102, Level 15, State 1, Line 2
    Incorrect syntax near ‘,’

    i have checked the code and don’t see any erroneous ‘,’ and there are no ‘red lines’ under any of the code in SSMS – I am using SQL Server 2008 if that helps

    any idea what the problem is? i have wasted a whole afternoon trying to copy these columns from one table to another!

  139. @Dave

    insert into entrydata
    (StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate)
    StaffID, EntryDate, DayIndex, ClientID, ProjectID, StartTime, EndTime, ClientAreaID, ElapsedTime, WeekBeginningDate from holdingtable where StaffID = ‘AMBRES003′ and WeekBeginningDate = ‘2009-01-26′

    When you are writing insert into table_name it should be immediately followed by a select statement.

    insert into table_name select col1,col2,col3 from table_name2
    where blah blah….


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

  141. Pingback: SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

  142. Pingback: SQL SERVER - Guidelines and Coding Standards Part - 1 Journey to SQL Authority with Pinal Dave

  143. I’m writing a batch job using .NET and want to compare a table in our data warehouse with a transactional table. These 2 tables reside on different servers and databases. I am creating a datahandler from .NET and have been told that creating 2 different data connections will not work ???

    I actually need to compare rows on each table to determine if rows may have been updated already.

    Any other ideas or will 2 different connections work with two seperate data adaptors? Any ideas welcome or if there is a better idea please share.


  144. What if you have a large amount of data and your transaction log fills. We are using a query to gather data from several tables and inserting into one table. The insert into select works well except where the result set is very large, the transaction log fills. This is a reporting db that is refreshed nightly from a highly normalized transactional db. After the restore, we are running a script to create & populate some unnormalized tables. What other method can we use for the large results set?

    Thank you!

  145. hai
    i am getting ora.00905 : missing keyword error
    with the following method
    can you please help me

    Method 2 : SELECT INTO
    This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
    USE AdventureWorks
    —-Create new table and insert into table using SELECT INSERT
    SELECT FirstName, LastName
    INTO TestTable
    FROM Person.Contact
    WHERE EmailPromotion = 2
    —-Verify that Data in TestTable
    SELECT FirstName, LastName
    FROM TestTable
    —-Clean Up Database
    DROP TABLE TestTable

  146. @Leslie Hurst

    Since the database is only for reporting, Why dont you keep your database recovery model in simple mode.

    If data is huge, if there are any Non-Clustered indexes that are not build on primary key or unique key, then disable those indexes, loading will be much faster. Also when you update statistics you might want to change recovery model to Simple.

    Look at this link:


    More Important : Please read comments by experts for the above link.

    When you change recovery model, it will break any backup chain that you might have scheduled. But assuming this is only for reporting services, I would not be concerned about log file.

    Hope this helps,

  147. @Rama

    I dont know if this feature is in ORACLE.

    From the error message you posted, I am guessing that you are trying to execute this query in ORACLE. Well script executes fine in SQL Server not sure about ORACLE.


  148. @Imran

    Just confirming, Oracle uses INTO in PL/SQL to set a variable. To load a TABLE it’s CREATE TABLE AS SELECT….

    I get confused in between the two sometimes. It’s not the different syntax, it’s when the same syntax means two different things.

  149. @Brian

    I agree with you.

    Your explanation, to perform similar action in ORACLE, we have to use below command is absolutely correct.


    But Brian there are chances that users copy scripts from internet and with out knowing that Script is written in T-SQL they could try to execute it on ORACLE which leads to these error messages.

    Brian any idea, which is according to ANSI SQL,

    SELECT * INTO table_name from ……


    CREATE TABLE Table_name as select ……..


  150. Hi!!

    i have just started working on SQL i need sum guide line how to create own table can u help me …


  151. Hi,

    I have different tables (say table1, table2,etc) with the following fields.

    All have same timestamp. I have to merge all tables into single table


    I think the time stamp can be updated as datetime format. But I dont know how to update columns by comparing with the timestamp. Please help


  152. Hello Dave

    I am doing a select and inserting into temp table. I want the rows inserted into temp table ina particular sequence.


    select a,b from table 1 order by a into temp table2

    But i see that table2 is not inserted in order. Is there a way to do this?


  153. Hello to all,
    I want to copy data from a database table in a server to database table of another server.
    I user this example
    insert into DocTypeGroup
    Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType
    from Opendatasource( ‘SQLOLEDB’,’Data Source=;User ID=sa;Password=gchaturthi’).dbIPFMCI.dbo.DocTypeGroup

    all table name database name username and password are ok but the following error shows —

    SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online.

    What is solution now….

  154. Hi pinal dave ,

    Thanks in advance

    I have one question about sql server 2005 TRIGGER.

    can i insert data into master-detail table using Trigger.

    say for example.

    i have one master table name(Info) and field(ID,Name,Age)
    and detail table name(sub_info) and field(SUB_ID,ID,ADDRESS). so now when i insert single row in info(master) table then using trigger also insert row in sub_info(detail table). but in sub_info table i want to pass address filed data from outside. so is it possible or not.

    also i create trigger which i given below. just refer it and reply me as soon as possible.


    declare @id int
    select top(1) @id = id from info order by id DESC insert into Sub_info(ID,address)values(@id,’Event issue’)

    insert into info(name,age) values(‘bhavin’,24). when i run this insert query then t\above trigger insert single row in sub_info table. but here address is static. so i want to add dynamic address.

    So please take a urgent and send me reply.

  155. I have two table table 1 & table 2

    table 1

    ——————— ———————

    table 2

    ——————— ———————

    I need result

    OLD_id New_ID
    ——————— ———————
    64518 5053
    59414 5756
    65007 8535
    57750 2328

    I dont have any condition .

  156. my query with union is returning the result

    OLD_id New_id
    ——————— ———————
    0 5585
    0 5891
    0 5895
    0 5896
    57750 0
    59414 0
    64518 0
    65007 0

    I need this result in this form
    OLD_id New_id
    ——————— ———————
    57750 5585
    59414 5891
    64518 5895
    65007 5896

    Any solution ??

  157. Hi,

    How to make condition. Are you sure that Row 1 in Table A has match with Ro1 of Table B?

    If that is the case then you can use this:

    ;with A AS(
    ) , B AS(
    select A.columnA, B.ColumnB
    from A
    INNER JOIN B ON a.RowNum = B.RowNum



  158. Can anybody tell me how to write a cursor to loop through the records of one table and insert them into another table?

  159. Dear Pinal,

    Thanks for this.
    I want to transfer table of one data of first database to table of second database using stored procedure.
    Will u please send me the answer on my mail id.


  160. hi
    i am trying to insert data from one table to another table with same field name but the datatype for one field is different that is for TravelDate field in the 1st table it is Varchar(15) & in the 2nd table the Datatype of TravelDate is Datetime so when im trying to insert data from 1st table to 2nd table im getting error as
    “Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting datetime from character string.”

    when im trying to insert from this query

    insert into linkinvoicesector(InvoiceSecID, InvoiceID, InvoiceNo, SOSecID, SOID, SONO, GDID, PAXID ,SecID ,PNR, DepartureCity, DestinationCity, DestinationFlag, TravelDate, FlightNo, ETA ,ETD, Class ,Status ,Flag)
    select InvoiceSecID, InvoiceID, InvoiceNo, SOSecID, SOID, SONO, GDID, PAXID ,SecID ,PNR, DepartureCity, DestinationCity, DestinationFlag, TravelDate, FlightNo, ETA ,ETD, Class ,Status ,Flag
    from linkinvoicesectors

    i have tried even by use convert while inserting but no use still same error.
    can i have any solution regarding this problem. I would be very thankfull to u if my problem is solved


  161. If I Have Two Databases On Two Different Server Connected With Each Other And I Have Got To Make View How Can i Collect Data From Both Databases I Mean What is The Way to Make Connection Between Both Databases?

  162. Thanks very much for this post i have been struggling but am finally getting somthing. My problem is the query below is repeating each record twice. what could be the problem.

    Select CandTrans.CentCode,CandTrans.CandNo,CandTrans.Subjcode,CandTrans.CandName,CassMarks.SubjName
    FROM CandTrans,CassMarks
    WHERE NOT EXISTS(SELECT CassMarks.CandNo FROM CassMarks WHERE(CandTrans.CandNo=CassMarks.CandNo))

  163. Hi,

    i am working on asp with backend as sqlserver 2000

    I have a diff problem

    i hav two tables for masters one is main and another is history table

    when some one modifies or updates the existing record
    the data of the existing record will be copied to the history table.

    but when iam using this below query

    insert into NRI_PIS_SCHEME_MASTER_HISTORY select SchemeId,Narration,Hslbrokper,
    BankPisPer,DPper,convert(datetime,StartDate,103), convert(datetime,EndDate,103) ,
    Remarks,Seqno , CreatedBy,CreatedDate,’aurionpro’,getdate(),’0′,’1′ from
    NRI_PIS_SCHEME_MASTER where SchemeId= ’15’

    the data is inserted twice

    i checked out the query it got displayed only once

    i donno what’s going on

    i tried diffly but nothing going in the right way pls help me

    thnx in advance :)

  164. @Kumar

    You can use, Symmetric Keys, Asymmetric Keys or Certificates to encrypt your data. When you store your data in database you need to specify a certificate name or symmetric or asymmetric key name and this will encrypt your data. When you want to retrieve your data back you can always specify certificate name and get your data.

    This is a very good tutorial. Step by step, I strongly suggest you please go through this.

    Link : http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/

    ~ IM.

  165. I want to insert data from one server database table
    to another server database table in sqlserver 2000.
    pls help me…

  166. @Jitender

    You can use any one these to get your task done.

    1. You can use DTS Package and using that DTS package you can import data from one sql server and load it into another sql server.

    2. You can create a linked server on any of the server, lets say you have a table1 on Server1 and table2 on server2, you want to copy data from table1 on server1 to table2 on server2.

    First you create a linked server, on server1 (name of linked server lets says is Server1) after you create this, execute this script on server2

    insert into table2 select * from server1.db_name.dbo.table1

    This should work fine, you have to change db_name in above query with the database name on server1 in which table1 is present.

    if you do not want to create a linked server because of security reasons, you can also use OPENROWSET Function. Read more about OPENROWSET in Books online, look at sample script provided in books online, That should give you a good start.


  167. hi, why my insert do not work??

    INSERT INTO psi_Checklist_Logs
    (application_id, checklist_id, data_month, activity_status, execute_Date, operation_status, summary, bgcolor, boxicon, starttime, endtime, checklist_code, application_code)
    SELECT APPLICATION_ID, OPERATIONCHECKLIST_ID, 200903, isnull(ACTIVITY_STATUS,”), EXECUTE_DATE, isnull(operation_status,”), isnull(COMMENTS,”), ”, ”, [start], [end], CheckCode, AppCode FROM psi_Checklist_Logs_bk

    Msg 512, Level 16, State 1, Procedure trig_Checklist_Logs, Line 19
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
    The statement has been terminated.

    thank you

  168. Hi All,

    I need to selectively copy records from one table to another i.e. copy if the record does not exit otherwise update a particular field.

    I guess I have to use cursor in a stored procedure. But I’m not sure how. Would appreciate if someone can put some code here.

    I’m using Firebird database.

    Thanks in advance.

  169. @Ali Kazim

    You need not use Cursors for this task,

    Instead of one query write two quries,

    1. Query to update existing Records
    2. Query to Insert New records

    Example Script :

    create table example1 ( eid int , ename varchar(10))

    insert into example1 values ( 1, ‘Apple’)
    insert into example1 values ( 2, ‘Boy’)
    insert into example1 values ( 3, ‘Cat’)
    insert into example1 values ( 4, ‘Doll’)

    select * from example1

    create table example2 (eid int, ename varchar(10))

    insert into example2 values ( 1, ‘Apple’)
    insert into example2 values ( 3, ‘Boy’) — Needs to be updated.

    select * from example2

    — Update Using Joins

    update example2
    SET ename = B.ename
    from EXAMPLE2 A
    join example1 B on A.EID = B.EID

    — Insert Using Joins
    insert into example2
    select A.eid
    from example1 A
    full outer join example2 B on A.eid = B.eid
    where B.eid is null

    — Update Using Where & IN Clause

    insert into example2
    select A.eid
    from example1 A
    where A.eid not in (select eid from example2)

    — Insert Using Where & IN Clause

    update example2
    set ename = A.ename from example1 A
    where A.eid in (select eid from example2)

    Some times Joins are faster than In and where clause, in some rare cases joins could be slower. That is the reason I have provided you examples using both, Joins and where clause,

    Check which one suits best for you consider only performance, both scripts will and should return same result set.

    One Suggestion: I read this comment some where, long back, Cursors and While Loops are for freshers.

    Use while loops and cursors carefully, they can kill your performance like anything.

    ~ IM.

  170. Hi,

    I how do i insert guid?

    i have created a table1( guid, field1)

    and i want to insert the guid in table2(field2, field3, guid)

    table1 has guid as PK

    i am having problems inserting record in table2, i want to insert the guid from table1 into table2.

  171. Thanks for this article, However it inserting new records into an existing child table is not working for me because it is dependent on a parsed version of one of the parent table fields. And because I use a table function to achieve this. How can I fix?

    I have SQL Server 2008 and Visual Studio 2008 and am trying to automatically insert new records into a child table based on new inserts into its parent table.

    I know that this should be simple: via an insert trigger. However, the complexity is that the new records into the child table need to be a parsed version of one of the parent table’s fields.

    To achieve this, I developed a table function which parses this one field in the parent table.

    Originally I had the Foreign Key defined below, but this wasn’t automatically inserting new records into my child table, so I commented it out.

    I have tried writing both recursive and non-recursive insert triggers, but I have received errors on almost every attempt. I was able to partly get non-recursive triggers to work…but without the parsing function. I think the trick is that this is a table function.

    How should I design this? Some of my pseudo code as follows:

    CREATE TABLE ParentTable
    strInput VARCHAR(8000) NULL

    CREATE TABLE ChildTable
    ParentID INT,
    FullRow VARCHAR(8000)
    –I removed the following FK cause it wasn’t automatically inserting new records:

    Sample output of what I want is:
    ParentID strInput
    2 ‘FFF|R|BC|D|EE’
    3 ‘GG|BB|CCHC|D|EE’

    ChildID ParentID strInput
    1 1 ‘AAA’
    2 1 ‘BB’
    3 1 ‘CCCCCC’
    4 1 ‘D’
    5 1 ‘EE’
    6 2 ‘FFF’
    7 2 ‘R’
    8 2 ‘BC’
    9 2 ‘D’
    10 2 ‘EE’

    My table-function removes the “|” delimiter with strInput as its input. How can I achieve the above results?


  172. I had to rebuild an Access Database that was kind of a mess. So I started from scratch, I created a database relational database and now I need to take the data from the old database to the new one which is on SQL Server 2005. So I am taking and old MDB and turning it into an ADP and I am trying to find the best way without loosing data, migrating the data from the old to the new. How would I do that??

    I was thinking of doing a plain insert, but the thing is the datatypes are not exactly the same

  173. i have 2 tables simillar to this example.
    Table A Table B
    group_id group_id count
    1 1
    1 2
    2 19
    1 33

    i need to write an sql statement that will count the number of occurrences of each group_id # in table A and place that count in the count column of Table B.

    Thank you in advance,

  174. We’ve found that OPENROWSET only ever returns 1 row in SQL Server 2008?

    For example, this code

    SELECT *
    ‘select 123 from dual union select 456 from dual’)

    … correctly returns 2 rows (123 and 456) in SQL 2000 but only returns 1 row (123) in SQL 2008.

    Is this a configuration issue? We’re connecting to an Oracle database.
    Thanks in advance

  175. Q. Write a SQL select statement. Given a table called EmailAddresses and the columns ID (int) and EmailAddress (varchar(100)), write a SQL SELECT statement that retrieves all email addresses that occur more than twice in the table.

  176. Pingback: SQL SERVER – 2008 – Copy Database With Data – Generate T-SQL For Inserting Data From One Table to Another Table Journey to SQL Authority with Pinal Dave

  177. thank you! this article was very enlightening! i used the select into query combined with an update query to clone a table and swap values of two columns in a circumstance.

    SELECT *
    INTO [new-table]
    FROM [old-table]

    DECLARE @temp AS decimal
    UPDATE [new-table]
    SET @temp=value1, value1=value2, value2=@temp
    WHERE id < 806

  178. Hi there..!

    I want to know how to copy the data and its properties(identities)/structure from the old table to new table. I am really upset with this thing. When I was just using SELECT INTO FROM statement, It only copy the data with basic structure of the old table.. Kindly need your help.. thanks!

  179. I tried by making TransactionOption to be Required.
    i want to insert a data throug child table
    for this i want to check if the data is all ready in master table than no problem,
    but if it is not in master table than i want ot insert data through child table.which is connect with the master table
    so please tell me what should i do

  180. Hi,

    how do i insert br_no?

    i have created a table1 mst_brng( br_no)pk
    i have created a table2 sec_mst_brng(br_no,br_mk)br_no is a FK
    and i want to insert the br_no in table2(br_no, br_mk)
    but i want check first if br_no allready in master table than no problem,
    but if it is not in master table than i want to insert data through child table so for this what should i do

  181. Hi Rushi,
    You are going right way. You have to use transaction.
    while inserting records in child table first you have to find that that record is already exist or not ,if not then first store into one local variable and then add it into master table first and then add in child table.

    I hope u will get it .

    Darshan Shah

  182. Hi Mitch,
    Basically we have to use select * into for temporary tables.
    We can use it to create basic structure with Data.
    But if you want to create table with all details then first you have to create script for it.
    GO Enterprise manager->right click on object ->Generate Script ->Create ->copy the script and use that script to create new object.
    after then use insert statment with select.

    I hope this will help you.

    Darshan Shah

  183. My tables are SubjectTable and GradeTable.SubjectTable has datafields of Course, Subject, Units While GradeTable has Studname, Subjects, Grade, Remarks. I inserted datafields of subject from SujectTable to GradeTable. And it leaves the other rows blank. How can I Add data to a Null rows?Can somebody help me with this problem of mine….thanks

  184. Hi pinal,

    i have a problem inserting multiple records into a table. My code looks like this:

    SELECT strength_app_men_inst_id from TACF_WFTA_STRENGTH_APP_MEN_INST where wf_task_assignment_id = @WfTaskAssignmentIDP1
    OPEN @MyCursor
    INTO @StrengthAppMenInstIDP1
    exec @ReturnCode = getid_sel @table = ‘TACF_WFTA_STRENGTH_APP_MEN_INST’, @id = @NewID2 OUTPUT, @mode = 2
    insert into TACF_WFTA_STRENGTH_APP_MEN_INST (id, wf_task_assignment_id, strength_app_men_inst_id) values (@NewID2, @WfTaskAssignmentIDP2, @StrengthAppMenInstIDP1)
    CLOSE @MyCursor
    DEALLOCATE @MyCursor
    IF @@error 0

    the select statement returns multiple values and i have to insert that into the same table. i know cursor is not a good way, but my select statement will always return a max of 10 values. Can you please check where i am going wrong, or a better way.

    Can you please help me with this as soon as possible. I would really appreciate this.


  185. FYI: in my test data, the select returns 2 values, but when i ran the query, i got only one row with @StrengthAppMenInstIDP1 as null, but it had a value.

    Dont know where i am going wrong…


  186. @Brian, hmm i dont think so. But i was able to figure out my issue.

    The correct one’s is:

    SELECT strength_app_men_inst_id from TACF_WFTA_STRENGTH_APP_MEN_INST where wf_task_assignment_id = @WfTaskAssignmentIDP1
    OPEN @MyCursor1
    FETCH NEXT FROM @MyCursor1 INTO @strength_app_men_inst_id
    exec @ReturnCode = getid_sel @table = ‘TACF_WFTA_STRENGTH_APP_MEN_INST’, @id = @NewID2 OUTPUT, @mode = 2
    insert into TACF_WFTA_STRENGTH_APP_MEN_INST (id, wf_task_assignment_id, strength_app_men_inst_id) values (@NewID2, @WfTaskAssignmentIDP2, @strength_app_men_inst_id)
    FETCH NEXT FROM @MyCursor1 INTO @strength_app_men_inst_id
    CLOSE @MyCursor1
    DEALLOCATE @MyCursor1

    Thanks for checking out… Harish

  187. @Harish

    I’m happy you could figure it out.

    I always try making it one query if possible. I could not see it here, because a PROCEDURE is being used in the middle.

    INSERT…SELECT is usually faster than a CURSOR. And as one query, everything will be in context too. And for many, it is much easier to read because all the logic is in one statement. It might be worth a bit of your time to review the entire process and see if it can be one statement.

  188. Thanks Brian, for your time and effort, i’ll re-look into my stored proc and try to make it simple as you suggested…

    Thanks again,


  189. @Sneha.

    I am not aware of any direct way of doing this.

    You could do this from SQL Server Management studio easily, but if you see Generate Script for this, you would see that
    1. Your original table is copied into a temp table,
    2. Original table is dropped and
    3. A new table is created with column added with the specified column location and
    4. Then data is copied from temporary table to this new table.
    5. And then name of this new table will be renamed to your original table.

    ~ IM.

  190. i have to same database with exactly same tables!
    i want to add data from DB A into DB B.
    i want to check if the primary key of a table in DB A doesn’t exist on DB B insert that record else update that record!

    help me !

  191. @Ehsan.

    What is the concern. You explained your scenario. But what is that you want from us.

    You want us to write a script for you or are you looking for any solution from us on how you can implement your requirement.

    Please briefly tell us what is that you want us to do ? what kind of help you are looking for.

    ~ IM

  192. Hi Ehsan,

    You can do this as:

    –Insert code
    INSERT INTO TableA(columns)
    SELECT Columns
    FROM TableB b
    SELECT a.Id
    FROM TableA a
    WHERE a.ID = b.ID

    –Update code
    UPDATE TableA
    SET Columns = b.Columns
    FROM TableA a
    INNER JOIN TableB b ON a.ID = b.ID

    Let me know if you have any questions.


  193. Hi Tejas Shah,

    I have a few slow performing T-SQL cursor stored procedures that I want to replace with normal T-SQL (i.e. remove the cursors). Can you provide examples.

  194. Hi Clive,

    First of you need to remove cursors.

    What you need to do is:

    Populate Table variable or Temp Table with the data, and then loop it thru as:

    Please find: h ttp://www.sqlyoga.com/2009/12/sql-server-how-to-remove-cursor.html


  195. hi i want to ask one question. plz can u people tell me how to copy whole data from one server to another server using SQL both at diffrent places.

  196. Hi all,
    I am working to an c# win form application with sql server.
    I have to table “father” and “child”, 1:n.
    I am populating forms with data and after i have to transfer data into database. i have some text object for “father table” and one datagridview with more lines for “child table”.
    I want to use a Sql Server procedure. How can I pass all informtion (text object and datagrid), as parameters, to the procedure?
    Is it possible to use XML.
    For populating the grid I use SqlDataAdapter and DataSet.
    thanks in advance

  197. insert into Emp_Perform_SecA_Details(empcode,CycleStartDate) values (‘000740′,select settingvalue from applicationsettingmaster where settingname=’AppStartDate’)

    it is getting error, what is the solution for that

    • Correct way is

      insert into Emp_Perform_SecA_Details(empcode,CycleStartDate)
      select ’000740′,settingvalue from applicationsettingmaster where settingname=’AppStartDate’

  198. I want to select all from table A and select table B where A.id=B.id

    table A

    table B

    out put should be

    A B
    2 2
    3 3

    can any one tell what the query is.

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

  200. Hi,
    I have one problem.. Problem is-
    I want to insert records into one table say “NewCountry” from other “Country” table but this “Country” table is located at different MS-SQL server so how can i insert records?

  201. Hi Mandar,

    If you want to do only through t-sql query then either use OPENDATASOURCE function or create a linked server and then use OPENQUERY function or by 4 part naming.
    If you are not sticked with T-SQL then better use import/export wizard and you can save it as a SSIS package for future use.

    Pinal Dave

  202. In my project I want to insert data to a table from another table in the database

    I used query as follows

    String st = “select code from state where name='” + this.statename.Text + “‘”;
    String str = “insert into branch(Code,Name,metro,state)values (‘” + this.txtbranchcode.Text + “‘,'” + this.txtbranchname.Text + “‘,'” + this.cmbmetro.SelectedIndex + “‘,'” + st + “‘)”;

    but compile time no error
    at run time it is showing error as syntax error at Karnataka

    here Karnataka means my statename.text

    pls correct me error

    • Change: “‘,’” + st + “‘)”;
      To: “‘,(” + st + “))”;

      The SELECT statement needs to be in parenthesis itself.

      Another way to do it would be to only use the SELECT statement, and not use the VALUES clause at all:

      String str = “insert into branch(Code,Name,metro,state) select ‘” + this.txtbranchcode.Text + “‘,'” + this.txtbranchname.Text + “‘,'” + this.cmbmetro.SelectedIndex + “‘,'” + code from state where name='” + this.statename.Text + “‘”;

  203. Hello,
    I want to insert from another table (2 tables)

    -Tbl1.Field1 (Pk), Field2, Field3, Field4(FK)
    -Tbl2.Field4 (PK), Field2, Field5, Field6, Field7

    I want to insert into Tbl1 but i have “Field2″ in antoher table (tbl2).
    I try to used
    — (“Insert Into Tbl1(Field1,Field2,Field3)
    Values(@Field1,@Field2,@Field3)Select Field2 From
    but there always show message error “Missing semicolon (;) at end of SQL statement”. But if i add the semicolon(;) at the end of SQL statment
    — (“Insert Into Tbl1(Field1,Field2,Field3)
    Values(@Field1,@Field2,@Field3);Select Field2 From
    then show message error “Characters found after end of SQL statement”

    I’m hoping somebody here knows what might be happening.


      create trigger railways_result_trigger on railways_result
      after insert
      if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, getdate (), 103)))
      delete from current_railways_result
      delete from railways_result
      delete from current_railways_result
      insert into current_railways_result select *,dense_rank()OVER (PARTITION BY stu_batch_time,exam_date ORDER BY stu_total_marks DESC)stu_rank
      FROM railways_result
      insert into railways_result_backup select *,dense_rank()OVER (PARTITION BY stu_batch_time,exam_date ORDER BY stu_total_marks DESC)stu_rank
      FROM railways_result
      ;with DelDup as (select row_number() over (partition by
      stu_name, stu_roll,exam_date,stu_rank,stu_batch_time order by stu_name,stu_roll,exam_date,stu_batch_time) as RowNo from railways_result_backup)
      Delete from DelDup where RowNo> 1

      sir i m creating trigger for delete two table records before insert,based on update date in table,insert into both table n get rank from first table backup to another table

      but its not working ,

      plz help me sir i need for project

      thanks and regards

  204. Hello Dave

    I have a strange issue and just wanted to know if it is possible in SQL 2005. Can you please guide me on this.
    I have a column with data like
    223 245 356
    223 356 222
    223 456
    etc (these are 3 digit numbers ie; first row contains 3 different values and last row has 2 different values)
    Is there a way that I can divide this data and insert into different rows.
    Example, in one row I have to insert the first value of the first column, 223 and in the next row I have to insert 245 and in the next 356 and so on. So the data present in this column would go into 10 rows. Am I clear?
    Please let me know if this can be achieved. Thank you for your time.


  205. Hello Tweety,

    use the below function:

    CREATE FUNCTION [dbo].[Split](@List VARCHAR(6000), @SplitOn VARCHAR(5), @MaxRowCount INT)
    RETURNS @RtnValue TABLE([Value] VARCHAR(100))
    SET @COUNT = 0
    WHILE @COUNT < @MaxRowCount
    SET @COUNT = @COUNT +1

    (SELECT DISTINCT CHARINDEX(',',','+@LIST+',',Position)
    INSERT INTO @RtnValue (Value)
    SELECT Substr FROM Substrs

    –Use this funtion to split the string as below:
    SELECT * FROM dbo.Split ('asd,asda,sdf,sdf,dfg',' ',100)

    Pinal Dave

  206. hi sir,
    good noon sir, i want the query details following task..

    Task: i have one document *.doc or *.pdf or *.txt anything. it contains the table and the fields are stuid,stuname. Then i create one table in named student and set the fileds as same for the doc.
    Then how to data insert document into database table..

  207. Hello Shanmuganathan,

    The best methos id to use Import/Export wizard to import data from file into SQL Server table. Otherwise there are other options like OPENDATASOURCE or BULK INSERT.
    For repeated use create a SSIS package.

    Pinal Dave

  208. i=how do i insert data from one table in database A to another table in database B. Both these tables are located on the same server.

    • The ordinal position of the column doesn’t matter as long as you use it in the proper place in the SELECT statement
      If you still need it, do it via management studio

  209. I have 2 tables viz… Table_1 and Table_2. In Table_2, I created a instead of trigger that modifies the data and then inserts it into the table. But when I try to copy data from Table_1 to Table_2 using the above method, only the last record is copied and the Trigger is also applied to it. Can you please tell me the reason for this?

  210. Hello, I am desparate. I have been working this insert statement for three days and I am just getting more confused. I get one error, think I have it fixed, get another error, get that fixed and come back to the same error again.

    I am trying to set up a database to track lab samples. I have a table for the SampleID (autonumber, ArtID (number), SamplePoint (Text), with a few other fields. I am trying to insert the SampleID, ArtID and the SamplePoint into a table that the technicians will be using to enter their test results that will be tied back to the samples taken table. Here is my latest code.

    Private Sub Combo65_AfterUpdate()
    Dim dbs As Database
    Set dbs = CurrentDb
    dbs.Execute “INSERT INTO FGResultsTable.(SampleID,[ArtID],[SPID]);” & _
    “SELECT FGSamplesTaken.[SampleID],FGSamplesTaken.[ArticleNo], FGSamplesTaken.[SPID] FROM FGSamplesTaken;”
    End Sub

    Please help me so I can stop screaming at my puppies.

    • Well you have at least two syntax errors in your stament. Here’s the corrected version. Compare it to your version and you’ll see where the problems are.

      dbs.Execute “INSERT INTO FGResultsTable(SampleID,[ArtID],[SPID]) ” & _
      “SELECT FGSamplesTaken.[SampleID],FGSamplesTaken.[ArticleNo], FGSamplesTaken.[SPID] FROM FGSamplesTaken”

  211. why sql express edition cannot open the table i made. i made a table named tblAlex, every time i open the table the error occur: This is the error:

    Unspecified error
    (MS Visual Database Tools)

    Program Location:

    at Microsoft.VisualStudio.DataTools.Interop.IDTTableDesignerFactory.BrowseTable(Object dsRef, Object pServiceProvider)
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.OpenTableNode.CreateDesigner(IDTDocToolFactoryProvider factoryProvider, IVsDataConnection dataConnection)
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.CreateDesigner()
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDataDesignerNode.Open()
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VirtualProject.Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ISqlVirtualProject.CreateDesigner(Urn origUrn, DocumentType editorType, DocumentOptions aeOptions, IManagedConnection con)
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.ScriptFactory.CreateDesigner(DocumentType editorType, DocumentOptions aeOptions, Urn parentUrn, IManagedConnection mc)
    at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.VsDocumentMenuItem.CreateDesignerWindow(IManagedConnection mc, DocumentOptions options)

    How to solve this problem?

  212. i have a main table, wherein i want to delete a row based on selection from user interface.
    i want to move that row to duplicate table of main say (Main_Dup)
    my idea is to restore it when needed i.e., move gain the row from main)Dup to main table .

    how to get this?

  213. i am using datetime datatype for the entity/column start_date
    when i am inserting only date the default time also inserting..
    i just want to insert date no need of time what i have to do sir
    in sqlserver2005..

  214. Hello Santu,

    Time part is always remaiin associated with date value in datatime data type column. But you can remove the time value by storing 0 with convert function as below:
    convert(varchar(10), getdate(),101)

    Pinal Dave

  215. Hi Pinal,

    How are you? I have one doubt.How can i do design the tables for country,state,city.i mean these tables are separate.
    Table Structure :
    Country – Table

    State – Table

    City – Table

    Duplicates are not allowed.How can i write SP.
    parameters (Con_Name,Sta_Name,Ci_Name)

  216. hello
    i am sandeep (bigb)
    i want to copy data in one table and insert aonther table.
    this table have same feilds

    can you give me a solustion.

    thank you

  217. How to copy record one table and insert another table

    insert into newtablename (new table feilds) select feild1,feild2,feild3 from tablename
    example :

    insert into employee_backupfile (id , name ,salary) select id, name, salary from emp2

  218. if i want to insert data in multiple tables in a single query then how it is possible?if yes then pls msg me in my emailid i need this.

    • Hello Sarika,

      You can insert into two tables using OUTPUT clause as below:

      insert into MyTable1
      output inserted.Col1, inserted.Col2 into MyTable2
      values (10,’wow’)

      Pinal Dave

      • Wow…SQL is becoming more fun,

        Never heard of this before, is this new to SQL Server 2008.

        Another way of doing this would be to create Instead of/ For trigger on table 1 and insert data into table 1, trigger will insert data into table2.

        ~ IM.

  219. hi ,
    i have a table of 16 column in sql that i am uploading excel file in using asp.net . all the column contains value either ‘yes’ or ‘no’.
    i need to calculate the percentage of ‘yes’ in each column and i need to transfer the calculated percentage value to another table .
    and one more thing , that percentage will be calculated using the “group by” clause .

    please help me..

    • Hello Madhu,

      i have created one test table say ‘test’ and i have inserted some records into it. Following is just sample of data that i have created..

      ID IsActive IsVisible

      1 yes yes
      2 no yes
      3 yes no
      4 no no
      5 yes no
      6 yes yes
      7 no no
      8 yes no
      9 yes no
      10 yes yes

      Now write query as-

      SELECT IsActive,CONVERT(VARCHAR(5),(COUNT(IsActive)*100)/(SELECT COUNT(ID) FROM test))+’%’ AS PercentageWiseYes_No FROM test
      GROUP BY IsActive

      After executing this query i am getting result as follows-

      IsActive PercentageWiseYes_N0

      no 30%
      yes 70%

      I think your requirement was like this.
      I am also new in MS-SQL Server but i think this query helps you.

      Mandar Kavishwar.

  220. Sir ur Code is very Effective but I want to transfer the data from one column of one table to another column of another table in existing database.

    kindly Please Reply

  221. Sir ur Code is very Effective but I want to transfer the data from one column of one table to another column of another table in existing database. without making any temporarily table

    kindly Please Reply

  222. Hi.

    I have 3 tables that I’m working with. I need to update all records in Table1 with the value from a similarly named column in Table3. However, I need to use a value from a column in Table2 to get to the right record in Table3.
    The common column in Table1 and Table2 is recid.
    The common column in Table2 and Table3 is VendorSKU.
    Finally, I need to delete all the records from Table3

    Will this work correctly in a Stored Procedure?

    UPDATE Table1 SET Table1.Cost = Table3.Cost
    FROM Table3 c, Table2 p
    INNER JOIN Table1 m ON m.recid = p.recid
    INNER JOIN Table2 ON p.VendorSKU = c.VendorSKU
    DELETE FROM Table3



  223. I have 2 table in sql server freetrial and freetrial_backup, freetrial table contain startdate and enddate when enddate is over it should transfer all data to freetrial_backup table pls help me(startdate is registrationdate and enddate is 2days after registrationdate)

  224. Hi Sharad,

    If this is the case that you want to copy one column from one table to another table, then you can directly write an update statement, if there is one-to-one relation between these tables. Else you need to write custom logic, based on requirement.

    You can use UPDATE in this case.



  225. hi,

    I need to insert the data from table1 to table to.

    Table1 has a primary key, and which is a foreign key in the table2.
    I want to retrieve the data of primary key from table1 and insert that to table2.

    Also table2 has a independent columns , so I need to insert the all columns.

    Kindly please reply,

    • Hi,

      I don’t quite follow. If you need just some value from table1 then just fetch it make insert to table2. For example:

      declare @id int
      select @id = id from table1 where …
      insert into table2 select @id, other, values, …

      Better run that inside a transaction though.

      But I think you need to elaborate your question a bit since I don’t see what could be the problem here.

  226. Hi Dave,

    I want to import data from one main table say ‘A’ to some 200 tables with some condition like if part_no=1 then it will go to part1 table.

    I am not interest to write insert query 200 times.

    Plz help me on this.


    • Run this
      copy the result
      Run them

      select 'insert into part'+cast(number as varchar(10))+' select * from where part_no=A'+cast(number as varchar(10))
      from master..spt_values
      where type='p' and number between 1 and 200

  227. Hello,
    I have a small question.I have 2 tables.One is a main table and the other is a temp table.The main table is a table which gets populated from the data in the temp table.(This is done on a daily basis).

    If we perform a select operation on Main table it lists all the data since years.

    I need help with 2 things.

    1) I just need to get data from today(I can set this query to system data I think).

    2) I need to make sure that data in Main table is not duplicated.(That is main table should not have duplicated rows).

    • Two points can be seen as

      insert into main_table(columns)
      select columns from temp_table

  228. I want to insert one database comlete data into other databse ex. test.district into finla.district
    What i can do

  229. hi,
    can any one help me pl!!
    in sql 2005.i have created a table with a column name as rid,total,cur_value and balance.
    In balance column, this total value minus(-) cur_value is stored.(eg.10000-100=900).
    900 is stored in balance column.
    1)now,after each transaction i wanna store this balance value into the total column again automatically…how to do it (eg.now automatically this 900 must go to total column again)?
    2) how to get a particular value in a table using aggregate as condition ?(e.g select name from table where MAX(id))
    3)is it possible to raise alarm or alert msg in sql server 2005 ???

  230. i have created a table in sql 2000 and i have transfered few
    rows into it from other table with in the same database,now i want to push few more rows into the same table from diffirent database, is it possible in sql 2000?

  231. Are there any limitations for amounts of data that can be copied using or methods with Simple recovery mode? If copied table has 300GB of BLOBs can we still use option to copy all data to another table or we should copy by portions?

  232. I think that we need to create table with name TestTable as shown below in Methode 2. Right?

    Method 2 : SELECT INTO
    This method is used when table is not created earlier and needs to be created when data from one table is to be inserted into newly created table from another table. New table is created with same data types as selected columns.
    USE AdventureWorks
    —-Create new table and insert into table using SELECT
    CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
    SELECT FirstName, LastName
    INTO TestTable
    FROM Person.Contact
    WHERE EmailPromotion = 2
    —-Verify that Data in TestTable
    SELECT FirstName, LastName
    FROM TestTable
    —-Clean Up Database
    DROP TABLE TestTable

  233. i have created two table tab1 with colomn name call_no, title, author_name, edition and second table tab2 colomn call_no, user_name, title, author_name. i want to insert title and author_name from tab1 to tab2. how can do it?

  234. I want to fetch the unique record from a table. I can use distict but it return records with only single column. Requirement is that all column’s values will come. I have three column emp_id, name and user_id.

    Any help will be appreciable.

    Thank you.

  235. hi
    itry to insert content of one table to another but my query has error
    my query is below:

    and error is :

    i know that tables have connected toghether
    but can any man help to solve this prob????
    please i need!!!!!

  236. This is a different subject.
    I have some very large table which I will like select all the data in those tables to different tables by using select into. Also, I want to set the row count to 100000 at a time until all the rows a select into the new table with a loop. Here is the code I have and it is not working properly. Can you help?

    SET ROWCOUNT 100000

    SET @RCOUNT = 10000

    WHILE @RCOUNT = 10000
    INSERT Tbl_archive2
    SELECT * FROM archive

    SELECT @RCOUNT = @@rowcount

  237. I have the cross dock making scans per HU’s. Number of scans = 6 that can happen at different times. The key is based on 2 fields 1> TPP and 2> HU

    Hence I should have a data line as follows:

    TPP HU Receive_Dt Stage_dt Load_Start Loading Loaded_Dt Load_Complete
    HOU 123 12-10-2009 12-11-2009 12-21-2009 12-21-2009 12-21-2009 12-21-2009

    But since the scans are done at multiple time stamps my database fills up like this

    TPP HU Receive_Dt Stage_dt Load_Start Loading Loaded_Dt Load_Complete
    HOU 123 12-10-2009 12-11-2009 – – – –
    HOU 123 – – 12-21-2009 – – –
    HOU 123 – – 12-21-2009 12-21-2009 12-21-2009 12-21-2009

    The other problem is the sheer volume of HU’s processed hence I do not know an ideal way to create a stored procedure that will index each key and plug in the dates accordingly. Any tips or examples you can share?

  238. i want a single query “to delete a record from table1 and insert that deleted record to table2″
    “i want to move a record to another table”

    please help me

  239. Hi Mukherjee,

    You can use this query to move data from one table to another table:

    FROM SourceTable
    INTO DestinationTable



  240. I had created a table student.and exected it. i then wanted to add more columns to the student table but i get a ‘create table denied’ message

  241. hi,

    i want to insert row in a table where two values are given which are to be inserted as it is but thje third value is to be fetched from the other table, how can i do this using Insert statement.

    insert into abc (aa,bb,cc)
    values (‘xx’, ‘yy’, –)

  242. Hi,

    I have a MDF file (database) and I want to copy the contents of a table in .mdf to a similar table in a .sdf file.

    I tried ==>
    SELECT *
    INTO sdfDB.ScannerData_Test
    FROM mdfDB.ScannerData_MDF

    also i tried ==>
    INSERT INTO sdfDB.ScannerData_Test
    SELECT *
    FROM mdfDB.ScannerData_MDF

    But no use. is this kinda copy not allowed on sdf files.

    If not, how do I copy the contents to this compact Database (.sdf)


  243. Hello,

    I have 2 databases and they are
    #1 an mdf file and
    #2 an sdf file.

    How to I transfer the contents of a table in .mdf to a similar table in .sdf database.

    I tried both mentioned methods in the first article, but it doesn’t really work for me.

    Is it because of the compact edition (.sdf) database what am using.

    Please suggest.


  244. This is the query for inserting data form one table to another table in different DATABASE


    insert into DocTypeGroup
    Select DocGrp_Id,DocGrp_SubId,DocGrp_GroupName,DocGrp_PM,DocGrp_DocType
    from Opendatasource( ‘SQLOLEDB’,’Data Source=;User ID=sa;Password=gchaturthi’).dbIPFMCI.dbo.DocTypeGroup

    It is Running

  245. One question related to your post …I have question table and answer table with quesid as foriegn key in answer table. there is another similar table structure and I want to insert from one table to another table i am using following query for it…

    –INSERT INTO QBQuestions (AreaID,UserID,QuestionType,QuestionTextActive)
    –SELECT AreaID,UserID,QuestionType,QuestionText,Active
    –FROM ContributedQuestions
    –WHERE CQuestionID in (18,19,20)
    –SET @QuestionID = SCOPE_IDENTITY()
    –INSERT INTO QBAnswers ( Answer,QuestionID,IsCorrect)
    –SELECT Answer,@QuestionID,IsCorrect FROM ContributedAnswers
    –WHERE CQuestionID in (18,19,20)

    Now to insert mulitple row at one time I used query like this
    DECLARE @lclMySQL as varchar(MAX)
    SET @lclMySQL = ‘INSERT INTO QBQuestions (AreaID,UserID,QuestionType,QuestionText,Active)’
    SET @lclMySQL = @lclMySQL +’SELECT AreaID,UserID,QuestionType,QuestionText,Active FROM ContributedQuestions’
    SET @lclMySQL = @lclMySQL + ‘SET IsActive =0 WHERE CQuestionID IN (‘+@QuestionID+’)

    ‘ where I am using @question id as string of quesiton id but in this case how can I insert question id as foriegn key to Answer table??? Is there some way in which i can use IN keyword in where condition and also insert forign key value in to answer table?????

  246. how to retrive data through using join condition.

    in table A i am having std no, std name, address.
    in table B I am having host id, std no, age, division

    now i want std name who are the people more than 30 age

    can u please help me with query

  247. Hi friends. I’ve a dought on sqlserver2000. As i’m going to do my major project, i wenna know how to add images into sqlserver 2000. and how to retrive. Please send me simple codes for that.
    For instants.

    con SqlConnection;
    con= new SqlConnection(server=”myserver”; uid=”sa”; pwd=”sa”;database=”mydatabase”,con);

  248. 1. Friends I need to write a procedure using cursor by for loop.

    Below are the scenario:

    1.. Data will be pulled from table1
    2. Use for loop for cursor into table1(Table1 records needs to go to table2 ,table3),

    3. Will also need to go into table2

    4. Will also need to go into table3

    Kindly help.

    • Why do you need a cursor?

      This is as simple as

      insert into table2(columns)
      select columns from table1
      where some_condition

      insert into table3(columns)
      select columns from table1
      where some_condition

  249. I tried this method, but it did not work for me.

    USE KudlerAccounting

    INSERT INTO Accounts2004(Expenditures)
    SELECT Expenditures
    FROM ExpendTotals2004
    WHERE Account > 0

    Error: Cannot insert the value NULL into column ‘Account’, table ‘KudlerAccounting.dbo.Accounts2004′; column does not allow nulls. INSERT fails.

  250. Hello, am using this query in sybase
    insert into table2 SELECT * FROM table1

    The two tables have the same table structure

    Getting below error
    An explicit value for identity field in table2 can only be specified in a insert statement when a field list is used

    can anyone help to proceed?


  251. Thanks Madhivanan,
    Used the query in this format
    insert into table2(col1,..colN) SELECT col1,…,ColN FROM table1

    There is one another problem now

    Explicit value specified for the identity field in table2 when ‘SET_IDENTITY_INSERT’ is OFF

    On checking table details sp_help table2 i did not get any information on this
    Is this a trigger avoiding insert into the table or do i need to ask the DBA to set the value for this to ON

  252. I think i need to do

    1)set identity_insert requests_archive on

    2) insert data

    3) set identity_insert requests_archive off

    i guess its correct?

  253. Hi,

    I have to write one procedure to insert records from table on one server DB(DEV server) to table with same name and structure on another server DB(TEST server).

    for eg. there is a table named employee on DEV server and TEST server both. and i have made some enteries in employee table on DEV server and now using procedure just want to move some particular records to employee table on TEST server DB.

    Please help me out


  254. Hi.
    Pinel I have a problem my problem is that I have two servers one is local server and another is Linked server.I send a table from local to linked server with same columns,Local server(parent) has a primary key with Identity column now I created triggers for Insert and Update and reflect data on linked server.Now I want that I will also created trigger for Insert and Update but in opposite direction.
    that is
    A->B and
    my problem is that when I fired trigger then there is message of Primary key violation how can i fixed this problem.plz send me info of bidirection trigger or any plan on my EMAILID

  255. I have the same problem to get new data from database and insert it into another database (same server) I just want the most effective way to transfare the new inserted data and make check upon it before add to my database.
    please advice

  256. To Mona,

    /*Inserting data from one database to another database.*/

    use Database1

    select*into Database2.Dbo.Table2 from Table1
    select*from Database2.Dbo.Table2

    — Here is :

    Database1:- is a database in which a table named ‘Table1′.
    Dbo:- default schema for all the databases.
    Table1:- a table in Database1

    Database2:- a new database
    Table2 :- a new table in Database2

    — This query will insert the data from Database1 ‘s table called ‘Table1′ to another database ‘Database2′ ‘s table i.e. in Table2.

    Hope it will helpfull.

  257. Dear sir

    I have one full/incri .bak file form nrega offline sql server 2005 server.
    how it is combile/merge in my nrega offline sql server 2005 same database without any data lose.

    The problem are that.

    In my block 54 village. block user work on 38 village data enty and i work on 16 village date entry.

    how i combine my backup .bak file in block user data. without any lose.

  258. good morning sir

    this query is very good
    i want to ask a one question in this query

    (1) two tables employee or salary. totoal row in employee 300
    no column or no row in salary
    i used that select * into salary from employee

    (2) i have added 100 rows in employee

    i want a that only 300 to 400 rows come in salary
    not to 1 to 300 rows
    i used that many query but give me some erro
    plz sir help me about this query

  259. good morning sir
    i have no use identity or unique column in table but i use primary key on (sal_id)
    i don’t about use of identity or unique
    thankyou sir

  260. I have get new data from other database and want insert it into my database
    (same server) what is most effective way to transfare the new data in my database.
    without my data lose. please advice

  261. good morning sir

    i have table employee in cms database

    column name


    total column 400

    output only 3 to 8 no come means 234-45
    plz sir send me answer this question
    thankyou sir

  262. hello sir
    sir i want a result in 3 to 8 no come .if value are string
    used select substring(column_name,3,8) value wii be come
    3 no between 8 no come in this query

  263. Hi! Newbie here, but this blog is very helpful. Have to dbs on remote server, and decided to test procedure before going live:

    1. Copy tableA to db2 from db1
    Used following successfully:
    SELECT * INTO trsql_TEST.dbo.Products FROM trsql.dbo.Products

    2. Copy duplicate table with new name to db2 from db1
    SELECT * INTO trsql_TEST.dbo.Products_2 FROM trsql.dbo.Products

    So now I have two tables (Products & Products_2) with identical structure in the same db (trsql_TEST).

    3. Now I want to copy a field to Products from Products_2… I tried UNSUCCESSFULLY the following:
    INSERT INTO Products (stock) SELECT stock FROM Products_2

    The result is that the stock field (column) is copied successfully, but ALL OTHER DATA IS NOW NULL !!!

    Help? Oh – Im using Studio Express and MS SQL 2005 dbs

    TIA – eric

    • OOPS – Just realized that 3. above (INSERT INTO Products (stock) SELECT stock FROM Products_2) is actually appending data –

      It doubles the # or rows, which are all NULL except the “stock” column, which it is picking up from the “Products_2″ table. The original rows are still there, unaltered.

  264. Hi
    i m asking one question that i have a grid view with bubble event like check box .and i want to retrieve all the records on the another page when i checck box and if i select more than one check box then all the name of we want display in the dropdown list another page Please solve my problem imidiatly.
    I m waiting!!!!!

  265. Hi All

    I would like to write a trigger on insert or update

    so it will write the recored from the table in the MS SQL database to the table in Oracle

    can some one help me in that

    the trigger must be written inthe MS SQl database and it should write the record to the Oracle table


  266. I have 6 table

    table 1 (1id, 1Name)
    table 2(2id, 2Name)
    table 3 (3id,3Name)
    table 5 (1Name, 2Name, 3Name, 4Name)

    now i want table6 to be

    table6(1id,2id,3id,4id) how can i do that

    can anyone please help me with this

  267. Hi all,

    i have a problem with this statement:

    INSERT INTO buchung_betr(BUDAT)
    SELECT BUDAT from Belegdaten

    sql server says: invalid column name although the column BUDAT exists in my database.

    Please help!

  268. hi pinal and madhivaan sir

    this time i am doing job in jla_logistics pvt ltd

    i am database handler in this company

    this company used tally 7.2 i want to say that

    tally means maintain full information of company a/c

    may i use sql server 2005 in this company

    if yes some me give example

    company profile -transportation

    may use sql server 2005 in this company

  269. hello pinal and madhivaan sir,
    First, sorry about my poor english.
    If it’s possible, I want to know that:

    How can I use the statement insert into table1 (field1,field2,field3,…) select variable1, table2.field2, table2.field3,….. when my table1 has a PK and autoincrement field? (Attention: field1 isn’t the PK and autoincrement field).
    When I execute the query returns:
    Subquery returned more than 1 value. This is not permitted when the subquery returns follows =, !=, <, , >= or when the subquery as used as an expression.
    I’m using SQL Server 2005 Express.

  270. i’m trying to make a procedure based on below instruction but it seems its not working can you help me.

    create a parameter that will accept a target database that user wish to saved his output table.
    Finally, call the stored procedure in other database aside from master.

    Hope you can help me thanks

  271. Hi Sir,

    Here My Table like this:
    First i’m creating table for Empdetails and defining datatypes and values like this:

    Insert into Empdetails values(Eid int primary key,Ename varchar(20) ,Designation varchar(20) ,Doj int,Salary int,Deptno int)

    I’m creating Another Table like EmpInformation

    From the above table changing like
    “Here Salary datatype is integer but i want change “double” &
    Doj datatype convert to DateTime format”

    Insert into Empdetails values(Eid int primary key,Ename varchar(20) ,Designation varchar(20) ,Doj DateTime,Salary Double,Deptno int)

    And copy the all records from Empdetails table to new EmpInformation table

    It’s Possible r not
    plz Reply me

  272. hi pinal sir.

    plz help me

    how to do this.

    i have two database

    database table

    mm emp
    mn emp1

    database mm hava a table emp and have a data this
    id name address
    1 mohit delhi
    2 mona haridwar

    and second database emp is blank

    id name address

    so i want to data transfer one tabel to another database.how to do this.i am using this
    select * into Northwind.dbo.employee from pubs.dbo.employee

    but it’s not working

    any guy’s plz help me. or send me code

  273. The “Method 2 : SELECT INTO ” is only a school case because none constraints are copied (no primary key, no default, no foreign key).
    In the true life, every table has constraints.
    I think the best to said is : “never use this method instead script the initial database then use Insert Into ie the Method 1″.

  274. hi, I have to unit five tables into a new table in sql server management studio 2008
    Insert into oldtest select * from
    (select * from newtest union all select * from testdaytop union all select * from testdaytrp union all select * from testdayfrp union all select * from testdayfip)
    can anybody help

    • It should be

      Insert into oldtest
      select * from newtest union all select * from testdaytop union all select * from testdaytrp union all select * from testdayfrp union all select * from testdayfip

  275. Hi madhivanan

    You suggested query is not working all the tables have same columns and actually I have to extract data from MYSQL server which I extracted in above five tables and now I want to join them in a single table in SQL server management studio 2008

    Insert into oldtest
    select * from newtest union all select * from testdaytop union all select * from testdaytrp union all select * from testdayfrp union all select * from testdayfip

  276. Hi,

    I need to generate a database script with table values also.. that is table creation + insert values like pubs database..

    pls guide

  277. I have two instance of sql server 2005 in the same box.
    When i create recode in table of first instance at same time using trigger i want to transfer that record in another instance of sql 2005.

    Waiting for your reply.

  278. Hi Dave,
    Is is possible to insert data from a table to another, where these two tables are located in different databases, and databases are located in different servers?
    basically inserting from one server to another.

    source table is in a remote server and destination table is in the local server.
    also I connect to these 2 servers using different connections.

  279. Hi I’ve one table called HISTORY. This table is heavily used in production environment. Now i Want to create another table of same structure. Also i want to move data from this table to another created table. also created table must be updated by HISTORY.

    plz help!!!!!!!!

    Its a kind of Archiving of table.

      • creating a table is not a problem………. moving data dynamically is required.

        I ll really appreciate if u can help out that…. let me give u one more info that..this table(history) contains more than 5 lakh rows and constantly increasing.


        History more than 5 lakh record(rows) and increasing.

        now i want to move data from history table to New_histroy table….and truncating the data from history @ weekly basis.

        so whatever data come to history table will be moved to new table @ sunday 00:00 am. deleting the data which is moved from history table.

        History table gets data 24/7.

  280. @varun.

    5 Lakhs Records is very normal. But size of the table for holding those 5 Lakhs Record matters…

    You already finalized that you want to go with Archiving ???

    Table Partition would be a good option.

    Archiving Solution,
    Even after you acrhive History Table, you need to update Indexes. Do you re-organize or rebuild Indexes on that table. Because lot of inserts and delete creates lot of fragmentation.

    Depending on what locks you are using when inserting data into table, you will be able to delete data from history table after archiving.

    Look at this sample Archiving procedure below


    I am not clear if I answered your questions, in case not, please ask your question clearly.

    ~ IM.

    • @Imran Mohammed

      this is good…thanks……… scenario is like this………..
      I’ve table called HISTORY…..it has more than 500000 rows….. what i want is whenever rows reaches more than 500000 …..move 250000 rows to ARCHIVED_HISTORY. Also delete the data from HISTORY table which has moved.

      Moving of rows shud be LIFO.

      Environment is Production Database.

      Please send the procedure.

      PLZ help.

    • @ Imran Mohammad

      I have two Tables Subject and subjectDim(SubID,SubName, Level,Total Marks) i have to insert data into SubjectDim where SubID is an auto number and subName will be selected from Subject and Level is ‘HSSC1′ and TotalMarks are 100.
      I need Urgent help plz reply me soon how to write this Query.
      Thanks in Advance

  281. i have two tables: ITEM_HISTORY and ITEM




    I need to get the ITEM_IDs for a SOURCE_ID for a time interval of like every few days..and i need to get the records inserted in a new table called PH_ABC with ITEM_ID, PART_ID, TIME_STAMP,YEAR_CODE, JULIAN, LAST_UPDATE

    here, i cant loop the items and i am getting only the last item from the whole set of items. and i dont want to get the duplicates.

    ALTER PROCEDURE [dbo].[insertitems](@PSourceID varchar(100),@PStartDate datetime,@PItemID varchar(100) out)
    DECLARE @VItemID varchar(100),@VTimeStamp datetime,@VJulian varchar(5),@VJulian1 varchar(100), @VYearPart int, @VDayPart int, @VZeroDate datetime, @VDate datetime,
    @VStartDate datetime, @VEndDate datetime

    SET @VStartDate = @PStartDate
    SET @VEndDate = DATEADD(dd,5,@VStartDate)


    IF((CONVERT(varchar(10),@VTimeStamp,120)>= @VStartDate) AND (CONVERT(varchar(10),@VTimeStamp,120) <= @VEndDate))
    TIME_STAMP = @VTimeStamp

    PRINT 'ITEM' + ':' + @VItemID

    VALUES(@VItemID, @VTimeStamp)
    SET @VJulian = SUBSTRING(@VItemID,3,5)
    SET @VJulian1 = '20' + @VJulian
    SET @VYearPart = CAST(LEFT(@VJulian1,4) AS int)
    SET @VDayPart = CAST(RIGHT(@VJulian1,3) AS int)
    SET @VZeroDate = DATEADD(yy,-1 * DATEDIFF(yy,0,GETDATE()),GETDATE())
    SET @VDate = DATEADD(dd,@VDayPart – 1, DATEADD(yy,@VYearPart-YEAR(@VZeroDate),0))

    PRINT 'Date' + ':' + CAST(@Vdate AS Varchar(100))

    DECLARE @VModelID varchar(100), @VLastUpdate datetime

    @VLastUpdate = LAST_UPDATE
    LOCATION = @PSourceID

    PRINT @VModelID

    SET PART_ID = @VModelID,
    JULIAN = @VJulian,
    YEAR_CODE = @VYearPart,
    LAST_UPDATE = @VLastUpdate
    TIME_STAMP = @VTimeStamp

    PRINT 'No items with that location'

    please help me with this….i have been trying for the last 5 days…

  282. Hi,
    Please I have a table with and identity column. When I try to insert 5,000 records from another table with the Insert Into and select statement. It takes forever to finish.

    But when i create a new table without any identity column, this same script doesn’t take a minute to finish.

    Please can you help me

  283. hi,
    i have two datagridview which is dgv1 and dgv2.
    how can i insert row from dgv1 into dgv2. both of them is data bound.

    let say, i have two table in database, beverage1 and beverage2. beverage1 consist of attributes of ID, Name and Price.it also has the data in it.
    in beverage2 consist the same attributes but no data in it.

    In dgv1 will display the beverage1. hence, how should i click entire one row and insert into dgv2.

  284. hi,
    i have two datagridview which is dgv1 and dgv2.
    how can i insert row from dgv1 into dgv2. both of them is data bound.

    let say, i have two table in database, beverage1 and beverage2. beverage1 consist of attributes of ID, Name and Price.it also has the data in it.
    in beverage2 consist the same attributes but no data in it.

    In dgv1 will display the beverage1. hence, how should i click entire one row and insert into dgv2.

    • Hi Anne,

      You can create a collection of datarows from the first grid [from these datarows you can fetch row specific attribute values], then you may add a ‘row click’ funtionality to your grid on which you may add each row instance to the second grid also you can update database from there.

      Hope this gives you an idea to solve your problem.

      If you have any further queries please tell me.


      Suvradeep Banerjee

  285. i have two tables: table1 and table2

    copy one table to another existing table


  286. Hello i have a request for the gurus

    i want to insert rows into a table where data are coming from 2 other tables

    DB_id, DB_name, owner

    server_id, server_name, sql_instance

    server_id, DB_Name, DB_id

    I want to insert data into table3 from data Table1 and table2

    how can i achieve this ??

    Thanks for all your help

  287. I have an xml file. I read the nodes of XML file into cte. My problem is that i need to load this data into an exisiting table and there is a stored procedure which i have to use to get the id value from its output parameter.

    Do i have to loop the cte to insert the record or is there any direct way to insert the cte data into table ?

  288. Pingback: SQL SERVER – CTAS – Create Table As SELECT – What is CTAS? Journey to SQLAuthority

  289. Hi

    I have one table it is having only one colun length is 120 char. the data is like this

    column name ID

    in the above data :-
    d10 is code
    12052011 is date
    B100 is type
    0528 is empcode
    0915 is time
    A100 is type
    0438 is empcode
    0920 is time
    a100 is type
    0436 is empcode
    0920 time

    like the above id fields is having length of 120 chara.

    so, I want tha data is:-

    0528 12/05/2011 B100 0915
    0438 12/05/2011 A100 0920
    0436 12/05/2011 A100 0920

    so, pl help with sql query


  290. Hi All,

    I have a scenario where i need to create a table using only the key columns [some specific columns] of another table. Is it possible?

    Any help would be appreciated.

    Thnaks in advance

  291. i have a table with no keys. the table size is about 40 million. i get a record which first needs to be checked for duplicate values in the table (agaisnt 4 of 10 columns) and if there are no duplicates, then i need to insert the value into the table.

    it take a large time if i use where clause or if exists etc. what alternate ways are there to check duplicates before inserting


    • @Balu.

      Don;t event think of a trigger. That will kill your performance.

      1. I would suggest you use a unique Constraint on the table. That would be the quickest way.

      2. You can use Check Constraint with a combination of Scalar function.

      This Scalar Function should accept column value as input and check for uniqueness in that table, and return either 1 or 0. This returned value will then be checked by Check Constraint defined on that column before inserting record in the table.

      Do you have Clustered Index on this table by any chance ?

      ~ IM.

      • Thank you. The table is indexed . Point is, composite keys are not feasible due to the some limitations and any other combinations i am trying , the result is crossing 8 to 10 seconds which is not allowed in the system. I was thinking about hash tables?

  292. If TestTable is exist already with 0-row or record if you want to insert all records from Contact table then you have to check both the table structure (Number of column and associated datatypes) & finally you can use the below query to insert rows from source table to destination table without mentioning the field or column name.

    INSERT INTO TestTable
    SELECT *
    FROM Person.Contact
    WHERE EmailPromotion = 2

    If any of the table set the replication mode then you have to mention the column name because at the you should not use keyword “*”, below query will help you.

    INSERT INTO TestTable (F1,F2)
    SELECT F1,F2
    FROM Person.Contact
    WHERE EmailPromotion = 2


  293. hello pinal and madhivaan sir,
    my name is happy
    sir i knew sql server,ms-Access i have exp in only for sql in windows xp
    but i have changed my company/ company use windows 7. sql server not downloading of windows 7/ i dont have use of oracle (database) plz sir help me about this topic
    sir i have many querires, backup point,csv into sql server, fuction from our website
    plz give me some oracle site when i learn oracle 10 g,9g, because company use oracle or DB2 universal database, sir plz help me about this topic
    thankyou sir

  294. hello sir,

    i want to transfer my data from one server to another server.
    is this possible. if is this possible then please give me solution how to transfer data from one server table to another server

    Jonish Aggarwal

  295. Hi am santhosh,
    I have a scenario like this..
    I have two tables personal_details and academic_details with same columns S_No attributes and details
    Obviously some rows of both the tables match with each other like name,age etc
    So i want to run a trigger like whenever an update takes place in academic_details it will make the corresponding updates in personal_details only for the rows which are common.
    the problem is that these rows are placed in different positions.
    to give an example the record with values 5 as S_No,educational qualification as attribute and +2 as details is placed at 5th position in academic_details where as the same is placed at 11th position in personal_details,still i want a trigger to update the details column in personal_details……

    Can anyone help me??

  296. I use this code,
    mysql> delimiter //
    mysql> create trigger acatoper after update on academic_details for each row
    -> begin
    -> update personal_details,academic_details set personal_details.details=academic_details.details where personal_details.attributes=acad
    -> end;
    -> //
    the query is ok…runs fine
    but if tryto update the table academic_details,i get the following error..

    mysql>delimiter ;
    mysql> update academic_details set details=’+1′ where S_No=5;

    ERROR 1442 (HY000): Can’t update table ‘academic_details’ in stored function/trigger because it is already used by statement which invoked t
    his stored function/trigger.

  297. Hello Sir,

    what i need is we are creating two tables in the second table some id field is there particular id can be stored in first table how can it possible

  298. I have a two tables, lets say TableA, TableB

    TableA Columns:
    TLID,TaskID, AID, Detail

    TableB Columns:
    TaskID, Title, Subject,AID

    I have added a column AID to TableA, And I want to copy data from TableB which has AID, with same TaskID on both tables,

    Please give a query…


  299. Hello, i could use some help
    I have two tables, Living Room, And Main,

    They both have Work_Order as a primary key,
    I have built the work_Order in Main to be a Foreign key in Living Room

    What i would like to have happen, is when ever a write a new row into Main. I I would like the database to also write a new row in living room, with the same Primary key. (the rest should be null).


  300. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 18 of 31 Journey to SQLAuthority

  301. hello pinal dave , madhivanan sir
    i am happy
    i have one table employee from cms company
    i want to show 25 rows in the table
    then use select top(25) * from employee
    if i want to show 15 to 40 rows in the table
    then i used that but give me error
    plz sir help me this query

  302. hello sir
    i don’t use of procedure and triggers
    i have read many articles but nothing
    view use virtual table means shortuct key
    index use arrange the table ato z
    update use update the table
    but procedure and triggers what use of sql server
    i knew that procedure and triggers are very most important role in sql server
    plz sir help me about this question pinal sir and madhivanan sir

  303. hello pinal dival and madhivanan sir

    i have create table employee
    emp_id int,
    emp_doj datetime

    i used that
    insert into employee values(01,’15-12-2011);
    but doj output is different 3637-1
    plz sir correct my doj problem

    select sum(sal_salary) as total salary from employee

    if i want to sum 10 to 50 cell what will be use of this query
    how is the use of automatic date come in the doj column
    plz sir help me about this query

    thankyou sir

  304. i want to insert a bulk data to another table, but other fields are same… here the column name ‘task_id’ only is same for both table

    i tried like this:

    INSERT INTO [dbo].[active_task]
    ,[task_id] )
    ,select task_id from task)

    but i cant get the solution, any alternative way?

    • INSERT INTO [dbo].[active_task]
      ,[task_id] )
      , task_id from task

  305. Hi All,

    Inserting data from one table to another table without duplication.
    Everybody asking transfer data from one table to another table without duplication of rows. so here is the example which i ve implemented.

    [SSDB].[DBO].[PERSON]=destination table.
    [CONFIGDB].[DBO].[CFG_PERSON] =source table.

    (dbid, tenant_dbid, last_name, first_name, address_line1, address_line2,
    address_line3, address_line4, address_line5, office, home, mobile, pager, fax, modem,
    phones_comment, birthdate, comment_, employee_id, user_name, password, is_agent,
    is_admin, state, csid, tenant_csid, place_dbid, place_csid, capacity_dbid, site_dbid,
    SELECT *
    FROM [ssdb].[dbo].[PERSON]
    WHERE ([CONFIGDB].[DBO].[CFG_PERSON].[DBID] = [ssdb].[dbo].[PERSON].[DBID]

    varun Gaur

  306. hello pinal dival and madhivanan sir

    i used that single quote but error has come(doj datetime)

    create table record
    emp_code int,
    emp_doj datetime,
    emp_city varchar(60),
    emp_age int

    insert into record values (001,’1582011′,’faridabad’,25);

    Msg 241, Level 16, State 1, Line 1
    Conversion failed when converting datetime from character string.

    how to come dafault datetime come in the emp_doj column
    plz give me example

    2 i have search that pagination but noting else
    plz sir i want to sum only for 15 cell to 40 cell in sal_salary column

  307. Is my query correct.. Its not working.. please help..

    insert into [TblPlayer] (OldId,RegNo,FirstName,MiddleName,LastName,FathersName,DateOfBirth,Category,Phone,Address,
    select pid,pid,fname,mname,lname,fathers_name,category,dob,category,contact,address,village,dor
    from [players]

  308. hello pinal dave and madhivanan sir

    my sql server set up is not working properly
    i want to free full download sql server 2005 version
    plz sir give me site of sql server 2005 version
    sir its very urgent
    plz sir help me

  309. hello madhivanan sir

    i have download sql servre 2005 express edition but management studio has not come,i have no idea which part download of sql server, i have tried that but sql server setup has not come, plz sir which part download of sql server setup include management studio, sorry sir i am irritating you, plz sir help me about this question

  310. Hi Dave,
    insert into table2
    select col1,col2,col3.. from table1 where col=’xyx’

    its not working for me in some situations.
    My table1 contains some null columns also,
    its working when my col2 and col3 is with data and if these two columns are null its not working for me.
    its showing x rows affected. but the data was not moving

  311. Hi experts,

    I have 1 orders table

    orders table
    OrderID OrderDate ProductName QtySold Amount
    1 11/11/11 item1 2 70
    2 11/11/11 item2 7 90

    I want to copy this details on another table Order details table

    like this :–

    Order details table
    OrderdetailsID OrderDate CustomerName ProductName QtySold Amount
    1 11/11/11 Jane Austen item1,item2 9 160

    What will b the Query for this ??

    Thanks in advance

  312. Hi All,
    I have one requirement like from 1 database i have to copy the data from some tables to another server database(taking backup on during night on every day.But not all tables).
    Here the table structure in two servers are same.But there is a primary key and foreign key relationship.So, i cannot use direct query to copy the data.Because while copying it will say duplicate records cannot insert.
    I have created a SP in that i will drop tables first and select * into and adding constraints to that tables. It will work fine.But taking long time.
    If you have any idea/option that will take less time, let me know.

    • Why dont you update existing data and add new data? The general logic is

      update t1
      set t1.col1=t2.col1, t1.col2=t2.col2,…
      from table1 as t1 inner join table2 as t2 on t1.keycol=t2.keycol

      insert into table1 (columns)
      select columns from table2 as t2 where not exists
      (select * from table1 where keycol=t2.keycol)

        create trigger railways_result_trigger on railways_result
        after insert
        if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, SYSUTCDATETIME (), 103)))
        delete from current_railways_result
        delete from railways_result
        delete from current_railways_result
        insert into current_railways_result select *,dense_rank()OVER (PARTITION BY exam_date ORDER BY stu_total_marks DESC)stu_rank
        FROM railways_result
        insert into railways_result_backup select * from current_railways_result

        sir, i m trying to delete all record from current_railways_result,railways_result and insert into railways_result
        when insert into particular table both table records delete but not inserted into

        plz help me sir
        tanks in advance sir..

          create trigger railways_result_trigger on railways_result
          after insert
          if exists(select distinct update_date from railways_result where update_date!=(SELECT CONVERT(date, SYSUTCDATETIME (), 103)))
          delete from current_railways_result
          delete from railways_result
          delete from current_railways_result
          insert into current_railways_result select *,dense_rank()OVER (PARTITION BY exam_date ORDER BY stu_total_marks DESC)stu_rank
          FROM railways_result
          insert into railways_result_backup select * from current_railways_result

          i m trying to delete data from current_railways_result, railways_result before
          new insert

          data deleted from both table,but when i going to insert new data today first time data not insert in table,second time inserted in table plz help me sir
          using trigger

  313. Hello sir I am new to SSIS and I have very simple task to do that I can easily do in SQl 2008, but for my work I have to make in SSIS.
    I have one source and destination table both in SQl 2008 and I want to Insert data from source table to destination. I tried a lot of work around in SSIS but not able to do that , Can you please tell me how to do that in SSIS. I will really arrpiciate you. here is my script that is working well in SQL.

    INSERT INTO [EnrollmentDM].[dbo].[table 1]
    from table 2 where SEGMNT_ID =’ISA’)

  314. thank for sharing

    I need to create a new table called “costumer” that included some of columns from the “user table”, and also “project table”. I built my suppliers table with spesific column names and I need to fill its column by using data of the other tables. Finally I am trying to finish; when user create a new account and project, the costumer table automatically fill with some of other two tables varieties with different column names.
    INFO: I have three different user types such as “suppliers”, “costumers”, “managers”. I am holding their information(include user types) in one table called users.

    Please help me if you have free time.

  315. sir
    I want to insert column form table into another table
    for this I wrote like this
    SELECT column_name(s)
    INTO new_table_name
    FROM old_tablename
    but i am getting error like ‘undeclared variable : name of new table ‘
    plZ suggest me how to do this

    Than U

  316. Dear Mr.Pinal,

    We need your help, we have 3 table : Sales Last Year, Sales Actual, and Sales Target.

    and then we need to get result for new table, the new table is “Sales YTD”,
    Sales YTD is cumulative from Sales Actual.

    could you give me the query for Sales YTD(Year-To-Date).

    [email removed]


  317. using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    using System.Data.SqlClient;
    using System.Data;

    namespace Assignment.Classes
    public class DBCon
    public static string con = “Data Source=HP-PC;Initial Catalog=AssignmentDB;Integrated Security=True”;
    //s public static string con = “Data Source=;Initial Catalog=AELANK;User ID=sa;Password=sssa;Connection Timeout = 2000000″;

    //public static string con = “Data Source=HASHENDRA-PC\\SQRSERVER;Initial Catalog=AELANK;Integrated Security=True”;

    public static SqlConnection conn = new SqlConnection(con);
    public static SqlCommand comm = new SqlCommand();
    public static SqlDataAdapter adapt = new SqlDataAdapter();
    public static DataSet dtset = new DataSet();
    public static string user;
    public static string department;
    public static string custommer;
    public static string selectedDate;
    public static string FromDate;
    public static string ToDate;
    public static string email;
    public static string ddlStateSales;
    public static string ddlStateCus;
    public static string SalseManager;

    public DBCon()


    public static DataTable SQLDTT(string strSQL)
    if (conn.State.ToString() == “Closed”) { conn.Open(); }

    SqlCommand comm = new SqlCommand();
    SqlDataAdapter adapt = new SqlDataAdapter();
    comm.CommandTimeout = 0;
    comm.CommandText = strSQL;
    comm.Connection = conn;
    adapt.SelectCommand = comm;

    DataTable DataTable = new DataTable();

    //comm.CommandText = strSQL;
    //comm.Connection = conn;
    //adapt.SelectCommand = comm;
    //dtset = new DataSet();
    return DataTable;


    public static DataSet SQLDT(string strSQL)
    if (conn.State.ToString() == “Closed”) { conn.Open(); }

    SqlCommand comm = new SqlCommand();
    SqlDataAdapter adapt = new SqlDataAdapter();
    comm.CommandTimeout = 0;
    comm.CommandText = strSQL;
    comm.Connection = conn;
    adapt.SelectCommand = comm;

    DataSet dtset = new DataSet();

    //comm.CommandText = strSQL;
    //comm.Connection = conn;
    //adapt.SelectCommand = comm;
    //dtset = new DataSet();
    return dtset;


    public static int GetNumOfRec(string strSQL)
    /// Use for get No of Records in SELECT command
    int intResult = -1;
    if (conn.State.ToString() == “Closed”) { conn.Open(); }

    strSQL = DBCon.SQLFormat(strSQL);
    SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
    intResult = (int)sqlCmd.ExecuteScalar();

    return intResult;
    //catch (Exception objError)
    // //MessageBox.Show(“System Error – ” + objError.Message.ToString(), “Application Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
    // return -1;

    public static string SQLFormat(string strSQL)
    strSQL = strSQL.Replace(“\r”, ” “);
    strSQL = strSQL.Replace(“\n”, ” “);
    strSQL = strSQL.Replace(“\t”, ” “);
    strSQL = strSQL.Replace(” “, ” “);
    return strSQL;

    public static string SQLER(string strSQL)
    if (conn.State.ToString() == “Closed”) { conn.Open(); }

    strSQL = SQLFormat(strSQL);
    SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
    string strResult = sqlCmd.ExecuteScalar().ToString();

    return strResult;


    public static SqlDataReader DataReader(string strSQL)
    if (conn.State.ToString() == “Closed”) { conn.Open(); }
    strSQL = SQLFormat(strSQL);
    SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
    SqlDataReader dataRed = null;
    sqlCmd.CommandTimeout = 0;
    dataRed = sqlCmd.ExecuteReader(CommandBehavior.CloseConnection);
    return dataRed;

    public static SqlDataReader oSQLER(string pSQL)
    if (conn.State.ToString() == “Closed”) { conn.Open(); }

    SqlDataReader objReader;
    SqlCommand objCommand;
    // conn.Close();
    // if (conn.State.ToString() == “Closed”) { conn.Open(); }
    objCommand = new SqlCommand(pSQL, conn);
    objReader = objCommand.ExecuteReader();
    return objReader;

    public static int SQLCX(string strSQL)
    if (conn.State.ToString() == “Closed”) { conn.Open(); }

    strSQL = SQLFormat(strSQL);
    SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
    sqlCmd.CommandTimeout = 0;
    int intResult = sqlCmd.ExecuteNonQuery();

    return intResult;
    public static int SQLCXRemoveRow(string strSQL, string item)
    if (conn.State.ToString() == “Closed”) { conn.Open(); }

    strSQL = SQLFormat(strSQL);
    SqlCommand sqlCmd = new SqlCommand(strSQL, conn);
    sqlCmd.CommandTimeout = 0;
    sqlCmd.Parameters.AddWithValue(“@value”, item);
    int intResult = sqlCmd.ExecuteNonQuery();

    return intResult;


  318. ur code is work well…
    but image datatype it gives an errer..
    it’s copies the rows … but images are stored in Table not displayed in image control……
    plz reply ….

  319. Hey Dave,
    It would be great if you could add an update to your post to explain that this will not work if there is a TRIGGER on that table which does not take multirow operations into consideration. The user can of course consider disabling/re-enabling the trigger as part of their script.



  320. HI Pinal,
    we are using sql server 2005,actually frequently we have to restore production database to acceptance environment ,sometimes database in acceptance have more tables than prod database. in that case i just use import\export wizard or select into command to transfer extra tables in acceptance to a temporary created database in same server, but while doing that size of the table in temp database is less than actual one.So ,while looking table structure there is no index’s,primary keys,foreign keys etc. could ypu please post your suggestion

  321. Hello sir…! I have little problem about database in server 2005..!
    I have a Table1 and there are 3 columns like, EmpID,EName,Designation..
    Designation column is complete NULL.. here i want to add values from different table which is Table 2. there are 2 columns like EmpID and Designation…!
    so what will be queary to add Designation values in Table1 from Table2 Designation column on the basis of EmpID!

  322. Hi,

    I am having three tables named like tbl_report1,tbl_report2,tbl_customer .my issue is i want to combine these tables data and structure into one table like tbl_company.is it possible?.if possible in the sense can you please give me the suggesstion.please do the needful.
    tbl_report1 columns:
    invoiceno(foreign key fk,particulars,quantity,perprice,amount,orderno,compname,invoicedate,ondate,invoiceid.

    tbl_report2 columns:

    from the above three tables i want to copy their data and structure into tbl_company.how can i proced.

  323. Hi All

    I’m inserting Data From One Table to Another in different databases in the same server, after this is done i have to delete the copied data from the source table. The records are selected based on few constraints in the WHERE clause This table has several dependent tables that also have to be moved.

    I have chosen the INSERT INTO…SELECT method because:

    1) I can monitor errors during the process
    2) I can use transactions and error handlers for testing purposes and to ensure all or nothing will be copied.

    I’m having the following dilemas because of this choice:

    1) Performance is not the best.

    2) Where to run the delete operation? A) Right after every single INSERT statement B) After all thousands INSERT statements.

    3) If i need to COMMIT/ROLLBACK INSERTs on target table and DELETEs on source tables, I wonder if SQL Server is able to handle both of them within the same TRANSACTION statement, or i have to first finish one transaction and then change database context (USE statement i.e.) to commit the operations in the other database.

    In the end, seems that for this kind of problem, the approach i have chosen has a high cost since the numbers of inserts are about 228000 (I’m copying one row along with dependent rows per each iteration of a loop), this makes me wonder if i shouldn’t just copy records from one table at the time, using INSERT INTO… SELECT …WHERE ID IN (SELECT IDS FROM SOURCE TABLE)

  324. hello sir my name is happy.

    Q1. how to create some table backup in sql server 2005
    becuase i knew that if full backup use database right click tasks and go for backup, translog backup
    Q2. what is use of stroed procedure in sql server. what is main differnce between view and stored proceduure and fuctions
    plz sir help me about question

  325. i want to move xml to sqldatabase they move propely bt every time they fetch the old data also..i want to insert only updated value everytime nt the old 1’s
    /My Code is here
    string xmlfile = Server.MapPath(“~/ResumeFolder/Export1.xml”);
    using (SqlConnection con = new SqlConnection())
    using (DataSet dsTemp = new DataSet())

    using (DataTable dt =dsTemp.Tables[0])
    using (SqlBulkCopy sb = new SqlBulkCopy(con))
    sb.BatchSize = 50;
    sb.DestinationTableName = “TBQUANTITY”;
    sb.ColumnMappings.Add(“Id”, “Id”);
    sb.ColumnMappings.Add(“ProductName”, “ProductName”);
    sb.ColumnMappings.Add(“CategoryName”, “CategoryName”);
    sb.ColumnMappings.Add(“Country”, “Country”);
    sb.ColumnMappings.Add(“OrderDate”, “OrderDate”);
    sb.ColumnMappings.Add(“OrderTime”, “OrderTime”);
    sb.ColumnMappings.Add(“UnitPrice”, “UnitPrice”);
    sb.ColumnMappings.Add(“Quantity”, “Quantity”);



  326. Hi there very nice website!! Man .. Excellent .. Wonderful .. I will bookmark your blog and take the feeds also?I am glad to find so many helpful information here in the post, we want develop more techniques on this regard, thank you for sharing. . . . . .

  327. Hi ,Can anybody know how to compute minimum value in a cube.As while cube uploads it data, it loads zeros at the places of Null …I want to calculate minimum value excluding these zeros. Thanks

  328. Hi everyone.
    I have two tables ,
    table1 and table2
    suppose table 1 contains emp id proj id and table 2 contains emp id,project id and updated date.the updated date of 2nd table will only change when there is change in project_id on 1st table.

    Kindly help

  329. Can anyone tell me how can i insert value from one table to another with other values like….

    @status int

    insert into Table2(FirstName,LastName,Status)
    select FirstName,LastName from Table1,@status

    I don’t know exact syntax kindly help me…

  330. Can Anyone Help Me…?

    In my problem data of one column in a table has to be moved to another column of same table depending on the time. Means for every day at 12:00Am data of column1 has to be moved to column2.

  331. Hi, i am using sql for databse and C# in visual studio……i want to update table1 which contains 4 columns(eg Name, CNIC, Attendance, Date) i want to select Name and CNIC culimns’ value from table2 and and want to enter values of attendance and date my self….please tell me how should i do this?
    i have try this..
    INSERT INTO Dailyreport (Name,CNIC,Attendance,Date) VALUES (SELECT Name,CNIC FROM Registration,(‘Present’, ‘” + date + “‘)) WHERE CNIC=” + n.ToString();
    but this does not work……….
    please help me………

  332. hello sir,
    i have a doubt regarding inserting records into two tables and my doubt is that …….

    let us consider the following two tables,

    the first table is “pradeep” …

    SQL>select * from pradeep;

    — ——————– ———-
    1 pradeep 1
    2 prasad 2
    3 lavanya 3
    4 tulasi 4
    5 ravi 5

    my second table is … “prasad” …

    SQL> desc prasad

    Name Null? Type
    —————————————– ——– —————————-






    [email removed]

  333. Hi, i need to insert data from table in one database to another database with some where conditions. Both tables have same columns (tablename,column name ,datatype etc).


  334. I have searched and tried a lot can anyone help me.
    my case is very complicated. I need to insert all the column values from a select * statement in one column of another table in multiple rows.
    hope you all understood what i want.

    i need each columnar data of table1 as 3 record in temptable.
    any solution?

  335. hello every one i have a doubt…..can u plz clarify it………………….

    my doubt is that………………..

    How to create a new table by copying particular records from another base table depending on the row number of the base table……….for example consider the following tables.

    table 1: pradeep (base table)

    empname empno1 loc
    azar 9156 hyd
    sachin 9458 chennai
    shewag 1245 banglore
    dravid 2589 vizag
    pradeep 2122 guntur

    table 2: prasad (which has to be created by copying the records from the base table)

    the table2 prasad should be as mentioned below:

    empname empno1 loc
    azar 9156 hyd
    sachin 9458 chennai
    shewag 1245 banglore

    Motto: i have to create a new table (i.e., Prasad) by copying only the first
    3 records from the base table (i.e., pradeep), it is very easy if we know
    the relevant data .


    (1). I have to copy the relevant data from base table to new table
    depending on the row number but not the data which is stored in
    the base table,i assume that i don’t know the data until & unless it
    is copied into the new table. So this operation has to be performed
    depending on the row number.
    (2) It is very easy to copy structure & records separately into new table
    ,but here i want to perform both operations (i.e.,copying structure
    & copying records) had to be done at a time.
    (3) Here there is a possibility to copy multiple records from base table
    to new table.

    ++++++ hope every one understood my requirements …………………………………….

    thanks in advance ……………… am waiting for your replies…………………

    [email removed]

    (if any one has any doubt in my requirements or any doubts in my explanation ..plz free to ask me)

  336. Hi Dav,

    I have 3 tables with the columns below



    3. eCatalog_Catalog_Item_Approval_Type

    So i want to update the 3rd table with 1st table coulmn “Catalog_ID” as 3rd “Catalog_ID” and from 2nd table all records to 3rd table based on the “ISO_alpha_2_country_code”. (County code as “US” or “MX”, or “CA”)

  337. I add two columns in my existing table(table A) with ten columns but now I am wondering how can I insert values into these new columns with insert and select statement as I need to extract values for these two new columns from another table (table B). There are two similar columns between table A and table B.

  338. Thank you very much for this helpful and clear article :)!

    This other article you posted (and the comments in it) was helpful also and kind of goes hand in hand with this current one:


    I experienced the “explicit value for the entity column” issue and went to that article :).