SQL Server: copy data from one column to another column?

I have two tables with the same column anomaly_id. I want to copy the row of anomaly_id from the first table to the second table using this code

UPDATE amb.anamoly_log_update SET anamoly_id = t2.anomaly_id FROM amb.anamoly_log_update t1 INNER JOIN amb.anomaly_fee t2 ON t1.anamoly_id=t2.anomaly_id

Even after I did that it shows 0 rows affected, when there is data in amb.anomaly.fee (source table)

Please help

Edit: Comment from post: I just want to copy all the anamoly_id from amb.anamoly_fee to amb.anamoly_log_update. My code might be nonsensical. Please do review it.

-------------Problems Reply------------

To copy the id from anomaly_fee to anamoly_log_update use :

INSERT INTO anamoly_log_update (anamoly_id)
SELECT anamoly_id FROM anomaly_fee

with both columns it looks like that:

INSERT INTO anamoly_log_update (anamoly_id,PID)
SELECT anamoly_id,PID FROM anomaly_fee

You only would copy the data if they where in both tables .. and then there is nothing update because you do not change the data => 0 rows affected

ON t1.anamoly_id=t2.anomaly_id

please think about what you really want to do and change your description ..

Does amb.anamoly_log_update contain at least one row corresponding to the anamoly_id that's present in amb.anamoly_fee? You are trying to join on two tables on anamoly_id.

You need to provide other linkage between tables than t1.anamoly_id=t2.anomaly_id or the query will do nothing

merge into amb.anamoly_log_update as t1
using amb.anomaly_fee as t2
on t1.anamoly_id=t2.anomaly_id
when matched then
update set t1.anamoly_id = t2.anomaly_id

Category:sql Views:0 Time:2012-01-22

Related post

  • SQL Server - Copy Data Between Tables 2010-09-20

    I need to copy a large amount (~200,000) of records between two tables inside the same SQL Server 2000 database. I can't change the original table to include the columns I would need, so the copy is the only solution. I made a script with insert sele

  • SQL Server - encrypting data in a database table's column 2011-03-18

    I've got a problem scenario w.r.t data cyphering or encryption/decryption in SQL Server 2005. Scenario: There is a specific table in the database The table has a column, let's say "Credit Card" The requirements is that the content or data in this col

  • In SQL Server, xml data are saved in a text column, how to query from this column? 2010-10-14

    In my situation, xml data are saved in a text column, how to query this against this column? For example: create table t1 ( id INT IDENTITY(1, 1) PRIMARY KEY, content text ) insert into t1(content) values ('<?xml version="1.0"?> <people>

  • SQL Server - copy data across tables , but copy the data only when it match with a specific column name 2012-03-22

    For example I got this 2 table dbo.fc_states StateId Name 6316 Alberta 6317 British Columbia and dbo.fc_Query Name StatesName StateId Abbotsford Quebec NULL Abee Alberta NULL 100 Mile House British Columbia NULL Ok pretty straightforward , how do I c

  • Sql Server Change Data Capture: Preserving history when adding columns? 2010-04-16

    When a new column is added to table that is configured for change data capture (cdc), the capture instance table will not have the new column until cdc is disabled and re-enabled for the source table. In the process the existing capture instance is d

  • Fluent NHibernate Problems with SQL Server 2008 DATE Column Values 2011-03-24

    Greetings, I'm having problems using a SQL Server 2008 DATE column in C# using Fluent NHibernate. When I try to update a record that has a value in a non-nullable DATE column prior to 1/1/1753 (the min date for a DATETIME), I'm getting an error sayin

  • SQL Server datetime data type does not allow many formats 2009-05-20

    I have a table in SQL Server 2005 which has a date time field. I want to save the date time value in mm/dd/yyyy hh:mm:ss format, but I guess SQL Server allows date time in yyyy-mm-dd HH:mm:ss.ll format. I can save date time value in mm/dd/yyyy hh:mm:

  • How to filter a table by T_SQL in SQL Server 2005, using a specific time for a column of "DateTime" datatype 2010-02-18

    How to filter a table by T_SQL in SQL Server 2005, using a specific time for a column of "DateTime" datatype? Thank you --------------Solutions------------- For example, to find all record with a time (irrespective of date) between 9AM and 11AM, you

  • SQL Server: export data via SQL query? 2010-05-07

    I have FK and PK all over my db and table data needs to be specified in a certain order or else I get FK/PK insertion errors. I'm tired of executing the wizard again and again to transfer data one table at a time. In the SQL Server export data wizard

  • SQL Server XML data type 2011-04-06

    I have a table in SQL Server 2005 SP4; in which one of the column will be of XML data type. The problem is my XML can be of three different formats. (i.e. XML is not same but can be up to three different format) I want to use typed XML so Question a)

  • Find binary duplicate records SQL Server 2008 (Data type image) 2011-05-18

    I have inherited a database that has a table (300gb) full of SQL datatype Image. I understand this datatype is depreciated. As a routine cleanup I want to delete all duplicate Image's from the table where certain conditions are met. How do I compare

  • SQL Server and date + 1 result 2011-07-16

    In case of the SQL Server. Say, if I have a table with [myDate] column that is of the type datetime, what would adding 1 to it mean in the following context? SELECT * FROM [myTable] WHERE [myDate] + 1 > @someDate --------------Solutions-----------

  • map .NET types to SQL Server 200 data types 2011-10-06

    How can I store a .NET double in an SQL Server 2008 data type? I get an exception 'Parameter value '1.3465492655247000' is out of range' when I try to store a double in a numeric(16, 16) column. Because the number has 16 decimal place and a 1 in fron

  • What is the simplest, most maintainable way to create a SQL Server ODBC Data Source? 2008-10-04

    I need a programmatic way of creating a SQL Server ODBC Data Source. I can do this by directly accessing the Registry. It would be better if this could be done via an available (SQL Server/Windows) API to protect against changes in the registry keys

  • SQL Server 2008 data types 2009-10-06

    What are the different SQL Server 2008 data types? I am specifically trying to find the equivalent of a long int. --------------Solutions------------- SQL Server 2008 Data Types : http://msdn.microsoft.com/en-us/library/ms187752.aspx Long Int (from V

  • SQL Server: Copy Stored Procedures 2009-11-10

    This question already has an answer here: SQL Server - copy stored procedures from one db to another 9 answers Is there an easy way to copy all stored procedures from one database to another? I have SQL Management Studio installed. --------------Solu

  • Is SQL Server 'MONEY' data type a decimal floating point or binary floating point? 2010-02-22

    I couldn't find anything that rejects or confirms whether SQL Server 'MONEY' data type is a decimal floating point or binary floating point. In the description it says that MONEY type range is from -2^63 to 2^63 - 1 so this kind of implies that it sh

  • Trouble using namespace in SQL Server XML Data Type query 2010-08-11

    For background see this question: http://stackoverflow.com/questions/1415003/sql-server-xml-data-type-query-issue I'm trying to query against an XML object in SQL Server 2005. The query works fine when there's no namespace defined in the XML. However

  • SQL Server 2008 data types: which ones should i use? 2010-10-08

    I am trying to figure out which data types I should use for my tables. Does someone have a very good, simple, tutorial on SQL Server 2008 datatypes (has to be practical!) ? --------------Solutions------------- http://www.databasejournal.com/features/

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.201 (s). 11 q(s)