Insert fields into a table from another table as distinct

why does this statement fail and what should it be? there are other fields in the table which all accept null so do i have to add them too?


 insert into SageMasterAddresses (account_ref)
  values
  select distinct(account_ref)
  from SageSalesnEW where SLXAccountREFID is null
Chris MichalczukConsultantAsked:
Who is Participating?
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.

Vikas GargBusiness Intelligence DeveloperCommented:
Hello

You should not use values while inserting from SQL statement
it is used only when passing static values

Try this

insert into SageMasterAddresses (account_ref)
  select distinct(account_ref)
  from SageSalesnEW where SLXAccountREFID is null

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>why does this statement fail
For starters, a statement like 'why does this fail' could be answered easier if you provide us the error message that returns when it is executed.

>what should it be?
Along the same lines, you tell us what you're trying to do here.

>there are other fields in the table which all accept null so do i have to add them too?
Yes.  If there are ANY fields other than account_ref that do not accept NULLS, then the above statement will fail, and yes you'll need to pass them like this...
insert into SageMasterAddresses (account_ref, column1, column2, column3)
select distinct(account_ref), NULL, NULL, NULL
from SageSalesnEW 
where SLXAccountREFID is null

Open in new window

0

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
awking00Commented:
Some relevant sample data for the SageSalesnEW table and what you would like to see in the SageMasterAddresses table would be most helpful.
0
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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.