I have created a table called 'A' with identity column ... Suppose if i am inserting the data from 'A' table to 'B' table
select * into B from A
Now i don't want Identity column for table B.
If any help is highly appreciatable
How about code along these lines:
/* I'm creating a table to use as my source... ignore if it you want */ create table dbo.test1 (id int identity(1,1) primary key, val int not null); insert dbo.test1 values (1); insert dbo.test1 values (3); insert dbo.test1 values (5); insert dbo.test1 values (7); /* The trick is here... creating a second copy of the id column that isn't recognised to be an identity column */ select top (0) id+0 as id2, * into dbo.test2 from dbo.test1 ; /* Now I drop the real id column and rename id2 to id */ alter table dbo.test2 drop column id; exec sp_rename 'dbo.test2.id2', 'id', 'COLUMN'; /* ...so that my insert 'just works' now */ insert dbo.test2 select * from dbo.test1; /* Now clean-up */ drop table dbo.test1; drop table dbo.test2;
See how you go with this...
select into means that the table structure for B is taken from A, so the identity property is carried over.
The only way to remove the identity property is recreate the table without it. You can do this in SSMS, by right-clicking the table, choose 'Design'. Then highlight the column you need, and in the column properties (lower part of the screen) in Identity Specification, change (Is Identity) to 'No'
You can also script out the change before you commit it to see what SQL is being generated to perform this. You will see that the whole table is copied out to another table without the Identity, the original table dropped, and finally the new table renamed to the old table.
Instead of using
SELECT * INTO, create table B in advance (without the identity definition) and then use
Run this SQL code (with the correct table names):
INSERT INTO Table_B SELECT * FROM Table_A
No one has followed this question yet.