Entering new codes - 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

General SQL Server Forums

New to SQL Server Programming

Entering new codes

Author

Topic

rds207
Posting Yak Master

198 Posts

Posted - 2010-03-10 : 19:38:22

i have two tables ,

table 1 :

CREATE TABLE [dbo].[DW_T_ASW_HOST_CODE](
[HOST_CODE] [int] IDENTITY(1,1) NOT NULL,
[HOST_NAME] [nvarchar](255) NULL)

table 2 :
CREATE TABLE [dbo].[DW_T_ASW_HOST_CODE_TEMP](
[HOST_NAME] [nvarchar](255) NULL
) ON [PRIMARY]

i have created an SSIS packages to update the codes in dw_t_asw_host_code table incrementing by 1

The sample data :
HOST_CODE HOST_NAME
1 NULL
2 pod1
3 $(saveResourceName)
4 $[/myJob/-destinationHost]
5 $[/myJob/KWRESOURCEPOOL]
6 $[/myJob/-pool]
7 $[/myJob/ResourceName]
8 $[/myProject/resName]

for eg if a new host_name is entered it should be entered as

9. new host_name

but the data is entered in alphabatical order , say for eg if the host name starts with 'a' is it given host code 1 ,

could any one please let me how the data is entered into the DW_T_ASW_HOST_CODE table by increasing the host code value by one ,

The stucture of my SSIS package is

i get the data past 7 days worth of data from table dw_t_ec_job into DW_T_ASW_HOST_CODE_TEMP, and if DW_T_ASW_HOST_CODE_TEMP table has any new host_names, it should be entered into DW_T_ASW_HOST_CODE.

The procedure i have to enter new data from DW_T_ASW_HOST_CODE_TEMP into DW_T_ASW_HOST_CODE is

Select DISTINCT(HOST_NAME) from dbo.DW_T_ASW_HOST_CODE_TEMP
where HOST_NAME not in (select DISTINCT(HOST_NAME) from DW_T_ASW_HOST_CODE)(Even this seems to be not entering new data)

Please help...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-11 : 11:43:19

isnt it enough to make HOST_CODE column identity and then make source ordered by HOST_NAME

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rds207
Posting Yak Master

198 Posts

Posted - 2010-03-11 : 12:38:13

My Stored procedure also not picking up the new data from temp table,

here is my stored proc ..

BEGIN

SET NOCOUNT ON;

Select DISTINCT(USER_NAME) from dbo.DW_T_ASW_USERCODE_TEMP
where USER_NAME not in (select(USER_NAME) from DW_T_ASW_USERCODE)

END

Please Help..

quote:
Originally posted by visakh16

isnt it enough to make HOST_CODE column identity and then make source ordered by HOST_NAME

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 11:00:24

and you're sure that you've new data existing in temp? what about below


Select DISTINCT(USER_NAME) from dbo.DW_T_ASW_USERCODE_TEMP t
where NOT EXISTS (select 1 from DW_T_ASW_USERCODE WHERE USER_NAME = t.USER_NAME)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources