June 23, 2017 at 5:21 am
Hello,I would like to insert from a table of articles with (1113 records), in a table (a) headers and (b) lines, insertions of 50 rows in 50 rows, for each header.That is, in table (a) the record a1 has 50 records in table (b1) of the article table, until all 1113 records have been posted.
June 23, 2017 at 5:39 am
performance wise, SQL server can insert millions of rows in about the same time as a single row or small number of rows,
so breaking up an operation into multiple steps would be that many steps slower(in your example, 22 times or so) ,
so the first question everyone will have is why? there's no reason to break it up, especially with a small amount of records.
regardless, to do something like this you need a loop of some kind. a cursor or while loop can do the iteration you are asking.
The more detail you provide, the better example we can give you.
It SEEMS like you are asking how to insert data into TWO tables, like a header/detail, right?
for that, if you need a reference to the header table, you need the output clause, or to re-join the header to the insert based on the description inserted.
the typical way to do it all at once in a pair of statements
CREATE TABLE Articles(
Articleid INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Title VARCHAR(100) )
INSERT INTO Articles(Title)
SELECT Header FROM SomeOtherTable
INSERT INTO ArticleDetails(Articleid ,ArticleBody)
SELECT at.ArticleID,ot.Detail FROM SomeOtherTable ot
INNER JOIN Articles at ON ot.Header = at.Title
Lowell
June 23, 2017 at 6:21 am
That's right, but i want to insert only 50 lines for each header until use all articles without repeat any. Example header n°1 - articles 1to50 ,
Header n°2 - articles 51to101, and so on.
Will be a set of stock documents in which each document only has 50 lines of articles.
June 23, 2017 at 6:45 am
we need Details. the actual DDL of your current table, a couple of fake sample rows of data, and the DDL of your destination tables.
with that, we can provide a tested, verified example.
aside from that, maybe what you are asking is how you can make your "virtual" header in groups of fifty.
you can do that with the NTILE function, which will group your data into groups of a max size of fifty in this example...is that what you want?
SELECT NTILE(50) OVER(ORDER BY name) AS HeaderID,
name FROM sys.all_objects
Lowell
June 23, 2017 at 6:46 am
juliosousa90 - Friday, June 23, 2017 6:21 AMThat's right, but i want to insert only 50 lines for each header until use all articles without repeat any. Example header n°1 - articles 1to50 ,
Header n°2 - articles 51to101, and so on.
Will be a set of stock documents in which each document only has 50 lines of articles.
Please provide some sample data, showing its desired state, before and after.
Also, you have not actually asked for anything – instead, you have described what you want to achieve. What is your question?
And please also describe the logic behind why it is necessary to use increments of 50. Given what you have told us, this requirement seems arbitrary and unnecessary.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 23, 2017 at 9:42 am
| TBL Articles | |||
| Id | Name | UnitPrice | Idwarehouse |
| 1 | arcticle01 | $1 | 1 |
| 2 | arcticle02 | $1 | 2 |
| 3 | arcticle03 | $1 | 3 |
| 4 | arcticle04 | $1 | 4 |
| 5 | arcticle05 | $1 | 5 |
| to | to | to | to |
| 1113 | article1113 | $1 | 1113 |
Now I want to automatically insert through the slq the data in the tables below
50 articles in each CDStockRows
| TBL CDStock (Id, NumberDoc, NameDocument, date, Total) |
| TBL CDStockRows (Id, IdCDStock, RowNumber, IdArticle, AmountUnit, Price, TotalRow) |
| TBL CDStock | |||
| Id | NumberDoc | Date | Total |
| 1 | 1 | 23-06-2017 | $50 |
| TBL CDStockRows | ||||||
| Id | IdCDStock | RowNumber | IdArticle | AmountUnit | Price | TotalRow |
| 1 | 1 | 1 | A1 | 1 | $1 | $1 |
| 2 | 1 | 2 | A2 | 4 | $1 | $4 |
| 3 | 1 | 3 | A3 | 2 | $2 | $2 |
| … | … | … | … | … | … | … |
| 50 | 1 | 50 | A50 | 3 | $1 | $3 |
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply