locked
Copy a row from one table to another RRS feed

  • Question

  • Dear All,

     

    I would like to copy a row from one table in database called Pictures in the Pictures database to another table called Pictures in NLL_GymAttendance. I have tried using the standard INSERT INTO but it has not worked. Both tables have the same structure.

     

    TABLE [dbo].[Pictures] (

                [MemberID] [int] NOT NULL ,

                [Picture] [image] NULL ,

                [rowguid]  uniqueidentifier ROWGUIDCOL  NOT NULL

    )

     

    INSERT INTO dbo.Pictures (Picture, MemberID, rowguid)

    SELECT     dbo.Pictures.Picture, dbo.Pictures.MemberID, dbo.Pictures. rowguid

    FROM         NLL_GymAttendance.dbo.Attendance INNER JOIN

                          dbo.Pictures ON NLL_GymAttendance.dbo.Attendance.MemberId = 1

     

    I have not used aliases just in case this was where I was going wrong as well!

     

    Can anyone advise me whether it is something special in the image data type that does not work the way less complex data types do?

     

    Thanks again for the help of the group.

     

    Hopefully one day I will be able to help others out other than always asking questions.

     

    Alastair

    Tuesday, October 4, 2011 2:40 PM

Answers

  • Try

    use NLL_GymAttendance
    
    INSERT INTO dbo.Pictures (MemberID, Picture)
    
    select MemberID, Picture from Pictures.dbo.Pictures where MemberID = 1

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Tuesday, October 4, 2011 3:01 PM
    • Marked as answer by KJian_ Tuesday, October 11, 2011 6:55 AM
    Tuesday, October 4, 2011 3:01 PM

All replies

  • Hi Alastair,


    Could you please post the error message you are receiving or describe the effect that the query is having.

     

    Regards,

    Tuesday, October 4, 2011 2:48 PM
  • You can not include RowGuid field as part of the insert as this is system type which is maintained by SQL Server. Also, your ON clause is quite strange - usually you match 2 tables based on the common field.

    Also, from which table you want to copy a row and which row?

    Can you please be more specific?


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, October 4, 2011 2:48 PM
  • HI Ally !
     
    You may use this query to get desired output;

    First try selecting records with this;

    SELECT     dbo.Pictures.Picture, dbo.Pictures.MemberID, dbo.Pictures. rowguid
    FROM       NLL_GymAttendance.dbo.Attendance 
    INNER JOIN dbo.Pictures ON NLL_GymAttendance.dbo.Attendance.MemberId = 1
    

    Then insert records with this apporach;

    SELECT     dbo.Pictures.Picture, dbo.Pictures.MemberID, dbo.Pictures. rowguid
    INTO  dbo.Pictures 
    FROM       NLL_GymAttendance.dbo.Attendance 
    INNER JOIN dbo.Pictures ON NLL_GymAttendance.dbo.Attendance.MemberId = 1
    

    What you were doing wrong was you have 4 columns in your SELECT list and in INSERT you have 3 columns.


     
    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

    Tuesday, October 4, 2011 2:51 PM
    Answerer
  • I have 2 databases:

    [1] Pictures with a table called Pictures;

    [2] NLL_GymAttendance with a table called Pictures

    I want to copy a record with an Image where ID = 1 (for example) in the Pictures Database to NLL_GymAttendance database. Both tables having the same structure.

    I have complicated the code by referring to an Attendance table as well. My desire is quite simply reproduce an image and other field data from one database to another.

    Thanks again. My coding is wrong but I don't know whether an INSERT INTO would work.

    Alastair

    Tuesday, October 4, 2011 2:59 PM
  • Not sure if this helps:

    INSERT INTO NLL_GymAttendance.dbo.Pictures (Picture, MemberID, rowguid)
    SELECT     Picture, MemberID, rowguid
    FROM         Pictures.dbo.Pictures
    
    --If you want to filter on MemberId = 1,Then try this
    INSERT INTO NLL_GymAttendance.dbo.Pictures (Picture, MemberID, rowguid)
    SELECT     Picture, MemberID, rowguid
    FROM         Pictures.dbo.Pictures 
    WHERE MemberId = 1
    
    



    If a post answers your question, please click "Mark As Answer" on that post or "Vote as Helpful".
    Blog : My Blog
    Tuesday, October 4, 2011 3:00 PM
  • Try

    use NLL_GymAttendance
    
    INSERT INTO dbo.Pictures (MemberID, Picture)
    
    select MemberID, Picture from Pictures.dbo.Pictures where MemberID = 1

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Edited by Naomi N Tuesday, October 4, 2011 3:01 PM
    • Marked as answer by KJian_ Tuesday, October 11, 2011 6:55 AM
    Tuesday, October 4, 2011 3:01 PM
  • Try Following

    Insert Into NLL_GymAttendance.dbo.Pictures
    SElect * from Pictures.dbo.Pictures where MemberID=1
    
    



    Mark as Answer If Reply Was Helpful
    Thanks
    Kuldeep Bisht
    Technical Lead @ Simplion Technologies
    Blog : www.dbsimplified.com
    Tuesday, October 4, 2011 3:35 PM
  • Hi Ally !

    You may use any of the below queries to achieve your goal;

    --1)
    INSERT INTO NLL_GymAttendance.dbo.Pictures (Picture, MemberID, rowguid)
    SELECT  dbo.Pictures.Picture, dbo.Pictures.MemberID, dbo.Pictures. rowguid
    FROM  Pictures.dbo.Pictures
    WHERE  Pictures.dbo.Attendance.MemberId = 1
    
    --2)
    SELECT  dbo.Pictures.Picture, dbo.Pictures.MemberID, dbo.Pictures. rowguid
    INTO  NLL_GymAttendance.dbo.Pictures
    FROM  Pictures.dbo.Pictures
    WHERE  Pictures.dbo.Attendance.MemberId = 1
    
    


           


    Please let me know if this doesn’t work for you. Hope I have answered you correctly.
     
    Thanks, Hasham

    Tuesday, October 4, 2011 5:00 PM
    Answerer