Fill a Table with much Data

[treFFnix]

I want to fill a Table quick. I have tried something but its not working. Here is my MSSQL-Script:

CREATE FUNCTION [dbo].[fillTable] ( @Amount INT )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
BEGIN
    DECLARE @Counter INT
    SET @Counter = 0

    WHILE @Counter < @Amount
    BEGIN
	INSERT INTO [Table] (Col1, Col2, Col3, Col4) SELECT '1', '2', '3', '4' ;    
        SET @Counter = @Counter + 1
    END
    RETURN
END
GO

And i want to call the function with

SELECT [fillTable] (100000);

to execute

INSERT INTO [Table] (Col1, Col2, Col3, Col4) SELECT '1', '2', '3', '4' ;

100000 times.

I need some help...

[Lusiphur]

For starters I'm not sure about the INSERT statement you're using, also where is the information being derived from? Another table? An application?

Your use of:

INSERT INTO [TABLE] (Col1, Col2, Col3, Col4) SELECT '1', '2', '3', '4' ;

seems incorrect to my knowledge as I'd always known it as:

INSERT INTO [TABLE] {Col1,Col2,Col3,Col4} VALUES {Val1,Val2,Val3,Val4}

at that point you can declare the valuables to match your input requirements (whatever they may be).

If the values are being derived from an application source of some sort a basic loop on the application end would suffice to input your values to the DB, if they're being derived from another table then I'm not seeing where in your example you're collecting those values to assign to the new table.

If the values are derived from a 2nd table then you would probably use something more like this:

INSERT INTO [TABLE] {Col1,Col2,Col3,Col4} VALUES {SELECT Val1,Val2,Val3,Val4 FROM [TABLE2] WHERE row=counter}

assuming you have some sort of numerically indexed column in the 2nd table.

I, personally, if transferring data from table2 (or even tables 2,3,4 & 5) to table1 would likely invest the 5 minutes to create a simple win-forms app to do it and build the world's simplest data loop lol but that's me.

On re-reading your post though, are you trying to input just the values "1" "2" "3" and "4" into the 4 columns repeatedly without an external data source? If So, then:

INSERT INTO [TABLE] {Col1,Col2,Col3,Col4} VALUES {'1','2','3','4'}

should suffice in place of the version you have with INSERT/SELECT in the same row. And, I believe, if the columns are set to any numeric data type you can do {1,2,3,4} instead of {'1','2','3','4'} as (but I could be mistaken) I believe the '' indicates character/string values.

[treFFnix]

Thanks for your Time, but I solved it by myself:

Thats what i wanted:

DECLARE @Counter INT
DECLARE @Anzahl INT
SET @Anzahl = 10000
SET @Counter = 0
WHILE @Counter < @Anzahl
BEGIN
	INSERT INTO [TABLE] ( [Col1], [Col2] , [Col3] , [Col4]) SELECT 'Value1', 'Value2', 'Value3', 'Value4';
	SET @Counter = @Counter + 1;
END
GO

[Lusiphur]

I'm still curious where you're deriving the values that are being inserted based on the original code but I'm glad it was solved :)

[treFFnix]

ok you got me, that is not the code i am using.

i use this (with random Values and a Random Date, as you can see):

DECLARE @Counter INT
DECLARE @Anzahl INT
SET @Anzahl = 10000
SET @Counter = 0
WHILE @Counter < @Anzahl
BEGIN
	INSERT INTO [t_value] ( [Key], [Time], [BatchId], [CharacteristicId], [Value]) SELECT CAST(RAND() * 100 as INTEGER) + 1, DATEADD(second, rand()*36000, DATEADD(day, rand()*120, '2010-01-01 08:00:00')), CAST(RAND() * 100 as INTEGER) + 1, CAST(RAND() * 100 as INTEGER) + 1, CAST(RAND() * 100 as INTEGER) + 1;
	SET @Counter = @Counter + 1;
END
GO

[Lusiphur]

Ah, well that makes a lot more sense now hehe.

Anywho, glad you solved it, should still mark the thread as solved to keep people from thinking it's an active thread going forward from here.

3:21am = goodnight! :D