May 1, 2003 at 3:22 pm
I have 5500 records that have a unique key between 1 and 9999. My problem is I need have a list of the unique key numbers that aren't in this table.
So if 1,3,and 5 are in the table, I need a way to put 2 and 4 into a temp table to then query another table.
I have thought about making a table with all 9999 numbers in it and then doing a select * from other_table where unqkey not in (select * from first_table) but I don't know how to insert 9999 records to the other_table without hitting the F5 key 9999 times.
Any ideas are appreciated,
Michelle
Michelle
May 1, 2003 at 3:49 pm
You could spend some time creating a cursor that loops through and selects into a table. Or you could cheat, go to excel put in a field name in A1 then in A2 put the number 1 then in A3 put =A2+1 copy that then hold down shift and go to row 10,000 and click then paste, save this and DTS it in as a table. It all depends on if it is worth the programming effort or if the stupid way is ultimately faster. I often find myself spending a lot of programming time for something I could do in Excel in 2 minutes. Work smarter, not harder!
May 1, 2003 at 4:01 pm
I also sometimes use Excel or Access to "engineer" a solution when I can't think of an easy, quick way to do it in SQL. But in this case, the SQL code to do it is simply:
delcare @n int
set @n = 1
while @n < 10000
begin
insert <tbl> (<col>) values (@n)
set @n = @n + 1
end
You don't even need an identity column for this.
Jay Madren
Jay Madren
May 1, 2003 at 5:08 pm
Here is another possibility I find usefull soemtimes. I have seen another way to do this once which was even cooler but off hand I don't remember it.
--Build a table of the numeric position possibilites
CREATE VIEW vw_Base AS
SELECT * FROM
(select 1 union select 2 union select 3 union select 4 union select 5 union
select 6 union select 7 union select 8 union select 9 union select 0) AS tblBase (nums)
GO
--Calc the values 0 thru 9999
CREATE VIEW vw_Thru9999 AS
SELECT
(ones + tens + hundreds + thousands) as Num
FROM
(select Nums from vw_Base) AS tblas (ones)
cross join
(select Nums * 10 from vw_Base) AS tblbs (tens)
cross join
(select Nums * 100 from vw_Base) AS tblcs (hundreds)
cross join
(select Nums * 1000 from vw_Base) AS tblds (thousands)
GO
--Display the values 1 thru 9999
select Num from vw_Thru9999 Where Num != 0 Order By Num
GO
Then all you have to join is right or left join to your main table making sure this is the table where all records come from and the main is only where there is a match. Then you perform an IS NULL on the main tables Key column and the remaining output will be the missing items. Of course you have to eliminate 0 yourself unless you want 0.
May 2, 2003 at 3:20 pm
Thanks All!
I too have used Excel and Access when I can't think how to do it in SQL, but wanted to know how it could be done. I appreciate you all and now have 3 new ways to think about getting table built.
Michelle
Michelle
May 7, 2003 at 9:15 am
I would create the other_table with an Identity field and a Dummy_field.
Manualy populate the Dummy_field with anything for the first 10 rows.
Then run a query of
Insert into Other_Table (Dummy_Field) Select Dummy_field from Other_Table
You would only have to run this Query about 10 times.
You could remove the dummy_field when done.
Jeffry
May 8, 2003 at 8:12 am
/*
This code generates all missing ID #'s in table.
UniqueID: column name of your unique ID
SourceTable: name of your table with ID's
*/
declare @LastID int
select
@LastID = max(UniqueID)
from
SourceTable
create table #ids (IdVal int)
declare @cntr int
set @cntr = 1
while @cntr < @LastID
BEGIN
if exists (select UniqueID
from SourceTable
where UniqueID = @cntr)
/*do nothing, "not exists" scans entire table */
set @cntr=@cntr
else
insert into #ids (IdVal)
select @cntr
set @cntr = @cntr + 1
END
select * from #ids
drop table #ids
May 8, 2003 at 10:07 am
Try this one--------->
SET NOCOUNT ON
--CREATE TEMP TABLE FOR TEST
CREATE TABLE #TEMPTABLE(NUM_ID INT)
INSERT INTO #TEMPTABLE(NUM_ID) VALUES(1)
INSERT INTO #TEMPTABLE(NUM_ID) VALUES(3)
INSERT INTO #TEMPTABLE(NUM_ID) VALUES(5)
INSERT INTO #TEMPTABLE(NUM_ID) VALUES(8)
INSERT INTO #TEMPTABLE(NUM_ID) VALUES(10)
--SELECT ST
SELECT TEMP1.NUM_ID + 1
FROM #TEMPTABLE TEMP1
INNER JOIN #TEMPTABLE TEMP2 ON TEMP1.NUM_ID = TEMP2.NUM_ID
WHERE TEMP1.NUM_ID + 1 <> TEMP2.NUM_ID AND TEMP1.NUM_ID < (SELECT MAX(NUM_ID) FROM #TEMPTABLE)
-- DROP TEMP TABLE
DROP TABLE #TEMPTABLE
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply