varchar identity not working sqlserver

Question

0.00/5 (No votes)

See more:

here getting first table value in second table and setting identity for id this is not working anyother way....

SQL

CREATE TABLE TestTable2
    (
    
      srlNo int NOT NULL
             IDENTITY(1, 1),
      Id    varchar(100),
      Name varchar(50) NULL,
      Addres varchar(100)
    )
ON  [PRIMARY]
go

CREATE TABLE TempTable2
    (
      Identifier    int NOT NULL
                     IDENTITY(1, 1),
      Id as 'test'+cast(Identifier as varchar(100)), 
                    
      Name           varchar(50) NULL,
      Addres         varchar(100)
    )
ON  [PRIMARY]
go

SET IDENTITY_INSERT TempTable2 ON
go

IF EXISTS ( SELECT  *
            FROM    TestTable2 ) 
    INSERT  INTO TempTable2 ( Id, Name,Addres )
            SELECT  Id,
                    Name,
                    Addres
            FROM    TestTable2 TABLOCKX
go

SET IDENTITY_INSERT TempTable2 OFF
go

DROP TABLE TestTable2
go

Exec sp_rename 'TempTable2', 'TestTable2'

C#

Error like this:
-----------------
Msg 271, Level 16, State 1, Line 4
The column "Id" cannot be modified because it is either a computed column or is the result of a UNION operator.
Caution: Changing any part of an object name could break scripts and stored procedures.

Posted 27-Jul-12 0:31am

oliver grace

Updated 27-Jul-12 0:36am

AshishChaudha

v2

Add a Solution

3 solutions

Solution 2

Try this:

SQL

CREATE TABLE TestTable2
    (
    
      srlNo int NOT NULL
             IDENTITY(1, 1),
      Id    varchar(100),
      Name varchar(50) NULL,
      Addres varchar(100)
    )
ON  [PRIMARY]
GO
 
CREATE TABLE TempTable2
    (
      Identifier    int NOT NULL
                     IDENTITY(1, 1),
      Id as 'test'+cast(Identifier as varchar(100)), 
                    
      Name           varchar(50) NULL,
      Addres         varchar(100)
    )
ON  [PRIMARY]
GO

SET IDENTITY_INSERT TempTable2 ON
GO
 
IF EXISTS
(SELECT *
 FROM
	 TestTable2)
INSERT
INTO
	TempTable2 (Id
			  , Name
			  , Addres)
SELECT Id
	 , Name
	 , Addres
FROM
	TestTable2 TABLOCKX
GO

SET IDENTITY_INSERT TempTable2 ON --Make IDENTITY_INSERT ON
GO

DROP TABLE TestTable2
GO

EXEC sp_rename 'TempTable2', 'TestTable2'

Posted 27-Jul-12 0:41am

Prasad_Kulkarni

Solution 3

Try this, You are trying to insert into an identity field.

SQL

CREATE TABLE TestTable2
    (
    
      srlNo int NOT NULL
             IDENTITY(1, 1),
      Id    varchar(100),
      Name varchar(50) NULL,
      Addres varchar(100)
    )
ON  [PRIMARY]
go
 
CREATE TABLE TempTable2
    (
      Identifier    int NOT NULL
                     IDENTITY(1, 1),
      Id as 'test'+cast(Identifier as varchar(100)), 
                    
      Name           varchar(50) NULL,
      Addres         varchar(100)
    )
ON  [PRIMARY]
go
 
SET IDENTITY_INSERT TempTable2 ON
go
 
IF EXISTS ( SELECT  *
            FROM    TestTable2 ) 
    INSERT  INTO TempTable2 ( Name,Addres )
            SELECT  
                    Name,
                    Addres
            FROM    TestTable2 TABLOCKX
go
 
SET IDENTITY_INSERT TempTable2 OFF
go
 
DROP TABLE TestTable2
go
 
--Exec sp_rename 'TempTable2', 'TestTable2'

Posted 27-Jul-12 0:42am

AshishChaudha

Add a Solution

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

Santhosh Kumar Jayaraman · Accepted Answer · 2012-07-27T00:41:00

Solution 1

In case if user wants identity also in varchar column for the above scenario..

SQL

drop table testtable2
go
CREATE TABLE TestTable2 (
srlNo int NOT NULL IDENTITY(1, 1),
Id varchar(100),
Name varchar(50) NULL,
Addres varchar(100)
) ON [PRIMARY]
go

insert into TestTable2 values('test1','sant','test')
go
CREATE TABLE TempTable2 (
Identifier int NOT NULL IDENTITY(1, 1),
Id as 'test'+cast(Identifier as varchar(100)),
Name varchar(50) NULL,
Addres varchar(100)
) ON [PRIMARY]
go
SET IDENTITY_INSERT TempTable2 ON
go
IF EXISTS ( SELECT * FROM TestTable2 )
INSERT INTO TempTable2 (Identifier, Name,Addres )
SELECT srlNo,Name, Addres FROM TestTable2
TABLOCKX
go
SET IDENTITY_INSERT TempTable2 OFF
go
DROP TABLE TestTable2
go
Exec sp_rename 'TempTable2', 'TestTable2'

select * from testtable2

Posted 27-Jul-12 0:41am

Santhosh Kumar Jayaraman

Updated 27-Jul-12 0:42am

v2

Comments

oliver grace 27-Jul-12 6:49am

EXCELLENT THANKS A LOT.....

Raghunatha_Reddy_S 27-Jul-12 6:59am

5*

oliver grace 27-Jul-12 7:03am

BUT HERE I AM NOT GETTING EXISTING TABLE Id values

oliver grace 27-Jul-12 7:16am

name,address i am getting fine except id ...
new id's generating for existing table data's

Santhosh Kumar Jayaraman 27-Jul-12 7:21am

No. You cant retain Id column values since id in new table depends on Identifier column

oliver grace 27-Jul-12 7:24am

then how to retain existing table id value

Santhosh Kumar Jayaraman 27-Jul-12 7:28am

If you want to follow ur new pattern such as 'test+1, 'test'+2 in ID column, then you cant retain existing values.And if you want to retain existing values from previous table, then you shouldnt use it as computed column. The only solution is You have to create new Id based on the current Identifier column

oliver grace 27-Jul-12 7:33am

is this thing possible in stored procedure..

Santhosh Kumar Jayaraman 27-Jul-12 7:33am

trying now..Will let you know

Santhosh Kumar Jayaraman 27-Jul-12 7:37am

No its not possible. Check this link.
http://msdn.microsoft.com/en-us/library/ms191250%28v=sql.105%29.aspx

It says "A computed column cannot be the target of an INSERT or UPDATE statement. " So you cannot insert or update even through stored proc

varchar identity not working sqlserver

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

varchar identity not working sqlserver

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us