July 14, 2017 at 9:39 am
I need help with the below cursor. i have the below data in the temp table . I need to use the temp table to load the data in 2 other tables. Table A & Table B.
Table A- It will be loaded with the data that has the Type value = B & C .RowNo column has values from the Row column of Temp table.
Table B- It will load all the data from temp table. PID is the foreign key from Table A(PID column). If the parentrow is 4 then Pid will be 2 because its identity value is 2 in TableA.
Thanks.
CREATE TABLE #temp(
Row VARCHAR(3)
,ParentRow VARCHAR(9)
,Part VARCHAR(4)
,Id VARCHAR(4)
,Type VARCHAR(4)
,Totals VARCHAR(6)
);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('1','4','2','1234','A','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('2','5','2','1234','A','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('3',NULL,'2','4444','B','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('4',NULL,'2','4444','C','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('5',NULL,'2','4444','C','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('6',NULL,'2','4444','C','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('7',NULL,'2','4444','C','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('8','4','2','4444','A','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('9','5','2','4444','A','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('10','3','2','4444','A','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('11','6','2','4444','A','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('12','7','2','4444','A','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('13','3','2','4444','A','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('14','6','2','4444','A','100');
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES ('15','7','2','4444','A','100');
| TableA | ||
| PID(identity) | Part | RowNo |
| 1 | 2 | 3 |
| 2 | 2 | 4 |
| 3 | 2 | 5 |
| 4 | 2 | 6 |
| 5 | 2 | 7 |
| TableB | ||||
| BID(identity) | Pid | Id | Type | Totals |
| 1 | 2 | 1234 | A | 100 |
| 2 | 3 | 1234 | A | 100 |
| 3 | 1 | 4444 | B | 100 |
| 4 | 2 | 4444 | C | 100 |
| 5 | 3 | 4444 | C | 100 |
| 6 | 4 | 4444 | C | 100 |
| 7 | 5 | 4444 | C | 100 |
| 8 | 2 | 4444 | A | 100 |
| 9 | 3 | 4444 | A | 100 |
| 10 | 1 | 4444 | A | 100 |
| 11 | 4 | 4444 | A | 100 |
| 12 | 5 | 4444 | A | 100 |
| 13 | 1 | 4444 | A | 100 |
| 14 | 4 | 4444 | A | 100 |
| 15 | 5 | 4444 | A | 100 |
DECLARE
@Row smallint,
@Parentrow smallint,
@Part int,
@id int,
@Type varchar(100),
@Totals varchar(10)
SET NOCOUNT ON
DECLARE INSERT_CURSOR CURSOR
FOR
SELECT
[Row]
,Parentrow
,[Part]
,id
,[Type]
,[Totals]
FROM #temp
OPEN INSERT_CURSOR;
FETCH NEXT
FROM INSERT_CURSOR
INTO
@Row,
@Parentrow,
@Part ,
@id,
@Type,
@Totals
WHILE (@@FETCH_STATUS = 0)
BEGIN
BEGIN
INSERT INTO TableA (
Part
,rowNo
)
VALUES (
@part
,@Row
)
SET @PID = (SCOPE_IDENTITY())
END
-----------tableB
BEGIN
INSERT INTO tableB (
PID
,ID
,Type
,Totals
)
VALUES (
,
,@ID
,@Type
,@Totals
)
SET @BID = (SCOPE_IDENTITY())
END
FETCH NEXT
FROM INSERT_CURSOR
INTO @Row,
@Parentrow,
@Part ,
@id,
@Type,
@Totals
END
CLOSE INSERT_CURSOR
DEALLOCATE INSERT_CURSOR
GO
July 14, 2017 at 10:06 am
I assume, by your post, you are trying to get the results you show above for TableA and TableB. As a result I'm not sure why you are using a Cursor, there is no need.
Secondly, WHY are you storing numerics as a varchar? Please don't do this. This means that when ordering by your [Row] column in ascending order you get: 1, 10, 11, 12, 13, 14, 15, 2, 3, 4, 5, 6, 7, 8, 9. As you can see, this is nonsensical when dealing with integers. DON'T store numerics as strings; store them as numerics. For the below to work, you need to change your data types (I have used INT). I have therefore included new CREATE statements for you:CREATE TABLE #temp(
Row int --Why was this a varchar? Changed to int.
,ParentRow int
,Part int --Why was this a varchar? Changed to int.
,Id int --Why was this a varchar? Changed to int.
,Type VARCHAR(4)
,Totals int --Why was this a varchar? Changed to int.
);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (1,4,2,1234,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (2,5,2,1234,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (3,NULL,2,4444,'B',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (4,NULL,2,4444,'C',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (5,NULL,2,4444,'C',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (6,NULL,2,4444,'C',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (7,NULL,2,4444,'C',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (8,4,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (9,5,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (10,3,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (11,6,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (12,7,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (13,3,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (14,6,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (15,7,2,4444,'A',100);
GO
SELECT *
FROM #temp;
GO
--Create table A
CREATE TABLE TableA (PID int IDENTITY(1,1),
Part varchar(4),
Id int);
--insert Data
INSERT INTO TableA (Part, Id)
SELECT T.Part, T.[Row] AS RowNo
FROM #temp T
WHERE Type IN ('B','C')
ORDER BY T.[Row] ASC;
GO
--Create TAbleB
CREATE TABLE TableB (BID int IDENTITY(1,1),
PID int,
Id int,
[Type] varchar(4),
Totals int);
--insert Data
INSERT INTO TableB (PID, Id, [Type], Totals)
SELECT A.PID,
T.Id,
T.[Type],
T.Totals
FROM #temp T
LEFT JOIN TableA A ON T.ParentRow = A.Id OR (T.ParentRow IS NULL AND T.[Row] = A.Id)
ORDER BY T.[Row] ASC;
GO
--Check the data
SELECT *
FROM TableA;
SELECT *
FROM TableB;
GO
--Clean up
DROP TABLE #temp;
DROP TABLE TableA;
DROP TABLE TableB;
Go
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 14, 2017 at 1:24 pm
Thom A - Friday, July 14, 2017 10:06 AMI assume, by your post, you are trying to get the results you show above for TableA and TableB. As a result I'm not sure why you are using a Cursor, there is no need.Secondly, WHY are you storing numerics as a varchar? Please don't do this. This means that when ordering by your [Row] column in ascending order you get: 1, 10, 11, 12, 13, 14, 15, 2, 3, 4, 5, 6, 7, 8, 9. As you can see, this is nonsensical when dealing with integers. DON'T store numerics as strings; store them as numerics. For the below to work, you need to change your data types (I have used INT). I have therefore included new CREATE statements for you:
CREATE TABLE #temp(
Row int --Why was this a varchar? Changed to int.
,ParentRow int
,Part int --Why was this a varchar? Changed to int.
,Id int --Why was this a varchar? Changed to int.
,Type VARCHAR(4)
,Totals int --Why was this a varchar? Changed to int.
);INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (1,4,2,1234,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (2,5,2,1234,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (3,NULL,2,4444,'B',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (4,NULL,2,4444,'C',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (5,NULL,2,4444,'C',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (6,NULL,2,4444,'C',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (7,NULL,2,4444,'C',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (8,4,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (9,5,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (10,3,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (11,6,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (12,7,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (13,3,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (14,6,2,4444,'A',100);
INSERT INTO #temp(Row,ParentRow,Part,Id,Type,Totals) VALUES (15,7,2,4444,'A',100);
GO
SELECT *
FROM #temp;
GO
--Create table A
CREATE TABLE TableA (PID int IDENTITY(1,1),
Part varchar(4),
Id int);
--insert Data
INSERT INTO TableA (Part, Id)
SELECT T.Part, T.[Row] AS RowNo
FROM #temp T
WHERE Type IN ('B','C')
ORDER BY T.[Row] ASC;
GO
--Create TAbleB
CREATE TABLE TableB (BID int IDENTITY(1,1),
PID int,
Id int,
[Type] varchar(4),
Totals int);
--insert Data
INSERT INTO TableB (PID, Id, [Type], Totals)
SELECT A.PID,
T.Id,
T.[Type],
T.Totals
FROM #temp T
LEFT JOIN TableA A ON T.ParentRow = A.Id OR (T.ParentRow IS NULL AND T.[Row] = A.Id)
ORDER BY T.[Row] ASC;
GO
--Check the data
SELECT *
FROM TableA;
SELECT *
FROM TableB;
GO
--Clean up
DROP TABLE #temp;
DROP TABLE TableA;
DROP TABLE TableB;
Go
It worked .Thanks a lot! 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply