update table question
-
I have a data table that I am populating from a text file. The text file is very raw data and has some of the fields left blank because they are supposed to inherit that information from an earlier record. Confusing. Here is an example:
ID WCode Reason HCode Loc
user1 125 rsn1 cd1 loc1
<null> 128 rsn3 cd27 loc3
<null> 721 rsn3 cd4 loc5
user2 122 rsn3 cd1 loc3
<null> 125 rsn9 cd5 loc5
The first record has user1 in the ID field. The second record is also for user1, but the system the data is pulled from doesnt populate that field if it is a repeat from the previous record. The same goes for the third record.
I am dumping this data, as is, into a table. Once I have it in my table, exactly as above, I need to update the ID field to reflect the appropriate user. Since none of the other fields are unique to the user, can anyone think of the logic to use in order to make these updates?
Help!
-
Try this out
BEGIN
SET NOCOUNT ON
DECLARE @vuser VARCHAR(20)
DECLARE @tbl TABLE
([Id] VARCHAR(20),
WCode INT,
Reason VARCHAR(10),
HCode VARCHAR(10),
Loc VARCHAR(10))
SELECT @vUser = ''
INSERT INTO @tbl VALUES ('user1',125,'rsn1','cd1','loc1')
INSERT INTO @tbl VALUES (NULL,128,'rsn3','cd27','loc3')
INSERT INTO @tbl VALUES (NULL,721,'rsn3','cd4','loc5')
INSERT INTO @tbl VALUES ('user2',122,'rsn3','cd1','loc3')
INSERT INTO @tbl VALUES (NULL,125,'rsn9','cd5','loc5')
UPDATE @tbl
SET @vUser = CASE WHEN [Id] IS NULL THEN @vuser ELSE [Id] END,
@vUser = [Id] = @vuser
FROm @tbl
SELECT * FROM @tbl
END
Ram
-
Ram,
Can the INSERT INTO values be populated from a query? I have approximately 115,000 records in my table so I dont want to type each of them out. I tried using a SELECT statement, and that errored out.
-
Try this!
BEGIN
SET NOCOUNT ON
DECLARE @vuser VARCHAR(20)
DECLARE @tbl TABLE
([Id] VARCHAR(20),
WCode INT,
Reason VARCHAR(10),
HCode VARCHAR(10),
Loc VARCHAR(10))
SELECT @vuser = ''
INSERT INTO @tbl VALUES ('user1',125,'rsn1','cd1','loc1')
INSERT INTO @tbl VALUES (NULL,128,'rsn3','cd27','loc3')
INSERT INTO @tbl VALUES (NULL,721,'rsn3','cd4','loc5')
INSERT INTO @tbl VALUES ('user2',122,'rsn3','cd1','loc3')
INSERT INTO @tbl VALUES (NULL,125,'rsn9','cd5','loc5')
SELECT 'INSERT INTO @tbl VALUES ('+CASE WHEN [Id] IS NULL THEN 'NULL' ELSE QUOTENAME([Id],'''') END+','+
CASE WHEN [Wcode] IS NULL THEN 'NULL' ELSE QUOTENAME([Wcode],'''') END+','+
CASE WHEN [Reason] IS NULL THEN 'NULL' ELSE QUOTENAME([Reason],'''') END+','+
CASE WHEN [HCode] IS NULL THEN 'NULL' ELSE QUOTENAME([HCode],'''') END+','+
CASE WHEN [Loc] IS NULL THEN 'NULL' ELSE QUOTENAME([Loc],'''') END+')'
FROM @tbl
UPDATE @tbl
SET @vUser = CASE WHEN [Id] IS NULL THEN @vuser ELSE [Id] END,
@vUser = [Id] = @vuser
SELECT * FROM @tbl
END
-
This is what I need to get away from:
INSERT INTO @tbl VALUES ('user1',125,'rsn1','cd1','loc1')
INSERT INTO @tbl VALUES (NULL,128,'rsn3','cd27','loc3')
INSERT INTO @tbl VALUES (NULL,721,'rsn3','cd4','loc5')
INSERT INTO @tbl VALUES ('user2',122,'rsn3','cd1','loc3')
INSERT INTO @tbl VALUES (NULL,125,'rsn9','cd5','loc5')
I would have 115,000 lines in this query instead of these five lines. Not really an option.
-
What Sriram has posted is for example for you to understand. You do the same practise of dumping your data to a table as you are currently doing and replace the tbl with your table name in the following script.
CREATE PROCEDURE UpdateID
AS
DECLARE @vuser VARCHAR(20)
SET @vuser =''
UPDATE tbl
SET @vuser = CASE WHEN [Id] IS NULL THEN @vuser ELSE [Id] END,
SELECT * FROM tbl
GO
EXEC UpdateID
GO.
Say your table name MyTable, replace tbl with Mytable in the above script.
Prasad Bhogadi
www.inforaise.com -
That worked great!!
Thank you for your help!
-
Thanks Prazad!!!
-
Well that was your script.
Thx
Prasad Bhogadi
www.inforaise.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply