January 27, 2012 at 3:34 pm
does this help you..??
No doubt there are other methods.
use [tempdb]
Go
IF OBJECT_ID('tempdb..Tally', 'U') IS NOT NULL DROP TABLE tempdb..Tally ;
IF OBJECT_ID('tempdb..TableA', 'U') IS NOT NULL DROP TABLE tempdb..TableA ;
IF OBJECT_ID('tempdb..TableB', 'U') IS NOT NULL DROP TABLE tempdb..TableB ;
IF OBJECT_ID('tempdb..TableC', 'U') IS NOT NULL DROP TABLE tempdb..TableC ;
SELECT TOP 100 IDENTITY(INT, 1, 1) AS N
INTO dbo.Tally
FROM sys.all_columns ac1
CREATE TABLE [dbo].[TableA](
[UniqueID] [varchar](3) NULL )
INSERT INTO [dbo].[TableA]([UniqueID])
SELECT 'ABC' UNION ALL
SELECT 'DEF' UNION ALL
SELECT 'GHI' UNION ALL
SELECT 'JKL'
CREATE TABLE [dbo].[TableB](
[NumberID] [int] )
INSERT INTO [dbo].[TableB]([NumberID])
SELECT 5
CREATE TABLE [dbo].[TableC](
[UniqueID] [varchar](3) NULL )
INSERT INTO [dbo].[TableC]([UniqueID])
SELECT 'JKL'
--SELECT * from Tablec
INSERT INTO TableC (UniqueID)
(
SELECT A.UniqueID
FROM TableA AS A INNER JOIN
TableC C ON A.UniqueID <> C.UniqueID
CROSS JOIN (SELECT T.N
FROM dbo.TableB B
INNER JOIN dbo.Tally T ON B.NumberID >= T.N) DT
)
SELECT * FROM TableC
ORDER BY UniqueID
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply