-2

this is my first question here. I am very new into SQL Server and T-SQL.

I would like to create a table, with a column that is using data from another column. I thought I could use select function, but it is not allowed.

How to do it?

It is very simple to create view in this way, but I would like to have a table not view.

It should look like

Column A, ColumnB, 
Column C=select count(*) from [another table] where....

Could you please advise?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ania Laszczak
  • 1
  • 1
  • Possible duplicate of [How do I create a table based on another table](http://stackoverflow.com/questions/18254104/how-do-i-create-a-table-based-on-another-table) – Tab Alleman Jan 14 '16 at 21:54
  • 3
    *Computed columns* cannot depend on other tables. If you need to frequently, create a **view** that contains that logic – marc_s Jan 14 '16 at 22:04

2 Answers2

0
    SELECT [COLUMN A],[COLUMN B],COUNT(*) as [COLUMN C] 
INTO [destination table] FROM [another table] where...

You should use an alias

cronos2546
  • 1,088
  • 7
  • 16
0

You create a table using the create table syntax because you will need to define the field names and sizes. Look the syntax up in Books Online. Do not ever use SELECT INTO unless you are creating a staging table for one-time use or a temp table. It is not a good choice for creating a new table. Plus, you don't say where any of the other columns come from except the column one, so it is may be impossible to properly set up the correct field sizes from the initial insert. Further, well frankly you should take the time to think about what columns you need and what data types they should be, it is irresponsible to avoid doing this for a table that will be permanently used.

To populate you use the Insert statement with a select instead of the values statement. If only column c come from another table, then it might be something like":

Insert table1 (colA, Colb, colC)
select 'test', 10, count(*) 
from  tableb
where ...

If you have to get the data from multiple tables, then you may need a join.

If you need to maintain the computed column as the values change in TableB, then you may need to write triggers on TableB or better (easier to develop and maintain and less likely to be buggy or create a data integrity problem) use a view for this instead of a separate table.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • What I have is a table [reservations] with coulmns: booking_id, person_id, trip_id. And I have one more table [trips] with column [available_places] and I would like to have one more column with [free_places] and here I need computation of how many bookings have been already made for this trip (select count(*) from bookings where trip_id=1. – Ania Laszczak Jan 14 '16 at 22:58
  • I do have such a View. But in my project I also need to have such a column in the table. I tried to use this syntax "ALTER TABLE table_name ADD column_name datatype" but got errors :-( – Ania Laszczak Jan 14 '16 at 23:05