Good evening everyone!

I do not understand what is wrong: I can not update the table by inserting new lines into it using the syntax insert into schemaname.Tablename2 select * from schemaname.Tablename1

Explanation:

  1. There is a cycle within which a procedure is called at each step.
  2. This procedure counts and creates a table in the database (removes the old one, keeps the new one in its place).

  3. After calling the procedure in the first step of the loop, I create the Tablename2 table with the following syntax:

    select *

    into schemaname.Tablename2

    from schemaname.Tablename1

(Tablename2 - according to the idea, the results of all other procedure calls should be saved in the next steps of the cycle. As a result, a huge table should be learned with the results of unloading from all steps)

  1. In the next steps of the cycle, I want to insert in the same table new data - the result of the procedure call (which has overwritten the table Tablename1).

    insert into schemaname.Tablename2

    select * from schemaname.Tablename1

But nothing works. Error: Tablename2 already exists in your database.

Ps: different methods with insert value with a list of field names where data should be inserted are powerless here, because more than 200 fields.

I would be grateful for any ideas - how to fix this error ...

Thanks for attention!

Code:

declare @num_int int IF OBJECT_ID('Analysis.dbo.TA_FinUsers_AllPeriod') IS NOT NULL DROP TABLE Analysis.dbo.TA_FinUsers_AllPeriod; /* determine object of cursor for the loop: it is a "serial number" for each "panel" from the table TA_Dates*/ declare db_cursor cursor for select num_ from dbo.TA_Dates /* go to loop */ open db_cursor /* declare variable, wich will run from step to step in the loop and catch the current value of cursor */ fetch next from db_cursor into @num_int while @@FETCH_STATUS = 0 begin print @num_int declare @endd_ datetime declare @ndays_ int declare @Year__ int declare @Month__ int /* set value for our first variables (next - use it in the fetching procedure)*/ select @endd_ = End_Datetime from dbo.TA_Dates where num_ = @num_int; print @endd_ select @ndays_ = Days_in_Period from dbo.TA_Dates where num_ = @num_int; print @ndays_ select @Year__ = Year_ from dbo.TA_Dates where num_ = @num_int; print @Year__ select @Month__ = Month_ from dbo.TA_Dates where num_ = @num_int; print @Month__ EXEC Fetch_Execute @endd = @endd_, @ndays = @ndays_, @reg = 0, @par = 0, @par_end = 22, @Year_ = @Year__ , @Month_ = @Month__ ; if (@num_int = 1) begin select * into Analysis.dbo.TA_FinUsers_AllPeriod from Analysis.dbo.TA_FinUsers end; if (@num_int > 1) begin insert into Analysis.dbo.TA_FinUsers_AllPeriod select * from Analysis.dbo.TA_FinUsers end; fetch next from db_cursor into @num_int print @num_int end close db_cursor deallocate db_cursor

    2 answers 2

    It is necessary to create a table before the cursor, because otherwise, Sql Server assumes that you are creating a table with select into with each loop, despite your conditions.

    • Merci! Everything works) - Tatyana Aulova

    Select INTO creates a new table. You can not use existing. Otherwise use INSERT INTO.

    • I did this in the next loop step: if (@num_int> 1) begin insert into Analysis.dbo.TA_FinUsers_AllPeriod select * from Analysis.dbo.TA_FinUsers end; here the error popped up. - Tatyana Aulova