Feed on
Posts
Comments

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.

Method 1 : INSERT INTO SELECT
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
GO
    ----Create TestTable
CREATE TABLE TestTable (FirstName VARCHAR(100), LastName VARCHAR(100))
    ----INSERT INTO TestTable using SELECT
INSERT INTO TestTable (FirstNameLastName)
    SELECT FirstNameLastName
    FROM Person.Contact
    WHERE EmailPromotion 2
    ----Verify that Data in TestTable
SELECT FirstNameLastName
    FROM TestTable
    ----Clean Up Database
DROP TABLE TestTable
GO

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
GO
    ----Create new table and insert into table using SELECT INSERT
SELECT FirstNameLastName
            INTO TestTable
    FROM Person.Contact
    WHERE EmailPromotion 2
    ----Verify that Data in TestTable
SELECT FirstNameLastName
    FROM TestTable
    ----Clean Up Database
DROP TABLE TestTable
GO

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://www.SQLAuthority.com)

161 Responses to “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. How to use the Insert into select along with direct data coming from the form in SQl Server 2005?


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


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

    thx.


  5. Hi devan

    select * into Northwind.dbo.employee from pubs.dbo.employee

    I think this would work for u

    thx,
    Karthi.


  6. Hello sir, I want the producure to transfer the data from one to another……….

    Plz send the query

    Regrads
    Sriram


  7. 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))
    AS
    – 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) )
    Begin

    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

    End

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

    GO


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


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

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

    Thanks in advance for your help!


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

    INSERT INTO MRA..T_MRA_MBR (MBR_ID, NCPDP_PRVDR_NUM)
    SELECT MBR_ID, NCPDP_PRVDR_NUM
    FROM P350..T_AH_MBR
    WHERE MBR_ACTV_IND = ‘Y’ AND MBR_ID NOT IN (SELECT MBR_ID FROM MRA..T_MRA_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.


  11. Hello,
    How Can I copy my local sql server 2005 table’s data to my host database?
    Thanks


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


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

    Ndindi


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


  15. hi pinal
    i just want to know the command

    what is command to populate data from one table to another table

    pls help me out

    Regards
    Dhirendra
    mumbai


  16. i want to import the data into a table from other table which is in different Database please help me out


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


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


  19. Hi,
    I want to insert data from one server database table
    to another server database table in vb6 code
    any one can help me


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

    Regards,
    jigar


  21. 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.”,
    this, SecurityEventCodes.EVENT_CODE_LOGIN_ATTEMPT_BY_LOCKED_USER);
    evt.Raise();
    Login1.FailureText = “Your account is locked. Please contact administrator.”;
    }
    }


  22. on December 17, 2007 at 7:43 pm manvendra Singh

    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.


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


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

    like

    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.

    Thanks,
    Vishakha


  25. HOW TO INSERT VALUES FROM ONE TABLE TO ANOTHER AND ADD THE TIME (GETDATE) AFTER A FORM SEARCH WITH FRONT PAGE


  26. Hi Suresh(23),

    change table names it should work for you.

    select t1,sum(qty)
    from
    (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

    cheers,
    anand.


  27. Hi suresh,

    more details if you are expecting below result:

    select t1,sum(t1_qty1) t1_qty,sum(t2_qty2) t2_qty
    from
    (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

    cheers,
    anand.


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

    cheers,
    anand.


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

    cheers,
    anand.


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


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


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


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

    Regards
    Lambda Swahili


  34. Hi……
    consider i hav three tables
    table1(t1col1,t1col2)
    table2(t2col1,t2col2)
    table3(t3col1,t3col2)
    i need to enter t1col1 and t2col2 in t3col1 and t3col2 respectively….

    plz guide me to implement this using insert select statement

    Regards,
    Bhanu


  35. Thanks, just what I needed.


  36. Hi Dave,

    Nice Blog. Helped Me alot..

    Thanks ;-)


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


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


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

    Insert into finalxlsitems1
    select * from
    OPENROWSET(’SQLOLEDB’,
    ’server’;’sa’;”,
    ‘SELECT *
    FROM server.reportserver.dbo.finalxlsitems1′
    )


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

    or

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

    THANKS


  41. Hi,
    I want to insert data from one server database table
    to another server database table in vb6 code
    any one can help me


  42. I want to insert data from one server database table
    to another server database table in oracle.
    pls help me…


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


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

    Thanks


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


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


  47. And I also have already use indexes…


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


  49. hey I got it.Thanks for the post.


  50. Hi

    Can we insert data from one table to other which is not the same database?


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

    INSERT INTO WC_WCLI_RETAIN
    (STOP_ORDER_NO, STOP_CRD, Phone_Number, order_type, ORD_RCVD_DT, LINE_TYPE, ROBOT_STATUS)
    SELECT ORDER_NO, CUST_REQD_DT, NATIONAL_NUMBER, ORD_CRT_TYPE, ORD_RCVD_DT, ‘C’, ‘RECEIVED’
    FROM dbo.HADES_CPS HC
    WHERE [PRODUCT_CODE] = ‘A72911′ AND [ORD_CRT_TYPE] = ‘S’ AND [ORD_RCVD_DT] =
    (SELECT CONVERT(VARCHAR(10), GETDATE(), 111))) AND ORDER_NO NOT EXISTS
    (SELECT 1
    FROM WC_WCLI_RETAIN WCR
    WHERE WCR.STOP_ORDER_NO = HC.ORDER_NO)

    Help !!!!


  52. on March 24, 2008 at 12:18 pm G.Prabhakaran

    hi,

    i want check duplicate data while insert into sql table

    Can any one help me how to do that


  53. Hi GC (51),

    What is the error you are getting?? Would help to solve your problem.


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


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

    Thanks
    kiran.


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


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


  58. i want to copy data from one column to another column inside a table.anyone can help me?
    jewel


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


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

    CREATE TABLE UEXAMPLE ( EID INT, EID2 AS EID )

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

    INSERT INTO UEXAMPLE ( EID ) VALUES (2)

    -> Simple insert statement.

    SELECT * FROM UEXAMPLE

    -> Simple select statement.

    hope this works.


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


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

    CREATE TABLE UEXAMPLE1 ( EID INT, EID1 INT , EID3 INT )
    CREATE TABLE UEXAMPLE2 ( EID INT, EID1 INT , EID3 INT )

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

    INSERT INTO UEXAMPLE2 VALUES ( 1,2,3)
    INSERT INTO UEXAMPLE2 VALUES ( 2,2,3)
    INSERT INTO UEXAMPLE2 VALUES ( 3,2,3)

    -> I inserted three rows in Uexample2

    SELECT * FROM UEXAMPLE2

    -> checking values using select statement.

    INSERT INTO UEXAMPLE1 VALUES ( 1,3,4)
    INSERT INTO UEXAMPLE1 VALUES ( 2,4,4)
    INSERT INTO UEXAMPLE1 VALUES ( 3,4,5)

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

    SELECT * FROM UEXAMPLE1

    -> Checking the values in Uexample1.

    INSERT INTO UEXAMPLE1 (EID1, EID3)
    SELECT EID1 , EID3
    FROM UEXAMPLE2
    WHERE UEXAMPLE1.EID = UEXAMPLE2.EID

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

    UPDATE UEXAMPLE1
    SET UEXAMPLE1.EID1 = UEXAMPLE2.EID1 , UEXAMPLE1.EID3 = UEXAMPLE2.EID3
    FROM UEXAMPLE2
    JOIN UEXAMPLE1 ON UEXAMPLE1.EID = UEXAMPLE2.EID

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

    Check the output.


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

    CREATE TABLE UEXAMPLE1 (EID INT, STATE CHAR(2))
    CREATE TABLE UEXAMPLE2 (EID INT, STATE CHAR(2))
    CREATE TABLE UEXAMPLE3 (EID INT, STATE CHAR(2))

    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.

    CREATE TRIGGER TR_UEXAMPLE1
    ON UEXAMPLE1
    FOR INSERT
    AS
    DECLARE @EID INT
    DECLARE @STATE CHAR(2)
    SELECT @EID= EID, @STATE= STATE FROM INSERTED
    INSERT INTO UEXAMPLE2 VALUES (@EID , @STATE)
    INSERT INTO UEXAMPLE3 VALUES (@EID , @STATE)

    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

    CREATE TRIGGER TR_UEXAMPLE2
    ON UEXAMPLE2
    FOR INSERT
    AS
    DECLARE @EID INT
    DECLARE @STATE CHAR(2)
    SELECT @EID= EID, @STATE= STATE FROM INSERTED
    INSERT INTO UEXAMPLE3 VALUES (@EID , @STATE)

    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 :

    INSERT INTO UEXAMPLE2 SELECT * FROM UEXAMPLE1

    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 !


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

    CREATE TABLE UEXAMPLE1 ( COLA INT, COLB INT )
    CREATE TABLE UEXAMPLE2 (COLA INT , COLB INT, COLC INT, COLD INT)

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

    INSERT INTO UEXAMPLE1 VALUES ( 1 , 123)
    INSERT INTO UEXAMPLE1 VALUES ( 2 , 223)
    INSERT INTO UEXAMPLE1 VALUES ( 3 , 323)

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

    CREATE PROC USP_UPDATE
    AS
    DECLARE @COL INT
    DECLARE @COLA CURSOR

    SET @COLA = CURSOR FOR
    SELECT COLA
    FROM UEXAMPLE1
    OPEN @COLA
    FETCH NEXT
    FROM @COLA INTO @COL
    WHILE @@FETCH_STATUS = 0
    BEGIN
    IF EXISTS (SELECT COLA FROM UEXAMPLE2 WHERE COLA= @COL)
    UPDATE UEXAMPLE2
    SET UEXAMPLE2.COLB = UEXAMPLE1.COLB FROM UEXAMPLE1 WHERE UEXAMPLE1.COLA= @COL AND UEXAMPLE2.COLA= @COL
    ELSE PRINT convert(varchar(10), @col )+ ‘ DOES NOT EXISITS IN UEXAMPLE2′
    FROM @COLA INTO @COL
    END
    CLOSE @COLA
    DEALLOCATE @COLA
    GO

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


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


  66. 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,
    author,author_2,title,yr,imprint,guide_1,guide_2,
    place,source,clnt_spon,discipline,type,remarks,abstract)
    values (1101,’document’,'yes’,(select class_no,acc_no,author,author_2,title,yr,imprint,
    guide_1,guide_2,place,source,clnt_spon,discipline,type,
    remarks,abstract from document_v where acc_no=’D 1173′))

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


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

    Thanks!


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

    CREATE TABLE UEXAMPLE1 ( EID1 INT , EID2 INT ,EID3 INT )
    CREATE TABLE UEXAMPLE2 ( EID1 INT , EID2 INT , EID3 INT , EID4 INT , EID5 INT)

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

    INSERT INTO SAVE_DOCUMENT_V (MEMBER_CODE,CLASS_NO,ACC_NO)
    VALUES (1102,(SELECT CLASS_NO FROM DOCUMENT_V WHERE (ACC_NO=’D 1173′)), (SELECT ACC_NO FROM DOCUMENT_V WHERE (ACC_NO=’D 1173′)))

    My solution :

    For your first query this could be a replacement :

    INSERT INTO
    UEXAMPLE1 (EID1 , EID2)
    SELECT ‘10′, EID2
    FROM
    UEXAMPLE2
    WHERE
    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 :

    INSERT INTO
    UEXAMPLE1 (EID1 , EID2, EID3)
    SELECT ‘20′, EID2, EID3
    FROM
    UEXAMPLE2
    WHERE
    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…


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

    OK

    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.

    regards

    Jobby


  70. hi sir ,
    can u pls help me???
    how can i retrieve all the data from the db row wise to my asp page table???


  71. INSERT INTO DestinationDatabase.dbo.DestinationTable (FIELDS Name)
    SELECT FIELDS NAME
    FROM SourceDatabase.dbo.SourceTable


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


  73. can u tell me how can I copy data from one database table to another database same table… ???


  74. can u tell the types of trigger..


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

    a) ” INSTEAD 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,

    http://www.sql-server-performance.com/articles/audit/ddl_triggers_p1.aspx

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

    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,

    http://www.sql-server-performance.com/articles/dev/triggers_2000_p1.aspx

    —————————

    Hope this helps

    Thanks,
    Imran.


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


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


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


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


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


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


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


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

    Thanks.


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

    Thanks


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


  86. 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″
    conn.Open()

    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.Execute(SQL)
    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.


  87. on June 3, 2008 at 2:58 pm Pranav Dave

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


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


  89. Your site is very clean, and easily readable. Thanks for your generous help. you’re the best!


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


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


  92. Thanks all for such a good posts


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


  94. INSERT INTO TABLE 2(SELECT * FROM TABLE 1);

    Works great on an Oracle DB.


  95. on June 27, 2008 at 3:08 pm Satyanand rai

    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

    ex
    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


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

    its very urgent need please send me the query.


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


  98. on July 2, 2008 at 2:45 am Imran Taher Mohammed

    @pavani.

    CREATE TABLE TICKET (TICKETID INT, BALANCE MONEY)
    CREATE TABLE ACCOUNT(ACID INT IDENTITY, BALANCE MONEY,TICKETID INT)

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

    CREATE TRIGGER TR_INSERT
    ON TICKET
    FOR INSERT — Remember this trigger is only for insert
    AS
    BEGIN
    DECLARE @TICKETID INT
    DECLARE @BALANCE MONEY
    SELECT @TICKETID = TICKETID FROM INSERTED
    SELECT @BALANCE = BALANCE FROM INSERTED
    INSERT INTO ACCOUNT (TICKETID , BALANCE) SELECT @TICKETID , @BALANCE
    END

    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.

    INSERT INTO TICKET VALUES ( 2 , 300)

    SELECT * FROM TICKET
    SELECT * FROM ACCOUNT

    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.

    Thanks,


  99. good,


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


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


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


  103. on July 12, 2008 at 10:01 pm Imran Mohammed

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

    CREATE TABLE EXAMPL (COLA INT , COLB VARCHAR(10))
    GO
    CREATE TABLE EXAMPLE (COLA INT , COLB VARCHAR(10))
    GO
    CREATE TRIGGER TR_EX1
    ON EXAMPL
    FOR INSERT
    AS
    DECLARE @VAR1 INT
    DECLARE @VAR2 VARCHAR(10)
    SELECT @VAR1 = COLA , @VAR2 = COLB FROM INSERTED
    INSERT INTO EXAMPLE SELECT @VAR1 , @VAR2
    GO

    INSERT INTO EXAMPL VALUES ( 1, ‘TRUE’)
    GO
    SELECT * FROM EXAMPL
    SELECT * FROM EXAMPLE
    GO

    Hope this helps.
    Thanks
    Imran.


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


  105. on July 16, 2008 at 2:50 am Imran Mohammed

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


  106. 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,
    Kamlakar
    Software Developer


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


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


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


  110. on July 25, 2008 at 10:26 am Imran Mohammed

    @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 :
    Example1:

    2 3 45
    12 13 45
    112 113 45

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

    Hope this helps.
    Thanks,
    Imran.


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


  112. 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,
    c.m_group_id
    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


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


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


  115. Hi murry,

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

    OR

    use this query

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

    e.g

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

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

    Regards,
    Vinit Satam


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


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


  118. on August 13, 2008 at 12:53 am Imran Mohammed

    @shameer

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

    Thanks,
    Imran.


  119. thanks a lot , it is very usefull for the requirement which i am trying on, can you tell me how can i configure the servers database in to the application


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


  121. Hi Dave,

    How do you insert data from text box into table?

    Regards,

    Zulfi


  122. on August 20, 2008 at 10:43 pm Imran Mohammed

    @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,
    Imran.


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


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


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


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

    Table-TOCHeaderSchema
    Columns-TOCHeaderSchemaID
    -TocHeaderScemaNameText
    -CreatedByLID

    Table-TOCHSElement
    Columns-TOCHSElementID
    -TOCHSLevel
    -TOCHSPrefixText
    -TOCHSCounterStyleID
    -TOCHeaderSchemaID

    Table-TOCHSCounterStyle
    Columns-TOCHSCounterStyleID
    -TOCHSCounterNumber—-this is autogenerated
    -TOCHSCounterLabelText

    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
    TOCHSCounterNumber—-1,2,3,4,5(autogenerated)
    TOCHSCounterLabelText—-A,B,C,D,E

    TOCHSCounterStyleID —2
    TOCHSCounterNumber—-1,2,3,4,5(autogenerated)
    TOCHSCounterLabelText—-a,b,c,d

    TOCHSCounterStyleID —3
    TOCHSCounterNumber—-1,2,3,4,5(autogenerated)
    TOCHSCounterLabelText—-i,ii,iii,iv,v…

    Thank You


  127. on September 4, 2008 at 9:34 am Imran Mohammed

    @ blue star

    Need more information ….

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

    Thanks
    Imran.


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


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

    thanks