SQL SERVER 2000 Insert SQL with Sub Query not working

Question

Post reply

SQL SERVER 2000 Insert SQL with Sub Query not working

Lennie

SSC Eights!

Points: 841
More actions
August 24, 2010 at 8:05 pm

#140330

Hi Good Guys, :w00t:
I need your help, Please Help me. 😛
he DATABase Administrator is away for 3 months holidays and I was asked to transfer Customer ContactTitle from the TblCustomers Table into a another Table TblContTitleRef and at the SQL QUERY ANALYSER I did not get it right
Here are the SQL String:
Insert into TblContTitleRef (ContactTitle) Values
(select distinct(ContactTitle) from TblCustomers where TblCustomers.ContactTitle <> Null);
These are the error message:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near ')'.
Please help me. Thanks.
Have a Good Day.
cheers,
Lennie

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

happycat59 One Orange Chip Points: 29389 More actions · Answer 1

You are using the wrong syntax

try the following instead

Insert into TblContTitleRef (ContactTitle)

select distinct(ContactTitle) from TblCustomers where TblCustomers.ContactTitle <> Null;

Lennie SSC Eights! Points: 841 More actions · Answer 2

Hullo Pussy Cat,

You mentioned that the SQL string I have created have syntax error but you did not provide the correct syntax. So that I can try it out. You could be wrong too.

happycat59 One Orange Chip Points: 29389 More actions · Answer 3

Did you actually read my reply - I included the correct code. How about trying that !

Lennie SSC Eights! Points: 841 More actions · Answer 4

Hi Pussy Cat,

I did read your earlier statement and found that there is no proper coding.

This is your statement from earlier reply

You are using the wrong syntax

try the following instead

Insert into TblContTitleRef (ContactTitle)

select distinct(ContactTitle) from TblCustomers where TblCustomers.ContactTitle <> Null;

that's the reason why I mentioned that you did not provide sample coding and mentioned that you could be wrong as well.

My SQL string is now working and I would like share it with other Newbies who may have similar problem:

Insert into TblContTitleRef (contactTitle)

Select Distinct(contactTitle)

From TblCustomers

Where (contactTitle is not NULL);

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Lennie (8/26/2010)
Hi Pussy Cat,
I did read your earlier statement and found that there is no proper coding

Um, I don't know what you're reading, but the third and fourth lines of his post have the proper code.

Bolded, to make it clearer:

You are using the wrong syntax
try the following instead
Insert into TblContTitleRef (ContactTitle)
select distinct(ContactTitle) from TblCustomers where TblCustomers.ContactTitle <> Null;

That is not what you posted, which was

Insert into TblContTitleRef (ContactTitle) Values
(select distinct(ContactTitle) from TblCustomers where TblCustomers.ContactTitle <> Null);

Though, I'm not sure that <> Null is going to do what you want, unless the ansi nulls setting is off.

Plus the brackets around the column name (after the distinct) are unnecessary. Distinct is not a function.

p.s. there's no one by the name 'pussy cat' here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lennie SSC Eights! Points: 841 More actions · Answer 6

One of the help name is pussycat59.

If you look at the earlier responds you will notice it.

Make it your practice to view earlier posting inorder to understand the problem

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Lennie (8/26/2010)
One of the help name is pussycat59.

Nope, it's happycat59.

If you look at the earlier responds you will notice it.
Make it your practice to view earlier posting inorder to understand the problem

Maybe it's not what you intended, but that came out sounded condescending and borderline rude. I did review the entire thread before posting, several times.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lennie SSC Eights! Points: 841 More actions · Answer 8

I made typo error it should be happycat59 and not pussycat59.

Because the name is happycat59 which that cat in my house is a happy cat so I called is pussycat.

Lennie SSC Eights! Points: 841 More actions · Answer 9

since there are name like HappyCat, I will eventually change my login name a YummyRat