Menu
I have a query like below and it generate a temporary table automatically based on parameter. So, the number of column of this table can be vary. Now , i need to add an auto incremental id column into this table.How i do it?
SELECT @SourceFields INTO ##StoreSourceInfo FROM testdb.dbo.@SourceTable
Note: 1) Number of source field & name of table pass using the parameter @SourceFields & @SourceTable.
2) So, the number of column can be vary on ##StoreSourceInfo table.
Current Result:
select * from ##StoreSourceInfo shows only the available column.
Expected Result:
select * from ##StoreSourceInfo query will show an additional auto incremental id column & all rest of the column available in the temp table.
Hope you get me. Thanks in advance.
305
To add a new AUTO_INCREMENT integer column named c : ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (c); We indexed c (as a PRIMARY KEY ) because AUTO_INCREMENT columns must be indexed, and we declare c as NOT NULL because primary key columns cannot be NULL .
To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function. For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
If you do really need to have a second column with "auto increment" type behavior, one way to get that is to add a second dummy table with an auto_increment column, and use a BEFORE INSERT trigger to do an insert into the dummy table, and retrieve the id value that was inserted.
Question: Is it possible to add an auto incremental identity column to any table in SQL Server after creating a table. Answer: There are two answers – No and Yes. Let us see them one by one. Answer No – If you have an integer column in your table and you want to convert that column to identity table. It is not possible with the help of SQL Server.
Now, we will modify the id column to be auto increment, using ALTER TABLE. In the above statement, you need to specify the table_name and column_name. Here’s the SQL statement to add AUTO INCREMENT constraint to id column. Next we will add a couple of rows in sales table.
In the MySQL server, it applies an auto increment field with the keyword AUTO_INCREMENT. By default, it starts with the number one and increases the value by one for each new record. In the example below, you will use the CREATE TABLE command to create a Students table and apply PRIMARY KEY and AUTO_INCREMENT to the ID column.
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).
SELECT
IDENTITY(INT, 1, 1) AS id
INTO #Temptable
FROM User
You can use row_number function
Select ROW_NUMBER() over (order by T.field1) rownum
, T.field1, T.field2 into #temp1
from @Table T
Use the identity function. See the link for an example. http://msdn.microsoft.com/en-us/library/ms189838.aspx
30
You have to try with following query to get your excepted result to add a extra auto increment column :
SELECT
IDENTITY(INT, 1,1) AS Rank,
@SourceFields
INTO
##StoreSourceInfo
FROM
testdb.dbo.@SourceTable
Means apply IDENTITY function...
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
