September 5, 2006 at 8:46 pm
Hi group,
Table ABC - non of the columns are Identity
Structure of ABC: RecID Int(4) - Not Identity
Name varcahr(100)
Address varchar(100)
Now I have to insert Name and Address from table XYZ and increment values for RECID for every record inserted. Can you please let know how can I do that? Thanks in advance.
September 5, 2006 at 9:15 pm
Change the column type to IDENTITY.
_____________
Code for TallyGenerator
September 5, 2006 at 9:27 pm
I want to change column type to IDENTITY through stored procedure. Is it possible?
September 5, 2006 at 10:41 pm
CREATE TABLE dbo.tmp_ABC
(
RecID int NOT NULL IDENTITY (1, 1),
Name varchar(100) NOT NULL,
Address varchar(100) NOT NULL
)
SET IDENTITY_INSERT dbo.tmp_ABC ON
INTO dbo.Tmp_ABC
(RecID , Name , Address )
SELECT RecID , Name , Address
FROM dbo.ABC TABLOCKX
SET IDENTITY_INSERT dbo.Tmp_ABC OFF
DROP TABLE dbo.ABC
EXECUTE sp_rename N'dbo.Tmp_ABC', N'ABC', 'OBJECT'
GO
If there are FK constraints you need to drop it first and recreate in new table.
_____________
Code for TallyGenerator
September 7, 2006 at 10:12 am
You can't change a column to add or remove the Identity property. The table-editing function in Enterprise Manager or Management Studio may make it look easy, but in both cases they are building a new table and copying all the data. And recreating all foreign keys, indexes, and everything else. And possibly breaking existing code that refers to the table. This is not a trivial operation that you want to put in a stored procedure to be run repeatedly.
If you want to leave the RecID column alone (non-identity) but still want to insert sequentially-numbered records, you can do that by first inserting the records into a temp table with a properly-seeded identity column and then inserting the records with good RecID values into the target table.
DECLARE
@cmd varchar(8000)
SELECT @cmd = replace('SELECT IDENTITY(int, <seed>, 1) as RecID, Name, Address INTO #newrecs FROM xyz', '<seed>', max(RecID)+1)
FROM abc
EXEC (@cmd)
INSERT INTO abc (RecID, Name, Address)
SELECT RecID, Name, Address FROM #newrecs
DROP TABLE #newrecs
This script assumes that no new records will be inserted into abc by other users. If that is a possibility, you could use max(RecID)+10 or +100 to leave a gap, or put it in a transaction and use "FROM abc WITH(TABLOCK, HOLDLOCK)" to prevent conflicts.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply