How can I combine different rows?

Hi!

I have a table looking like

(username) (account number) (start date) (end date) (product)

wich I can have up to 4 lines for the same client.

I wist to transfert those lines into a new table looking like

(username) (account number) (start date 1) (end date 1) (product 1)
(start date 2) (end date 2) ... (product 4)

How (in SQL) I could do it?>I have a table looking like ..

Please post DDL instead of your personal narrative. If you had done
the talbe properly, i mgiht look like this:

CREATE TABLE AccountHistory
(acct_nbr INTEGER NOT NULL,
product_nbr INTEGER NOT NULL,
product_cnt INTEGER DEFAULT 1 NOT NULL
CHECK(product_cnt BETWEEEN ! AND 4),
PRIMARY KEY (acct_nbr, product_nbr, product_cnt),
user_name VARCHAR(25) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date));

I left out the REFERENCES clause you would need and some other
things.

Quote:

Originally Posted by

Quote:

Originally Posted by

>which I can have up to 4 lines [sic] for the same client. <<

Lines appear on a paper form or an input screen; a table has rows.
You need a constraint to enforce this rule.

Quote:

Originally Posted by

Quote:

Originally Posted by

>I wish to transfer those lines into a new table looking like .. <<

You also failed to give any rules for sorting the repeating groups.
But th real question is why are you doing this at all?? That would
violate First Normal Form (1NF). This is not a good way to write
SQL.

How can I can rid of rows that have -1.#IND

I have several rows that got imported into floats with the -1.#IND value. I
want to run a query to find out which rows it is but I always get a divide
by zero error.
I tried select * from mytable where mycolumn/1 = 0Try:
DELETE
FROM YourTable
WHERE YourFloatCol < CAST(-9.9999999999999998E+37 AS FLOAT)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:%23yhdsoDRFHA.2384@.tk2msftngp13.phx.gbl...
> I have several rows that got imported into floats with the -1.#IND value.
I
> want to run a query to find out which rows it is but I always get a divide
> by zero error.
> I tried select * from mytable where mycolumn/1 = 0
>
>|||If I try:
select cast('-1.#IND' as float)
I get:
Server: Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to float.
What is the value that you want to use to filter the rows?
AMB
"Joe" wrote:

> I have several rows that got imported into floats with the -1.#IND value.
I
> want to run a query to find out which rows it is but I always get a divide
> by zero error.
> I tried select * from mytable where mycolumn/1 = 0
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:386B64F9-2E3C-4D90-BC38-E2DB3BA62478@.microsoft.com...
> If I try:
> select cast('-1.#IND' as float)
> I get:
> Server: Msg 8114, Level 16, State 5, Line 2
> Error converting data type varchar to float.
> What is the value that you want to use to filter the rows?
Alejandro,
I'm not sure how the OP got these values, or why SQL Server accepts
them, but I've had this issue in the past when working with the ATL OLE DB
libraries. If you forget to initialize a float before passing it in, that's
what you end up with (I think it's -infinity). It can be a real nightmare
to deal with... If you cast the values to VARCHAR, they show up as -1.#IND.
And at least in my case, SELECTing them w/o the CAST resulted in QA throwing
an error.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--|||Adam,
Thanks for the explanation, it is very interesting.
AMB
"Adam Machanic" wrote:

> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:386B64F9-2E3C-4D90-BC38-E2DB3BA62478@.microsoft.com...
> Alejandro,
> I'm not sure how the OP got these values, or why SQL Server accepts
> them, but I've had this issue in the past when working with the ATL OLE DB
> libraries. If you forget to initialize a float before passing it in, that
's
> what you end up with (I think it's -infinity). It can be a real nightmare
> to deal with... If you cast the values to VARCHAR, they show up as -1.#IND
.
> And at least in my case, SELECTing them w/o the CAST resulted in QA throwi
ng
> an error.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>|||Thanks Adam! In my case the values where NaN's but I was able to correct it
now.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ex4sruDRFHA.612@.TK2MSFTNGP14.phx.gbl...
> Try:
>
> DELETE
> FROM YourTable
> WHERE YourFloatCol < CAST(-9.9999999999999998E+37 AS FLOAT)
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:%23yhdsoDRFHA.2384@.tk2msftngp13.phx.gbl...
value.
> I
divide
>|||Joe,
In case you or anyone is still reading this thread, see
http://groups.google.co.uk/groups?q...ass+float_field
If you look at the IEEE floating point specification and at
CAST(YourFloatCol as binary(8)), you should be able to come up
with a way to filter these values out based on the binary value.
Steve Kass
Drew University
Joe wrote:

> Thanks Adam! In my case the values where NaN's but I was able to correct i
t
> now.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:ex4sruDRFHA.612@.TK2MSFTNGP14.phx.gbl...
>
> value.
>
> divide
>
>

How can I avoid using a CURSOR inside of another CURSOR

I am trying to avoid using a CURSOR inside of another CURSOR.

Here's what I have to do...

I have 3 tables:

Table1 has 500,00 rows

Table2 has 25 rows

Table3 is empty

For each row in Table1, I want to INSERT 25 rows (from Table2) into Table3.

Using a CURSOR inside of another CURSOR, my code looks like this...

Define and open CURSORA for TABLE1

FETCH the first row from TABLE1 using CURSORA

WHILE (@.@.FETCH_STATUS<>-1)

Define and open CURSORB for Table2

FETCH the first row from Table2 using CURSORB

WHILE (@.@.FETCH_STATUS<>-1)

INSERT a row into TABLE3 (using the data from Table1 and Table2)

FETCH the next row from Table2 USING CURSORB

CLOSE the CURSORB cursor.

FETCH the next row from Table1 USING CURSORA

CLOSE the CURSORA cursor.

Using this CURSOR inside of another CURSOR is taking way too long.

Is there an SQL technique that I can use to get around using multiple cursors?

TIA!!!

DLdfrd

Use a setbased operation, its hard to guess the solution if you don′t give us more information what you actually want to do.

HTH, jens Suessmeyer.

|||You will probably want to use a Cross Join query -- TableA Cross Join TableB. Make a select statement that returns the columns you desire, then turn it into an Insert/Select.|||

Hey anomulous,

Thanks for the advice...

A Cross Join works very well. The join still takes awhile but one of the tables had 1.7 million rows but the Cross Join is a real big improvement.

This week, I have learned Inner Joins and Left Outer Joins. Today, I learned about Cross Joins.

Thanks again...

How can I append only new rows in a table using SSIS?

Hi,

Im creatting an SSIS project that uses an Data Flow OLE DB Source to read data from an SQL Table and import it into a Destination table using Data Flow OLE DB Destination. but now everytime I run the project it appends all the rows not the new data rows only. How can I make the application so that it appends only the new data from a source table to a destination table. Is there maybe another Data Flow Control that can copy source table to destination and the next time it runs it only copy new rows. or any other way to do this using SSIS.

Your assistance will be highly appreciated.

Use the Lookup component. Set it up to return the keys of the destination table. Map the keys to your input data. Then grab the red arrow coming out of the lookup and hook it up to your destination.|||

Hi Phil,

Im new to SSIS, please bear with me. I have maped the lookup component to the destination table and hook the redline out of the lookup to destination OLE DB Destination which is also mapped to a destination table. It coppies all the rows.

Please email me the sample project, my email is sibu.khanyile@.enerserv.co.za

Thank you in advance

|||Make sure that you use a derived column to TRIM() the key fields going into the lookup component. Also know that the keys must have the same CaSE as those in the destination table in order to match.

how can i add heading in Matrix Report for columns and rows

How can i add a column heading for coulumns and row heading for rows in Matrix report created by wizard. i m using vs 2005 ..You need to create a Row Group or Column Group but at least from my perspective I have always stayed from Matrix's in MS RS and the reason why is - I cannot control the extrapolation of the matrix to the defined width of my report - probably a lousy reason - but I "fake" out based on a regular table layout a matrix look and feel with drill through's...
If the user wants to see everything then "click" and everything will be displayed - but for the first panel of data I provide as much possible releavant info and you want to see more then click on a link and I will navigate you to eternity! Also, when I navigate I keep the primary data field they clicked on as a parm and display this in the subsequent (called module) so the have a "path" of wheer they started there "infinite journey" from!
Best regards,

how best to replace a corrupt user table

Hello,
I must have a corrupt user table in my user database. If I try to get
a count of rows, I get the following error:
Could not open FCB for invalid file ID 18 in database
And if I run "DBCC CHECKTABLE ('myTable')" in QA, I get this message:
Server: Msg 7965, Level 16, State 2, Line 1
Table error: Could not check object ID 220579874, index ID 0 due to
invalid allocation (IAM) page(s).
Server: Msg 8946, Level 16, State 1, Line 1
Table error: Allocation page (1:23417559) has invalid IAM_PAGE page
header values. Type is 1. Check type, object ID and page ID on the
page.
DBCC results for 'myTable'.
There are 0 rows in 0 pages for object 'myTable'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'myTable'(object ID 220579874).
repair_allow_data_loss is the minimum repair level for the errors
found by DBCC CHECKTABLE (myDatabase.dbo.myTable).
A little background: we recently went through a server crash and lost
our transaction log, but we were able to rebuild the transaction log
and get the database back online. Also, the data files for this
database are over 420 GB in size. This is all SQL2000.
In the case of "myTable," it's only the table that's important, not
the data. So assuming I can recreate the table correctly, what is the
proper way to go about fixing this problem? I haven't tried dropping
myTable yet because this is a new situation for me and I don't want to
mess up.
Thanks as always,
EricIf I read it correctly you don't worry about the lost data. Instead you want
the table schema back?
do you have a source control where you can retrieve SQL scripts? if not, do
you have last known good backup to restore and get the schema retrieved?
"Eric Bragas" <ericbragas@.yahoo.com> wrote in message
news:4bf75b52-f331-4712-b80b-219798cee653@.m34g2000hsf.googlegroups.com...
> Hello,
> I must have a corrupt user table in my user database. If I try to get
> a count of rows, I get the following error:
> Could not open FCB for invalid file ID 18 in database
> And if I run "DBCC CHECKTABLE ('myTable')" in QA, I get this message:
> Server: Msg 7965, Level 16, State 2, Line 1
> Table error: Could not check object ID 220579874, index ID 0 due to
> invalid allocation (IAM) page(s).
> Server: Msg 8946, Level 16, State 1, Line 1
> Table error: Allocation page (1:23417559) has invalid IAM_PAGE page
> header values. Type is 1. Check type, object ID and page ID on the
> page.
> DBCC results for 'myTable'.
> There are 0 rows in 0 pages for object 'myTable'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'myTable'(object ID 220579874).
> repair_allow_data_loss is the minimum repair level for the errors
> found by DBCC CHECKTABLE (myDatabase.dbo.myTable).
> A little background: we recently went through a server crash and lost
> our transaction log, but we were able to rebuild the transaction log
> and get the database back online. Also, the data files for this
> database are over 420 GB in size. This is all SQL2000.
> In the case of "myTable," it's only the table that's important, not
> the data. So assuming I can recreate the table correctly, what is the
> proper way to go about fixing this problem? I haven't tried dropping
> myTable yet because this is a new situation for me and I don't want to
> mess up.
> Thanks as always,
> Eric|||Hi, Rick, and thanks. That's correct, I only need the table schema
back. No, there is no source control here on staging servers. Yes, I
have a backup, but I would need a new server just to restore a
database of that size. We simply don't have the space available.
Can you tell me (in theory and/or in actuality) what is the best way
to resolve this situation without a backup or source control? I don't
want to adjust all my scripts, etc. that use this table by switching
them to point to another table. I want to fix this correctly, but
don't know what is correct.|||How about renaming the table and then creating a new one with the old name?
"Eric Bragas" <ericbragas@.yahoo.com> wrote in message
news:4bf75b52-f331-4712-b80b-219798cee653@.m34g2000hsf.googlegroups.com...
> Hello,
> I must have a corrupt user table in my user database. If I try to get
> a count of rows, I get the following error:
> Could not open FCB for invalid file ID 18 in database
> And if I run "DBCC CHECKTABLE ('myTable')" in QA, I get this message:
> Server: Msg 7965, Level 16, State 2, Line 1
> Table error: Could not check object ID 220579874, index ID 0 due to
> invalid allocation (IAM) page(s).
> Server: Msg 8946, Level 16, State 1, Line 1
> Table error: Allocation page (1:23417559) has invalid IAM_PAGE page
> header values. Type is 1. Check type, object ID and page ID on the
> page.
> DBCC results for 'myTable'.
> There are 0 rows in 0 pages for object 'myTable'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'myTable'(object ID 220579874).
> repair_allow_data_loss is the minimum repair level for the errors
> found by DBCC CHECKTABLE (myDatabase.dbo.myTable).
> A little background: we recently went through a server crash and lost
> our transaction log, but we were able to rebuild the transaction log
> and get the database back online. Also, the data files for this
> database are over 420 GB in size. This is all SQL2000.
> In the case of "myTable," it's only the table that's important, not
> the data. So assuming I can recreate the table correctly, what is the
> proper way to go about fixing this problem? I haven't tried dropping
> myTable yet because this is a new situation for me and I don't want to
> mess up.
> Thanks as always,
> Eric|||Thanks, Aaron, that much worked. But now what do I do with the
"myTable_backup" table? Is there some way to drop it? If I try now,
I get the following error message:
Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 18 in database 'myDatabase'.
Connection Broken
Seems like there's still something wrong with my database that needs
to be fixed. Any ideas?|||If I were to play it safe, I would create a new database, transfer all the
*good* objects and data there, drop the old database and rename the new one.
(You might now want to consider investing in a backup/recovery plan.)
"Eric Bragas" <ericbragas@.yahoo.com> wrote in message
news:f4e3dfc4-d6d4-4e08-9de4-a0bd93856b74@.e25g2000prg.googlegroups.com...
> Thanks, Aaron, that much worked. But now what do I do with the
> "myTable_backup" table? Is there some way to drop it? If I try now,
> I get the following error message:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 18 in database 'myDatabase'.
> Connection Broken
>
> Seems like there's still something wrong with my database that needs
> to be fixed. Any ideas?|||Thanks, Aaron, I like that idea about moving the good objects to
another database. Seems like there must be a way to "fix" the
existing database, but perhaps not. I'm going to take your advice.
About the backup/recovery necessity, I have a hard time getting the
managers to recognize the possibility of a problem and dealing with it
before it happens. We have no space left on the HDD, very strange
database config's, and no written backup policy in place that I know
of, but we shove on like Stampeders. But hey, thanks for the advice!
Eric Bragas|||> About the backup/recovery necessity, I have a hard time getting the
> managers to recognize the possibility of a problem and dealing with it
> before it happens.
Do they know what you're spending your time on right now?
A

how best to replace a corrupt user table

Hello,
I must have a corrupt user table in my user database. If I try to get
a count of rows, I get the following error:
Could not open FCB for invalid file ID 18 in database
And if I run "DBCC CHECKTABLE ('myTable')" in QA, I get this message:
Server: Msg 7965, Level 16, State 2, Line 1
Table error: Could not check object ID 220579874, index ID 0 due to
invalid allocation (IAM) page(s).
Server: Msg 8946, Level 16, State 1, Line 1
Table error: Allocation page (1:23417559) has invalid IAM_PAGE page
header values. Type is 1. Check type, object ID and page ID on the
page.
DBCC results for 'myTable'.
There are 0 rows in 0 pages for object 'myTable'.
CHECKTABLE found 0 allocation errors and 2 consistency errors in table
'myTable'(object ID 220579874).
repair_allow_data_loss is the minimum repair level for the errors
found by DBCC CHECKTABLE (myDatabase.dbo.myTable).
A little background: we recently went through a server crash and lost
our transaction log, but we were able to rebuild the transaction log
and get the database back online. Also, the data files for this
database are over 420 GB in size. This is all SQL2000.
In the case of "myTable," it's only the table that's important, not
the data. So assuming I can recreate the table correctly, what is the
proper way to go about fixing this problem? I haven't tried dropping
myTable yet because this is a new situation for me and I don't want to
mess up.
Thanks as always,
Eric
If I read it correctly you don't worry about the lost data. Instead you want
the table schema back?
do you have a source control where you can retrieve SQL scripts? if not, do
you have last known good backup to restore and get the schema retrieved?
"Eric Bragas" <ericbragas@.yahoo.com> wrote in message
news:4bf75b52-f331-4712-b80b-219798cee653@.m34g2000hsf.googlegroups.com...
> Hello,
> I must have a corrupt user table in my user database. If I try to get
> a count of rows, I get the following error:
> Could not open FCB for invalid file ID 18 in database
> And if I run "DBCC CHECKTABLE ('myTable')" in QA, I get this message:
> Server: Msg 7965, Level 16, State 2, Line 1
> Table error: Could not check object ID 220579874, index ID 0 due to
> invalid allocation (IAM) page(s).
> Server: Msg 8946, Level 16, State 1, Line 1
> Table error: Allocation page (1:23417559) has invalid IAM_PAGE page
> header values. Type is 1. Check type, object ID and page ID on the
> page.
> DBCC results for 'myTable'.
> There are 0 rows in 0 pages for object 'myTable'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'myTable'(object ID 220579874).
> repair_allow_data_loss is the minimum repair level for the errors
> found by DBCC CHECKTABLE (myDatabase.dbo.myTable).
> A little background: we recently went through a server crash and lost
> our transaction log, but we were able to rebuild the transaction log
> and get the database back online. Also, the data files for this
> database are over 420 GB in size. This is all SQL2000.
> In the case of "myTable," it's only the table that's important, not
> the data. So assuming I can recreate the table correctly, what is the
> proper way to go about fixing this problem? I haven't tried dropping
> myTable yet because this is a new situation for me and I don't want to
> mess up.
> Thanks as always,
> Eric
|||Hi, Rick, and thanks. That's correct, I only need the table schema
back. No, there is no source control here on staging servers. Yes, I
have a backup, but I would need a new server just to restore a
database of that size. We simply don't have the space available.
Can you tell me (in theory and/or in actuality) what is the best way
to resolve this situation without a backup or source control? I don't
want to adjust all my scripts, etc. that use this table by switching
them to point to another table. I want to fix this correctly, but
don't know what is correct.
|||How about renaming the table and then creating a new one with the old name?
"Eric Bragas" <ericbragas@.yahoo.com> wrote in message
news:4bf75b52-f331-4712-b80b-219798cee653@.m34g2000hsf.googlegroups.com...
> Hello,
> I must have a corrupt user table in my user database. If I try to get
> a count of rows, I get the following error:
> Could not open FCB for invalid file ID 18 in database
> And if I run "DBCC CHECKTABLE ('myTable')" in QA, I get this message:
> Server: Msg 7965, Level 16, State 2, Line 1
> Table error: Could not check object ID 220579874, index ID 0 due to
> invalid allocation (IAM) page(s).
> Server: Msg 8946, Level 16, State 1, Line 1
> Table error: Allocation page (1:23417559) has invalid IAM_PAGE page
> header values. Type is 1. Check type, object ID and page ID on the
> page.
> DBCC results for 'myTable'.
> There are 0 rows in 0 pages for object 'myTable'.
> CHECKTABLE found 0 allocation errors and 2 consistency errors in table
> 'myTable'(object ID 220579874).
> repair_allow_data_loss is the minimum repair level for the errors
> found by DBCC CHECKTABLE (myDatabase.dbo.myTable).
> A little background: we recently went through a server crash and lost
> our transaction log, but we were able to rebuild the transaction log
> and get the database back online. Also, the data files for this
> database are over 420 GB in size. This is all SQL2000.
> In the case of "myTable," it's only the table that's important, not
> the data. So assuming I can recreate the table correctly, what is the
> proper way to go about fixing this problem? I haven't tried dropping
> myTable yet because this is a new situation for me and I don't want to
> mess up.
> Thanks as always,
> Eric
|||Thanks, Aaron, that much worked. But now what do I do with the
"myTable_backup" table? Is there some way to drop it? If I try now,
I get the following error message:
Server: Msg 5180, Level 22, State 1, Line 1
Could not open FCB for invalid file ID 18 in database 'myDatabase'.
Connection Broken
Seems like there's still something wrong with my database that needs
to be fixed. Any ideas?
|||If I were to play it safe, I would create a new database, transfer all the
*good* objects and data there, drop the old database and rename the new one.
(You might now want to consider investing in a backup/recovery plan.)
"Eric Bragas" <ericbragas@.yahoo.com> wrote in message
news:f4e3dfc4-d6d4-4e08-9de4-a0bd93856b74@.e25g2000prg.googlegroups.com...
> Thanks, Aaron, that much worked. But now what do I do with the
> "myTable_backup" table? Is there some way to drop it? If I try now,
> I get the following error message:
> Server: Msg 5180, Level 22, State 1, Line 1
> Could not open FCB for invalid file ID 18 in database 'myDatabase'.
> Connection Broken
>
> Seems like there's still something wrong with my database that needs
> to be fixed. Any ideas?
|||Thanks, Aaron, I like that idea about moving the good objects to
another database. Seems like there must be a way to "fix" the
existing database, but perhaps not. I'm going to take your advice.
About the backup/recovery necessity, I have a hard time getting the
managers to recognize the possibility of a problem and dealing with it
before it happens. We have no space left on the HDD, very strange
database config's, and no written backup policy in place that I know
of, but we shove on like Stampeders. But hey, thanks for the advice!
Eric Bragas
|||> About the backup/recovery necessity, I have a hard time getting the
> managers to recognize the possibility of a problem and dealing with it
> before it happens.
Do they know what you're spending your time on right now?
A