Hi there,
alter table HardwareInventory alter column [HIID] [nvarchar] IDENTITY (1,1) NOT NULL
Getting this error -
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IDENTITY'.
can someone please suugest
thanks
akhil
Hi(SQL Server 2000)I have an existing table (t) with a column that is NOT an identity column(t.ID), but it has manually inserted "row numbers". I want to make thiscolumn become an identity column. This column is a key field to othertables, so I want to keep the row numbers that are allready inserted.From the Query Analyzer, how do I do this?Thanks in advance!Regards,Gunnar VøyenliEDB-konsulent asNORWAY
View 3 Replies View RelatedHi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.
View 2 Replies View RelatedHi,
How to make (int) Identity auto increment field showed like
0001
0002
0003
0004
...
I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.
Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?
Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint
View 2 Replies View RelatedI'm working with a third-party database (SQL Server 2005) and the problem here is the following:
- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).
- The PK of T is an Integer.
Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.
Considering that I can't change the PK, what is the best approach to solve this problem?
To sum up:
* I need to have processes in parallel inserting on T
* I can't change anything on T
* The PK is NOT an Identity
Hi all,
The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..
i cannot implement this constraint, it throws the error when i execute the below Alter query,
ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE
the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.
any workarounds for this ?
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
Hello;
My Memebership table has Guid column as Primary key.
But I would like to add Auto numbering Identity column to this table.
Is this idea OK or it will bring some problems?
Thank you in advance for your help
i have a table
table1
column1 int not null
column2 char not nul
column3 char
i want to script a change for table1 to alter column1 to be the table identity column. not primary.
Hi,
I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.
I tried
alter table <table_name>
alter column <col_name> int Identity (1,1)
but it fails.
Ajay
WORD4LIFE
(http://www.word4life.com)
Hi,
I have two tables table1 and new_table
Table1 has id_value column which is int and it is idenity specification is yes and identity increment is 1 .
And I have a NEW_TABLE with column name new_id which should store current id_value of Table1.
This type of functionality is requirement for my project.
I should get a current value of id_value from table1 . if I say SELECT * FROM NEW_TABLE ;
Please help me out to fix this issue
Thanks
Purnima
I have a table that contains names that are all in upper case, this column is called in many different areas of my web app. I wanted to make the names all lowercase, or with the leading character only capitalized.
How can I make a column within a SQL table lowercase at the SQL server end and not the programming side?
thanks,
Frank
Here is my create table command
CREATE TABLE EXCLUSIVEITEM(VIEW_ID INTEGER NONCLUSTERED IDENTITY(1,1)PRIMARY KEY,
VIEW_LATEST_REPLY_DATE CLUSTERED INDEX DATETIME)
Is this the way to create one of my columns clustered. The books that I have all use stored procedure I only want to use regular sql commands. Is this possible?
Hai Everyone,
I have a table with data like this
Tran_no Description Debit Credit
1 SW 1000
1 BR1 500
1 BR2 200
1 BR3 300
2 SQ 500
2 BW1 200
2 BW2 200
2 BW3 100
Now what I wanna do this that
Tran_no Val1 Val2 Val3 Val4
1 1000 500 200 300
2 500 200 200 100
Thanks in advance
Nirene
I have a store procedure to create a table temp, populate data from Origin1 table, and then add a new column SSN to temp table, update the SSNs from another table. but the following code gives error, pleaes help!
Create table temp (
....
)
Insert into temp
select * from Origin1
Alter table temp ADD Name int
Update temp
Set SSN = (select SSN from Origin2
where id = Origin.id)
it gives 'Invalid column name: 'SSN'
I am aware that the add new column doesn't take effect until end of the SP, but there must be a way, Thanks for any inputs.
if a customer can have up to 10 acounts and each account has a balance,
in customer_account table, the same custID repeats and relates to an
accountID and each account has its own balance.
How to display each customer in ONE row and list his account one next to another ?
for example:
custID, acct#1, acct#1Balance, acct#2, acct#2Balance, acct#3, acct#3Balance
how to do write SQl to achive it ?
This is chanllenging to me...
Your advise is greatly appreciated !
This is what I would like to do...
1. Alter Table Status
Add ConsiderOpenFlag int null
2. UPDATE values...
3. Alter Table Status
Alter ConsiderOpenFlag int not null
Steps 1 and 2 are easy. What I cannot figure out is step three.
I don't want to have a default on that column, though I wouldn't mind adding it and then dropping it later if it would help.
Jonathan
Hello, I have a table (publication) that has a primary key pubID which is an identity specification. This ID already has relationships to other tables.
I am having problems trying to make one of the other columns in the same table to auto generate. This column (bibNumber) is not related to any other table. All i want to do is when i create a new record in the table, auto generate a number for this column bibNumber.
How can i do this?
I am using sql server express 2005
Regards
Hello, everyone:
I have a table with an indentity column as first column. At beginning it is continue such as 0-50. I delete last 20 columns by hand. The 0-31 is left. When the new data is inserted, I hope the new indentity column begin from 32. How to do that? Now the indentity column begin from 51 as the new data is inserted.
Thanks a lot
ZYT
I have a table with a TEXT column that allows nulls.
Now I need to change it, and make it NOT NULL, but I Can't
ALTER TABLE BUSINESS_UNIT_LANG
alter column VISION text not null
And I get:
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'VISION' because it is 'text'.
Is it impossible to make a text column NOT NULL when it accepts NULLS ?
All rows and columns are populated, in consequence there isn't any col with null values
Any idea?
Thanks!
when we display hierarchies, we'd like the user to have the option of expanding a column for wider viewing, perhaps by dragging a border. Is this possible?
View 3 Replies View Related the Table columns is like this NO ProductNo Area In Out1 0001 US NULL NULL2 0002 UK NULL Y3 0003 FR Y NULL 4 0004 FR Y NULL5 0005 UK Y NULL I have Query get the result belowArea Count In&OutUS 1UK 2FR 2 the Area is Group By by Area and the Count columns is counting how many recoreds Table for each Areathe problem is the column "In & OUt'I have to make sure if the "In" or "Out" is Null , if one of them is Null the plus 1 so the result would like I have Query get the result belowArea Count In&OutUS 1 1
UK 2 1
FR 2 0 which syntax I can use for the problem? I just think maybe I can use IsNULL?but I have no idea how to wirte a query ...can you give me a hint? thank you
Hi,
I have a table of results for various measured quantites and i need to turn this into a view. Only problem is i need to seperate the measured quantities and their respective values into seperate columns.
At the moment I have something like:
Quantity : Value
--------------------
Quantity 1 : 0.12
Quantity 1 : 0.56
Quantity 2 : 2.36
Quantity 2 : 5.34
Quantity 2 : 4.13
Quantity 3 : 10
Quantity 3 : 15
and I need a view that looks like:
Quantity 1 : Quantity 2 : Quantity 3
-------------------------------------
0.12 : 2.36 : 10
0.56 : 5.34 : 15
null(?) : 4.13 : null(?)
I've tried using pivots but they don't seem to help
Any ideas?
Hi, I want to change an int column (not null) to identity column. I tried
the following:
alter table myTable alter column ID int identity(10, 1) not null
But it failed with the error message:
Incorrect syntax near the keyword 'identity'.
Can someone please show me the correct statement (if it exists)>
Many thanks.
I need to insert and update a table. One column of the table needs to conform to a format as: XXXXXXX.XXXXX
If any data is not complying to the format an error needs to be thrown.
How to implement this constraint!
Currently I'm using a UDF and a physical temp table to accomplish thisand I want to know if there's any way of doing it in a query orsubquery instead...In TableA there are a bunch of rows:InvitationID (PK) PartyID Partygoer1 1 Jim2 20 Bob3 2 Frank4 1 Robert5 1 PeteIn TableB are rows:PartyID (PK) PartyDate PartyName1 1/1/2000 WildParty2 1/1/2000 BoringPartyWhen a user runs a query to search for all parties on 1/1/2000 I wantthe result to look like:PartyID PartyDate PartyName Partygoer1 1/1/2000 WildParty Jim, Robert, Pete2 1/1/2000 BoringParty FrankI'm hoping there's a solution to this.Thanks,lq
View 9 Replies View RelatedI'm making some reports that are made for getting printed. I currently have three groups of reports, each group having four charts (four A4 papers/group). To cut down on the sheer number of papers on the boards I've been asked to merge these charts so that we'll have all three groups in one chart. This works fine for three of the charts (two line charts and one column chart), where the results gets neatly organized in group order. Where I ran into problems was the last chart..
The last chart is a stacked column chart. With one group, it works quite well; the x-axis is by year/week, and for each week there's a bar where the lower part is "done" and the upper part is "failed".
Now when doing this graph with multiple groups (groups as series), it doesn't put the different groups beside each other as I would've wished, instead it piles them on top of each other as if it was a 100% stacked column.
So the question is; is there some way to make a stacked column graph with multiple series that are one stacked bar per group instead of having all of them stacked on top of each other? Am I just missing some small detail here?
I created a query that got the following result. But I expect to get the structure like, care_nbr, cust_nbr,legal_name, address_type=physical address, addr_line_1, addr_line_2, address_type-primary address, ddr_line_1, addr_line_2. That means I only need primary and physical address, and expect them to show in a row to each care_nbr. How to perform that?
CARE_Nbr||Cust_Nbr||Legal_Name||||||| Address_Tpye |||Addr_Line_1 ||||||||||||||||Addr_Line_2
99000001||004554||Mac Marketing, LLC||Billing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Mailing Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Primary Address||210 Parktowne Blvd Ste. 1||NULL
99000001||004554||Mac Marketing, LLC||Physical Address||210 Parktowne Blvd Suite 1||NULL
My script is here, and the sample result is attached. How should I modify this query to get my expected result?
select a.CARE_Number,
a.Customer_Nbr_Txt,
a.Customer_Type_Txt,
a.Legal_Name_Txt,
c.Address_Type_Txt,c.Address_Line_1_Txt,c.Address_Line_2_Txt,
a.Customer_Status_Txt,
a.Legal_Entity_Type_Txt,
a.Business_Unit_Txt
FROM dw_mart.dbo.DimCustomer a
[code]....
The reason I say this is because a subtotal of a dollar amount will take up more space than other values. Right now, I'm forced to make all columns the same larger width because it appears to be all wrapped into 1 column width setting. I can try to change the value of the subtotal column, "matrixcolumn4", but it reverts to the other value after I press enter to apply the changes.
View 1 Replies View RelatedI am sorry if this is the wrong forum for this question but I can not find better fit.
I am using Visual Studio 2005 to write an asp.net web site which includes an SQL data base. In the databaseI have a table called 'ACCOUNTS' which has primary key column called 'ID' and a nvarchar column called 'Name'.
I want the values in the 'Name' column to be unique in the table so that all accounts have unique names. I don't want to make the 'Name' column the primary key because I want people to be able to change the name of their account after it has been created.
It seems to me there must be some way to add such a constraint to the table but I can find none. I have searched the documentation and seen loads of references to unique columns but I can't find any way to set a column to unique thru the IDE (or any other way). Can anyone help me?
Thanks in advance for any help.