Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Retreiving and inserting records in an SP 1

Status
Not open for further replies.
pyttviper

pyttviper

MIS
May 15, 2001
37
0
0
US
i am running a large query that produces a recordset. what i want to do is put those records into a table but i don't know the syntax to do it
this is what i have so far

Code: 
declare
@identifier as varchar(50),
@Occurrances as numeric,
@Status as int,
@Notes as varchar(30),
@Type as varchar(10),
@DTLMR as datetime

declare x cursor for
"SQL STATEMENT"-- contains the query

open x

fetch next from x into @identifier, @Occurrances, @Status , @Notes, @Type, @DTLMR
 
Sort by date Sort by votes

You can use SELECT INTO or INSERT. The cursor is not needed unless you are doing some data manipulations that can't be handled in the SQL statement.

SELECT INTO example: Creates a new table

SELECT * INTO NewTable
FROM OldTable
WHERE <criteria>

NewTable can be a temporary table.

INSERT example: appends records to an existing table

INSERT Table2
SELECT * FFROM Table1
WHERE <criteria>

If you need to insert values using variables you can still use the INSERT statement.

INSERT Table2 (Col1, Col2, ..., ColN)
VALUES (@var1, @var2, ..., @VarN)

The values inserted can be variables or literals.

Hope this helps get you started. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Upvote 0 Downvote
don' i need a looping structure to go through the produced recordset?

this is the query in its entirety
Code: 
CREATE PROCEDURE SP_Problem_Query_Run  AS

declare
@identifier as varchar(20),
@Occurrances as numeric,
@Status as int,
@Notes as varchar(30),
@Type as varchar(10),
@DTLMR as datetime

declare x cursor for

SELECT 	Identifier, Occurrences, Status, LEFT(Notes,20) AS Notes, 'ECB' AS TYPE, DatetimeLastMessageReceived
FROM	(SELECT Identifier, Notes, Status, COUNT(*) AS Occurrences , DatetimeLastMessageReceived
	FROM  	AssetCoordinate INNER JOIN Asset ON AssetCoordinate.AssetKey = Asset.dwKey
	WHERE 	AssetCoordinate.DateTime  > (GETDATE()-28) AND
		AssetCoordinate.Latitude > 0 AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/4)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/8))=1) AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/8)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/16))=1) AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/32)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/64))=1) AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/16)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/32))=1)
	 GROUP BY ALL Identifier, Notes, Status, DatetimeLastMessageReceived) AS DTS
WHERE 	(Occurrences > 10 ) AND 
	(RIGHT(Identifier,6) < 10000) AND	
	(DatetimeLastMessageReceived > GETDATE()-14) AND
	((Status = 0) OR (Status = 3) OR (Status = 2) OR (Status = 1)) AND
 	((NOT(Notes LIKE '%N/C%')) OR (Notes IS NULL)) AND
	((NOT(Notes LIKE '%DIS%')) OR (Notes IS NULL)) AND
	((NOT(Notes LIKE '%ECB%')) OR (Notes IS NULL))

UNION
SELECT 	Identifier, Occurrences, Status, LEFT(Notes,20) AS Notes, 'ECN' AS TYPE, DatetimeLastMessageReceived
FROM	(SELECT Identifier, Notes, Status, COUNT(*) AS Occurrences , DatetimeLastMessageReceived
	FROM  	AssetCoordinate INNER JOIN Asset ON AssetCoordinate.AssetKey = Asset.dwKey
	WHERE 	AssetCoordinate.DateTime  > (GETDATE()-28) AND
		AssetCoordinate.Latitude > 0 AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/4)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/8))=0) AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/8)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/16))=0) AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/32)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/64))=1) AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/16)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/32))=1)
	 GROUP BY ALL Identifier, Notes, Status, DatetimeLastMessageReceived) AS DTS
WHERE 	(Occurrences > 10) AND 
	(RIGHT(Identifier,6) < 10000) AND	
	(DatetimeLastMessageReceived > GETDATE()-14) AND
	((Status = 0) OR (Status = 3) OR (Status = 2) OR (Status = 1)) AND
 	((NOT(Notes LIKE '%N/C%')) OR (Notes IS NULL)) AND
	((NOT(Notes LIKE '%DIS%')) OR (Notes IS NULL)) AND
	((NOT(Notes LIKE '%ECN%')) OR (Notes IS NULL))
UNION
SELECT 	Identifier, Occurrences, Status, LEFT(Notes,20) AS Notes, 'EOF' AS TYPE, DatetimeLastMessageReceived
FROM	(SELECT Identifier, Notes, Status, COUNT(*) AS Occurrences , DatetimeLastMessageReceived
	FROM  	AssetCoordinate INNER JOIN Asset ON AssetCoordinate.AssetKey = Asset.dwKey
	WHERE 	AssetCoordinate.DateTime  > (GETDATE()-28) AND
		AssetCoordinate.Latitude > 0 AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/16)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/32)) = 1)  
	GROUP BY ALL Identifier, notes, status,DatetimeLastMessageReceived) AS DTS
WHERE 	Occurrences =0 AND 
	(RIGHT(Identifier,6) < 10000) AND	
	(DatetimeLastMessageReceived > GETDATE()-14) AND
	((Status = 0) OR (Status = 3) OR (Status = 2) OR (Status = 1)) AND
 	((NOT(Notes LIKE '%N/C%')) OR (Notes IS NULL)) AND
	((NOT(Notes LIKE '%EOF%')) OR (Notes IS NULL)) AND
	((NOT(Notes LIKE '%GPS%')) OR (Notes IS NULL))
UNION
SELECT 	Identifier, Occurrences, Status, LEFT(Notes,20) AS Notes, 'EON' AS TYPE, DatetimeLastMessageReceived
FROM	(SELECT Identifier, Notes, Status, COUNT(*) AS Occurrences , DatetimeLastMessageReceived
	FROM  	AssetCoordinate INNER JOIN Asset ON AssetCoordinate.AssetKey = Asset.dwKey
	WHERE 	AssetCoordinate.DateTime  > (GETDATE()-28) AND
		AssetCoordinate.Latitude > 0 AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/16)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/32)) = 0)  
	GROUP BY ALL Identifier, notes, status,DatetimeLastMessageReceived) AS DTS
WHERE 	Occurrences =0 AND 
	(RIGHT(Identifier,6) < 10000) AND	
	(DatetimeLastMessageReceived > GETDATE()-14) AND
	((Status = 0) OR (Status = 3) OR (Status = 2) OR (Status = 1)) AND
 	((NOT(Notes LIKE '%N/C%')) OR (Notes IS NULL)) AND
	((NOT(Notes LIKE '%EON%')) OR (Notes IS NULL)) AND
	((NOT(Notes LIKE '%GPS%')) OR (Notes IS NULL))
UNION
SELECT 	Identifier, Occurrences, Status, LEFT(Notes,20) AS Notes, 'TLN' As TYPE , DatetimeLastMessageReceived
FROM	(SELECT Identifier, Notes, Status, Count(*) AS Occurrences , DatetimeLastMessageReceived
	FROM 	AssetCoordinate INNER JOIN Asset ON AssetCoordinate.AssetKey = Asset.dwKey
	WHERE 	(AssetCoordinate.DateTime  > GETDATE()-28) AND
		((Floor(AssetCoordinate.AssetCoordinateFlags/1)-2*Floor(AssetCoordinate.AssetCoordinateFlags/2))=0) AND
		((Floor(AssetCoordinate.AssetCoordinateFlags/2)-2*Floor(AssetCoordinate.AssetCoordinateFlags/4))=0) AND   
		((Floor(AssetCoordinate.AssetCoordinateFlags/32)-2*Floor(AssetCoordinate.AssetCoordinateFlags/64))=1) AND  
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/16)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/32))=1) AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/4)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/8))=1)
	GROUP BY ALL Identifier, notes, status, DatetimeLastMessageReceived) AS DTS
WHERE 	(Occurrences > 4) AND
	(RIGHT(Identifier,6) < 10000) AND	
	(DatetimeLastMessageReceived > getdate()-14) AND
	((Status = 0) OR (Status = 3) OR (Status = 2) OR (Status =1)) AND
	(NOT(Notes LIKE '%DIS%') OR (ISNULL(LEN(Notes),0)=0)) AND
	(NOT(Notes LIKE '%N/C%') OR (ISNULL(LEN(Notes),0)=0)) AND
	(NOT(Notes LIKE '%TLN%') OR (ISNULL(LEN(Notes),0)=0)) 
UNION
SELECT 	Identifier, Occurrences, Status, LEFT(Notes,20) AS Notes, 'TLA' As TYPE , DatetimeLastMessageReceived
FROM	(SELECT Identifier, Notes, Status, Count(*) AS Occurrences , DatetimeLastMessageReceived
	FROM 	AssetCoordinate INNER JOIN Asset ON AssetCoordinate.AssetKey = Asset.dwKey
	WHERE 	(AssetCoordinate.DateTime  > GETDATE()-28) AND
		((floor(AssetCoordinate.AssetCoordinateFlags/1)-2*floor(AssetCoordinate.AssetCoordinateFlags/2))=1) AND
		((floor(AssetCoordinate.AssetCoordinateFlags/2)-2*floor(AssetCoordinate.AssetCoordinateFlags/4))=1)  
	GROUP BY ALL Identifier, notes, status, DatetimeLastMessageReceived) AS DTS
 WHERE 	(Occurrences > 4) AND
	(RIGHT(Identifier,6) < 10000) AND	
	(DatetimeLastMessageReceived > getdate()-14) AND
	((Status = 0) OR (Status = 3) OR (Status = 2) OR (Status =1)) AND
	(NOT(Notes LIKE '%DIS%') OR (ISNULL(LEN(Notes),0)=0)) AND
	(NOT(Notes LIKE '%N/C%') OR (ISNULL(LEN(Notes),0)=0)) AND
	(NOT(Notes LIKE '%TLA%') OR (ISNULL(LEN(Notes),0)=0))
UNION
SELECT 	Identifier, Occurrences, Status, LEFT(Notes,20) AS Notes, 'TLB' As TYPE , DatetimeLastMessageReceived
FROM	(SELECT Identifier, Notes, Status, Count(*) AS Occurrences , DatetimeLastMessageReceived
	FROM 	AssetCoordinate INNER JOIN Asset ON AssetCoordinate.AssetKey = Asset.dwKey
	WHERE 	(AssetCoordinate.DateTime  > GETDATE()-28) AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/1)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/2))=1) AND
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/2)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/4))=1) AND   
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/32)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/64))=1) AND  
		((FLOOR(AssetCoordinate.AssetCoordinateFlags/16)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/32))=1) 
	GROUP BY ALL Identifier, notes, status, DatetimeLastMessageReceived) AS DTS
WHERE 	(Occurrences > 4) AND
	(RIGHT(Identifier,6) < 10000) AND	
	(DatetimeLastMessageReceived > getdate()-14) AND
	((Status = 0) OR (Status = 3) OR (Status = 2) OR (Status =1)) AND
	(NOT(Notes LIKE '%DIS%') OR (ISNULL(LEN(Notes),0)=0)) AND
	(NOT(Notes LIKE '%N/C%') OR (ISNULL(LEN(Notes),0)=0)) AND
	(NOT(Notes LIKE '%TLB%') OR (ISNULL(LEN(Notes),0)=0)) 
ORDER BY Type, Identifier 

open x

fetch next from x into @identifier, @Occurrances, @Status , @Notes, @Type, @DTLMR 
select  @identifier, @Occurrances, @Status , @Notes, @Type, @DTLMR
 
Upvote 0 Downvote

What are hoping to accomplish? If you need to read through the cursor, you'll need a looping control. Typically, we create a WHILE loop in a T-SQL script to control looping.

FETCH NEXT FROM x INTO ...
WHILE (@@FETCH_STATUS = 0)
BEGIN
<additional statements>

FETCH NEXT FROM x INTO ...
END

However, as I mentioned you don't need to loop through the cursor in order to INSERT records into a table. In fact, it is best to avoid cursors as much possible. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

V
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
202
Volvere
V
peac3
  • Locked
  • Question
Gaps and islands
Replies
8
Views
133
Chris Miller
Chris Miller
WomanPro
  • Locked
  • Question
Insert into query adds records to unreadable format, how do I change that? 1
Replies
5
Views
137
Chris Miller
Chris Miller
BobThornton
  • Locked
  • Question
Drop a table based on the count of records in another table
Replies
13
Views
105
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
120
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top