I have would like to insert n rows where fieldA = 1
When I insert the new rows, I would like to set FieldA = [some value]
I see in the docs that I can insert a range like this:
<code>
INSERT INTO Tbl_A
SELECT * FROM Tbl_A WHERE FieldA = 1
</code>
Is there anything I can do to change the value of FieldA to [some value]
before INSERTING it?
Thanks for any tips!Steve,
There needs to be data in the table prior to the update so perform the
INSERT first then perform the UPDATE. Also, choose a different table for
the INSERT.
i.e.,
INSERT INTO Tbl_A_1
SELECT * FROM Tbl_A WHERE FieldA = 1
UPDATE TBL_A_1
SET FIELDA = xxx
WHERE FIELDA = 1
You can put this in a user-defined transaction if required.
HTH
Jerry
"Steve" <sss@.sss.com> wrote in message
news:usiPxvSyFHA.2992@.TK2MSFTNGP11.phx.gbl...
>I have would like to insert n rows where fieldA = 1
> When I insert the new rows, I would like to set FieldA = [some value]
> I see in the docs that I can insert a range like this:
> <code>
> INSERT INTO Tbl_A
> SELECT * FROM Tbl_A WHERE FieldA = 1
> </code>
> Is there anything I can do to change the value of FieldA to [some value]
> before INSERTING it?
> Thanks for any tips!
>|||Hi Steve
Best practices recommend that you NEVER use SELECT * in your production
code, but always explicitly list the columns you want to return. So if you
take that to heart you can insert whatever you like in place of FieldA.
INSERT INTO Tbl_A
SELECT MyNewValue as FieldA, FieldB, FieldC ...
WHERE FieldA = 1
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Steve" <sss@.sss.com> wrote in message
news:usiPxvSyFHA.2992@.TK2MSFTNGP11.phx.gbl...
>I have would like to insert n rows where fieldA = 1
> When I insert the new rows, I would like to set FieldA = [some value]
> I see in the docs that I can insert a range like this:
> <code>
> INSERT INTO Tbl_A
> SELECT * FROM Tbl_A WHERE FieldA = 1
> </code>
> Is there anything I can do to change the value of FieldA to [some value]
> before INSERTING it?
> Thanks for any tips!
>
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23DVt35SyFHA.612@.TK2MSFTNGP10.phx.gbl...
> Steve,
> There needs to be data in the table prior to the update so perform the
> INSERT first then perform the UPDATE. Also, choose a different table for
> the INSERT.
> i.e.,
> INSERT INTO Tbl_A_1
> SELECT * FROM Tbl_A WHERE FieldA = 1
> UPDATE TBL_A_1
> SET FIELDA = xxx
> WHERE FIELDA = 1
> You can put this in a user-defined transaction if required.
> HTH
> Jerry
>
> "Steve" <sss@.sss.com> wrote in message
> news:usiPxvSyFHA.2992@.TK2MSFTNGP11.phx.gbl...
>
Hi Jerry, thank you for the response. I should have been more clear. I see
what you are suggesting and it makes sense, however I don't wish to change
the original rows from the select statement, only the rows that were
inserted later. With your example, I would be changing both the selected
and inserted rows FieldA value or am I missing something?
I basically want to copy n rows and change a field on the copied rows only.
Thanks for the post!
Steve|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:udl$N9SyFHA.3720@.TK2MSFTNGP11.phx.gbl...
> Hi Steve
> Best practices recommend that you NEVER use SELECT * in your production
> code, but always explicitly list the columns you want to return. So if you
> take that to heart you can insert whatever you like in place of FieldA.
> INSERT INTO Tbl_A
> SELECT MyNewValue as FieldA, FieldB, FieldC ...
> WHERE FieldA = 1
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Steve" <sss@.sss.com> wrote in message
> news:usiPxvSyFHA.2992@.TK2MSFTNGP11.phx.gbl...
Thanks for the post Kalen,
I agree with that you said about not selecting wildcards, that is my mistake
;)
I will attempt what you have outlined here, it looks promising, I would like
to find a solution that will work with SqlServer and MS Access so that our
sales guys can take the app with them on their laptops.
I'll let you know how I make out.
Thanks again!
Showing posts with label range. Show all posts
Showing posts with label range. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
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.
Thanks
Hope 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 Smeyer.
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.googlegr oups.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
> --
>
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
Hope 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 Smeyer.
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.googlegr oups.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
> --
>
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 Smeyer.
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
> --
>
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 Smeyer.
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
> --
>
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
> --
>
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
> --
>
Subscribe to:
Posts (Atom)