I am doing a table fill in SQL. Created a temporary table, for example

create table #sometable2 (id int,n int,m int,v int, b int) SET @i=1 WHILE @i<=100 BEGIN

there is a big work code to fill out

insert in temporary table

 insert into #sometable2 values (@id,@n,@m,@v,@b) SET @i=@i+1 END insert into MyTable values (@id,@n,@m,@v,@b) select * from #sometable2 order by sid DROP TABLE #sometable2

sorted, and now I need to put all this data into the existing main table. But the problem is that I do not write 100 lines to the main table, for example (that is, how many of them), but only 1.

    2 answers 2

    Well, you should have studied the syntax ....

    MS SQL interprets your commands like this: 1) Insert one row into the table with the listed variables:

     insert into MyTable values ( @id , @n , @m , @v , @b )

    2) Select records from the temporary table:

     select * from #sometable2 order by sid

    And in fact, as I understand it, you wanted this:

     insert into MyTable select * from #sometable2
    • @ minamoto, then such a question, the filling does not occur because of the following error: The INSERT statement conflicted with the FOREIGN KEY constraint "D_Sale_FK2". Occurred in database "DB", table "dbo.D", column 'DId'. somehow it turns out that there are no values ​​or what? and if you do without a temporary table, then this error also exists, but the table is filled without several records, and no records are added at all through the temporary table - wicS
    • one
      This means that you are trying to insert data into the table that cannot be inserted there - a foreign key violation occurs. One of the fields refers to another table, and the values ​​you insert do not match in that table. If you do an insert directly into the table — each insert passes through a separate query, those records that have a match — are inserted, which have not — are not inserted. When you do an insert through a temporary table, the query is one and it is either completely executed or not executed at all if at least one of the rows has no match. - minamoto

    If I understand the problem correctly, the insert should be done like this:

     insert into MyTable select * from #sometable2