asked
Adding numerical data
I have a table that literally only holds one column which is a numeric value that is used to populate a field in a different table, once this value has been used that number is deleted from the table. Can you please advise the most efficient way of adding rows to this table, usually only 1000 or 2000 at a time or should it be changed to a Identity value, however as there is no other columns in the table how do you add rows? It's currently written as a while loop but don't think this is the most efficient piece of code and feel it should be set based but not sure how to tackle that.
I know that the question has already been answered, but I would like to comment on the part of **how do you add rows** to the table which only has one integer identity column. This is not the most efficient way of course, but there is a way to add a desired number of records to such a table:
-- create a table
create table dbo.JustOneColumn
(
col1 int identity(1, 1) primary key clustered
);
go
set nocount on;
-- insert 1000 records into it:
insert into dbo.JustOneColumn default values;
go 1000
-- insert another 2000 records. Assuming that
-- there are never any deletes, the values
-- will not have any holes :)
insert into dbo.JustOneColumn default values;
go 2000
select max(col1) LastValue from dbo.JustOneColumn;
go
set nocount off;
go
drop table dbo.JustOneColumn;
go
-- output with results to text:
Beginning execution loop
Batch execution completed 1000 times.
Beginning execution loop
Batch execution completed 2000 times.
LastValue
-----------
3000 The above is nasty because it is not set based; just wanted to show the syntax for adding rows to the table with only one identity column.
How do you know how many rows to insert? Can you use INSERT INTO YourTable SELECT TOP ( 100000 ) ROW_NUMBER() OVER ( ORDER BY c1.column_id ) FROM master.sys.all_columns c1 CROSS JOIN master.sys.all_columns c2 of course, you may not need 100000 rows ... [edit] To start at a given number, simply get the highest existing number from somewhere DECLARE @toprow int SELECT @toprow = MAX(YourCol) FROM YourTable INSERT INTO YourTable SELECT TOP ( 1000 ) ROW_NUMBER() OVER ( ORDER BY c1.column_id ) + @toprow FROM master.sys.all_columns c1 CROSS JOIN master.sys.all_columns c2 [ more edit ] Thoughts on changing this to an IDENTITY column - could well be the answer, depends on what might happen if rows are deleted and there are gaps, how many rows are likely to be in the table. Ever. What objects reference this table/column etc. Try it in test and see what catches fire :)
How you can specify the number to start with this? For example I already have 45467 in the column and want to start at 45468? Many thanks.
see my code in the comment on Kev answer. Oh, hang on I'll add it here too
Building on @Fatherjack s starting point, you could add the 'next' 1000 values with this insert into YourTable select top 1000 n from( SELECT TOP ( 10000000 ) ROW_NUMBER() OVER ( ORDER BY c1.column_id ) n FROM master.sys.all_columns c1 CROSS JOIN master.sys.all_columns c2 )rowgen where n > (select MAX(id) from YourTable) but to be honest the whole thing seems 'wrong', and this is limited by the inner `TOP` Instead of pregenerating the next n values, can you not simply derive the next one each time you need to add one?
ooh, funs :) To start from a given number I would have done DECLARE @toprow int SELECT @toprow = MAX(Mycol) FROM yourtable INSERT INTO YourTable SELECT TOP ( 100000 ) ROW_NUMBER() OVER ( ORDER BY c1.column_id ) + @toprow FROM master.sys.all_columns c1 CROSS JOIN master.sys.all_columns c2 agree it sounds wrong. need more context.
There is a table with data in it and one column is not populated, this column is currently populated from TableX which is the one I'm looking at populating. I can however, see no reason why this can't be changed to an identity column, your thoughts please.
is this a one-off retrospective population or an ongoing requirement?
It's something that currently we run monthly, part of the process is written in Access 97 so I'm re-writing the whole process, and trying to improve it where I can.
