Sql Column had no values

Question

I have a sql table that I am trying to add a column from another table to. Only when I execute the alter table query it does not pull the values out of the table to match the column where I am trying to make the connection.

For example I have column A from table 1 and column A from table 2, they are supposed to coincide. ColumnATable1 being an identification number and ColumnATable2 being the description.

I tried this but got an error...

alter table dbo.CommittedTbl
add V_VendorName nvarchar(200)
where v_venkey = v_vendorno

It tells me that I have incorrect syntax... Anyone know how to accomplish this?

Do it in two statements. Remove the where clause from the alter table statement and have a look at how you can use `update` to move the data from one table to another. — Mikael Eriksson, Jun 05 '12 at 20:14
You'd get much more helpful answers if you were to provide us with your extant structure. — swasheck, Jun 05 '12 at 20:14
Why are you duplicating an nvarchar(200) value and storing it in both tables? This is wasteful to store redundantly and, since it's a name it's likely to change, which means you will have to update in more than one place. Typically surrogate keys are used to make this linkage. — Aaron Bertrand, Jun 05 '12 at 20:23

score 2 · Accepted Answer · answered Jun 05 '12 at 20:14

2

alter table dbo.CommittedTbl
add V_VendorName nvarchar(200);

go

update c
set c.V_VendorName = a.V_VendorName
from CommittedTbl c
join TableA a
on c.v_venkey = a.v_vendorno;

go

I'm just guessing at your structure here.

answered Jun 05 '12 at 20:14

swasheck

4,644
2
29
56

score 1 · Answer 2 · answered Jun 05 '12 at 20:14

1

alter table 2 add column A <some_type>;
update table2 set column A = (select column_A from table2 where v_venkey = v_vendorno);

Your names for tables and columns are a bit confusing but I think that should do it.

answered Jun 05 '12 at 20:14

Florin Stingaciu

8,085
2
24
45

score 1 · Answer 3 · answered Jun 05 '12 at 20:15

There is no WHERE clause for an ALTER TABLE statement. You will need to add the column (your first two lines), and then insert rows based upon a relationship you define between the two tables.

ALTER TABLE syntax: http://msdn.microsoft.com/en-us/library/ms190273%28v=sql.90%29.aspx

score 1 · Answer 4 · answered Jun 05 '12 at 20:27

There are several languages within SQL:

DDL: Data Definition Language - this defines the schema (the structure of tables, columns, data types) - adding a column to a table affects the table definitions and all rows will have that new column (not just some rows according to a criteria)

DML: Data Manipulation Language - this affects data within a table, and inserting, updating or other changes fall into this and you can update some data according to criteria (and this is where a WHERE clause would come in)

ALTER is a DDL statement, while INSERT and UPDATE are DML statements.

The two cannot really be mixed as you are doing.

You should ALTER your table to add the column, then INSERT or UPDATE the column to include appropriate data.

score 0 · Answer 5 · answered Jun 05 '12 at 20:14

0

Is it possible that you want a JOIN query instead? If you want to join two tables or parts of two tables you should use JOIN.

have a look at this for a start if you need to know more LINK

hope that helps!

answered Jun 05 '12 at 20:14

Logard

1,494
1
13
27

Sql Column had no values

5 Answers5