Temp Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Temp Tables

Author

Topic

kalyur
Starting Member

7 Posts

Posted - 2006-11-15 : 13:57:30

I have been working on this problem for quite some time. Now I need URGENT help.

I have two temp tables. #temp1 and #temp2.
#temp1 has these fields - ID, Name, Desc
#temp2 has these fields - DateModified, Status

Both these tables have equal number of rows.

What I want to create is another temp table holding all these values. ie ID, Name, Desc, DateModified, Status

Is this possible to do?

ANY help is deeply appreciated!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 14:03:50

How can you tell which records/row in #temp2 belongs to which records/rows in #temp1?

Peter Larsson
Helsingborg, Sweden

kalyur
Starting Member

7 Posts

Posted - 2006-11-15 : 14:07:07

What I'm looking for is to "merge" these two tables. Something like
#temp1 + #temp2 . I just need to have sort of concat these two tables. I mentioned that both tables have equal number of rows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 14:09:29

So any row in #temp2 can be matched with any row in #temp1?

Or you can use CROSS JOIN so that any row in #temp2 get matched with ALL rows in #temp1!

select id, name, desc, datemodified, status
from #temp1
cross join #temp2

Peter Larsson
Helsingborg, Sweden

kalyur
Starting Member

7 Posts

Posted - 2006-11-15 : 14:18:38

I tried that solution. Maybe I typed in a flurry and didnt make myself clear.

If I have these rows in #temp1

ID Name
1 XXX

2 YYY

And these rows in #temp2

Status
0

1

What I would like is a #temp3 with these values

ID Name Status
1 XXX 0
2 YYY 1

Is this possible at all?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 14:20:53

Why not

ID Name Status
1 XXX 10
2 YYY 01

instead?

Peter Larsson
Helsingborg, Sweden

kalyur
Starting Member

7 Posts

Posted - 2006-11-15 : 14:23:35

I already know that these values must match the exact row. So I just am trying to stick these two tables together.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 14:27:09

How do you know that Status 0 match Id 1 with name XXX?
Why can't Status 0 match Id 2 with name YYY?

It is possible to do this BUT YOU HAVE TO TELL US THE BUSINESS LOGIC BEHIND YOUR REQUEST!

Peter Larsson
Helsingborg, Sweden

kalyur
Starting Member

7 Posts

Posted - 2006-11-15 : 14:32:47

What I do is retrieve a set of records from a table matching some criteria and store it in #temp1 . Suppose that has 20 rows. This table has a set of old id's which I need to disregard and create a new set of id's for each record in this table. I store these new id's a #temp2. So eventually I have tow tables one with data with old values and one with just new ids. I require to create a table taking in values from #temp1 and sort of "attaching" the new id's column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-15 : 14:46:36

I understand, but since you split the information, how do you keep together the information?
It would be much better if #temp1 was

create table #temp1 (rowid int identity(1, 1), oldid int, name varchar(20))

now insert into #temp1 (oldid, name) and the new id is represented by rowid.

Peter Larsson
Helsingborg, Sweden

kalyur
Starting Member

7 Posts

Posted - 2006-11-15 : 14:53:45

That worked!

Thank you so much. I need a break now

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-11-15 : 15:53:08

Just FYI, that is a horrible table design. You should always have atleast 1 key field that you can link to. Since you are using a identity on a temp table, you will consitantly be using the same ID everytime you re-create the table causing many potential issues down the road. If the intention is not a ID, but rather just a row number, then Peso's post will work . If you do trully want a ID that will be used for reference, create a key based of something (i.e date combined with oldID, another unique field, etc).

Kristen
Test

22859 Posts

Posted - 2006-11-15 : 16:12:46

I expect you've "got it" now, kalyur, but the important point is that there is no inherent order to data in the database. If you have an Identity column then an ascending number will be allocated as each row as it is inserted - and THEN you can order by that Identity value, and bring some Order to the data - i.e. the order-the-records-were-added. But without some sort of ID there is no implied order.

Unfortunately there is Order 99.lots-of-9s% of the time, and this leads you into a false sense of security.

INSERT INTO MyTable
SELECT *
FROM MyOtherTable
ORDER BY CreatedDate

So ... a bunch of records are inserted into MyTable ordered by CreateDate - which to all intents and purposes is a useful "human order".

If I then do:

SELECT *
FROM MyTable

I will (99.lots-of-9s% of the time) get them in CreateDate order. Note that I did NOT enforce the sequence using an ORDER BY.

Sadly for the other 0.lots-of-0s% of the time things go badly, AND the circumstances are VERY difficult to reproduce, and thus this introduces a very subtle, hard-to-find, bug)

Lets assume we have a VERY busy server. Its trashing itself to death because there are way more users and queries than there is available RAM to process them. So the data in my query is only held in memory for a split second before someone else's data overrides it/

In fact SQL Server is getting totally Tee-d Off that it has to work so hard ....

I'm just about to do my

SELECT *
FROM MyTable

query when my mate, precisely 1-millisecond before, does:

SELECT *
FROM MyTable
WHERE name = 'Kristen'

and further more the [name] column of MyTable is indexed.

SQL Server breathes a sigh of relief, it uses the Index to grab the 10 rows which match "Kristen", without having to pull the whole table, and uses that Index Info to key into MyTable without having to read the whole table.

Then 1 millisecond later it sees my

SELECT *
FROM MyTable

query. Oh Bother! says SQL Server, "more work". Then SQL Server checks its Query Optimisation and realises that it still has 10 rows in memory from my mate's query. "Hooray" say SQL Server, "somewhat less work to do". So SQL Server uses the 10 rows already in memory, and fishes the rest of the table off the disk.

And guess what? those 10 rows come up first in my query, so now:

SELECT *
FROM MyTable

is NO LONGER giving my the records in CreateDate order, I have the first 10 rows for "Kristen" and then the remainder of the query.

So Order in databases is undefined until you provide an ORDER BY clause. And when you want to JOIN two tables together there has to be some commonality between the tables, otherwise every row in TableA gets joined to EVERY row in TableB (yes, there would be LOT of rows in the ResultSet) [This is known as a Cartesian Join]

Hope that helps a bit!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources