Newbie question : query keeps running

[tvbruwae]

Hi

I wrote a query to clear a DB table and refill it with updated data from another table. It contains a lot of INSERT-lines because the table format is different for the new table.

However, when I run the query, although the table seems to fill up correctly (as far as I can check), the query doesn't stop until the log file is full (I enlarged the file a couple of times too). Is there something obviously wrong with the query?

Here it is :

TRUNCATE TABLE Complete_Listing

DECLARE @AssetTag varchar(255), @SerialNo varchar(255), @Category varchar(255), @Brand varchar(255), @Model varchar(255), @BusType varchar(255), @CPUType varchar(255), @CPUSpeed varchar(255), @BIOSDate varchar(255), @BIOS_ID varchar(255), @CDROM varchar(255), @GraphicsAdapter varchar(255), @HardDrive varchar(255), @FreeHDDSpace varchar(255), @Memory varchar(255), @MACAddress varchar(255), @IPAddress varchar(255), @IPHostname varchar(255), @Status varchar(255), @Owned_by varchar(255), @Conditions varchar(255), @WarrantyExpiration varchar(255), @OperatingSystem varchar(255), @Tag_DockingStation varchar(255), @Tag_Printer varchar(255), @Tag_Monitor varchar(255), @Tag_Scanner varchar(255)

DECLARE pclist_cursor CURSOR FOR
SELECT * FROM PCList

OPEN pclist_cursor

FETCH NEXT FROM pclist_cursor
INTO @AssetTag, @SerialNo, @Category, @Brand, @Model, @BusType, @CPUType, @CPUSpeed, @BIOSDate, @BIOS_ID, @CDROM, @GraphicsAdapter, @HardDrive, @FreeHDDSpace, @Memory, @MACAddress, @IPAddress, @IPHostname, @Status, @Owned_by, @Conditions, @WarrantyExpiration, @OperatingSystem, @Tag_DockingStation, @Tag_Printer, @Tag_Monitor, @Tag_Scanner

WHILE @@FETCH_STATUS = 0
BEGIN
INSERT Complete_Listing VALUES (@AssetTag, "Serial No", @SerialNo)
INSERT Complete_Listing VALUES (@AssetTag, "Category", @Category)
INSERT Complete_Listing VALUES (@AssetTag, "Brand", @Brand)
INSERT Complete_Listing VALUES (@AssetTag, "Model", @Model)
INSERT Complete_Listing VALUES (@AssetTag, "Bus Type", @BusType)
INSERT Complete_Listing VALUES (@AssetTag, "CPU Type", @CPUType)
INSERT Complete_Listing VALUES (@AssetTag, "CPU Speed", @CPUSpeed)
INSERT Complete_Listing VALUES (@AssetTag, "BIOS Date", @BIOSDate)
INSERT Complete_Listing VALUES (@AssetTag, "BIOS ID", @BIOS_ID)
INSERT Complete_Listing VALUES (@AssetTag, "CD-ROM", @CDROM)
INSERT Complete_Listing VALUES (@AssetTag, "Graphics Adapter", @GraphicsAdapter)
INSERT Complete_Listing VALUES (@AssetTag, "Total Harddrive Space", @HardDrive)
INSERT Complete_Listing VALUES (@AssetTag, "Free Harddrive Space", @FreeHddSpace)
INSERT Complete_Listing VALUES (@AssetTag, "Memory", @Memory)
INSERT Complete_Listing VALUES (@AssetTag, "MAC Address", @MACAddress)
INSERT Complete_Listing VALUES (@AssetTag, "IP Address", @IPAddress)
INSERT Complete_Listing VALUES (@AssetTag, "IP Hostname", @IPHostname)
INSERT Complete_Listing VALUES (@AssetTag, "Status", @Status)
INSERT Complete_Listing VALUES (@AssetTag, "Owned by", @Owned_by)
INSERT Complete_Listing VALUES (@AssetTag, "Conditions", @Conditions)
INSERT Complete_Listing VALUES (@AssetTag, "Warranty Expiration", @WarrantyExpiration)
INSERT Complete_Listing VALUES (@AssetTag, "Operating System", @OperatingSystem)
INSERT Complete_Listing VALUES (@AssetTag, "Tag Docking Station", @Tag_DockingStation)
INSERT Complete_Listing VALUES (@AssetTag, "Tag Printer", @Tag_Printer)
INSERT Complete_Listing VALUES (@AssetTag, "Tag Monitor", @Tag_Monitor)
INSERT Complete_Listing VALUES (@AssetTag, "Tag Scanner", @Tag_Scanner)

FETCH NEXT FROM pclist_cursor
INTO @AssetTag, @SerialNo, @Category, @Brand, @Model, @BusType, @CPUType, @CPUSpeed, @BIOSDate, @BIOS_ID, @CDROM, @GraphicsAdapter, @HardDrive, @FreeHDDSpace, @Memory, @MACAddress, @IPAddress, @IPHostname, @Status, @Owned_by, @Conditions, @WarrantyExpiration, @OperatingSystem, @Tag_DockingStation, @Tag_Printer, @Tag_Monitor, @Tag_Scanner

END
CLOSE pclist_cursor
DEALLOCATE pclist_cursor
GO

I haven't been using SQL for a long time, so there might be a serious error in here. Any help would be most appreciated.

Thanks

 

[vishnuprasad]

The query seems to be fine, just try to use "commit" after the fetch statement within the while...
or make the entire Inserts as a unit of work using explicit "Begin trans".

I feel that this might work.

 

[tvbruwae]

Thanks for helping. It seems that the query eventually stops after all, but with a log file of over 60MB remaining. Can the logging be turned off or decreased so that I don't need to allocate that much space?