Need to populate a table with 200k records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need to populate a table with 200k records

Author

Topic

Gorovdude23
Starting Member

4 Posts

Posted - 2012-07-11 : 10:06:55

Hello,

I am new to SQL and I have a table that i need to add 200k records to it.

There are 2 Columns in the table, one for ID (INT) and one for UserName (VARCHAR(255)).

The ID Column values begins with 100 and running untill the last record (end with 200099)

The UserName Column values are combination of pstring prefix ("user") and a number (starts with 0 and increasing)

Here is the SQL Stred Procedure that I wrote for it, i mange to run it but nothing happns...

Can someone please assist?

Thanks,

gorovDude


CREATE PROCEDURE test AS 
BEGIN

DECLARE  @ind INT
SET @ind = 100
DECLARE  @ind2 INT
SET @ind2 = 0

DECLARE  @v_query VARCHAR(255)
DECLARE  @str VARCHAR(255)
DECLARE  @str2 VARCHAR(255)
DECLARE  @TableName VARCHAR(255)

BEGIN
WHILE (@ind <=10)
SET @str = CAST(@ind as varchar(255))
SET @str2 = CAST(@ind2 as varchar(255))
SET @TableName = 'dbo.TEMP'
SET @v_query = 'INSERT INTO ' + @TableName + 'VALUES ( ' + @str + ' , ''user' +  @str2 + ''')'  ;

EXEC (@v_query)

SET	@ind = @ind + 1;
SET	@ind2 = @ind2 + 1;

END

END
GO

Here is how I Run the Store Procedure


EXECUTE test

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-11 : 10:29:31

Your while loop will be effective only for the next line in your code unless you use a BEGIN/END pair. So change would need to do something like this:

WHILE (@ind <=10)
BEGIN
SET @str = CAST(@ind as varchar(255))
SET @str2 = CAST(@ind2 as varchar(255))
SET @TableName = 'dbo.TEMP'
SET @v_query = 'INSERT INTO ' + @TableName + 'VALUES ( ' + @str + ' , ''user' +  @str2 + ''')'  ;

EXEC (@v_query)

SET	@ind = @ind + 1;
SET	@ind2 = @ind2 + 1;
END

But, that is likely to be a slow operation. Do something like this using a numbers table.

-- Create a numbers table.
CREATE TABLE #N(n INT NOT NULL PRIMARY KEY CLUSTERED );
;WITH N(n) AS
(SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 200000)
INSERT INTO #N SELECT n FROM N OPTION (MAXRECURSION 0);

-- use the numbers table to populate the TempValues table.
INSERT INTO dbo.TempValues
SELECT
	n+99,
	'user' + CAST(n-1 AS VARCHAR(32))
FROM #N; 

-- Delete numbers table.
DROP TABLE #N;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-11 : 11:30:53

cant understand need of dynamic sql here

Also why not make ID a identity column with seed as 100 and make userName computed column based on it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Gorovdude23
Starting Member

4 Posts

Posted - 2012-07-12 : 01:52:54

Thanks sunitabeck and visakh16
@visakh16 > The table is given and i am not able / allowed to change it.

As for the solution by @sunitabeck, I found it very usable and i changed it a bit to meet my exact needs / existing table configuration and here is how it currently look like


-- Create a numbers table.
CREATE TABLE #N(n INT NOT NULL PRIMARY KEY CLUSTERED );
;WITH N(n) AS
(SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < 149999)
INSERT INTO #N SELECT n FROM N OPTION (MAXRECURSION 0);

-- use the numbers table to populate the TempUsersCreation table.
INSERT INTO dbo.TempUsersCreation
SELECT
		n
		,'NULL'
		,'NULL'
		,'NULL'
		,'NULL'
		,'NULL'
		,'Person'
		,'NULL'
		,'NULL'
		,'ffuser' + CAST(n-1 AS VARCHAR(32))
		,n+99
FROM #N; 

-- Delete numbers table.
DROP TABLE #N;

As you can see the table that i need to populate has 11 Columns (I asked about the way to deal with 2 specific columns in the previous message). Now when i want to use these Queries to fill it, I need to know how to populate special Columns (Example - the Cross Reference ID is a Auto number column)

When I try to run the "INSERT INTO" Query, I get an Error (not supprisig) of:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

If you need the exact defenition of the TempUsersCreation Table, here it is:


Column Name             Data Type       Allow Nulls
[Cross Reference ID]	int	        Unchecked
[Created By]	        nvarchar(50)	Checked
[Creation Date]	        datetime	Checked
[Modified By]	        nvarchar(50)	Checked
[Modification Date]	datetime	Checked
[Row Status]	        nvarchar(15)	Checked
Object	                nvarchar(15)	Checked
[External System]	nvarchar(15)	Checked
[External ID]	        bigint	        Checked
[External Key]	        nvarchar(50)	Checked
[Analytic ID]	        int	        Checked

Thanks Once Again,
GorovDude

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-12 : 06:57:46

Remove the single quotes around the NULLs in the select list:

INSERT INTO dbo.TempUsersCreation
SELECT
		n
		,NULL
		,NULL
		,NULL
		,NULL
		,NULL
		,'Person'
		,NULL
		,NULL
		,'ffuser' + CAST(n-1 AS VARCHAR(32))
		,n+99
FROM #N;

If you want to insert a NULL, it should be listed without the quotes. When you list it with quotes, you are inserting the string NULL rather than a NULL value.

Gorovdude23
Starting Member

4 Posts

Posted - 2012-07-12 : 08:05:20

Works like Magic. Thanks!

One more Question / Request:

In the Numbers Table I have numbers between 0 and 212,399

There are 4 string prefix that I need to use:
AAA for numbers between 0 - 149,999
BBB for numbers between 150,000 - 209,999
CCC for numbers between 210,000 - 211,799
DDD for numbers between 211,800 - 212,399

How can I add it to the Query?

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-12 : 09:49:29

You can use a case expression. Something like this:

CASE 
	WHEN n <= 149999 THEN 'AAA'
	WHEN n <= 209999 THEN 'BBB'
	WHEN n <= 211799 THEN 'CCC'
	WHEN n <= 212399 THEN 'DDD'
	ELSE ''
END
	+ CAST(n-1 AS VARCHAR(32)) -- if you need to append n

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 10:10:10

quote:
Originally posted by Gorovdude23

Works like Magic. Thanks!

One more Question / Request:

In the Numbers Table I have numbers between 0 and 212,399

There are 4 string prefix that I need to use:
AAA for numbers between 0 - 149,999
BBB for numbers between 150,000 - 209,999
CCC for numbers between 210,000 - 211,799
DDD for numbers between 211,800 - 212,399

How can I add it to the Query?

Thanks!

Use a CASE...WHEN statement if its one time calculation

like


..
CASE WHEN number BETWEEN 0 AND 149999
     THEN 'AAA'
     WHEN number BETWEEN 150000 AND 209999
     THEN 'BBB'
...
END AS YourNewColumnName

for a more scalable and maintainable long term solution add a mapping table like


NumberRanges
--------------
RangeDesc         StartValue         EndValue
----------------------------------------------
AAA                  0                149999
BBB               150000              209999
...


and use a join in your query like

JOIN NumberRanges nr
ON yourtable.Numbers BETWEEN nr.StartValue AND nr.EndValue
...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources