Replacing NULLS

Table1 Definition
[timestamp], datetime
[System201], int
[System500], int
[System345], int
... (the number of [System###] fields will be different for each facility I deploy this in)

Current Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 NULL NULL
1/3/03 NULL 5 8

I would like to replace the NULL's with the value from the previous record. Is there a quick and dirty way to do this?

Future Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 2 4
1/3/03 3 5 8

JeffreyI see a cursor in your future (ducks and hides)...

Seriously, the only way the I know of to transpose one row of data onto another in the type of scenario you describe is to do it with a cursor:

-- Declare Local variables (one for each column, plus an
-- additional variable for each column to store the previous value)
DECLARE
@.TimeStamp datetime,
@.System201 int,
@.System500 int,
@.System345 int,
...
@.System201_Old int,
@.System500_Old int,
@.System345_Old int,
...
@.Counter int

-- Declare the cursor
DECLARE rolling_cursor CURSOR
FROM SELECT * FROM TABLE1 ORDER BY TimeStamp

-- Open the cursor
OPEN rolling_cursor

-- Set an initial counter
Set @.Counter = 1

-- Fetch the first row of values into the local variables
FETCH NEXT FROM rolling_cursor INTO
@.TimeStamp,
@.System201,
@.System500,
@.System345

-- WHILE your way through the table, updating the next row
-- with the previous rows data when the next row's columns
-- are null (see COALESCE)
WHILE fetch_status <> -1
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
IF @.Counter = 1
BEGIN
@.System201_Old = @.System_201
@.System500_Old = @.System_500
@.System345_Old = @.System_345
END
ELSE
BEGIN
UPDATE TABLE1 SET
@.System201 = COALESCE(@.System201, @.System201_Old),
@.System201 = COALESCE(@.System201, @.System201_Old),
@.System201 = COALESCE(@.System201, @.System201_Old),
...
WHERE TimeStamp = @.TimeStamp

@.System201_Old = @.System_201
@.System500_Old = @.System_500
@.System345_Old = @.System_345
@.Counter = @.Counter + 1
END

FETCH NEXT FROM rolling_cursor INTO
@.TimeStamp,
@.System201,
@.System500,
@.System345
END

-- Close and deallocate the cursor
CLOSE rolling_cursor
DEALLOCATE rolling_cursor

-- Print a message to the user to show how many records were updated
Print @.Counter + ' records updated.'

Notes:
1. Not tested
2. Not guaranteed
3. Mileage may vary
4. Please don't sue me
5. Don't sic the DBA gods on me for suggesting a cursor
6. I am assuming that the PK on this table is TimeStamp (and that Timestamp is sequential). This solution does not work otherwise.

Best of luck,

Hugh Scott
Originally posted by JeffreyELewis
Table1 Definition
[timestamp], datetime
[System201], int
[System500], int
[System345], int
... (the number of [System###] fields will be different for each facility I deploy this in)

Current Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 NULL NULL
1/3/03 NULL 5 8

I would like to replace the NULL's with the value from the previous record. Is there a quick and dirty way to do this?

Future Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 2 4
1/3/03 3 5 8

Jeffrey|||Jeez, i spend all that time formatting the script with spaces and everything (to show nesting) and it comes out looking out looking like that!?!?!

First, I humbly apologize if it is difficult to read through. Second, it anyone knows how to make spaces or tabs work on this forum, I would be grateful.

Regards,

Hugh Scott|||keeping my comments on cursors to myself...

edit your post and put a code at the beggining and a /code at the end like using bold, that should do it.

Also I think your logic may be flawed, your cursor sorts by time stamp and the examples don't reflect that.|||I appreciate your amendments. You're right on the comment about timestamps -- I missed that in the original statement of requirements.

Oh well, back to the drawing board!!!

Regards,

Hugh Scott

Originally posted by Paul Young
keeping my comments on cursors to myself...

edit your post and put a code at the beggining and a /code at the end like using bold, that should do it.

Also I think your logic may be flawed, your cursor sorts by time stamp and the examples don't reflect that.|||1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?|||I assumed that the PK was Timestamp. I inferred that the poster made an error and meant to put /03 instead of /02. I know what happens when you assume...

Kindest regards,

Hugh Scott

Originally posted by ispaleny
1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?|||Your right. That was a typo. The year should have been 03 instead of 02 and the PK is the [timestamp] field.|||it looks like hmscott provided the answer. Post back and let us know if it worked.|||Now, when your information is clear, I can post my solution.

/*
Script drops Table1, uses transaction to support 2 solutions in one script
*/

--INIT
drop table Table1
GO
create table Table1(
[timestamp] datetime primary key clustered
,[System201] int null
,[System500] int null
,[System345] int null
)
GO
set dateformat MDY
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/1/03', 1, 2, 4)
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/2/03', 3,NULL,NULL)
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/3/03',NULL, 5, 8)
GO

begin tran
select * from Table1

--SOLUTION WITHOUT CURSOR OR PSEDOCURSOR
/*
Recomended use of clustered PK on [timestamp] and separate index on each (System...) column for large table
*/
update t1 set
t1.[System201]=
(
select t2.[System201]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System201] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System201] is null
update t1 set
t1.[System500]=
(
select t2.[System500]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System500] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System500] is null
update t1 set
t1.[System345]=
(
select t2.[System345]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System345] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System345] is null

select * from Table1
rollback tran
begin tran
select * from Table1

--PSEDOCURSOR SOLUTION
declare @.timestamp datetime
declare @.System201 int
declare @.System500 int
declare @.System345 int
declare @.timestampPre datetime
declare @.System201Pre int
declare @.System500Pre int
declare @.System345Pre int
set nocount on
select @.timestampPre=[timestamp],@.System201Pre=[System201],@.System500Pre=[System500],@.System345Pre=[System345] from Table1 t1
where t1.[timestamp]=(select min(t2.[timestamp]) from Table1 t2)
while 1=1 begin
select @.timestamp=[timestamp],@.System201=[System201],@.System500=[System500],@.System345=[System345] from Table1 t1
where t1.[timestamp]=(select min(t2.[timestamp]) from Table1 t2 where t2.[timestamp]>@.timestampPre)
if @.@.rowcount=0 break
set @.System201Pre=isnull(@.System201,@.System201Pre)
set @.System500Pre=isnull(@.System500,@.System500Pre)
set @.System345Pre=isnull(@.System345,@.System345Pre)
if @.System201 is null or @.System500 is null or @.System345 is null
update Table1 set
[System201]=@.System201Pre
,[System500]=@.System500Pre
,[System345]=@.System345Pre
where [timestamp]=@.timestamp
set @.timestampPre=@.timestamp
end
set nocount off

select * from Table1
rollback tran|||Forgive me for being simple but there is a quick and dirty way to update previous record with data from next record.

Select *, identity(int ,1,1) as seq
into #temp
from "your table"
order by timestamp

update t1
set t1.System201 = t2.System201
from #temp t1 inner join #temp t2 on
t1.seq = t2.seq - 1|||Yes, I call that algorithm "ordered streams". But I was trying to solve
situation like this

1 1
2 NULL
3 NULL
->
1 1
2 1
3 1

by query without cursor.|||Why not use a subquery?

select t.fldnam
from tblnam t
where t.valfld is not null
and t.valfld < ( select t1.valfld
from tblnam t1
where t1.keyfld = t.keyfld )

I'm suppose it isn't the finnest but I'm shure it's cheaper than cursors, no?|||Cesar, I do not uderstand what you mean by your code.

It can be solved by single quary

select t.keyfld,t.valfld,t1.keyfld,t1.valfld
from tblnam t
join tblnam t1 on t1.keyfld=(select max(t2.keyfld) from tblnam t2 where t2.keyfld<=t.keyfld and t2.valfld is not null)

But this query is not scalable (N^3), cursor is better algorithm.|||I don't know exactly how to calculate the difference between subqueries and joins, focusing in througput, because I used to work only with lower than 100,000 records tables, so my point is I agree about cursor structures and algorithms in theoretical terms, but not in speed, at least in MSSQL7 implementation.
So I'm always preffer to complicate my scripts with more sophisticated selects/joins/temporary tables/indexes, etc...
The only drawback I've found is maintenance, because almost any VB programmer can deal with cursors but complex SQL statements.
So in my record's range its a question of maintenance/performance for me and I've choseen performance until now, against cursors.
What do you think?|||I already compared cursor and noncursor solutions at this forum.
Problem solvable without cursor can be faster without cursor always.

1. I just say, that single query solution of this problem is slower than simple cursor solution.
2. I do not understand your code

select t.fldnam
from tblnam t
where t.valfld is not null
and t.valfld < ( select t1.valfld
from tblnam t1
where t1.keyfld = t.keyfld
)|||Originally posted by ispaleny
1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?

I just tried to insert a non-cursor sample about finding previous record.

Replacing data in a string

Hi

I have a table with unique data in but it has been inputted, below is an example of the data

6331245073
638 535 0797
7023593578
ID Number 650 379 4428
OCA8 9F 32
ocb92w153
vh235704

I need to strip out the all letters and spaces so it will look like this:

6331245073
6385350797
7023593578
6503794428

ect....

Thanks

Rich

Hi Rich,

You need to create a scalar function to do this. Here is an example:
CREATE FUNCTION FormatString(@.input VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE
@.character CHAR(1),
@.newstring VARCHAR(100),
@.counter INT

SET @.counter = 0
SET @.newstring = ''

WHILE @.counter < LEN(@.input)
BEGIN
SET @.counter = @.counter + 1
SET @.character = SUBSTRING(@.input, @.counter, 1)

IF @.character >= '0' AND @.character <= '9'
SET @.newstring = @.newstring + @.character
END

RETURN @.newstring
END

You can call the function as follow (note the dbo. prefix):

SELECT dbo.FormatString('ID Number 650 379 4428' )
SELECT dbo.FormatString(fieldname) FROM tablename

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Thanks Geert did the job

Rich

Replace OpenDatabase with?

I have a MDB that consists largely of a number of linked tables to
external data files, which contain various activity data. I then open
this db from my ADP project using OpenDatabase and OpenRecordset,
which I believe are based on DAO (are they?).
I am in the process of attempting to remove all DAO code from the ADP.
I have never used ADODB to open an MDB, however, and I was wondering
if anyone has a few pointers? It's pretty simple code...
'open the file via the linked table in the mdb, and check the date
inside it
Set db = OpenDatabase("L:\Tools\Reconciliation
\Reconciliation.mdb")
Set act = db.OpenRecordset("Activity")
startDate = CDate(act.fields(1))
I'm hoping this can be converted to something using ADOBD and
connection strings. I'm pretty familiar with these, but they seem to
be extremely fragile so any advice to start would be great!
MauryDim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
'Do some stuff here
cnn.Close
Set cnn = Nothing
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
>I have a MDB that consists largely of a number of linked tables to
> external data files, which contain various activity data. I then open
> this db from my ADP project using OpenDatabase and OpenRecordset,
> which I believe are based on DAO (are they?).
> I am in the process of attempting to remove all DAO code from the ADP.
> I have never used ADODB to open an MDB, however, and I was wondering
> if anyone has a few pointers? It's pretty simple code...
> 'open the file via the linked table in the mdb, and check the date
> inside it
> Set db = OpenDatabase("L:\Tools\Reconciliation
> \Reconciliation.mdb")
> Set act = db.OpenRecordset("Activity")
> startDate = CDate(act.fields(1))
> I'm hoping this can be converted to something using ADOBD and
> connection strings. I'm pretty familiar with these, but they seem to
> be extremely fragile so any advice to start would be great!
> Maury|||On Mar 25, 10:27=A0pm, "Dave Patrick" <DSPatr...@.nospam.gmail.com>
wrote:
> =A0 =A0Dim cnn As ADODB.Connection
> =A0 =A0Set cnn =3D CurrentProject.Connection
> =A0 =A0'Do some stuff here
> =A0 =A0cnn.Close
> =A0 =A0Set cnn =3D Nothing
Ummm, no.
Maury|||That's very descriptive!
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
Ummm, no.
Maury

Replace OpenDatabase with?

I have a MDB that consists largely of a number of linked tables to
external data files, which contain various activity data. I then open
this db from my ADP project using OpenDatabase and OpenRecordset,
which I believe are based on DAO (are they?).
I am in the process of attempting to remove all DAO code from the ADP.
I have never used ADODB to open an MDB, however, and I was wondering
if anyone has a few pointers? It's pretty simple code...
'open the file via the linked table in the mdb, and check the date
inside it
Set db = OpenDatabase("L:\Tools\Reconciliation
\Reconciliation.mdb")
Set act = db.OpenRecordset("Activity")
startDate = CDate(act.fields(1))
I'm hoping this can be converted to something using ADOBD and
connection strings. I'm pretty familiar with these, but they seem to
be extremely fragile so any advice to start would be great!
Maury
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
'Do some stuff here
cnn.Close
Set cnn = Nothing

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
>I have a MDB that consists largely of a number of linked tables to
> external data files, which contain various activity data. I then open
> this db from my ADP project using OpenDatabase and OpenRecordset,
> which I believe are based on DAO (are they?).
> I am in the process of attempting to remove all DAO code from the ADP.
> I have never used ADODB to open an MDB, however, and I was wondering
> if anyone has a few pointers? It's pretty simple code...
> 'open the file via the linked table in the mdb, and check the date
> inside it
> Set db = OpenDatabase("L:\Tools\Reconciliation
> \Reconciliation.mdb")
> Set act = db.OpenRecordset("Activity")
> startDate = CDate(act.fields(1))
> I'm hoping this can be converted to something using ADOBD and
> connection strings. I'm pretty familiar with these, but they seem to
> be extremely fragile so any advice to start would be great!
> Maury
|||On Mar 25, 10:27Xpm, "Dave Patrick" <DSPatr...@.nospam.gmail.com>
wrote:
> X XDim cnn As ADODB.Connection
> X XSet cnn = CurrentProject.Connection
> X X'Do some stuff here
> X Xcnn.Close
> X XSet cnn = Nothing
Ummm, no.
Maury
|||That's very descriptive!

Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Maury Markowitz" wrote:
Ummm, no.
Maury

REPLACE name if code like

Hello

for MS SQL 2000

I want to replace a value if a Code LIKE 'A%'

MyTable :
ID
Name
Code

SELECT ID, Name, Number FROM MyTable

but if Code LIKE 'A%' then Name = 'LAN'

how can i do that ?

thank youselect ID
, case when Code like 'A%'
then 'LAN'
else Name end as Name
, Number
from MyTable|||it works

thank you

REPLACE Integers with Text Data

I am working with a database named “Documents” that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:

1 = Text

2 = Report

3 = Background

4 = Index

I would like to create a new column named “DocType” in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I).I was able to easily create the new column and cast the data type from integer to varchar:

--CREATE NEW COLUMN “DocType” WITH VARCHAR DATATYPE

ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL

Go

--UPDATE NEW COLUMN WITH CAST STRING

UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))

Go

But I have problems with the REPLACE method for replacing the numbers with letters.First I tried this based on the examples in MSDN Library:

--REPLACE NUMBERS WITH LETTERS

UPDATE Fulldocuments REPLACE (DocType,"1","T")

Which produced an error message: “Incorrect syntax near 'REPLACE'.”

Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:

UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")

Which produced the same error message: “Incorrect syntax near 'REPLACE'.”

I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.

Your UPDATE statement syntax is incorrect. And the 2nd syntax where you are trying to cast to DT_WSTR is not a valid SQL syntax also. See SQL Server Books Online for the complete UPDATE statement syntax and examples. UPDATE statement consists of SET, FROM and WHERE clauses. You are missing the SET clause. And you should use single-quotes preferably for string literals otherwise you will get errors most of the time depending on your SET options. Modify your update statement to:

UPDATE Fulldocuments SET DocType = REPLACE (DocType,'1','T')

But it doesn't seem like you want to create this DocType column in the first place. You should create a separate lookup table that contains the ids and description or type. You can then join with that table based on your ID value to get the description. This is much more flexible approach that doing it your way.

|||Thanks for correcting my syntax and the advice about creating a separate lookup table.

Replace first 3 digit of phone numbers

Hi All,

I am trying to replace only first 3-digit of phone number starting with 011 to 000.

CREATETABLE #temp(stringRep VARCHAR(60))

INSERTINTO #temp(stringRep)VALUES('011-203-0011')

INSERTINTO #temp(stringRep)VALUES('011-203-2333')

DECLARE @.startPos INT

SET @.startPos = 1

UPDATE #temp SET stringRep =REPLACE(stringRep,SUBSTRING(stringRep, 1,

3),'000')

SELECT*FROM #temp

DROPTABLE #temp

stringRep

000-203-0000
000-203-2333

it also replaces the last three digit with 000. the result i would like to get is

stringRep

000-203-0011
000-203-2333

can anybody advise me on this? thanks!

Try:

update #temp

set stringRep = stuff(stringRep, 1, 3, '000')

where stringRep like '011%'

AMB

sql

Replace existing table

Hi. I am new to SQL Server so please excuse me if my question is stupid or asked in the wrong place.

I am replicating a number of Visual FoxPro tables in SQL Server. The tables are updated daily. I have got several of them replicated fine through using BULK INSERT to load the existing data and INSERT/UPDATE/DELETE to replicate the daily updates.

However, one of the Visual FoxPro tables is re-created from scratch each day. I could do the same in SQL Server but I would like users to have uninterrupted access to it (i.e. I do not want a query to fail while I am deleting and then re-upsizing the table).

One method that occurs to me would be to upsize the table under a different temporary name and then BEGIN TRANSACTION, delete the old table, rename the new table to the old table and then COMMIT TRANSACTION. Would this work? And is there a better way of doing this?

Many thanks

Dom

I know the users have to go to lunch daily..., so announce a lunch break at 1 pm for all the people and do what to do; if your users don't want to leave do it after they go home.

Sure ,before take a full backup if you have time.

|||Thanks ggciubuc, but I'm afraid thats not an option. The update needs to be done as early as possible (but no earlier than 8am) so it cannot wait until the users have gone home, but it will not be at a consistent time of day because the start time depends on the time taken to complete a number of prior processes.|||

Make a Integration Services package , put it in a job that runs before 8 a.m (eg. 6 a.m); the package can contain:

1.a task that delete the SQL table

2. a task that re-create the SQL table

3. a task with bulk insert

with workflow you can prioritize the processes|||

Thanks Gigi - I have not come across Integration Services before, I will check it out.

However, as stated above, it cannot run earlier than 8am (typically it will run at some point around 10am but some days it may not be until 1pm or later) and I would like to avoid disrupting users if possible.

|||

I recommend to use your proposed solution (with minor change) for this task & (it may be done on the SSIS Package or DTS package), but your approach seems to be good enough to fit for your requirement.

1. Create a table on different temp name (not temp table -- #)

2. Do the manipulation on created table

3. On single transaction, Delete the existing row, insert from created table, drop the created table.

|||

Many thanks Manivannan, I'll give it a go. Deleting all of the the existing rows and then inserting the new records makes much more sense, I guess.

Could I even use a BULK INSERT to append the records (does BULK INSERT work inside a transaction?) and avoid creating the temporary table altogether?

|||

You can use the BULK INSERT on explicit(user) transaction. It can be roll backed also. J

repeating an insert statement

I want a insert statement to repeat X number of times (X will be a variable
passed from a user interface)...any hints on how to code the sql'
Thanks!!!Put it in a stored proc with a WHILE Loop.
CREATE PROC YourProc
@.Loop INT
AS
WHILE @.Loop > 0
BEGIN
INSERT INTO Table VALUES (x)
SET @.Loop = @.Loop - 1
END
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||And I might add that if X happens to be a large number, there may be a
need--for better performance--to control the number of INSERTs you want to
commit in a single transaction. By default, each INSERT commits as a single
transaction, which may not be most efficient if you are doing many
single-INSERT commits in a row.
Linchi
"Gerry M" wrote:
> I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!
What data do you want to insert? Perhaps you want all the integers from 1 to
X, in which case you can use a numbers table to help you:
INSERT INTO SomeTable (z)
SELECT num
FROM numbers
WHERE num BETWEEN 1 AND @.x -- your parameter ;
--
David Portas|||If it's SQL2005, you can do without a numbers auxiliary table. To insert the
same integer X times:
with tmp as (
select 1 as a, 1 as b
union all
select a, b+1 from tmp where b < 100 -- or @.x
)
insert junk(a)
select a from tmp;
To insert integers from 1 to @.x:
with tmp as (
select 1 as a
union all
select a + 1 from tmp where a < 100 -- or @.x
)
insert junk(a)
select a from tmp;
Linchi
"David Portas" wrote:
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >I want a insert statement to repeat X number of times (X will be a variable
> > passed from a user interface)...any hints on how to code the sql'
> >
> > Thanks!!!
> What data do you want to insert? Perhaps you want all the integers from 1 to
> X, in which case you can use a numbers table to help you:
>
> INSERT INTO SomeTable (z)
> SELECT num
> FROM numbers
> WHERE num BETWEEN 1 AND @.x -- your parameter ;
> --
> David Portas
>
>|||Hi Gerry!
You could try
INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
GO 10;
This will loop through all statements in the batch 10 times. That's assuming
that you want identical copies of the same row to be inserted of course. And
it doesn't work with variables unfortunately, as they get re-declared and
re-assigned in every loop.
Regards,
Jan
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>I want a insert statement to repeat X number of times (X will be a variable
> passed from a user interface)...any hints on how to code the sql'
> Thanks!!!|||> INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
> GO 10;
The "GO n" method will work with SQL Server tools like SSMS or SQLCMD but
not from application code. GO is a batch terminator recognized only by the
SQL Server tools and is not actually sent to the server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jan Van der Eecken" <jkerner@.mweb.co.za> wrote in message
news:OBAl5ZaGIHA.4956@.TK2MSFTNGP06.phx.gbl...
> Hi Gerry!
> You could try
> INSERT INTO MyTable ( MyValue ) VALUES ( 1 )
> GO 10;
> This will loop through all statements in the batch 10 times. That's
> assuming that you want identical copies of the same row to be inserted of
> course. And it doesn't work with variables unfortunately, as they get
> re-declared and re-assigned in every loop.
> Regards,
> Jan
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>>I want a insert statement to repeat X number of times (X will be a
>>variable
>> passed from a user interface)...any hints on how to code the sql'
>> Thanks!!!
>|||Andrew, how do I pass the variable to the stored proc? Do I use a select
statement to select it?...I want it all to fit into a single job...this is
what I have so far...but it only inserts a single record...
(In this example freeintfield_01 has a value of 100)
Select freeintfield_01
from absences
where type=206 and status=1 and freedatefield_05 is null
create proc sngen
@.freeintfield_01 int
as
while @.freeintfield_01>0
begin
insert into sngenerator (itemcode)
select itemcode
from absences
where type=206 and status=1 and freedatefield_05 is null and
freeintfield_01=@.freeintfield_01
set @.freeintfield_01=@.freeintfield_01-1
end
"Andrew J. Kelly" wrote:
> Put it in a stored proc with a WHILE Loop.
> CREATE PROC YourProc
> @.Loop INT
> AS
> WHILE @.Loop > 0
> BEGIN
> INSERT INTO Table VALUES (x)
> SET @.Loop = @.Loop - 1
> END
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >I want a insert statement to repeat X number of times (X will be a variable
> > passed from a user interface)...any hints on how to code the sql'
> >
> > Thanks!!!
>|||Are you talking about a SQL Agent job? If so then like this:
DECLARE @.freeintfield_01 int
SET @.freeintfield_01 = (SELECT freeintfield_01
from absences
where type=206 and status=1 and freedatefield_05 is null )
EXEC dbo.sngen @.freeintfield_01
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> Andrew, how do I pass the variable to the stored proc? Do I use a select
> statement to select it?...I want it all to fit into a single job...this
> is
> what I have so far...but it only inserts a single record...
> (In this example freeintfield_01 has a value of 100)
> Select freeintfield_01
> from absences
> where type=206 and status=1 and freedatefield_05 is null
> create proc sngen
> @.freeintfield_01 int
> as
> while @.freeintfield_01>0
> begin
> insert into sngenerator (itemcode)
> select itemcode
> from absences
> where type=206 and status=1 and freedatefield_05 is null and
> freeintfield_01=@.freeintfield_01
> set @.freeintfield_01=@.freeintfield_01-1
> end
>
> "Andrew J. Kelly" wrote:
>> Put it in a stored proc with a WHILE Loop.
>> CREATE PROC YourProc
>> @.Loop INT
>> AS
>> WHILE @.Loop > 0
>> BEGIN
>> INSERT INTO Table VALUES (x)
>> SET @.Loop = @.Loop - 1
>> END
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>> >I want a insert statement to repeat X number of times (X will be a
>> >variable
>> > passed from a user interface)...any hints on how to code the sql'
>> >
>> > Thanks!!!
>>|||Yes, the loop works fine but it does not insert the data (itemcode)100 times,
just once....?
"Andrew J. Kelly" wrote:
> Are you talking about a SQL Agent job? If so then like this:
> DECLARE @.freeintfield_01 int
> SET @.freeintfield_01 = (SELECT freeintfield_01
> from absences
> where type=206 and status=1 and freedatefield_05 is null )
> EXEC dbo.sngen @.freeintfield_01
>
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> > Andrew, how do I pass the variable to the stored proc? Do I use a select
> > statement to select it?...I want it all to fit into a single job...this
> > is
> > what I have so far...but it only inserts a single record...
> > (In this example freeintfield_01 has a value of 100)
> >
> > Select freeintfield_01
> > from absences
> > where type=206 and status=1 and freedatefield_05 is null
> >
> > create proc sngen
> > @.freeintfield_01 int
> > as
> > while @.freeintfield_01>0
> >
> > begin
> >
> > insert into sngenerator (itemcode)
> > select itemcode
> >
> > from absences
> > where type=206 and status=1 and freedatefield_05 is null and
> > freeintfield_01=@.freeintfield_01
> >
> > set @.freeintfield_01=@.freeintfield_01-1
> > end
> >
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Put it in a stored proc with a WHILE Loop.
> >>
> >> CREATE PROC YourProc
> >> @.Loop INT
> >>
> >> AS
> >>
> >> WHILE @.Loop > 0
> >> BEGIN
> >>
> >> INSERT INTO Table VALUES (x)
> >>
> >> SET @.Loop = @.Loop - 1
> >> END
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >> >I want a insert statement to repeat X number of times (X will be a
> >> >variable
> >> > passed from a user interface)...any hints on how to code the sql'
> >> >
> >> > Thanks!!!
> >>
> >>
>|||If the loop is executing 100 times and the data is not being inserted you
must have an issue with the select statement.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
news:F8F7A55F-0E1B-4351-B157-AAB90FA04F59@.microsoft.com...
> Yes, the loop works fine but it does not insert the data (itemcode)100
> times,
> just once....?
> "Andrew J. Kelly" wrote:
>> Are you talking about a SQL Agent job? If so then like this:
>> DECLARE @.freeintfield_01 int
>> SET @.freeintfield_01 = (SELECT freeintfield_01
>> from absences
>> where type=206 and status=1 and freedatefield_05 is null )
>> EXEC dbo.sngen @.freeintfield_01
>>
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
>> > Andrew, how do I pass the variable to the stored proc? Do I use a
>> > select
>> > statement to select it?...I want it all to fit into a single
>> > job...this
>> > is
>> > what I have so far...but it only inserts a single record...
>> > (In this example freeintfield_01 has a value of 100)
>> >
>> > Select freeintfield_01
>> > from absences
>> > where type=206 and status=1 and freedatefield_05 is null
>> >
>> > create proc sngen
>> > @.freeintfield_01 int
>> > as
>> > while @.freeintfield_01>0
>> >
>> > begin
>> >
>> > insert into sngenerator (itemcode)
>> > select itemcode
>> >
>> > from absences
>> > where type=206 and status=1 and freedatefield_05 is null and
>> > freeintfield_01=@.freeintfield_01
>> >
>> > set @.freeintfield_01=@.freeintfield_01-1
>> > end
>> >
>> >
>> > "Andrew J. Kelly" wrote:
>> >
>> >> Put it in a stored proc with a WHILE Loop.
>> >>
>> >> CREATE PROC YourProc
>> >> @.Loop INT
>> >>
>> >> AS
>> >>
>> >> WHILE @.Loop > 0
>> >> BEGIN
>> >>
>> >> INSERT INTO Table VALUES (x)
>> >>
>> >> SET @.Loop = @.Loop - 1
>> >> END
>> >>
>> >>
>> >> --
>> >> Andrew J. Kelly SQL MVP
>> >> Solid Quality Mentors
>> >>
>> >>
>> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
>> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
>> >> >I want a insert statement to repeat X number of times (X will be a
>> >> >variable
>> >> > passed from a user interface)...any hints on how to code the sql'
>> >> >
>> >> > Thanks!!!
>> >>
>> >>
>>|||Yes, I found it...thanks for your help!
Gerry
"Andrew J. Kelly" wrote:
> If the loop is executing 100 times and the data is not being inserted you
> must have an issue with the select statement.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> news:F8F7A55F-0E1B-4351-B157-AAB90FA04F59@.microsoft.com...
> > Yes, the loop works fine but it does not insert the data (itemcode)100
> > times,
> > just once....?
> >
> > "Andrew J. Kelly" wrote:
> >
> >> Are you talking about a SQL Agent job? If so then like this:
> >>
> >> DECLARE @.freeintfield_01 int
> >>
> >> SET @.freeintfield_01 = (SELECT freeintfield_01
> >> from absences
> >> where type=206 and status=1 and freedatefield_05 is null )
> >>
> >> EXEC dbo.sngen @.freeintfield_01
> >>
> >>
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >> Solid Quality Mentors
> >>
> >>
> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> news:819E8C07-8667-4865-BF86-D0AF005D3FC6@.microsoft.com...
> >> > Andrew, how do I pass the variable to the stored proc? Do I use a
> >> > select
> >> > statement to select it?...I want it all to fit into a single
> >> > job...this
> >> > is
> >> > what I have so far...but it only inserts a single record...
> >> > (In this example freeintfield_01 has a value of 100)
> >> >
> >> > Select freeintfield_01
> >> > from absences
> >> > where type=206 and status=1 and freedatefield_05 is null
> >> >
> >> > create proc sngen
> >> > @.freeintfield_01 int
> >> > as
> >> > while @.freeintfield_01>0
> >> >
> >> > begin
> >> >
> >> > insert into sngenerator (itemcode)
> >> > select itemcode
> >> >
> >> > from absences
> >> > where type=206 and status=1 and freedatefield_05 is null and
> >> > freeintfield_01=@.freeintfield_01
> >> >
> >> > set @.freeintfield_01=@.freeintfield_01-1
> >> > end
> >> >
> >> >
> >> > "Andrew J. Kelly" wrote:
> >> >
> >> >> Put it in a stored proc with a WHILE Loop.
> >> >>
> >> >> CREATE PROC YourProc
> >> >> @.Loop INT
> >> >>
> >> >> AS
> >> >>
> >> >> WHILE @.Loop > 0
> >> >> BEGIN
> >> >>
> >> >> INSERT INTO Table VALUES (x)
> >> >>
> >> >> SET @.Loop = @.Loop - 1
> >> >> END
> >> >>
> >> >>
> >> >> --
> >> >> Andrew J. Kelly SQL MVP
> >> >> Solid Quality Mentors
> >> >>
> >> >>
> >> >> "Gerry M" <GerryM@.discussions.microsoft.com> wrote in message
> >> >> news:89A1C091-2DC5-485D-B652-6109C4BDA3D0@.microsoft.com...
> >> >> >I want a insert statement to repeat X number of times (X will be a
> >> >> >variable
> >> >> > passed from a user interface)...any hints on how to code the sql'
> >> >> >
> >> >> > Thanks!!!
> >> >>
> >> >>
> >>
> >>
>

Repeat on each "page"

After finally overcoming a number of hurdles with LisaNicholls help, I've got one hurdle left that I can't seem to get around. A system I'm updating currently outputs attendance registers in HTML which is built using some complex ASP.NET code. To make this more manageable, I decided to try and handle this output in Reporting Services.

Whilst the output looks fairly inocious and simple, in theory it's been a nightmare to implement. Because it's inteded for print output, my intention was to output the report straight to PDF, however I'm encountering an issue trying to get some of the data to "repeat" on each of the PDF pages.

If you look at the following image; http://mparter.pwp.blueyonder.co.uk/images/register_page.png, you'll see 4 main areas.

The problem areas are the red, green and blue ones.

The unmarked bottom section is a matrix which pages fine on PDF. The TEXBOXES in the red area can possibly be worked-around by using report parameters. The TABLES in the blue and green areas are the main problem. I can't include them in a report header workaround because there could be multiple rows per table

So, back to my question, how do I get the three marked coloured areas to repeat on each "page" of the PDF?

you can keep those coloured part in page header.insert page header and insert row above and keep the data in the header section.it will display in each page|||But you can't put controls into a report header? Unless I'm reading your post wrong?|||I presume there's no solution for this fundamental issue?

Repeat on each "page"

After finally overcoming a number of hurdles with LisaNicholls help, I've got one hurdle left that I can't seem to get around. A system I'm updating currently outputs attendance registers in HTML which is built using some complex ASP.NET code. To make this more manageable, I decided to try and handle this output in Reporting Services.

Whilst the output looks fairly inocious and simple, in theory it's been a nightmare to implement. Because it's inteded for print output, my intention was to output the report straight to PDF, however I'm encountering an issue trying to get some of the data to "repeat" on each of the PDF pages.

If you look at the following image; http://mparter.pwp.blueyonder.co.uk/images/register_page.png, you'll see 4 main areas.

The problem areas are the red, green and blue ones.

The unmarked bottom section is a matrix which pages fine on PDF. The TEXBOXES in the red area can possibly be worked-around by using report parameters. The TABLES in the blue and green areas are the main problem. I can't include them in a report header workaround because there could be multiple rows per table

So, back to my question, how do I get the three marked coloured areas to repeat on each "page" of the PDF?

you can keep those coloured part in page header.insert page header and insert row above and keep the data in the header section.it will display in each page|||But you can't put controls into a report header? Unless I'm reading your post wrong?|||I presume there's no solution for this fundamental issue?

repeat information

Hello everyone,

I have a report that shows some people′s information (sex, birthday, phone number, e-mail...)

And I did a table that shows these information by expanding the person name..

for example:

+ Pedro

+ Mike

+ Matt

and when I click on Pedro should appear:

- Pedro

Sex: Male

Birthday: 02/15/1987

+ Mike

+ Matt

My problem is when the information is collapsed, it shows:

- Pedro

Male

02/15/1987

- Mike

Male

12/12/1985

- Matt

Sex: Male

Birthday: 06/21/1980

Does anyone know why this is happening?

PS: Im sorry if this thread has some gramaticals problems.

Set the detail section to visible=false, if not already?|||How do you organize detail section? Sex and Birthday text in separate column or you use expression like = "Sex: " & Fields!Sex.Value etc ?|||If you created a grouping that contained the names, then the detail could contain sex and birthday references. The detail section formatting doesn't matter in this instance. The key would be to initially set the row visibility hidden = false.

I think this should do it. Hope it helps.