Error in sql statement

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

ALTER TABLE AllotmentTab
ADD ID INT IDENTITY PRIMARY KEY,Registration AS '4MT08' + RIGHT('5100' + CAST(ID AS VARCHAR(50)), 6)
PERSISTED)

can any one telme what s the error.its showing error near PERSISTED

Posted 6-Mar-12 1:18am

ythisbug

Add a Solution

Comments

ythisbug 6-Mar-12 7:25am

Msg 2705, Level 16, State 4, Line 1
Column names in each table must be unique. Column name 'ID' in table 'AllotmentTab' is specified more than once.

now coming error like this..i removed bracket after persisted

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2012-03-06T01:25:00

Solution 1

You are doing string concatenation in a data definition query - Registration AS '4MT08' + RIGHT('5100' + CAST(ID AS VARCHAR(50)), 6) cannot be included in this type of sql statement.

Posted 6-Mar-12 1:25am

Abhinav S

Comments

ythisbug 6-Mar-12 7:38am

ACTUALLY I WANT TO ADD COLUMN THAT I CAN SET 4MT085100 VALUE IN THAT COLUMN THEN I WANT TO SET THIS AS AUTO INCREMENT.SO M TRYING THAT QUERY.CAN YOU SUGGEST ME?

E.F. Nijboer · Answer 2 · 2012-03-06T01:25:00

Solution 2

PERSISTED) looks to be something on its own. It looks like something that belongs to something and not to the statement above.

Good luck!

Posted 6-Mar-12 1:25am

E.F. Nijboer

Comments

Pete O'Hanlon 6-Mar-12 7:29am

Sadly, that part has nothing to do with the error he's getting. The actual error is the QUERY syntax in the alter table - this is not allowed.

ythisbug 6-Mar-12 7:44am

ok..ACTUALLY I WANT TO ADD COLUMN THAT I CAN SET 4MT085100 VALUE IN THAT COLUMN THEN I WANT TO SET THIS AS AUTO INCREMENT.SO M TRYING THAT QUERY.CAN YOU SUGGEST ME?

E.F. Nijboer 7-Mar-12 3:28am

My advice would be to create another table that has the ID as you want it. You need to do some copying and renaming but I think that's the only way.

Pete O'Hanlon · Answer 3 · 2012-03-06T01:28:00

Solution 3

You are attempting to mix Data Manipulation and Query syntax in one place. The ALTER TABLE statement is intended to modify a table structure, which is what the first part is doing. The second part looks like you are trying to retrieve a value. What are you trying to achieve here?

Posted 6-Mar-12 1:28am

Pete O'Hanlon

Comments

ythisbug 6-Mar-12 7:31am

ACTUALLY I WANT TO ADD COLUMN THAT I CAN SET 4MT085100 VALUE IN THAT COLUMN THEN I WANT TO SET THIS AS AUTO INCREMENT.SO M TRYING THAT QUERY.CAN YOU SUGGEST ME?

**AmitGajjar** · Answer 4 · 2012-03-06T01:51:00

Solution 4

Hi,

As Pete o'Hanlon said. you are mixing your query,

You need two different query, first for alter your table then second for inserting your first record. and 4MT085100 is varchar field then it could not be auto incremental. you need to write Before Insert Trigger (it should execute before inserting row) that will generate your next number and achieve your increment behavior.

Thanks
-Amit

Posted 6-Mar-12 1:51am

AmitGajjar

Comments

ythisbug 6-Mar-12 7:58am

ok thanks can you tel me how to write query for that.m stuck for this

AmitGajjar 6-Mar-12 8:01am

1) create alter query to modify your column
2) insert your first record.
3) create trigger.(if you don't know about trigger then first you need to go through then basics of triggers)

write some code and if you get stuck/ getting any error, you can post it.

Thanks.