Insert from tabbed file with a conditional

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 2008 Forums

Transact-SQL (2008)

Insert from tabbed file with a conditional

Author

Topic

DavidWIII
Starting Member

3 Posts

Posted - 2013-05-30 : 16:11:03

I need help with a stored procedure.

I have two tables CRIS_USER and CRIS_USER_ LOCATION. My query will read a tabbed text file UserLocation.

CRIS_USER has two relevant columns – id and login_id
CRIS_ USER_ LOCATION has – id, cris_user_id, location_id
CRIS_USER.id is the foreign key cris_user_id in CRIS_USER_LOCATION
The query reads UserLocation text file into #LocationTemp - UserName, UserLogin, Loc_1, Loc_2, Loc_3, Loc_4, Loc_5

CRIS_ USER_ LOCATION.location_id is a three digit number – 121, 155, 063, 071, or 750.
Loc_1,… Loc_5 will each have a “x” or a null value.

Loc_1 = 121
Loc_2 = 155
Loc_3 = 063
Loc_4 = 071
Loc_5 = 750

Locs with an “x” indicate that a row is added to CRIS_USER_LOCATION if it does not exist for that login_id.

The query needs to SELECT the id from CRIS_USER WHERE LocationTemp.login = CRIS_USER.login_id.

SELECT CRIS_USER.id
FROM CRIS_USER
WHERE CRIS_USER.login_id = #LocationTemp .login

The result of that SELECT, CRIS_USER.id, is used to check if a location_id exists for a cris_user_id in the CRIS_ USER_ LOCATION table if the value for that location has an “x” in #LocationTemp. That result will also be the cris_user_id when a row is added.

-- If Loc_1 = “x” (or is not null) (I might use case)
SELECT *
From CRIS_ USER_ LOCATION
WHERE CRIS_USER.id(result from previous query) = CRIS_USER_LOCATION. cris_user_id and #LocationTemp. location_id = “121”

If the result of that query is null, insert a row in CRIS_USER_LOCATION. I then have to do the same for Loc_2,…etc. It would be possible to insert up to five rows into CRIS_ USER_ LOCATION from one row in #LocationTemp .

If Loc_1 = X, insert 121
If Loc_2 = X, insert 155
If Loc_3 = X, insert 063
If Loc_4 = X, insert 071
If Loc_5 = X, insert 750

Here is the code to load the text file:

IF OBJECT_ID('TEMPDB..#LocationTemp') IS NULL
	BEGIN	
	  CREATE TABLE #LocationTemp(UserName       varchar(50),UserLogin varchar(20),Loc_1 varchar(1),Loc_2 varchar(1),Loc_3 varchar(1),Loc_4 varchar(1), Loc_5 varchar(1))
      BULK INSERT #LocationTemp
      FROM '\\431605-DB1\NRVCS\UserLocation.txt'  
      
      WITH (ROWTERMINATOR ='\n' 
            ,FIELDTERMINATOR = '\t' 
      
      ,FIRSTROW=2)    
      PRINT 'File data copied to Temp table'      
END

I might create a second temp table - #LocationTemp2. The next step might be to create a row in #LocationTemp2 with CRIS_USER.id and #LocationTemp. I would only create a row for each Loc that has an “x” and does not already have an entry for that cris_user_id and location_id in CRIS_USER_LOCATION. This is where I need help if I do it this way. It might require a case or if statement.

CRIS_USER_ LOCATION.id is the primary key and is not set to auto increment. I have to find the highest key value and increment by one when creating a new record.

I would then end up with:

DECLARE @maxid NUMERIC(10,0)
SELECT @maxid = coalesce(MAX(ID), 0)
FROM   dbo.LOGINS WITH (UPDLOCK)
INSERT INTO dbo.CRIS_ USER_ LOCATION(id, cris_user_id, location_id)
SELECT row_number() OVER(ORDER BY (SELECT NULL)) + @maxid,cris_user_id, location_id
FROM   #LocationTemp2;

I know my question is lengthy. Do I need to clarify?
Any suggestions? Should I create a second temp table? Is there a better way to go about it? I’ve also thought about a SELECT within a SELECT but am unsure how to do that in combination with an INSERT. I think I would still need a case or if statement.
Thanks

DavidWIII
Starting Member

3 Posts

Posted - 2013-05-30 : 16:32:21

Here are the tables:

CREATE TABLE [dbo].[CRIS_USER] (
[id] numeric(10, 0) NOT NULL,
[login_id] varchar(20) NOT NULL,
[active] numeric(1, 0) NOT NULL,
[password] varchar(50) NULL,
[PW_DATE] datetime NULL,
[DEFAULT_PASSWORD] varchar(50) NULL,
[FIRST_NAME] varchar(50) NULL,
[LAST_NAME] varchar(50) NULL,
[EMERGENCY_USER] numeric(10, 0) NULL)
ON [PRIMARY]

CREATE TABLE [dbo].[CRIS_USER_LOCATION] (
[id] numeric(10, 0) NOT NULL,
[cris_user_id] numeric(10, 0) NOT NULL,
[location_id] numeric(10, 0) NOT NULL)
ON [PRIMARY

CRIS_USER_LOCATION is the only one updated.

DavidWIII
Starting Member

3 Posts

Posted - 2013-06-03 : 13:13:39

I wrote a procedure that works, but I'm not sure it is the best way to go about it. Please let me know if there is a more efficient way.


IF OBJECT_ID('TEMPDB..#LocationTemp') IS NULL
  
   BEGIN	
     --DROP TABLE #LocationTemp
  CREATE TABLE #LocationTemp(UserName varchar(50),UserLogin varchar (20),Loc_1 varchar(1),Loc_2 varchar(1),Loc_3 varchar(1),Loc_4 varchar(1), Loc_5 varchar(1))
      BULK INSERT #LocationTemp
      FROM '\\431605-DB1\NRVCS\UserLocation.txt'  
      -- Path to the file.If this file is on server,be sure this file exists on the server.
      WITH (ROWTERMINATOR ='\n' 
      --  New Line Feed (\n) automatically adds Carrige Return (\r)
      ,FIELDTERMINATOR = '\t' 
      --delimiter   
      ,FIRSTROW=2)    
      PRINT 'File data copied to Temp table'      
END
    
IF OBJECT_ID('TEMPDB..#LocationTemp2') IS NULL
  BEGIN
    CREATE TABLE #LocationTemp2(cris_user_id numeric(10, 0),location_id numeric(10,0) )
    
    
    INSERT INTO #LocationTemp2(cris_user_id, location_id)
    SELECT CRIS_USER.id, + 121
    FROM   CRIS_USER JOIN #LocationTemp
       ON (CRIS_USER.login_id = #LocationTemp.UserLogin) 
       WHERE #LocationTemp.Loc_1 = 'X'
     INSERT INTO #LocationTemp2(cris_user_id, location_id)
    SELECT CRIS_USER.id, + 155    FROM   CRIS_USER JOIN #LocationTemp
       ON (CRIS_USER.login_id = #LocationTemp.UserLogin) 
       WHERE #LocationTemp.Loc_2 = 'X'
      INSERT INTO #LocationTemp2(cris_user_id, location_id)
    SELECT CRIS_USER.id, + 063 
     FROM   CRIS_USER JOIN #LocationTemp
       ON (CRIS_USER.login_id = #LocationTemp.UserLogin) 
       WHERE #LocationTemp.Loc_3 = 'X'
       INSERT INTO #LocationTemp2(cris_user_id, location_id)
       SELECT CRIS_USER.id, + 071 
     FROM   CRIS_USER JOIN #LocationTemp
       ON (CRIS_USER.login_id = #LocationTemp.UserLogin) 
       WHERE #LocationTemp.Loc_4 = 'X'
       INSERT INTO #LocationTemp2(cris_user_id, location_id)
       SELECT CRIS_USER.id, + 750 
       FROM   CRIS_USER JOIN #LocationTemp
       ON (CRIS_USER.login_id = #LocationTemp.UserLogin) 
       WHERE #LocationTemp.Loc_5 = 'X'
  END    
   DECLARE @maxid NUMERIC(10,0)
   
   BEGIN TRANSACTION
   SELECT @maxid = coalesce(MAX(ID), 0)
   FROM   dbo.CRIS_USER_LOCATION WITH (UPDLOCK)
   
   INSERT INTO CRIS_USER_LOCATION(id,cris_user_id, location_id)
    SELECT row_number() OVER(ORDER BY (SELECT NULL)) + @maxid,cris_user_id, location_id
    FROM   #LocationTemp2 
    WHERE   cris_user_id NOT IN
     (
     SELECT  CRIS_USER_LOCATION.cris_user_id
      From CRIS_USER_LOCATION
      WHERE #LocationTemp2.cris_user_id = CRIS_USER_LOCATION.cris_user_id
      AND #LocationTemp2.location_id = CRIS_USER_LOCATION.location_id
      )
   COMMIT TRANSACTION
GO

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-06-03 : 14:15:57

Sorry I am not that good in reading long articles but have had a quick view on yours one. I would appreciate if you could shorten your scenario and make it specific to the issue you're facing or help required onto. An easy way would be to provide sample data in consumable format (by consumable I mean in the form on insert statements so that we can straight away use it and get you the desired ouput from it)

To that end, what I understand is that you've

1) A file that has tab separated info: UserName, UserLogin, Loc_1, Loc_2, Loc_3, Loc_4, Loc_5
2) A base table (CRIS_USER) that has user info: id, login_id (as UserName of the text file)
3) A base table (CRIS_ USER_ LOCATION) that has location info: id, cris_user_id, location_id

So whats the case, do you want to add "file" information into location (base) table that does not exist? Again, illustrate it with the help of sample data in consumable format. Else it would be difficult to go through all the narrative.

Cheers
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources