SQL Server autoInsert statment

Question

0.00/5 (No votes)

See more:

Hi,

hi have table like data

ID DocumentScanLabelId BookingId NumberOfLables 
1         6            21845914         3       
2         7            21877297         3

But i need the data like
if numberoflables is 3 then i need three rows to be inserted if it has 5 then 5 row to be inserted

for example

ID DocumentScanLabelId BookingId NumberOfLables 
1         6            21845914         3
1         6            21845914         3 
1         6            21845914         3
2         7            21877297         3
2         7            21877297         3
2         7            21877297         3

Like this i want result set

Please help me out

[edit]Code block added - OriginalGriff[/edit]

Posted 11-Dec-12 0:43am

chetankumar1333

Updated 11-Dec-12 6:45am

fjdiewornncalwe

v3

Add a Solution

Comments

choudhary.sumit 11-Dec-12 6:45am

from where NumberOfLables values are coming?? i think the first row is insrted manually and the rest should be automatic. am i right??

chetankumar1333 11-Dec-12 6:51am

Hi,

its a table

choudhary.sumit 11-Dec-12 6:54am

aren't you using any front end?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

__TR__ · Accepted Answer · 2012-12-11T01:32:00

Here is a sample code

SQL

CREATE TABLE Data
(
	ID INT,
	DocumentScanLabelId INT,
	BookingId INT,
	NumberOfLables INT
)

INSERT INTO Data
SELECT 1, 6,    21845914, 3 UNION ALL      
SELECT 2, 7,    21877297, 3 

--SELECT * FROM Data;

DECLARE @ID INT
DECLARE @RowCount INT
 
DECLARE @NumberOfLables INT

DECLARE @MaxID INT
SELECT @MaxID = MAX(T), @Id = MIN(T) FROM 
(
	SELECT DENSE_RANK() OVER (ORDER BY ID) AS T, *  FROM Data
) X


WHILE @Id <= @MaxID
BEGIN
	SELECT @NumberOfLables = NumberOfLables FROM
	(
		SELECT DENSE_RANK() OVER (ORDER BY ID) AS T, *  FROM Data
	) X
	 WHERE T = @Id

	SELECT @RowCount = COUNT(*) FROM 
	(
		SELECT DENSE_RANK() OVER (ORDER BY ID) AS T, *  FROM Data
	) X
	 WHERE T = @Id


	WHILE @NumberOfLables > @RowCount
	BEGIN
		INSERT INTO Data
		SELECT TOP 1 ID, DocumentScanLabelId, BookingId, NumberOfLables  FROM
		(
			SELECT DENSE_RANK() OVER (ORDER BY ID) AS T, *  FROM Data
		) X
		 WHERE T = @Id

		SET @RowCount = @RowCount + 1

	END

	SET @ID = @ID + 1

END


SELECT * FROM Data ORDER BY ID

I am not sure what your requirement is but I would like to add that it is not a good idea to insert duplicate records into your table.