0

My goal is to manipulate an existing database table by applying a scalar multiple, a discount rate, across all existing columns to generate a present value. Then I would like the output to to be a new table.

alter table dbo.[DBO]
add scalar as power(0.95,(9 + cast([rowno] as float))/12);
create table [Table] as 
(select 
ID,
sum([Column I] * scalar) as newI,
sum([Column J] * scalar) as newJ 
from dbo.[DBO]
group by ID);

When the code below is run, the following error message is returned:

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '('.

It appears the error has to do with "(select", but I cannot make the proper edits to resolve it.

squillman
  • 13,363
  • 3
  • 41
  • 60
Noah
  • 11
  • 3
  • Because that's not valid syntax for CREATE TABLE in SQL Server (I removed the MySQL tag since the error message you posted is from SQL Server, they're not the same product). If you want to create a table on the fly in SQL Server the syntax is `SELECT ... INTO NewTable FROM ...` – squillman Jun 06 '22 at 19:43
  • SQL Server is `select * into new table from existing table` I believe https://stackoverflow.com/questions/16683758/how-to-create-a-table-from-select-query-result-in-sql-server-2008 – xQbert Jun 06 '22 at 19:44
  • No it does not @Stu since my question involves a direct manipulation with that scalar multiple. I also see contradictory responses within that link. – Noah Jun 06 '22 at 21:03
  • (SELECT ID, sum([old_I] * scalar) as new_I,INTO [New] FROM [Old] GROUP BY ID); Just gets me an error with the keyword "INTO". – Noah Jun 06 '22 at 21:06
  • `dbo.[DBO]` Yes, that's a great idea. Use the typical default schema name as a table name. – SMor Jun 06 '22 at 21:41

1 Answers1

0

You're trying to create the table with data - a select statement. You need to create the table structure first:

CREATE TABLE tablename (
ID int,
NewI varchar(255),
etc....
)

Create the table first then separately run your select as select into statement:

SELECT col names
INTO new_table
FROM datasource
wittle.pie
  • 1
  • 2
  • Your explanation is a bit confusing. The select into will create the new table but you make it sound like you have to create the table first and then use select into. This would cause an error. – Sean Lange Jun 06 '22 at 19:49
  • Apologies the "new_table" should be the table name of the table you created. The database needs to know the structure of the table, even if it was only a temp table, before it can add data to it. Create the table with the column names and data types. Then insert data in to the table. Two different steps. Do you get an error creating the table now or inserting data in to it? – wittle.pie Jun 06 '22 at 19:59
  • You can't use select into for an existing table. You are mixing up two different ways of inserting data. – Sean Lange Jun 06 '22 at 20:03