Insert With Text Based on Top Row?

I have a table with 3 columns, a varchar, a text, and an int64 (for
PK).
I need to take the text value from highest-numbered int64'd row, and
insert a new row with a new varchar value.
CREATE TABLE [TestTbl] (
[MyVarChar] [varchar] (128) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[MyText] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestTbl_Id] [int] IDENTITY (1, 2) NOT NULL ,
)
Insert into TestTbl(MyVarChar,MyText) Values ('a','aaaa')
-- This does not work, how should I word it?
insert into TestTbl( MyVarChar,MyText)
values ( 'bb' , (select top 1 MyText from TestTbl order by TestTbl_Id
desc) )
Thanks.> insert into TestTbl( MyVarChar,MyText)
> values ( 'bb' , (select top 1 MyText from TestTbl order by TestTbl_Id
> desc) )
How about :
INSERT TestTbl(MyVarChar, MyText)
SELECT TOP 1 'bb', MyText
FROM TestTbl
ORDER BY TestTbl_ID DESC
Or maybe you could explain why you need to redundantly repeat the same text
from the previous "most recently inserted" row.|||Thanks for the proper syntax.
Column MyVarChar contains a version information string and MyText
contains an XML document. When the version of the front-end
applcaction is changed, we want to copy the data to match the new
version while keeping the older version in "parallel existence". Then
we can compare program operation between different front ends (by
doing a select where MyVarChar='versionstring') and if things look
good we can just use the newer one.
Thanks again for the help.
On Mon, 12 Sep 2005 13:40:21 -0400, "Aaron Bertrand [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>How about :
>INSERT TestTbl(MyVarChar, MyText)
> SELECT TOP 1 'bb', MyText
> FROM TestTbl
> ORDER BY TestTbl_ID DESC
>Or maybe you could explain why you need to redundantly repeat the same text
>from the previous "most recently inserted" row.
>

Insert two rows into two tables at the same time from a formview

I have a formview that uses a predefined dataset based on a cross table query. When the formview is in insert mode I need to insert the data into two seperate tables. Essentially I have tblPerson and tblAddress and my formview is capturing username, password, name, address line1, address line 2, etc. I presume I need to use a stored procedure to insert a row into tblPerson and then insert a row intp tblAddress. This is easy enough to do but the tables use RI and tblPerson has an imcremental primary key which needs to be innserted into a foreign key field in my address row. How do I do this? I'm using SQL Server.

If you're passing all of the information into your Stored Procedure, then can't you simply retrieve the last ID inserted via SCOPE_IDENTITY? This assumes your using an identity column within tblPerson.

|||

Thanks for your reply. I'm not familiar with this command because I'm from a MySQL background. So I essentially I use the following

INSERT INTO tblPerson (name, username, password) VALUES (@.name, @.username, @.password);

INSERT INTO tblAddress (FK_tblPerson, address1, address2) VALUES (scope_identity(),@.address1, @.address2);

|||

Yes, except that I'd declare a variable and place the results of SCOPE_IDENTITY into it. Then I'd use that variable for my next INSERT.

Insert Trigger to Update table

Hi,

Does anyone know of a simple way to do this? I want to create an
insert trigger for a table and if the record already exists based on
some criteria, I want to update the table with the values that are
passed in via the insert trigger without having to use all the 'set'
statements for each field (so if we add fields in the future I won't
have to update the trigger). In other words, I want the trigger code
to look something like this:

if exists (select * from TableA where Fld1 = inserted.Fld1) then
//don't do insert, do an update instead (would i want to rollback here?
and will I have access to the 'inserted' table still?)
Update TableA
Set TableA.<all the fields> = Inserted.<all the fields>
where Fld1 = inserted.Fld1
end if

Any help or ideas would be appreciated.
Thanks,
TeresaUPDATE requires that you specify the columns by name. It's best
practice to do so in an INSERT statement too.

Always specify the column names. In the long run this will improve
reliability and save you development time.

--
David Portas
SQL Server MVP
--|||takilroy@.yahoo.com wrote:

> Hi,
> Does anyone know of a simple way to do this? I want to create an
> insert trigger for a table and if the record already exists based on
> some criteria, I want to update the table with the values that are
> passed in via the insert trigger without having to use all the 'set'
> statements for each field (so if we add fields in the future I won't
> have to update the trigger). In other words, I want the trigger code
> to look something like this:
> if exists (select * from TableA where Fld1 = inserted.Fld1) then
> //don't do insert, do an update instead (would i want to rollback here?
> and will I have access to the 'inserted' table still?)
> Update TableA
> Set TableA.<all the fields> = Inserted.<all the fields>
> where Fld1 = inserted.Fld1
> end if
> Any help or ideas would be appreciated.
> Thanks,
> Teresa

Nice hack.

A rollback is no good because you'd lose the update as well. But you could
delete the inserted row.

You may also have issues with primary keys and other constraints. If a
constraint fires before the trigger, your insert will fail on a pk
constraint and your clever trigger will never fire.

Finally, the performance issue is real. Doing the insert, deleting it, and
then updating causes only one real write to the table that has to be
committed, but carries three complete journal writes. An update only
carries two discreet write. It might be worthwhile to pump a few million
operations in each combination so you can at least speak knowledgeably
about what the real performance price is.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@.(Sec)ure(Dat)a(.com)|||On 12 May 2005 08:50:10 -0700, takilroy@.yahoo.com wrote:

>Hi,
>Does anyone know of a simple way to do this? I want to create an
>insert trigger for a table and if the record already exists based on
>some criteria, I want to update the table with the values that are
>passed in via the insert trigger without having to use all the 'set'
>statements for each field (so if we add fields in the future I won't
>have to update the trigger). In other words, I want the trigger code
>to look something like this:
>if exists (select * from TableA where Fld1 = inserted.Fld1) then
>//don't do insert, do an update instead (would i want to rollback here?
>and will I have access to the 'inserted' table still?)
> Update TableA
> Set TableA.<all the fields> = Inserted.<all the fields>
> where Fld1 = inserted.Fld1
>end if
>Any help or ideas would be appreciated.
>Thanks,
>Teresa

Hi Teresa,

There is no way to avoid listing the columns in an UPDATE statement. If
there were, I'd recommend against it (just as I recommend against using
SELECT * or INSERT without column-list in production code).

Also, your trigger's pseudo-code will do an update for all rows that
were inserted if at least one of them exists in TableA. You could remove
the existance check; the effect will be the same (rows that are not in
TableA won't be changed, rows that are will be - and if no row in
inserted is also in TableA, nothing changes in TableA), but it will
somewhat improve performance.

Also, Kenneth is correct - constraints are checked before the trigger is
executed. The only way around that is to use an INSTEAD OF trigger that
updates rows that are already present and inserts rows that are not yet
present.

If you need help transforming this to an INSTEAD OF trigger, just
holler.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

insert query question

Hi I have two temporary tables in a query and need to combine them as
described below. Thanks.
Table 1-results from query 1 based on a start and end date
*********************************************
*day * location * type * cost * Name* color*weight*
*********************************************
*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
***********************************************
* 2/3/07* calif * food * .50 * candy * blue *.1lb *
***********************************************
Table 2 list of all names
***************************
* name * location * Cost * Type *
***************************
*cat * AZ * $2.00 * animal *
***************************
*hamer *Texas *$1.0 *tool *
***************************
*candy *calif *.50 * food *
****************************
table 2 lists all of the named items. I would like to insert records from
table2 into table 1 in a fashion that will leave table 2 with all of the
named items for each date, as shown below. It does not write over what is in
table one but inserts records so all named items show up for every day.
Table1 after updated
*********************************************
*day * location * type * cost * Name* color*weight*
*********************************************
*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
***********************************************
*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
table2
**********************************************
*2/1/07* calif * food * .50 * candy *NULL *NULL *from
table2
**********************************************
* 2/3/07* calif * food * .50 * candy * blue *.1lb *
***********************************************
* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
***********************************************
*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
***********************************************
--
Paul G
Software engineer.Without knowing really basic stuff like keys it is pure guesswork
trying to write a query.
Something like this might get you started. Or not. The general idea
is to use a CROSS JOIN of the dates against the names to get the set
of rows you want in the results, then join that result to the detail
to fill in the rest.
SELECT A.day, B.location, B.type, B.cost, B.Name,
C.color, C.weight
FROM (SELECT DISTINCT day FROM Tbl1) as A
CROSS JOIN
Tbl2 as B
LEFT OUTER
JOIN Tbl1 as C
ON A.day = C.day
AND A.name = C.name
Roy Harvey
Beacon Falls, CT
On Mon, 20 Aug 2007 13:36:00 -0700, Paul
<Paul@.discussions.microsoft.com> wrote:
>Hi I have two temporary tables in a query and need to combine them as
>described below. Thanks.
>Table 1-results from query 1 based on a start and end date
>*********************************************
>*day * location * type * cost * Name* color*weight*
>*********************************************
>*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
>***********************************************
>* 2/3/07* calif * food * .50 * candy * blue *.1lb *
>***********************************************
>Table 2 list of all names
>***************************
>* name * location * Cost * Type *
>***************************
>*cat * AZ * $2.00 * animal *
>***************************
>*hamer *Texas *$1.0 *tool *
>***************************
>*candy *calif *.50 * food *
>****************************
>table 2 lists all of the named items. I would like to insert records from
>table2 into table 1 in a fashion that will leave table 2 with all of the
>named items for each date, as shown below. It does not write over what is in
>table one but inserts records so all named items show up for every day.
>Table1 after updated
>*********************************************
>*day * location * type * cost * Name* color*weight*
>*********************************************
>*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
>***********************************************
>*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
>table2
>**********************************************
>*2/1/07* calif * food * .50 * candy *NULL *NULL *from
>table2
>**********************************************
>* 2/3/07* calif * food * .50 * candy * blue *.1lb *
>***********************************************
>* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
>***********************************************
>*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
>***********************************************|||thanks for the response. I left off the key column. Table 1 it is
**************************************************
day_id prim key * day (datetime)* type,name cost are all (varchar(20))
and table2 is
***************************************************
name_id prim key * location cost type are all (varchar(20)).
--
I will try what you have provided.
Paul G
Software engineer.
"Roy Harvey" wrote:
> Without knowing really basic stuff like keys it is pure guesswork
> trying to write a query.
> Something like this might get you started. Or not. The general idea
> is to use a CROSS JOIN of the dates against the names to get the set
> of rows you want in the results, then join that result to the detail
> to fill in the rest.
> SELECT A.day, B.location, B.type, B.cost, B.Name,
> C.color, C.weight
> FROM (SELECT DISTINCT day FROM Tbl1) as A
> CROSS JOIN
> Tbl2 as B
> LEFT OUTER
> JOIN Tbl1 as C
> ON A.day = C.day
> AND A.name = C.name
> Roy Harvey
> Beacon Falls, CT
>
> On Mon, 20 Aug 2007 13:36:00 -0700, Paul
> <Paul@.discussions.microsoft.com> wrote:
> >Hi I have two temporary tables in a query and need to combine them as
> >described below. Thanks.
> >
> >Table 1-results from query 1 based on a start and end date
> >
> >*********************************************
> >*day * location * type * cost * Name* color*weight*
> >*********************************************
> >*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
> >***********************************************
> >* 2/3/07* calif * food * .50 * candy * blue *.1lb *
> >***********************************************
> >Table 2 list of all names
> >***************************
> >* name * location * Cost * Type *
> >***************************
> >*cat * AZ * $2.00 * animal *
> >***************************
> >*hamer *Texas *$1.0 *tool *
> >***************************
> >*candy *calif *.50 * food *
> >****************************
> >table 2 lists all of the named items. I would like to insert records from
> >table2 into table 1 in a fashion that will leave table 2 with all of the
> >named items for each date, as shown below. It does not write over what is in
> >table one but inserts records so all named items show up for every day.
> >Table1 after updated
> >*********************************************
> >*day * location * type * cost * Name* color*weight*
> >*********************************************
> >*2/1/07* texas * tool * $1.00* hamer* black * 1lb *
> >***********************************************
> >*2/1/07* az * animal * $2.00* cat * NULL * NULL*from
> >table2
> >**********************************************
> >*2/1/07* calif * food * .50 * candy *NULL *NULL *from
> >table2
> >**********************************************
> >* 2/3/07* calif * food * .50 * candy * blue *.1lb *
> >***********************************************
> >* 2/3/07*az * animal * $2.00* cat * NULL * NULL*from table2
> >***********************************************
> >*2/3/07* texas * tool * $1.00* hamer* NULL * NULL *from table2
> >
> >***********************************************
>

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
> --
>

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
> --
>

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
> --
>