SQLTeam.com | Weblogs | Forums

Insert based on first row value


#1

I have a fixed file that I am importing into a single column with data similar to what you see below:

ABC$        WC        11683                                    
11608000163118430001002010056788000000007680031722800315723      
11683000486080280000002010043213000000007120012669100126691      
ABC$        WC                         000000020000000148000     
ABC$        WC        11683                                    
1168101057561604000050200001234000000027020023194001231940      
54322010240519720000502000011682000000035640006721001067210      
1167701030336257000050200008765000000023610029066101151149      
11680010471244820000502000011680000000027515026398201263982

I have a fixed file that I am importing into a single column with data similar to what you see below:
ABC$ WC 11683
11608000163118430001002010056788000000007680031722800315723
11683000486080280000002010043213000000007120012669100126691
ABC$ WC 000000020000000148000
ABC$ WC 11683
1168101057561604000050200001234000000027020023194001231940
54322010240519720000502000011682000000035640006721001067210
1167701030336257000050200008765000000023610029066101151149
11680010471244820000502000011680000000027515026398201263982

I want to split and insert this data into another table but I want to do so as long as the '11683' is equal to a column value in a different table + 1. I will then increment that value (not seen here).

The following insert works as expected.

INSERT INTO SDG_CWF
    (
        GAME,SERIAL,WINNER,TYPE
    )
SELECT convert(numeric, substring(blob,28, 5)),convert(numeric, substring(blob, 8, 9)),
        (Case when (substring(blob, 6,2)='10') then '3' 
              when (substring(blob, 6,2)='11') then '4' 
              else substring(blob, 7, 1)
         End),
        (Case when (substring(blob, 52,2)='10') then '3' 
              when (substring(blob, 52,2)='11') then '4' 
              else substring(blob, 53, 1)
         End)

FROM sdg_winn_blob

I am wondering though how I code it to look at that first row and if the '11683' (substring(sdg_winn_blob.blob, 23,5)) is equal to the number value in another table + 1 then do the insert else return an error message.

Update
I tried adding the following before my insert but I am still not sure how to get it to check only the first row for '11683'

USE myDB
declare @blob as varchar(59) 
declare @Num as varchar(5)

Select @blob = substring(blob, 1, 59) from SDG_WINN_BLOB
Select @Num = num from numtable
set @Num = @Num + 1

#2

What you can do is something like shown below. However, you will need a way to figure out the "first row". The rows in a database table are an unordered collection. So when you select top(1), unless you specify the order, it is not guaranteed that SQL Server will return what you think is the first row. If you have some other means of identifying the row from which you want to pick the number 11683, that would be sufficient as well.

Regardless, the following will work only if you have a deterministic way of identifying the one row that contains that number that you want to pick.

USE myDB
GO
DECLARE @Num AS VARCHAR(5)

SELECT TOP ( 1 )
        @Num = SUBSTRING(blob, 28, 5)
FROM    SDG_WINN_BLOB
ORDER BY AnIDColumn; -- You need some way to get the first row.

IF EXISTS
(
	SELECT * FROM AnotherTable a
	WHERE SomeColumn = @Num
)
BEGIN
	SET @Num = @Num + 1;
	-- Insert the rows and do other stuff.
END