July 8, 2003 at 2:44 pm
my file is very big and each 2 lines I need concat on one...ex: read first line and then read second line and save it on the first line...well I'm doing this, but it's not working...any idea?
delete from temp where col001 is null
DELETE FROM temp1
DECLARE @col001 CHAR(255)
DECLARE @COL002 CHAR(255)
DECLARE csrSites CURSOR FOR
SELECT col001 FROM temp
OPEN csrSites
FETCH NEXT FROM csrSites INTO @Col001
FETCH NEXT FROM csrSites INTO @Col002
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO temp1 (col001)
SELECT substring(col001,1,150) as FIRSTLINE FROM temp
FETCH NEXT FROM csrSites INTO @col001
INSERT INTO temp1 (col002)
SELECT substring(col001,1,100) as SECONDLINE FROM temp
FETCH NEXT FROM CSRsITES INTO @COL002
END
SELECT *
FROM TEMP1
CLOSE csrSites
DEALLOCATE csrSites
July 9, 2003 at 6:34 am
Each time you run 'INSERT INTO temp1 ' you create a new record. Try the following code.:
create table myTemp (col001 varchar(250))
insert into myTemp values ( 'this is some text' )
insert into myTemp values ( 'this line 2 of text' )
insert into myTemp values ( 'this line 3 of text' )
insert into myTemp values ( 'this line 4 of text' )
create table #Temp1 (col001 varchar(250))
DECLARE @col001 varCHAR(255)
DECLARE @COL002 varCHAR(255)
DECLARE csrSites CURSOR FOR
SELECT col001 FROM myTemp
OPEN csrSites
FETCH NEXT FROM csrSites INTO @Col001
FETCH NEXT FROM csrSites INTO @Col002
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #Temp1 (col001) values (@Col001 + @Col002)
FETCH NEXT FROM csrSites INTO @col001
FETCH NEXT FROM CSRsITES INTO @COL002
END
SELECT *
FROM #Temp1
CLOSE csrSites
DEALLOCATE csrSites
July 9, 2003 at 6:44 am
You must also note the SQL does not guarantee the order in which results are returned if you have no ORDER BY clause. Even though you may currently be getting expected results for the table TEMP, it could change unexpectedly anytime in the future, returning rows in a different order to that that in which they're physically ordered.
I've seen this happen, so it's just something to watch out for.
Cheers,
- Mark
July 9, 2003 at 11:35 am
As alphaindex pointed out, INSERT creates a new row each time. You can solve it via alphaindex's solution or change your second INSERT to an UPDATE.
INSERT to create the row with part of the data and then UPDATE it with the rest of the data.
-SQLBill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply