Automated row creation - SQL Server Forums

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)

Automated row creation

Author

Topic

j.lawson
Starting Member

3 Posts

Posted - 2014-05-27 : 15:11:55

Hello folks,

Trying to create a test environment at my job for a particular issue we're having and I need to be able to create 500 rows in a particular table.

Now I know there has to be an easier way than 500 insert into statements, correct?

Any help would be wonderful. May be a stupid question, I apologize.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-27 : 15:20:33

If the data for each row is distinct, you will have to have 500 insert statements (or one insert statement with 500 items). E.g.

INSERT INTO yourTable (col1, col2)
VALUES
	(1,2),
	(3,4),
	(8,9);

If they are all the same values, you can write one insert statement and repeat it 500 times like this:

INSERT INTO yourTable (col1, col2)
VALUES
	(1,2);
GO 500

j.lawson
Starting Member

3 Posts

Posted - 2014-05-27 : 15:31:04

so there would be no way for me to make a unique primary key for each row? I need to do an ID type column for each that should be incremental.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-27 : 15:54:06

Do you need to generate a unique value for each row being inserted making sure that the value generated doesn't already exist in the table? Or just unique amongst those 500?

j.lawson
Starting Member

3 Posts

Posted - 2014-05-27 : 16:23:43

preferably unique, but starting at a certain number.. (there are already 5 rows, so starting at 6) .. but if this isn't possible then just unique amongst the 500 would be enough.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-27 : 16:54:00

You might be able to do something like:

DECLARE @CurrentMax INT;

SET @CurrentMax = COALESCE((SELECT MAX(ColumnName) FROM MyTable), 0)

;WITH 
Tens (N)     AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                 SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9), 
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), 
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2), 
Tally (N)    AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

SELECT  N + @CurrentMax
FROM    Tally
WHERE N < 501

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources