SELECT [schema] = s.name, [table] = t.name FROM sys.schemas AS s INNER JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id] WHERE EXISTS ( SELECT 1 FROM sys.identity_columns WHERE [object_id] = t.[object_id] );
CREATE TABLE [dbo].[Foo]( [FooId] [int] IDENTITY(1,1) NOT NULL, [BarId] [int] IDENTITY(1,1) NOT NULL ) returns Msg 2744, Level 16, State 2, Line 1 Multiple identity columns specified for table ‘Foo’. Only one identity column per table is allowed. So, no, you can’t have two identity columns. You can of course make the primary key not … Read more
If you use the DBCC CHECKIDENT command: DBCC CHECKIDENT (“YourTableNameHere”, RESEED, 1); But use with CAUTION! – this will just reset the IDENTITY to 1 – so your next inserts will get values 1, then 2, and then 3 –> and you’ll have a clash with your pre-existing value of 3 here! IDENTITY just dishes … Read more
Expanding a bit on the answers from @Guru and @Ronnis, you can hide the sequence and make it look more like an auto-increment using a trigger, and have a procedure that does the insert for you and returns the generated ID as an out parameter. create table batch(batchid number, batchname varchar2(30), batchtype char(1), source char(1), … Read more
Add an id column to the csv file and leave it blank: id,Name,Address ,name1,addr test 1 ,name2,addr test 2 Remove KEEPIDENTITY keyword from query: BULK INSERT Employee FROM ‘path\tempFile.csv ‘ WITH (FIRSTROW = 2,FIELDTERMINATOR = ‘,’ , ROWTERMINATOR = ‘\n’); The id identity field will be auto-incremented. If you assign values to the id field … Read more
If you think about it, the auto-increment number should not be transactional. If other transactions had to wait to see if the auto-number was going to be used or “rolled back”, they would be blocked by the existing transaction using the auto-number. For example, consider my psuedo code below with table A using an auto-number … Read more
Another potential way to do this for SQL Server, which has less reliance on the system tables (which are subject to change, version to version) is to use the INFORMATION_SCHEMA views: select COLUMN_NAME, TABLE_NAME from INFORMATION_SCHEMA.COLUMNS where COLUMNPROPERTY(object_id(TABLE_SCHEMA+’.’+TABLE_NAME), COLUMN_NAME, ‘IsIdentity’) = 1 order by TABLE_NAME
You are encountering this behaviour due to a performance improvement since SQL Server 2012. It now by default uses a cache size of 1,000 when allocating IDENTITY values for an int column and restarting the service can “lose” unused values (The cache size is 10,000 for bigint/numeric). This is mentioned in the documentation SQL Server … Read more
You can’t alter the existing columns for identity. You have 2 options, Create a new table with identity & drop the existing table Create a new column with identity & drop the existing column Approach 1. (New table) Here you can retain the existing data values on the newly created identity column. Note that you … Read more