How to just insert one column value into a multi column temp table

Hello Experts. I have a temp table, and initially I just want to insert into it, one column value "EntityId". So after the insert statement all EntityId column fields will be populated based upon my insert statement that involves a sub query.

 My insert statetement is failing though. I have tried changing the syntax of the insert statement, and my syntax is incorrect. Can someone tell me how to change the INSERT statement below, so that it will insert the #TempTable with the EntityId values?

CREATE TABLE #TempTable (EntityId varchar(64),
                              NameOfEntry varchar(150),
                              AgeId varchar(10))

INSERT INTO #TempTable SELECT EntityId from Customers where EntityId NOT IN (Select EntityId from OldCustomers)
LVL 2
brgdotnetcontractorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Try
INSERT INTO #TempTable SELECT EntityId,'','' from Customers where EntityId NOT IN (Select EntityId from OldCustomers) 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim HornMicrosoft SQL Server Data DudeCommented:
INSERT INTO #TempTable 
SELECT EntityId 
from Customers
where EntityId NOT IN (Select EntityId from OldCustomers)

Open in new window

The reason your query fails is because the INSERT INTO clause does not spell out the EntityId column, so it expects that all columns in the table will be inserted, and when there is only one in the select it fails.

Should be...
INSERT INTO #TempTable (EntityId)  -- looky here
SELECT EntityId 
from Customers
where EntityId NOT IN (Select EntityId from OldCustomers)

Open in new window


This also means that it will insert NULL values into NameOfEntry and AgeId, so if you have something else in mind the query will have to be changed to accommodate that.
brgdotnetcontractorAuthor Commented:
Thank you Jim. I could swear I tried that before posting the question, and it was not working. I was probably just really tired. Thanks so much. I liked your solution the best. HauMinChin, also thank you very much.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.