Can I BCP a single colume into an exsisting table?

[Charles Deaton]

I need to update one column in a very large table. Can I bcp, bulk copy, bulk insert... one column from another table into an existing table? For example:

table RES column STATUS needs to be updated with the same column from another table (PROP).

I have looked at bcp but it looks like it wants to insert/update all columns in the row. I just need the one column updated and I need it done fast.

Any help would be appreciated.

Charlie.

[Kristine Greenlee]

BCP, which is bulk copy, and BULK INSERT are both going to insert rows, not update them. I think the other option is to run an UPDATE statement like the one below:

UPDATE Res
SET Status = P.Status
FROM Res R INNER JOIN Prop P
ON R.id = P.id -- or whatever the join fields are

This statement may take a while since it's a very large table, but just make sure you have proper indexes on the tables.
------------
Charles Deaton at 9/25/00 12:51:50 PM

I need to update one column in a very large table. Can I bcp, bulk copy, bulk insert... one column from another table into an existing table? For example:

table RES column STATUS needs to be updated with the same column from another table (PROP).

I have looked at bcp but it looks like it wants to insert/update all columns in the row. I just need the one column updated and I need it done fast.

Any help would be appreciated.

Charlie.

[Charles Deaton]

Thanks Kristine!

Now lets change the scenario a little. Suppose I want to update the same column in the same table but want to use a txt file with the following format:

12345678 09/21/00 01:12:25

where the first column in the text file is an 8 digit number, a blank then the date and time in the second column (17 characters).





------------
Kristine Greenlee at 9/25/00 1:33:03 PM

BCP, which is bulk copy, and BULK INSERT are both going to insert rows, not update them. I think the other option is to run an UPDATE statement like the one below:

UPDATE Res
SET Status = P.Status
FROM Res R INNER JOIN Prop P
ON R.id = P.id -- or whatever the join fields are

This statement may take a while since it's a very large table, but just make sure you have proper indexes on the tables.
------------
Charles Deaton at 9/25/00 12:51:50 PM

I need to update one column in a very large table. Can I bcp, bulk copy, bulk insert... one column from another table into an existing table? For example:

table RES column STATUS needs to be updated with the same column from another table (PROP).

I have looked at bcp but it looks like it wants to insert/update all columns in the row. I just need the one column updated and I need it done fast.

Any help would be appreciated.

Charlie.

[Kristine Greenlee]

I would say that you would have to bcp the txt file into a table in SQL Server and use an UPDATE statement. Or use DTS transformations


------------
Charles Deaton at 9/25/00 2:51:57 PM

Thanks Kristine!

Now lets change the scenario a little. Suppose I want to update the same column in the same table but want to use a txt file with the following format:

12345678 09/21/00 01:12:25

where the first column in the text file is an 8 digit number, a blank then the date and time in the second column (17 characters).





------------
Kristine Greenlee at 9/25/00 1:33:03 PM

BCP, which is bulk copy, and BULK INSERT are both going to insert rows, not update them. I think the other option is to run an UPDATE statement like the one below:

UPDATE Res
SET Status = P.Status
FROM Res R INNER JOIN Prop P
ON R.id = P.id -- or whatever the join fields are

This statement may take a while since it's a very large table, but just make sure you have proper indexes on the tables.
------------
Charles Deaton at 9/25/00 12:51:50 PM

I need to update one column in a very large table. Can I bcp, bulk copy, bulk insert... one column from another table into an existing table? For example:

table RES column STATUS needs to be updated with the same column from another table (PROP).

I have looked at bcp but it looks like it wants to insert/update all columns in the row. I just need the one column updated and I need it done fast.

Any help would be appreciated.

Charlie.