Help?
-
Hey all,
I have been trying all day and for the life of me I cannot figure this out, I think my mind has become blank from working on this so long.
I have a super basic request from a user....
I have 1 record, I am inserting it into a temp table ( making 4 records from the orignal one), i'm also adding 2 fields, the default value of record #1 in the temp table needs to have value 1 for the first new field and 1 for the second new field.
What I need is 4 record with the same infomation like I have in my screenshot, however I'm going to take 580 input records and hopefully create 3000+ records in my temp table.
Ignore the ident field please.
See the first record , it should have "1" for currentsectionid and "1" for format, then record 2 should have "2" for currentsectionid and "2" for formatID, so on and so forth until record 4 which is correct.
I know that its my stupid while loop. I jsut need someones imput on how to do this correctly :crazy:
I know it is correct but each next time through the loop changes the values so they seem to always be the same which isn't what I want.
Here is the code that made that lol it's ugly....
use RonnieProd
GO
Drop Table ##CurSectionInfo
Create Table ##CurSectionInfo
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[CurrentSectionID] [int],
[FormatID] [int] NULL,
[DateOfIssue] [datetime] NULL,
[Description95] [varchar](255) NULL,
[Description04] [varchar](255) NULL,
[Notes] varchar(255) NULL
)
declare @fnum int
declare @formatid int
set @fnum = 1
set @FormatID = 1
while @fnum <=4
begin
insert into ##Cursectioninfo (DateOfIssue,Description95,Description04,Notes)
select top 1 RP.LDOI,RP.Descrip,RP.Descrip04,RP.Remarks
from dbo.RonnieProd as RP
where emsno ='01000' and new is null
update ##CursectionInfo
set formatid = @formatid
update ##CursectionInfo
set CurrentSectionID = @fnum
set @fnum = @fnum +1
set @formatid = @formatid +1
end
go
select * from ##Cursectioninfo
-
🙂 Hi there!
😛 Hope this helps
-- FIRST OF ALL, I DON'T USE CURSORS ^__^ heard tere where bugs in them or speed issues
-- FIRST TEMPORARY TABLE (Copy of Original)
CREATE TABLE #Test
(
IDINTIDENTITY(1,1)NOT NULL,
ValueAVARCHAR(MAX)NOT NULL,
ValueBVARCHAR(MAX)NOT NULL
)
GO
-- HERE YOU INSERT THE Values of the Ordiginal Table to #Test Table
DECLARE @i INT,
@j-2 INT,
@k INT
SELECT @i = 1, @j-2 = 1
WHILE @i<=1000
BEGIN
INSERT INTO #Test VALUES('abc '+CAST(@i AS VARCHAR(MAX)),CAST(@i*2 AS VARCHAR(MAX))+' xyz')
SET @i=@i+1
END
-- Here I'm just checking the values of my #Test Table
SELECT * FROM #Test
-- OK, so why not just use the original table? Simple, I want new ID's. the original tables ID's might not be in order or have missing bumbers due to some reasons such as deletion
-- SECOND TEMPORARY TABLE
CREATE TABLE #Temp
(
IDINTIDENTITY(1,1)NOT NULL,
ValueAVARCHAR(MAX)NOT NULL,
ValueBVARCHAR(MAX)NOT NULL,
ValueCVARCHAR(MAX)NOT NULL,
ValueDVARCHAR(MAX)NOT NULL
)
SET @i = (SELECT COUNT(1) FROM #Test)
-- INSERTION OF THE 1-4 or 1-n or whatever you call it
WHILE @j-2 <= @i
BEGIN
SET @k=1
WHILE @k<=4
BEGIN
INSERT INTO #Temp
SELECT ValueA, ValueB, @k, @k FROM #Test WHERE ID = @j-2
SET @k=@k+1
END
SET @j-2=@j+1
END
SELECT * FROM #Temp
--DROPPING OF THE TWO TEMPORARY TABLES
DROP TABLE #Test
GO
DROP TABLE #Temp
GO
_____________________________________________
"Press any key...
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply