A Delete loop

Results 1 to 2 of 2

Thread: A Delete loop

  1. #1
    Join Date
    Dec 1969

    Default A Delete loop

    I have a loop that inserts records into a production table (and get the data from from a temporary table). What I want to do now, is check if a record exists, and if so, delete it. It needs to check the production table against the temp table data, based on certain conditions. The conditions are:<BR><BR>WHERE tblTemp.TripTypeID= @tablename.TripTypeID<BR>AND<BR>tblTemp.Respondent ID = @tablename.RespondentID<BR>AND<BR>tblTemp.QuarterI D= @tablename.QuarterID<BR><BR>This is the code I have to create the temp table and to populate it. Currently, if I run the SP again, it just adds the records...<BR><BR>CREATE PROCEDURE spDataImport<BR><BR> @number smallint, --the number of variables to be created as columns<BR> @tablename varchar(100), --the name of the production table<BR> @dtspackage varchar(100) --the name of the DTS package to import from raw data file into temp table <BR>AS<BR>SET NOCOUNT ON<BR><BR>declare<BR> @cnt int,<BR> @update varchar(100),<BR> @id varchar(100),<BR> @tonull varchar(100),<BR> @where varchar(100),<BR> @space varchar(100),<BR> @counter int,<BR> @sql varchar(8000),<BR> @dtsstring varchar(1000)<BR><BR>--check if the temporary table exists<BR>--if it does, drop it<BR>IF EXISTS (SELECT name <BR> FROM sysobjects <BR> WHERE name = &#039;tblTemp&#039;<BR> AND type = &#039;U&#039;)<BR> DROP TABLE tblTemp<BR><BR>--here we create a temporary table<BR>set @cnt = 1<BR>set @sql = &#039;CREATE TABLE tblTemp (TripTypeID tinyint, RespondentID int NOT NULL, QuarterID smallint, &#039; <BR> <BR>WHILE @cnt &#060;= @number <BR>BEGIN <BR> select @sql = @sql + &#039; V&#039; + CAST(@cnt as varchar(3)) + &#039; varchar(10), &#039; <BR> select @cnt = @cnt + 1 <BR>END <BR>select @sql = @sql + &#039;)&#039; <BR>EXEC (@sql) <BR><BR>--now we run the dts package to import<BR>--the data from the data source<BR>--into the temporary table<BR>set @dtsstring = &#039;exec master..xp_cmdshell "dtsrun /S .... /U ..... /E /N &#039; + @dtspackage + &#039;"&#039;<BR>EXEC (@dtsstring)<BR><BR>--here we delete all the spaces in the fields from the data source<BR>--and replace them with NULL<BR><BR>set @update = &#039;update tblTemp SET &#039;<BR>set @tonull = &#039; = NULL &#039;<BR>set @where = &#039;WHERE &#039;<BR>set @space = &#039; = &#039;&#039; &#039;&#039;&#039;<BR>set @counter = 1<BR><BR>while @counter &#060;= @number<BR>begin --Loop through Activities<BR><BR>set @id = &#039;V&#039; + cast(@counter as varchar(3))<BR>set @sql = @update + @id + @tonull + @where + @id + @space<BR>print @sql<BR>exec (@sql)<BR>set @counter = @counter + 1<BR>end<BR><BR>--here we check if the data has already been added<BR>--if so, we delete the data for that quarter and insert the data from the Temp table<BR><BR><BR><BR><BR>--here we insert the new records<BR>--from the temporary table into the production table<BR>declare @insert varchar(1000)<BR>declare @from varchar(100)<BR>declare @notnull varchar(100)<BR><BR>set @insert = &#039;insert &#039; + @tablename + &#039; (TripTypeID, RespondentID, QuarterID, VariableID) select TripTypeID, RespondentID, QuarterID,&#039;<BR>set @from = &#039; from tblTemp where &#039;<BR>set @notnull = &#039; is not null&#039;<BR>set @counter = 1<BR><BR>while @counter &#060;= @number<BR>begin --Loop through Activities<BR><BR>set @id = &#039;V&#039; + cast(@counter as varchar(3))<BR>set @sql = @insert + @id + @from + @id + @notnull<BR>print @sql<BR>exec (@sql)<BR>set @counter = @counter + 1<BR>end<BR><BR>--drop the temp table<BR>DROP TABLE tblTemp<BR>GO<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: A Delete loop

    You&#039;ll have to work out all the dynamic stuff yourself, but basically you want something like this...<BR><BR>delete from t1<BR>from [production table] as t1<BR>inner join tblTemp as t2 on t2.TripTypeID = t1.TripTypeID and t2.RespondentID = t1.RespondentID and t2.QuarterID= t1.QuarterID<BR><BR>Gavin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts