Click here to Skip to main content
15,867,756 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
I have designed a table in Microsoft SQL Server 2005 it has only two columns and both columns are identity fields. First column's identity value start from 1 and second column identity value start from 1001. I will insert 1000 records in this table. How do I can do this?
Posted
Updated 10-Nov-10 17:49pm
v2
Comments
PSK_ 10-Nov-10 23:58pm    
How you have created 2 identity column, SQL server allows only one identity column in a table??
Abdul Quader Mamun 11-Nov-10 0:01am    
Yes, It did not mind when I created.
PSK_ 11-Nov-10 0:06am    
SQL designer removes the previously created identity once you create a new one.
Abdul Quader Mamun 11-Nov-10 0:08am    
OK, can It be possible to insert only one column that is identity?
PSK_ 11-Nov-10 0:21am    
I have updated the answer for this answer.

1 solution

1- You can't have 2 identity key on a table.

2- If you have 1 identity column and you want to insert 1000 records you can do like following.

DECLARE @Counter int
SET @Counter=1
WHILE (@Counter) <= 1000
BEGIN
	-- Wrtie your insert statement here
	SET @Counter = @Counter +1

end


3- If you have a table having 1 column of type identity in that case you can add a row like following.

INSERT INTO TABLE_NAME DEFAULT VALUES
 
Share this answer
 
v2
Comments
Abdul Quader Mamun 11-Nov-10 0:15am    
Idea is not bad but do not this way.
Abdul Quader Mamun 11-Nov-10 0:17am    
Can we consider Trigger for two identity like?
PSK_ 11-Nov-10 0:22am    
For your scenario you should make the second column as calculated field. Which depends on the value of first column instead of a trigger.
Abdul Quader Mamun 11-Nov-10 0:25am    
Yes it is work for single identity column. Thanks!
PSK_ 15-Nov-10 2:59am    
If your issue is resolved in that case you should close this question.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900