Insert n rows into table

Question

Post reply

Insert n rows into table

planet115

Hall of Fame

Points: 3181
More actions
July 22, 2003 at 7:52 am

#82295

Can anyone suggest a good way to insert n rows into a table without using a cursor?
Only one field needs to be populated (with a zero) -- default/empty values will be fine for the rest.
I thought of:
INSERT INTO tblTarget (intTargetField)
SELECT TOP 10 0 FROM tblWithLotsOfRows
to add 10 rows for example. However, I'ld prefer not to have to depend on having a table with at least 'n' rows (as n could be quite big).

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

guarddata SSCertifiable Points: 6583 More actions · Answer 1

Take a look at SET ROWCOUNT. We have used this with some success.

SET ROWCOUNT 10

INSERT INTO tblTarget( intTargetField )

SELECT 0 FROM tblWithLotsOfRows

SET ROWCOUNT 0

Guarddata-

planet115 Hall of Fame Points: 3181 More actions · Answer 2

Thanks.

However, unless I am missing something, this doesn't do away with the need to have an existing table with at least n rows in it, and that's the problem -- sorry, should have made that clear from the start.

guarddata SSCertifiable Points: 6583 More actions · Answer 3

Ahhh - Sorry about that. So you want to create N empty rows in the target table?

I suppose you could use a while loop. I wouldn't use a cursor since is requires selecting on something.

Why do you want to pre-populate a lot of *empty* rows? Seems like there might be another approach to this problem.

Guarddata-

planet115 Hall of Fame Points: 3181 More actions · Answer 4

Yup, I'm sure there is a better way to get where I'm going... all and any advice appreciated ...

I have a table (tblBitMask) with two fields. A primary key and a non-null integer value field (BinaryValue).

The value field will always be 2 to the power of an integer (e.g. 1,2,4,8,16 etc.). This table is basically a lookup on a bitmask binary field in another table -- what's important is that not all the powers of 2 will be in there .. for example this table may contain:

1,2,8 (note, no 4)

I need to produce a table containing all the possible combinations of the numbers in tblVarBin. In this case that would be 8 results:

0,1,2,3,8,9,10,11

Final restriction .. must work in SQL Server 7.0

Now that I've found a very convoluted solution to this problem, it is probably time for some kind person to step forward and introduce me to a built in function that does this :).

Anyway, the way I've done it is below, and it should be apparant why I want to prepopulate the results table with 2 ^ (Row count in tblVarBin).

However, it still depends on there being a table with lots of rows. Maybe I could depend on sysobjects CROSS joined with itself, but I'ld rather not if I don't have to.

--------------------------------------

CREATE PROCEDURE up_BinaryCombinations

AS

DECLARE @intBlankRows int

SET @intBlankRows=Power(2,(SELECT Count(*) FROM tblBitMask))

--SELECT @intBlankRows

-- 1) Create a temp table (#tblResults) with a counter and a sum field with -- Count(*) FROM tblBitMask ^ 2 -- rows

CREATE TABLE #tblResults (

ResultsID int identity NOT NULL ,

Total int NOT NULL

)

-- **************************** HERE -- I DON'T WANT TO USE sysobjects .. cross join??? *******

SET ROWCOUNT @intBlankRows

INSERT INTO #tblResults (Total) SELECT 0 FROM sysobjects

SET ROWCOUNT 0

-- 2) Create a temp table (#tblBinaryValues) with a counter and the original Mask values (ordered asc)

CREATE TABLE #tblBinaryValues (

BinaryValueID int identity NOT NULL ,

BinaryValue int NOT NULL

)

INSERT INTO #tblBinaryValues (BinaryValue) SELECT BinaryValue FROM tblBitMask ORDER BY BinaryValue ASC

--3) Update @tblResults to show all possible combinations of binary values, based on the masks available in @tblBinaryValues

UPDATE tr

SET tr.Total=BitMaskSum

FROM

#tblResults tr,

(

SELECT

r.ResultsID,

Sum(bv.BinaryValue) As BitMaskSum

FROM

#tblResults r,

#tblBinaryValues bv

WHERE ((r.ResultsID-1) & Power(2,(bv.BinaryValueID-1)))=Power(2,(bv.BinaryValueID-1))

GROUP BY r.ResultsID

) AS tblDerived

WHERE tr.ResultsID=tblDerived.ResultsID

SELECT * FROM #tblResults

GO

which contains

0-n integers.

guarddata SSCertifiable Points: 6583 More actions · Answer 5

Phew! I don't think I'm catching your vision. Set me straight - I am going for the concept more than the implementation. Do you have IDs that relate to a specific set of bitmap combinations. Something like:

ID = 1, Value = 11 (all combinations of 1, 2, 8)

ID = 4, value = 17 (combinations of 1 and 16)

These could be stored as

1,1

1,2

1,8

4,1

4,16

OR

1,11

4,17

You would like a procedure where, given an ID, the result set is all possible values related to the ID?

EXEC GetPossibleValues( 1 ) produces

0, 1, 2, 3, 8, 9, 10 and 11

EXEC GetPossibleValues( 4 ) produces

0, 1, 16, 17

Am I understanding this correctly?

One more question - Is there a specified limit? Is this a one-byte map, two bytes, unlimited?

Sorry to be slow on catching on here.

Guarddata-

planet115 Hall of Fame Points: 3181 More actions · Answer 6

You've understood it perfectly ... despite my convoluted explanation AND the fact that I left out one stage in an attempt to simplify (in my example it processes all values in tblBitMask, but the real version will work exactly as you have said).

The limit would probably kick in at 4 bytes (so the result set can come out as an int) -- I think that will be enough in this instance.

The code I posted seems to work as far as I can tell, but there may/must be an easier way.

I probably will take your suggestion and use a while loop to populate the initial table, I just wondered if there was a 'purer' way.

guarddata SSCertifiable Points: 6583 More actions · Answer 7

I am sure what you are doing would work. Here is another option...

Since the value is really a bitmap, it is fine to store it as an integer rather than each value separately. (17 rather than 1 and 16)

CREATE PROCEDURE DetermineOptions (

@mapID INT

) AS

DECLARE @mapValues INT, @mapWalk INT

SET NOCOUNT ON

--Get the bitmap from the table

SELECT @mapValues = ISNULL( PossibleValues, 0) from Table WHERE ID = @mapID

--Temporary table to accumulate output

CREATE TABLE #tmpVals (

aVal ID

)

--Zero is always an option

INSERT INTO #tmpVals VALUES ( 0 )

--Start with the first map option

SET @mapWalk = 1

WHILE @mapWalk <= @mapValues

BEGIN

--Is this option set

IF ( @mapWalk & @mapValues ) = @mapWalk

BEGIN

--Add this to all existing entries

INSERT INTO #tmpVals

SELECT aVal + @mapWalk FROM #tmpVals

END

--Step to the next option

SET @mapWalk = @mapWalk * 2

END

--Output the results

SELECT DISTINCT aVal FROM #tmpVals

ORDER BY aVal

RETURN

Don't know if that is any easier or faster, but it seems to work in a maximum of 32 loops.

Guarddata-

planet115 Hall of Fame Points: 3181 More actions · Answer 8

Thanks very much for that guarddata. It's always very educational for me to see different ways of approaching problems.

quote:
Since the value is really a bitmap, it is fine to store it as an integer rather than each value separately. (17 rather than 1 and 16)

Definitely. In this instance, I'm just doing a few sprocs for somebody else who is busy, so I don't have the big picture. There probably is a good reason why it is stored the way it is though -- of course the values could always be summed before using your procedure.

guarddata SSCertifiable Points: 6583 More actions · Answer 9

Yeah - that's why I love these forums. Helps me open my mind to new ideas. If the individual offsets were stored in separate rows, I would probably cursor through the entries and still add them the way this one does. Whatever.

Thanks

Guarddata-