How to Add a Column

Transact-SQL
1

Hello Community,

Can someone show me how to modify the sql script to add a column?
I would like to add column_c INT NULL

I think the code is something like:

ALTER TABLE table_name ADD column_c INT NULL

However, I don't know where to place the above code in the following sql script:

SELECT TOP 20
  Model.ModelName
 ,Stock.Cost
FROM Data.Model
INNER JOIN Data.Stock
  ON Model.ModelID = Stock.ModelID

Thanks

2

Hi Community

I tried the following, but when I executed the script the column 'mynewcolumn2' didn't appear, even though the query was successful.

SELECT TOP 20
Model.ModelName
,Stock.Cost
FROM Data.Model
INNER JOIN Data.Stock
ON Model.ModelID = Stock.ModelID
ALTER TABLE Data.Model ADD mynewcolumn2 INT NULL

Any thoughts?

3

You can't do it in one single statement.

To add the column, run:
ALTER TABLE Data.Model ADD mynewcolumn2 INT NULL

Then you can do your select statement

4

Hi bitsmed,

If I understand you correctly, you're saying that I need to do the following:

ALTER TABLE Data.Model ADD mynewcolumn2 INT NULL
SELECT TOP 20
Model.ModelName
,Stock.Cost
FROM Data.Model
INNER JOIN Data.Stock
ON Model.ModelID = Stock.ModelID

Is that correct?

5

What I'm saying is, run the alter statement once.
When finished, you can run your select statement.

ps: Your select statement has nothing to do with changing the table

you could do (but only once as you cant keep adding the same column to a table):

ALTER TABLE Data.Model ADD mynewcolumn2 INT NULL
GO

SELECT TOP 20
Model.ModelName
,Stock.Cost
FROM Data.Model
INNER JOIN Data.Stock
ON Model.ModelID = Stock.ModelID

1 Like