Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: Trying to Update Column in database A from Database B

    I have tried Insert, Update, bcp
    all getting different errors.

    What Im trying to do is copy column ChannelName in table b, database b to Channel Name in table a in database a.

    Here is what Ive tried so far. On some Ive got errors for return of query is exceeding 1.
    On the other I got cannot violation of PK_V_CHANNEL_INFO which is a trigger.

    In the both database the ID, and Primary key are ChannelID.

    UPDATE dbo.V_CHANNEL_INFO
    SET ChannelName = (SELECT ChannelName FROM [Backup].dbo.nicecamera)
    WHERE dbo.V_Channel_INFO.ChannelID = [Backup].dbo.nicecamera.ChannelID

    Insert INTO dbo.V_CHANNEL_INFO (ChannelID,ServerChannelNO,ChannelName,ChannelType ,ServerID,ServerGroupNo,DataType,PTZID,CameraColor Support,Status,CameraID,DeviceID,Resolution,CGroup ID,PortNO)
    select DISTINCT ChannelID,ServerChannelNO,ChannelName,ChannelType, ServerID,ServerGroupNo,DataType,PTZID,CameraColorS upport,Status,CameraID,DeviceID,Resolution,CGroupI D,PortNO
    from "Backup".dbo.nicecamera

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    maybe this would work for you...

    Code:
    UPDATE vci
       SET vci.ChannelName = nc.ChannelName
      from dbo.V_CHANNEL_INFO vci
      join [Backup].dbo.nicecamera nc on vci.ChannelID = nc.ChannelID
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    and if you are trying to add new records to V_CHANNEL_INFO you might try this...

    Code:
    Insert INTO dbo.V_CHANNEL_INFO (ChannelID,ServerChannelNO,ChannelName,ChannelType,ServerID,ServerGroupNo,DataType,PTZID,CameraColorSupport,Status,CameraID,DeviceID,Resolution,CGroupID,PortNO)
    select DISTINCT ChannelID,ServerChannelNO,ChannelName,ChannelType,ServerID,ServerGroupNo,DataType,PTZID,CameraColorSupport,Status,CameraID,DeviceID,Resolution,CGroupID,PortNO 
      from [Backup].dbo.nicecamera nc
      left join dbo.V_CHANNEL_INFO vci on nc.ChannelID = vci.ChannelID
     where vci.ServerChannelNo is null
    Please realise I have no way of debugging this.
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Trying to Update Column in database A from Database B

    Originally posted by reaperonu
    I have tried Insert, Update, bcp
    all getting different errors.

    What Im trying to do is copy column ChannelName in table b, database b to Channel Name in table a in database a.

    Here is what Ive tried so far. On some Ive got errors for return of query is exceeding 1.
    On the other I got cannot violation of PK_V_CHANNEL_INFO which is a trigger.

    In the both database the ID, and Primary key are ChannelID.

    UPDATE dbo.V_CHANNEL_INFO
    SET ChannelName = (SELECT ChannelName FROM [Backup].dbo.nicecamera)
    WHERE dbo.V_Channel_INFO.ChannelID = [Backup].dbo.nicecamera.ChannelID

    -- comments-----------------------
    I do not see any logic in query above. If you want to update table by using join - it needs to use something like this:

    update tablea set fielda=tableb.fieldb
    from tableb where tablea.id=tableb.id

    -- comments-----------------------

    Insert INTO dbo.V_CHANNEL_INFO (ChannelID,ServerChannelNO,ChannelName,ChannelType ,ServerID,ServerGroupNo,DataType,PTZID,CameraColor Support,Status,CameraID,DeviceID,Resolution,CGroup ID,PortNO)
    select DISTINCT ChannelID,ServerChannelNO,ChannelName,ChannelType, ServerID,ServerGroupNo,DataType,PTZID,CameraColorS upport,Status,CameraID,DeviceID,Resolution,CGroupI D,PortNO
    from "Backup".dbo.nicecamera
    -- comments-----------------------
    I am not sure that PK_V_CHANNEL_INFO is a trigger - I guess it is a primary key. Check it again.
    Last edited by snail; 10-17-03 at 17:18.

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    Originally posted by Paul Young
    maybe this would work for you...

    Code:
    UPDATE vci
       SET vci.ChannelName = nc.ChannelName
      from dbo.V_CHANNEL_INFO vci
      join [Backup].dbo.nicecamera nc on vci.ChannelID = nc.ChannelID


    Returns Ambiguos column name 'ChannelID'

  6. #6
    Join Date
    Oct 2003
    Posts
    3
    Originally posted by Paul Young
    and if you are trying to add new records to V_CHANNEL_INFO you might try this...

    Code:
    Insert INTO dbo.V_CHANNEL_INFO (ChannelID,ServerChannelNO,ChannelName,ChannelType,ServerID,ServerGroupNo,DataType,PTZID,CameraColorSupport,Status,CameraID,DeviceID,Resolution,CGroupID,PortNO)
    select DISTINCT ChannelID,ServerChannelNO,ChannelName,ChannelType,ServerID,ServerGroupNo,DataType,PTZID,CameraColorSupport,Status,CameraID,DeviceID,Resolution,CGroupID,PortNO 
      from [Backup].dbo.nicecamera nc
      left join dbo.V_CHANNEL_INFO vci on nc.ChannelID = vci.ChannelID
     where vci.ServerChannelNo is null
    Please realise I have no way of debugging this.

    ---- Comment--
    This is also returning ambigous cloumn names for all.--

    The pupose of this code is to copy just the column ChannelName for nicecamera to V_CHANNEL_INFO.

    I see constraints on CHANNELID in VCI.

    The nicecamera database is a copy of v_channel_info that was copied to a disk. when the system has to be reghosted i pop the floppy in and run my program to transfer just this column back into the V_CHANNEL_INFO table.

    The SQL backup will not work for this.

    Thanks for all the help, and so quickly..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •