How to set parenthesis in column name in create table Sql query

Question

I want to create a table in SQL Server2012 which has column name which includes parenthesis as

create table temp_sp 
( 
    logtime datetime,
    vcs_api varchar,
    L3(S1)Status varchar,
    L3(S2)Status varchar,
    beam_current real,
    beam_energy real,
    st1_vs1_bag1_rb real,
    ring_avg_pressure real
)

But when I use parenthesis with L3(S1)Status, L3(S2)Status then I get an error

Incorrect Syntax near '('.Expecting ')'or ','.**

How to resolve it?

Is this related to your other question: http://stackoverflow.com/questions/27799091/openquery-throws-error-when-used-with-win2k8-sql2k12 ? And you should always specify a size for variable length fields (i.e. `VARCHAR(50)` instead of just `VARCHAR`). The default length in some cases is only `1` and in others is `30`. — Solomon Rutzky, Jan 08 '15 at 05:11

score 11 · Answer 1 · edited May 23 '17 at 12:14

Parenthesis , single comma are reserved word for sqlserver. Generally avoid this type of thing.

Still you want , you need to use Bracket [] for this to tell sqlserver that it is the string , not reserved word.

So [L3(S1)] Status varchar this will work for you.

Good article :- http://technet.microsoft.com/en-us/library/aa224033%28v=sql.80%29.aspx

Which "special" characters are allowed in SQL Server varchar fields?

What characters are valid in an SQL Server database name?

http://msdn.microsoft.com/en-us/library/ms175874.aspx

Characters that must be escaped in Tsql

Felix Pamittan · Accepted Answer · 2015-01-08T05:22:54.977

5

Just put it inside []

create table temp_sp (

    logtime datetime,

    vcs_api varchar,

    [L3(S1)Status] varchar,

    [L3(S2)Status] varchar,

    beam_current real,

    beam_energy real,

    st1_vs1_bag1_rb real,

    ring_avg_pressure real

)

EDIT: As the comment below stated: You should not leave variable length fields with an unspecified length. VARCHAR and NVARCHAR has two defaults, 1 or 30, depending on how/where it is used.

edited Jan 08 '15 at 05:22

answered Jan 08 '15 at 04:57

Felix Pamittan

31,544
7
41
67

You should not leave variable length fields with an unspecified length. VARCHAR and NVARCHAR has two defaults, 1 or 30, depending on how/where it is used. – Solomon Rutzky Jan 08 '15 at 05:21

score 2 · Answer 3 · answered Jan 08 '15 at 04:58

2

Use square brackets

[Like (this)]

But don't do it. It makes all your SQL awkward to write and may also make SPs and code calls more difficult.

answered Jan 08 '15 at 04:58

Ryan

3,924
6
46
69

Ok,but I have to create a temporary table which store results from stored procedure .For this I have to create a table with column name same as that of stored procedure and my stored procedure contains column with (). – SRY_JAVA Jan 08 '15 at 05:04
No rule that same structure need, you can change the name at runtime. Also avoid this thing, other wise in future create an issue. – Ajay2707 Jan 08 '15 at 05:16
@SRY_JAVA No, the table that the results are inserted into does not need to have the same column names. They need to have the same datatypes (or ones that are implicitly convertable). They don't even need to have the same number of fields: the table needs to have at least the same number, but can have more fields. If the table has more fields than the proc returns, make sure to specify the field list in the INSERT. – Solomon Rutzky Jan 08 '15 at 05:20

How to set parenthesis in column name in create table Sql query

3 Answers3