Insert by selecting multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert by selecting multiple columns

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-03-14 : 12:42:30

I have to insert n-records based on a query and have to assign 7 values, comming from a second query.

Insert into table1 (ID, column1, column2,..., column7)
values (@ID, @column1, @column2,..., column7)

Query1 returns the ID (Format vector:1 x n)
Query2 returns the values for column1-7 (Format vector: 7 x 1) which do not vary for the n records

The correct matrix of values (Format 7 x n) is described by:

SELECT a.ID, b.column1, b.column2,..., b.column from
(SELECT ID from TABLE2) a
JOIN
(SELECT column1, column2,..., column7 from TABLE3) b on a.ID <> '0'

Unfortunatly I can't place this query into the INSERT clause like

Insert into table1 (ID, column1, column2,..., column7)
           values ((SELECT a.ID, b.column1, b.column2,..., b.column from
                   (SELECT ID from TABLE2) a
                    JOIN
                   (SELECT column1, column2,..., column7 from TABLE3) b on a.ID <> '0'))

It throws 2 errors:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS
and (this is NOT true):
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

Any comment on that?
Regards,
Martin

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-03-14 : 14:33:38

Two common ways to insert into a table are through: INSERT VALUES and INSERT SELECT
So pick one or the other and don't try to combine them. The select method can be any valid select statement. So assuming the statement that you have inside the values() returns rows that match the column list of your insert into list then just remove the "values()" part.

insert into table1 (<columnList>)
select <sameColumnList>
from ....

Be One with the Optimizer
TG

barnabeck
Posting Yak Master

236 Posts

Posted - 2013-03-14 : 17:13:41

Thank you, I will try this out later, because it became secondary as I need to combine the input with an update and I'm having a problem I will start another thread on.

Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources