I have a column that I would like to copy data to.  The column definitions are exactly the same.  I tried creating an Insert Into query and changed the type to Insert Results.  Everytime I run this query, all the values in my column remain to be null.  Below is a sample of what I did:

INSERT INTO Customers
                      ([CustomerID1])
SELECT     [CustomerID]
FROM         Customers AS Customers_1

I got a message back '(116873 row(s) affected)'.  I don't know what it affected.

Thanks, Iris

Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated.  It is not
discerning between null values and non null values.  So, if you start with
the following data:

CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony

Run the SQL once and you will get this data in the table:

CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL

Run the SQL again and you will get this data in the table:

CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL

If your SQL is doing this, then you don't have a key on your table, which is
your first problem.

I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one.  The following SQL should do
this.

Update Customers set CustomerID1 = CustomerID

However, I cant think of a reason why you would want to do this.  You
probably have some database design issues to work out.  Once you get the
design down, you probably wont have a need to do this at all.


I have a column that I would like to copy data to.  The column definitions
are exactly the same.  I tried creating an Insert Into query and changed the
type to Insert Results.  Everytime I run this query, all the values in my
column remain to be null.  Below is a sample of what I did:

INSERT INTO Customers
                      ([CustomerID1])
SELECT     [CustomerID]
FROM         Customers AS Customers_1

I got a message back '(116873 row(s) affected)'.  I don't know what it
affected.

Thanks, Iris

Jim,

There is no key on this table because it is a table with historical records.  I pulled this data from a mainframe database. 

Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated.  It is not
discerning between null values and non null values.  So, if you start with
the following data:

CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony

Run the SQL once and you will get this data in the table:

CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL

Run the SQL again and you will get this data in the table:

CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL

If your SQL is doing this, then you don't have a key on your table, which is
your first problem.

I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one.  The following SQL should do
this.

Update Customers set CustomerID1 = CustomerID

However, I cant think of a reason why you would want to do this.  You
probably have some database design issues to work out.  Once you get the
design down, you probably wont have a need to do this at all.


I have a column that I would like to copy data to.  The column definitions
are exactly the same.  I tried creating an Insert Into query and changed the
type to Insert Results.  Everytime I run this query, all the values in my
column remain to be null.  Below is a sample of what I did:

INSERT INTO Customers
                      ([CustomerID1])
SELECT     [CustomerID]
FROM         Customers AS Customers_1

I got a message back '(116873 row(s) affected)'.  I don't know what it
affected.

Thanks, Iris

At a minimum you should have certain fields defined as not null (CustomerID for example).  There should also be some way of identifying unique records, even in a history table (although not in an audit table).  Usually when I have worked with history tables they would have a date field that would be part of the primary key.  When multiple entries were allowed on the same date, some sort of sequence field was used to insure uniqueness.

  There is no key on this table because it is a table with historical records.  I pulled this data from a mainframe database. 

  Your SQL, if I read it correctly, is creating a new row for every existing
  row in your table, with only one column (CustomerID1) populated.  It is not
  discerning between null values and non null values.  So, if you start with
  the following data:

  CustomerID, CustomerID1, Name
  1, NULL, John
  2, NULL, Sue
  3, NULL, Tony

  Run the SQL once and you will get this data in the table:

  CustomerID, CustomerID1, Name
  1, NULL, John
  2, NULL, Sue
  3, NULL, Tony
  NULL, 1, NULL
  NULL, 2, NULL
  NULL, 3, NULL

  Run the SQL again and you will get this data in the table:

  CustomerID, CustomerID1, Name
  1, NULL, John
  2, NULL, Sue
  3, NULL, Tony
  NULL, 1, NULL
  NULL, 2, NULL
  NULL, 3, NULL
  NULL, 1, NULL
  NULL, 2, NULL
  NULL, 3, NULL
  NULL, NULL, NULL
  NULL, NULL, NULL
  NULL, NULL, NULL

  If your SQL is doing this, then you don't have a key on your table, which is
  your first problem.

  I assume that you want to update the CustomerID1 field in the existing rows,
  not insert a new row for every exiusting one.  The following SQL should do
  this.

  Update Customers set CustomerID1 = CustomerID

  However, I cant think of a reason why you would want to do this.  You
  probably have some database design issues to work out.  Once you get the
  design down, you probably wont have a need to do this at all.


  I have a column that I would like to copy data to.  The column definitions
  are exactly the same.  I tried creating an Insert Into query and changed the
  type to Insert Results.  Everytime I run this query, all the values in my
  column remain to be null.  Below is a sample of what I did:

  INSERT INTO Customers
                        ([CustomerID1])
  SELECT     [CustomerID]
  FROM         Customers AS Customers_1

  I got a message back '(116873 row(s) affected)'.  I don't know what it
  affected.

  Thanks, Iris

Ok, I'm remembering this now.  It does have a transaction date and time field.  I'll set that as the unique key.   Thanks.

At a minimum you should have certain fields defined as not null (CustomerID for example).  There should also be some way of identifying unique records, even in a history table (although not in an audit table).  Usually when I have worked with history tables they would have a date field that would be part of the primary key.  When multiple entries were allowed on the same date, some sort of sequence field was used to insure uniqueness.

There is no key on this table because it is a table with historical records.  I pulled this data from a mainframe database. 

Your SQL, if I read it correctly, is creating a new row for every existing
row in your table, with only one column (CustomerID1) populated.  It is not
discerning between null values and non null values.  So, if you start with
the following data:

CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony

Run the SQL once and you will get this data in the table:

CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL

Run the SQL again and you will get this data in the table:

CustomerID, CustomerID1, Name
1, NULL, John
2, NULL, Sue
3, NULL, Tony
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, 1, NULL
NULL, 2, NULL
NULL, 3, NULL
NULL, NULL, NULL
NULL, NULL, NULL
NULL, NULL, NULL

If your SQL is doing this, then you don't have a key on your table, which is
your first problem.

I assume that you want to update the CustomerID1 field in the existing rows,
not insert a new row for every exiusting one.  The following SQL should do
this.

Update Customers set CustomerID1 = CustomerID

However, I cant think of a reason why you would want to do this.  You
probably have some database design issues to work out.  Once you get the
design down, you probably wont have a need to do this at all.


I have a column that I would like to copy data to.  The column definitions
are exactly the same.  I tried creating an Insert Into query and changed the
type to Insert Results.  Everytime I run this query, all the values in my
column remain to be null.  Below is a sample of what I did:

INSERT INTO Customers
                      ([CustomerID1])
SELECT     [CustomerID]
FROM         Customers AS Customers_1

I got a message back '(116873 row(s) affected)'.  I don't know what it
affected.

Thanks, Iris

This is normal as you're not "copying" the values, you're inserting new rows,
where only the new column is populated with data.

You need to UPDATE the new column with the values in the old column.

update <table>
           set <new column> = <old column>


ML

---
http://milambda.blogspot.com/

That's correct, I'd rather update.  I'll have to start back over since my test table now has all those null values in it like Jim mentioned.  Thanks.

This is normal as you're not "copying" the values, you're inserting new rows,
where only the new column is populated with data.

You need to UPDATE the new column with the values in the old column.

update <table>
           set <new column> = <old column>


ML

---
http://milambda.blogspot.com/

I did this and it shows (116873 row(s) affected).  What else could I possibly be doing wrong?

Iris

This is normal as you're not "copying" the values, you're inserting new rows,
where only the new column is populated with data.

You need to UPDATE the new column with the values in the old column.

update <table>
           set <new column> = <old column>


ML

---
http://milambda.blogspot.com/

Thats what it should show.  You are updating every existing row, and populating CustomerID1 with the value from CustomerID.
  I did this and it shows (116873 row(s) affected).  What else could I possibly be doing wrong?

  Iris

  This is normal as you're not "copying" the values, you're inserting new rows,
  where only the new column is populated with data.

  You need to UPDATE the new column with the values in the old column.

  update <table>
             set <new column> = <old column>


  ML

  ---
  http://milambda.blogspot.com/

Aren't you trying to get a copy of ALL the values?


ML

---
http://milambda.blogspot.com/

The tables that I'm working with need some normalizing, so I'm backing up now.

Aren't you trying to get a copy of ALL the values?


ML

---
http://milambda.blogspot.com/

Hello, Iris

I guess that you want to use something like this:
UPDATE Customers SET CustomerID1=CustomerID

However, if the INSERT that you wrote was executed sucessfully, I
suspect that you got a lot of rows with invalid or missing
informations, because the number of rows in the table was doubled (i.e.
you now may have 233566 rows in the table).

Razvan

That's correct :)

Hello, Iris

I guess that you want to use something like this:
UPDATE Customers SET CustomerID1=CustomerID

However, if the INSERT that you wrote was executed sucessfully, I
suspect that you got a lot of rows with invalid or missing
informations, because the number of rows in the table was doubled (i.e.
you now may have 233566 rows in the table).

Razvan

reverse like?
Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?
inserting single precision data into sql server float column
SQL dilema
CTE vs. Table Variable (Paging)