CODEWITHSUNDEEP
sqlsql-server
RVProgramer
RVProgramer

Reputation: 3

Insert with select statement gives error Only one expression in select without exists

I am new to SQL Server and have a problem with an insert statement. I am to convert an old database to a SQL server relational database. I am transferring the old data into new tables. The old records are not complete which is causing problems because the fields in the new tables do not allow null values. So what I am trying to do is in insert n/a in the missing fields and then use the select statement to retrieve the available data from the old table all at the same time so I don't get null value not allowed, but I get the error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS along with the Insert statement has more columns than the values statement. I sure there is a way to do this but I can't figure it out, hope someone can help. Below is an abbreviated description to the statement.

insert into database1.dbo.table (col1, col2, .....col10)
values('n/a','n/a',(select col3, col4...col10 from database2.dbo.table)

Upvotes: 0

Views: 81

Answers (1)

D-Shih
D-Shih

Reputation: 46249

You can try to use INSERT INTO ... SELECT

INSERT INTO database1.dbo.table  (col1, col2, .....col10)
SELECT 'n/a',
       'n/a',
       col3, 
       col4,
       ...col10
FROM database2.dbo.table

Upvotes: 2

Related Questions