Problem copying data from column in same table

I have a column that I would like to copy data to. The column definitions a
re 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 co
lumn 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 affec
ted.
Thanks, IrisYour 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.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
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/|||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, I'd rather update. I'll have to start back over since my te
st 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/|||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|||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.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
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|||I did this and it shows (116873 row(s) affected). What else could I possibl
y 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 populat
ing CustomerID1 with the value from CustomerID.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message news:ut408SeHGHA.
3408@.TK2MSFTNGP12.phx.gbl...
I did this and it shows (116873 row(s) affected). What else could I possibl
y 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/|||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 ha
ve worked with history tables they would have a date field that would be par
t of the primary key. When multiple entries were allowed on the same date,
some sort of sequence field was used to insure uniqueness.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message news:OYAFhQeHGHA.
528@.TK2MSFTNGP12.phx.gbl...
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.
"Iris Faber" <Iris.Faber@.mid.state.ms.us> wrote in message
news:eVU%23k3dHGHA.1288@.TK2MSFTNGP09.phx.gbl...
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