how to piece multiple tables into one table

Question

Post reply

how to piece multiple tables into one table

zishanz

SSCommitted

Points: 1852
More actions
April 26, 2002 at 5:09 pm

#78393

Hi, I have 4 tables and I would like to piece all the 4 tables into one table. How should I do it in an efficient way?
My four tables are:
table_1(user_id numeric(8,0), text1 varchar(8000) )
table_2(user_id numeric(8,0), text2 varchar(8000) )
table_3(user_id numeric(8,0), text3 varchar(8000))
table_4(user_id numeric(8,0), text4 varchar(8000))
the user_id in all the 4 tables are the same.
I would like to piece these 4 tables into one table as
table_bigone(user_id numeric, content_concat text )
the column for content_concat should be:
text1 + text2 + text3 + text4
The major reason I need to do this is:
I am suffering from MS sqlserver's varchar type 8000 limitation, and '+' operator can't be used for text type.
Is there any easy or efficient way to do the piece-together work instead of looping through each table each record for a concat,
and also, when I concat, I need a variable to hold it, but text type can't be declared as a variable.
Thanks in advance.
Abby

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

paras_98 SSCrazy Points: 2994 More actions · Answer 1

Try this...

insert into table_bigone(userid, text_con)

select

table1.userid,

table1.text1 + table2.text2 + table3.text3 + table4.text4

from

table1, table2, table3, table4

where

table1.user_id = table2.user_id and

table1.user_id = table3.user_id and

table1.user_id = table4.user_id

Paras Shah

Evision Technologies

Mumbai, India

Paras Shah
Evision Technologies
Mumbai, India

zishanz SSCommitted Points: 1852 More actions · Answer 2

Great! That really works for me!

Thank you very much.

By the way, if table_1, table_2, table_3 and table_4 are temp tables, what's their scope?

In other words, if In my sp, I do this:

create procedcure sp_0

as

begin

create #table1

create #table2

create #table3

create #table4

-- then call sp1

-- then call sp2

-- then call sp3

-- then call sp4

Would temp #table1 be avilable to sp1

and #table2 be available to sp2 ...

Thanks again for your help!

Abby

paras_98 SSCrazy Points: 2994 More actions · Answer 3

This should work. Not too sure as I haven't tried it. But according to my knowledge, temp tables are available till they are droppped.

quote:
Great! That really works for me!
Thank you very much.
By the way, if table_1, table_2, table_3 and table_4 are temp tables, what's their scope?
In other words, if In my sp, I do this:
create procedcure sp_0
as
begin
create #table1
create #table2
create #table3
create #table4
-- then call sp1
-- then call sp2
-- then call sp3
-- then call sp4
Would temp #table1 be avilable to sp1
and #table2 be available to sp2 ...
Thanks again for your help!
Abby

Paras Shah

Evision Technologies

Mumbai, India

Paras Shah
Evision Technologies
Mumbai, India

Leon Platt SSCommitted Points: 1921 More actions · Answer 4

Taken from TSQL Help file:

Temporary Tables

You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

SQL statements reference the temporary table using the value specified for table_name in the CREATE TABLE statement:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)

INSERT INTO #MyTempTable VALUES (1)

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE:

A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.

All other local temporary tables are dropped automatically at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Procedures below where created in the chain of execution can see it directly. Thos above where it is created cannot. So where you create in the first and call the second the second can see. But if you create in the second and called in the first by table name it would not. Only the returning dataset is seen.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)