creating an auto number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

creating an auto number

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 14:18:15

Hello,

I am inserting some data from a temp to an existing table. The existing table has a primary key that is a number that just appears to be number 1-460 (there are 460 rows in the table). The data type for the primary key is int.

When I insert from my temp table to the existing I want to be able to add the next number in but am not sure how I can set that up??
I have pasted my code below. I am inserting into Lab_test_add_conf and the primary key value is lab_test_conf_id. In my select statement where I am getting the values to input I just put a number for now but that isn't going to work. You can ignore the rest of the numbers I have inserted and commas with no values I actually need to go add those to the temp table and just set their default values but I just first wanted to determine how I can create an autonumber. This is a third party software so I don't want to change the actual datatype of the existing table.

Insert Into Lab_Test_Add_Conf
(lab_test_conf_id,system_id,labtest_key,value_code,
value_description,value_code_system,value_type,units,sequence_number,table_name,
field_name,created_by,create_timestamp,modified_by,modify_timestamp,row_timestamp)

select '460','2250',labtestkey,valuecode,value_description,'L',value_type,units,
sequence_number,table_name,field_name,'58',,'58',,
from #TempLabTestConfigImport

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 14:23:21

just add an identity column to your temporary table

Create Table #TempLabTestConfigImport
(temp_id int identity(1,1),labtestkey varchar(10) null,valuecode varchar(32)null, 
value_description varchar(255) null,value_type varchar(32) null,
units varchar(32) null, table_name varchar(30) null,
field_name varchar(30) null)


and then insert data to it. And while inserting to main table use like this


DECLARE @MaxID int

SELECT @MaxID=MAX(lab_test_conf_id)
FROM Lab_Test_Add_Conf


Insert Into Lab_Test_Add_Conf
(lab_test_conf_id,system_id,labtest_key,value_code,
value_description,value_code_system,value_type,units,sequence_number,table_name,
field_name,created_by,create_timestamp,modified_by,modify_timestamp,row_timestamp)

select @MaxID + temp_id,'2250',
labtestkey,valuecode,value_description,'L',value_type,units,
sequence_number,table_name,field_name,'58',,'58',,
from #TempLabTestConfigImport

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 15:36:40

Wow you have so many great ideas. I am working on testing that out. I just am inserting some extra values into that temp table. How do you write the code to create a default value? The bolded part I am trying to assign default values to. Its says there is an error near 57 so I don't think I am doing it right?

Create Table #TempLabTestConfigImport
(temp_id int identity(1,1),labtestkey nvarchar(10) null,valuecode nvarchar(10)null,
value_description nvarchar(50) null,value_type char(2) null,
units nvarchar(5) null,sequence_number int null, table_name nvarchar(50) null,
field_name nvarchar(50) null,created_by int 57,create_timestamp getdate(), modified_by int 57,
modify_timestamp getdate(), row_timestamp getdate())

quote:

Originally posted by visakh16

just add an identity column to your temporary table

Create Table #TempLabTestConfigImport
(temp_id int identity(1,1),labtestkey varchar(10) null,valuecode varchar(32)null, 
value_description varchar(255) null,value_type varchar(32) null,
units varchar(32) null, table_name varchar(30) null,
field_name varchar(30) null)


and then insert data to it. And while inserting to main table use like this


DECLARE @MaxID int

SELECT @MaxID=MAX(lab_test_conf_id)
FROM Lab_Test_Add_Conf


Insert Into Lab_Test_Add_Conf
(lab_test_conf_id,system_id,labtest_key,value_code,
value_description,value_code_system,value_type,units,sequence_number,table_name,
field_name,created_by,create_timestamp,modified_by,modify_timestamp,row_timestamp)

select @MaxID + temp_id,'2250',
labtestkey,valuecode,value_description,'L',value_type,units,
sequence_number,table_name,field_name,'58',,'58',,
from #TempLabTestConfigImport

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 15:48:14

Nevermind I figured it out....you have to add the word default! :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-23 : 15:50:30

quote:
Originally posted by sross81

Wow you have so many great ideas. I am working on testing that out. I just am inserting some extra values into that temp table. How do you write the code to create a default value? The bolded part I am trying to assign default values to. Its says there is an error near 57 so I don't think I am doing it right?

Create Table #TempLabTestConfigImport
(temp_id int identity(1,1),labtestkey nvarchar(10) null,valuecode nvarchar(10)null,
value_description nvarchar(50) null,value_type char(2) null,
units nvarchar(5) null,sequence_number int null, table_name nvarchar(50) null,
field_name nvarchar(50) null,created_by int DEFAULT 57,create_timestamp DEFAULT (getdate()), modified_by int DEFAULT 57,
modify_timestamp DEFAULT (getdate()), row_timestamp DEFAULT (getdate()))

quote:
Originally posted by visakh16

just add an identity column to your temporary table

Create Table #TempLabTestConfigImport
(temp_id int identity(1,1),labtestkey varchar(10) null,valuecode varchar(32)null,
value_description varchar(255) null,value_type varchar(32) null,
units varchar(32) null, table_name varchar(30) null,
field_name varchar(30) null)

and then insert data to it. And while inserting to main table use like this

DECLARE @MaxID int

SELECT @MaxID=MAX(lab_test_conf_id)
FROM Lab_Test_Add_Conf

Insert Into Lab_Test_Add_Conf
(lab_test_conf_id,system_id,labtest_key,value_code,
value_description,value_code_system,value_type,units,sequence_number,table_name,
field_name,created_by,create_timestamp,modified_by,modify_timestamp,row_timestamp)

select @MaxID + temp_id,'2250',
labtestkey,valuecode,value_description,'L',value_type,units,
sequence_number,table_name,field_name,'58',,'58',,
from #TempLabTestConfigImport

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 16:11:58

I added the default and identity values to the temp table but I don't know how to get it to insert. I added all the new columns to the insert into statment for the temp table but not sure how to alias them into the select clause since they are coming from the temp table which has nothing in it yet??

Create Table #TempLabTestConfigImport
(temp_id int identity(1,1),system_id int default 2250, labtestkey nvarchar(10) null,
valuecode nvarchar(10)null, value_description nvarchar(50) null,
value_code_system varchar(10) default 'L',value_type char(2) null,
units nvarchar(5) null,sequence_number int null, table_name nvarchar(50) null,
field_name nvarchar(50) null,created_by int default 57,create_timestamp datetime default getdate(),
modified_by int default 57,modify_timestamp datetime default getdate(), row_timestamp datetime default getdate())

With labtestcheck (labtestcode)
as
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)
Insert Into #TempLabTestConfigImport
(temp_id,system_id,labtestkey,valuecode,value_description,value_code_system,
value_type,units,sequence_number,table_name,field_name,created_by,create_timestamp,modified_by,
modify_timestamp, row_timestamp)

SELECT temp_id,system_id,
l.labtestcode,t.valuecode,t.valuedesc,value_code_system,
t.valuetype,t.units,t.SequenceCount,t.tablename,t.fieldname, created_by,
create_timestamp,modified_by,modify_timestamp, row_timestamp
FROM labtestcheck l
INNER JOIN
(
select t.labtestcode,b.valuecode,b.valuedesc,b.valuetype,b.units,
row_number() over (partition by t.labtestcode order by b.valuecode) as SequenceCount,
b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode)t
ON t.labtestcode=l.labtestcode

sross81
Posting Yak Master

228 Posts

Posted - 2008-05-23 : 16:58:28

Ok nevermind I guess I don't need to include the values that are already preset into the insert into and select statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources