[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

variable column count

NH

3/21/2007 2:04:00 PM

I have a load of tables named RESULT1,RESULT2,RESULT3, etc.

What I want to do is automatically load the contents of all of these tables
into one large table.

The problem is that the number of columns varies dramatically..

For example; RESULT1 may have 5 columns, RESULT2 may have 3 columns, and
RESULT3 may have 4 columns.. (all of which are integers)

I have created a table called RESULTS with 4 columns

When I try something along the lines of;

INSERT INTO RESULTS
SELECT * FROM RESULTS1

I get an error saying that the number of columns don't match..

I thought that if the column names matched, then a mismatched number of
columns didn't matter, but I was obviously wrong...

Is there a way round this that doesn't involve specifying each column? I
don't want to do this because the number of columns is constantly varying,
and can be as many as 255!

Thanks

NH
4 Answers

NH

3/21/2007 2:08:00 PM

0

********EDIT**********

Sorry, I made a mistake in the first post..

It should have read:

I have a load of tables named RESULT1,RESULT2,RESULT3, etc.

What I want to do is automatically load the contents of all of these tables
into one large table.

The problem is that the number of columns varies dramatically..

For example; RESULT1 may have 5 columns, RESULT2 may have 3 columns, and
RESULT3 may have 4 columns.. (all of which are integers)

I have created a table called RESULTS with 7 columns

When I try something along the lines of;

INSERT INTO RESULTS
SELECT * FROM RESULTS1

I get an error saying that the number of columns don't match..

I thought that if the column names matched, then a mismatched number of
columns didn't matter, but I was obviously wrong...

Is there a way round this that doesn't involve specifying each column? I
don't want to do this because the number of columns is constantly varying,
and can be as many as 255!

Thanks

NH

xyb

3/21/2007 2:13:00 PM

0

On 3?21?, ??10?04?, NH <N...@discussions.microsoft.com> wrote:
> I have a load of tables named RESULT1,RESULT2,RESULT3, etc.
>
> What I want to do is automatically load the contents of all of these tables
> into one large table.
>
> The problem is that the number of columns varies dramatically..
>
> For example; RESULT1 may have 5 columns, RESULT2 may have 3 columns, and
> RESULT3 may have 4 columns.. (all of which are integers)
>
> I have created a table called RESULTS with 4 columns
>
> When I try something along the lines of;
>
> INSERT INTO RESULTS
> SELECT * FROM RESULTS1
>
> I get an error saying that the number of columns don't match..
>
> I thought that if the column names matched, then a mismatched number of
> columns didn't matter, but I was obviously wrong...
>
> Is there a way round this that doesn't involve specifying each column? I
> don't want to do this because the number of columns is constantly varying,
> and can be as many as 255!
>
> Thanks
>
> NH

first,how can you load all the data that some of them have the columns
over 4 into a large table.
second,if this make te sence,use dynamic sql with reference to sys
objects table,you can google from pubs :)

NH

3/21/2007 4:26:00 PM

0

Thanks, I am going to go down the dynamic SQL route..

"xyb" wrote:

> On 3æ??21æ?¥, ä¸?å?10æ?¶04å??, NH <N...@discussions.microsoft.com> wrote:
> > I have a load of tables named RESULT1,RESULT2,RESULT3, etc.
> >
> > What I want to do is automatically load the contents of all of these tables
> > into one large table.
> >
> > The problem is that the number of columns varies dramatically..
> >
> > For example; RESULT1 may have 5 columns, RESULT2 may have 3 columns, and
> > RESULT3 may have 4 columns.. (all of which are integers)
> >
> > I have created a table called RESULTS with 4 columns
> >
> > When I try something along the lines of;
> >
> > INSERT INTO RESULTS
> > SELECT * FROM RESULTS1
> >
> > I get an error saying that the number of columns don't match..
> >
> > I thought that if the column names matched, then a mismatched number of
> > columns didn't matter, but I was obviously wrong...
> >
> > Is there a way round this that doesn't involve specifying each column? I
> > don't want to do this because the number of columns is constantly varying,
> > and can be as many as 255!
> >
> > Thanks
> >
> > NH
>
> first,how can you load all the data that some of them have the columns
> over 4 into a large table.
> second,if this make te sence,use dynamic sql with reference to sys
> objects table,you can google from pubs :)
>
>

bob

3/22/2007 12:22:00 PM

0

No! Stop! How about using VIEWs?

Create VIEWs on your tables with the right numbers of columns:

INSERT INTO viw_three_columns
SELECT * FROM results3

INSERT INTO viw_four_columns
SELECT * FROM results4

Let me know how you get on

wBob

"NH" wrote:

> Thanks, I am going to go down the dynamic SQL route..
>
> "xyb" wrote:
>
> > On 3æ??21æ?¥, ä¸?å?10æ?¶04å??, NH <N...@discussions.microsoft.com> wrote:
> > > I have a load of tables named RESULT1,RESULT2,RESULT3, etc.
> > >
> > > What I want to do is automatically load the contents of all of these tables
> > > into one large table.
> > >
> > > The problem is that the number of columns varies dramatically..
> > >
> > > For example; RESULT1 may have 5 columns, RESULT2 may have 3 columns, and
> > > RESULT3 may have 4 columns.. (all of which are integers)
> > >
> > > I have created a table called RESULTS with 4 columns
> > >
> > > When I try something along the lines of;
> > >
> > > INSERT INTO RESULTS
> > > SELECT * FROM RESULTS1
> > >
> > > I get an error saying that the number of columns don't match..
> > >
> > > I thought that if the column names matched, then a mismatched number of
> > > columns didn't matter, but I was obviously wrong...
> > >
> > > Is there a way round this that doesn't involve specifying each column? I
> > > don't want to do this because the number of columns is constantly varying,
> > > and can be as many as 255!
> > >
> > > Thanks
> > >
> > > NH
> >
> > first,how can you load all the data that some of them have the columns
> > over 4 into a large table.
> > second,if this make te sence,use dynamic sql with reference to sys
> > objects table,you can google from pubs :)
> >
> >