HOW can I assign the value of @.@.IDENTITY to the any variable in SQL SERVER .
Thanks,
DECLARE @.var int
INSERT INTO <table> VALUES <...> SELECT @.var= @.@.IDENTITY
On another note, I'd recommend using SCOPE_IDENTITY() instead of @.@.IDENTITY. check out books online for some info. ScopeIdentity is more accurate in returning the autonumber id.
This is a very basic question on replication.
I'm having a central Server with SQL Server 2005 Standard Edition and Other sites with Sql Express Server 2005.
Other sites will also be adding New records and data will be replicated to Central server and from there it will be distributed to all sites.
Question is that if Other sites are also adding Records how i can assing Identity values in those databases. There are few restricitons on this :-
1. I don't want to use GUID.
2. Numbers should be sequential that is after 1000, 1001, 1002 etc. should come.
i thought of adding Negative Values in the primary key on other sites and then when data is replicated on central server then replace it with sequential key but i'm not clear on how to accomplish this.
any help will be highly appreciable.
You can specify identity ranges. See books online topic "Replicating Identity Columns". You can also search books online for "replication identity" for a range of topics.|||thanks for your reply.
i saw the topics which you have mentioned. As per my requirement i/o specify individual ranges i have to keep this Identity column in Sequence for all sites. so i will have to do this manually..
is it possible to point towards some code which does that as i'm sure this is a very common requirement and lot of people must have already written generic code to accomplish this.
I tried
Alter table table1 alter column column1 Identity(1,1);
Alter table table1 ADD CONSTRAINT column1_def Identity(1,1) FOR column1
they all can not work,
any idea about this? thanks
Code Snippet
--create test table
createtable table1 (col1 int, col2 varchar(30))
insertinto table1 values(100,'olddata')
--add identity column
altertable table1 add col3 intidentity(1,1)
GO
--rename or remove old column
execsp_rename'table1.col1','oldcol1','column'
OR
altertable table1 dropcolumn col1
--rename new column to old column name
execsp_rename'table1.col3','col1','column'
GO
--add new test record and review table
insertinto table1 values('newdata')
select*from table1
|||
You can't alter the existing columns for identity.
You have 2 options,
1. Create a new table with identity & drop the existing table
2. Create a new column with identity & drop the existing column
But take spl care when these columns have any constraints / relations.
Code Snippet
/*
For already craeted table Names
Drop table Names
Create table Names
(
ID int,
Name varchar(50)
)
Insert Into Names Values(1,'SQL Server')
Insert Into Names Values(2,'ASP.NET')
Insert Into Names Values(4,'C#')
*/
Code Snippet
--In this Approach you can retain the existing data values on the newly created identity column
CREATE TABLE dbo.Tmp_Names
(
Id int NOT NULL IDENTITY (1, 1),
Name varchar(50) NULL
)ON [PRIMARY]
go
SET IDENTITY_INSERT dbo.Tmp_Names ON
go
IF EXISTS(SELECT * FROM dbo.Names)
INSERT INTO dbo.Tmp_Names (Id, Name)
SELECT Id, Name FROM dbo.Names TABLOCKX
go
SET IDENTITY_INSERT dbo.Tmp_Names OFF
go
DROP TABLE dbo.Names
go
Exec sp_rename 'Tmp_Names', 'Names'
Code Snippet
--In this approach you can’t retain the existing data values on the newly created identity column;
--The identity column will hold the sequence of number
Alter Table Names Add Id_new Int Identity(1,1)
Go
Alter Table Names Drop Column ID
Go
Exec sp_rename 'Names.Id_new', 'ID','Column'
|||
thank you
but I could not change the existing number order and add identity
any idea about this?
|||Nope. There is no way to alter a column to have the identity property. You will have to create a new table and insert into it if you want initial control over the values in the column.Dear All,
I wanted to add a IDENTITY constraint in a column using the ALTER command, How do I go about doing that?
Please help.
Code Snippet
create table #what (something varchar(10))
alter table #what
add anIdentity int identity
insert into #what
select 'This' union all
select 'is' union all
select 'just' union all
select 'another' union all
select 'test'
select * from #what
/*
something anIdentity
- --
This 1
is 2
just 3
another 4
*/
Sorry for incomplete or unclear question.
What I mean is Altering the existing column and adding Identity to that column
|||I think to do that you will need to do the work of creating a new table and move the data into the new table. Care needs to be take to pay attention to the seeding of the new identity column and all constraint work that needs to be done.
Will someone please check me on this?
|||Unfortunately, i don't think you are able to alter an existing column to be an identity. Your best workaround is to create a new table with the correct schema, dump the data into it and then rename it.
However, I appreciate this can be a messy and impractical approach. Your only other option is to just add an extra identity column.
HTH
|||The following should work:
Code Snippet
CREATE TABLE TestTable
(
ToBeIdentity INT,
TestCol VARCHAR(20)
)
INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(1, 'Col1')
INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(2, 'Col2')
INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(3, 'Col3')
INSERT INTO TestTable(ToBeIdentity, TestCol) VALUES(4, 'Col4')
SELECT
*
INTO
#TempTransfer
FROM
TestTable
ALTER TABLE TestTable DROP COLUMN ToBeIdentity
DELETE FROM TestTable
ALTER TABLE TestTable ADD ToBeIdentity INT IDENTITY(1, 1)
SET IDENTITY_INSERT TestTable ON
INSERT INTO TestTable
(ToBeIdentity,
TestCol)
SELECT
*
FROM
#TempTransfer
SET IDENTITY_INSERT TestTable OFF
INSERT INTO TestTable(TestCol) VALUES('Col5')
SELECT
*
FROM
TestTable
DROP TABLE #TempTransfer
It is not necessary to create a new table and transfer the data -with a very large table that could be quite a performance hit.
As this example demonstrates, you can add an IDENTITY column to an existing table. (NOTE: This does not guarantee any order to the data.)
Code Snippet
CREATE TABLE #MyTable
( RowID int,
MyValue varchar(20)
)
INSERT INTO #MyTable VALUES ( 25, 'Value 1' )
INSERT INTO #MyTable VALUES ( 2, 'Value 2' )
INSERT INTO #MyTable VALUES ( 61, 'Value 3' )
INSERT INTO #MyTable VALUES ( 33, 'Value 4' )
-- Keep Existing Column and Data
ALTER TABLE #MyTable
ADD NewRowID int IDENTITY(1, 1)
SELECT *
FROM #MyTable
RowID MyValue NewRowID
-- -- --
25 Value 1 1
2 Value 2 2
61 Value 3 3
33 Value 4 4
-- If you want to keep the Column Name -but not data
ALTER TABLE #MyTable
DROP COLUMN RowID, NewRowID
ALTER TABLE #MyTable
ADD RowID int IDENTITY(1, 1)
SELECT *
FROM #MyTable
DROP TABLE #MyTable
MyValue RowID
-- --
Value 1 1
Value 2 2
Value 3 3
Value 4 4
If you want to maintain or coerce order, creating a new table and transferring the data would allow you to use ORDER BY on the insert statement.
Arnie Rowland wrote:
It is not necessary to create a new table and transfer the data -with a very large table that could be quite a performance hit.
As this example demonstrates, you can add an IDENTITY column to an existing table.
Code Snippet
...
CREATE TABLE #MyTable
( RowID int,
MyValue varchar(20)
)
Your foreign keys on the original column become messed up unless you preserve the original values.
For an ASP.net project, I have had a DropDownList with a static ArrayList.
The ArrayList will be defined from a View, where there is no Identity PK.
I also have used cbxDropDownListName.SelectedIndex to add new data to
a table, where an Indetity PK is used to reference the ArrayList.
I am wondering how can I add an identity PK to my view?
TIA,
Jeffrey
Why don't you select an identity column from one of the tables in the view? Pick an identity column that, by the view definition, would be unique in the view result set.
