Insert Query - Please help

Does anyone know how to insert records into another table based on a range.
See the following example
Table A contains
Col1 (From), Colb (To), Colc (Date)
902,905,01/01/2005
906,907, 01/03/2005
Table B Need to contain
Col1 (From), Colb (To), Colc (Date)
902,902,01/01/2005
903,903,01/01/2005
904,904,01/01/2005
905,905,01/01/2005
906,906,01/03/2005
907,907,01/03/2005
Any help gratefully appreciated.
ThanksHope to understood you right:
-- =============================================-- Declare and using a READ_ONLY cursor
-- =============================================DECLARE Looper CURSOR
READ_ONLY
FOR Select [From],[To],[Date] From TableA
DECLARE @.From Smallint
DECLARE @.TO Smallint
DECLARE @.Date varchar(40)
OPEN Looper
FETCH NEXT FROM Looper INTO @.From,@.to,@.Date
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
While @.From <= @.To
BEGIN
Insert Into TableB Values(@.From,@.To,@.Date)
SET @.From = @.From +1
END
END
FETCH NEXT FROM Looper INTO @.From,@.to,@.Date
END
CLOSE Looper
DEALLOCATE Looper
GO
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"Sarah Kingswell" <skingswell@.xonitek.co.uk> schrieb im Newsbeitrag
news:%237D6ntZQFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to insert records into another table based on a
> range. See the following example
> Table A contains
> Col1 (From), Colb (To), Colc (Date)
> 902,905,01/01/2005
> 906,907, 01/03/2005
> Table B Need to contain
> Col1 (From), Colb (To), Colc (Date)
> 902,902,01/01/2005
> 903,903,01/01/2005
> 904,904,01/01/2005
> 905,905,01/01/2005
> 906,906,01/03/2005
> 907,907,01/03/2005
> Any help gratefully appreciated.
> Thanks
>|||Create a table of numbers if you aren't using one already:
http://www.bizdatasolutions.com/tsql/tblnumbers.asp
Then do this:
INSERT INTO TableB (col1, colb, colc)
SELECT N.num, N.num, A.date
FROM Numbers AS N, TableA AS A
ON N.num BETWEEN A.col1 AND A.colb
It may be useful to put the above query in a view rather than a table.
--
David Portas
SQL Server MVP
--|||Here goes:
-- Auxiliary table of numbers
SET NOCOUNT ON
USE tempdb
GO
IF OBJECT_ID('Nums') IS NOT NULL
DROP TABLE Nums
GO
CREATE TABLE Nums(n INT NOT NULL)
DECLARE @.max AS INT, @.rc AS INT
SET @.max = 1000 -- change @.max according to your needs
SET @.rc = 1
BEGIN TRAN
INSERT INTO Nums VALUES(1)
WHILE @.rc * 2 <= @.max
BEGIN
INSERT INTO Nums SELECT n + @.rc FROM Nums
SET @.rc = @.rc * 2
END
INSERT INTO Nums SELECT n + @.rc FROM Nums WHERE n + @.rc <= @.max
COMMIT TRAN
ALTER TABLE Nums ADD PRIMARY KEY(n)
CREATE TABLE A
(
a INT,
b INT,
c DATETIME
)
INSERT INTO A VALUES(902, 905, '20050101')
INSERT INTO A VALUES(906, 907, '20050301')
CREATE TABLE B
(
a INT,
b INT,
c DATETIME
)
INSERT INTO B
SELECT a + n - 1 AS a, a + n - 1 AS b, c
FROM A JOIN Nums
ON n <= b - a + 1
SELECT * FROM B
a b c
-- -- --
902 902 2005-01-01 00:00:00.000
903 903 2005-01-01 00:00:00.000
904 904 2005-01-01 00:00:00.000
905 905 2005-01-01 00:00:00.000
906 906 2005-03-01 00:00:00.000
907 907 2005-03-01 00:00:00.000
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sarah Kingswell" <skingswell@.xonitek.co.uk> wrote in message
news:%237D6ntZQFHA.3868@.TK2MSFTNGP10.phx.gbl...
> Does anyone know how to insert records into another table based on a
> range. See the following example
> Table A contains
> Col1 (From), Colb (To), Colc (Date)
> 902,905,01/01/2005
> 906,907, 01/03/2005
> Table B Need to contain
> Col1 (From), Colb (To), Colc (Date)
> 902,902,01/01/2005
> 903,903,01/01/2005
> 904,904,01/01/2005
> 905,905,01/01/2005
> 906,906,01/03/2005
> 907,907,01/03/2005
> Any help gratefully appreciated.
> Thanks
>|||Thanks every much for your fast responses.. I have managed to get this
working with Jens answer. Cheers
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113557510.015892.253740@.o13g2000cwo.googlegroups.com...
> Create a table of numbers if you aren't using one already:
> http://www.bizdatasolutions.com/tsql/tblnumbers.asp
> Then do this:
> INSERT INTO TableB (col1, colb, colc)
> SELECT N.num, N.num, A.date
> FROM Numbers AS N, TableA AS A
> ON N.num BETWEEN A.col1 AND A.colb
> It may be useful to put the above query in a view rather than a table.
> --
> David Portas
> SQL Server MVP
> --
>