Hi,
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
asked
Identity column Problem
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...
Also, you don't need to do the `top (0)` if you're happy with loading the data on the first hit. So you can just do `SELECT id as id2, * into ...`, and the alter/rename bits.
You just need to replace the * with a list of all the columns apart from the identity column.
Need all columns of table A but the column which has identity should not be available after copying the data from A to B table
What do you mean by "should not be available"? Can you please provide more detail for your needs?
I think he wants to ensure that it no longer is a constraint... just a data column.
using 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.
You can create the table with two copies of the identity field, and then remove the original and rename the new one. I've put an answer in now to be able to demonstrate.
Instead of using SELECT * INTO, create table B in advance (without the identity definition) and then use INSERT INTO.
- Generate the create script for Table A.
- Remove the IDENTITY portion of the identity column definition.
- Change the table name (in the script) to the correct name for Table B.
- Execute the script.
Run this SQL code (with the correct table names):
INSERT INTO Table_B SELECT * FROM Table_A
If this entire process needs to be run in a script, you can generate dynamic SQL by querying sys.columns or syscolumns (depending on your version of SQL Server).
