Looping through tables

Hi, I'm really new to SQL server so I need some assistance.
I have an SQL server database where there are some tables that are
named like this:
XXCCYY
where XX is the table name, CC is a two digit company code to identify
the correct company (eg 01 or 12) and YY are the two last numbers in
the year (01 for 2001 etc).
I would like to create a single table (or a view) in which I bring in
data from all tables that have a certain name (the XX part) and add a
column with the company code to all records.
How can I do this?Well, start with
CREATE TABLE #TableNames (name AS SYSNAME)
GO
INSERT INTO TableNames
SELECT t1.name
FROM sysobjects t1
INNER JOIN sysindexes t2
ON t1.id = t2.id
WHERE t2.indid <= 1
AND t2.rows > 0
AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
AND t1.name LIKE 'XX%'
GO
Now , loop throu the names and build a dynamix sql to insert the data , make
sure that all tables have the same structure
<joakim.andersson@.permobil.se> wrote in message
news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...
> Hi, I'm really new to SQL server so I need some assistance.
> I have an SQL server database where there are some tables that are
> named like this:
> XXCCYY
> where XX is the table name, CC is a two digit company code to identify
> the correct company (eg 01 or 12) and YY are the two last numbers in
> the year (01 for 2001 etc).
> I would like to create a single table (or a view) in which I bring in
> data from all tables that have a certain name (the XX part) and add a
> column with the company code to all records.
> How can I do this?
>|||if want use view
create view viewname as
select "C1",* from XXCCYY
union all
select "C2",* from X1CCYY
union all
select "C3",* from X1CCYY
where c1,c2,c3 is company code
--soonyu
"joakim.andersson@.permobil.se" wrote:
> Hi, I'm really new to SQL server so I need some assistance.
> I have an SQL server database where there are some tables that are
> named like this:
> XXCCYY
> where XX is the table name, CC is a two digit company code to identify
> the correct company (eg 01 or 12) and YY are the two last numbers in
> the year (01 for 2001 etc).
> I would like to create a single table (or a view) in which I bring in
> data from all tables that have a certain name (the XX part) and add a
> column with the company code to all records.
> How can I do this?
>|||Hi Uri,
That was really helpful and I've done the create table part and it
worked like a charm. BUT I'm not good at all at the programming part so
could you please give me a hint at how to make that loop as well. The
SQL part I can handle - just not the programming.
Uri Dimant skrev:
> Well, start with
> CREATE TABLE #TableNames (name AS SYSNAME)
> GO
> INSERT INTO TableNames
> SELECT t1.name
> FROM sysobjects t1
> INNER JOIN sysindexes t2
> ON t1.id = t2.id
> WHERE t2.indid <= 1
> AND t2.rows > 0
> AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
> AND t1.name LIKE 'XX%'
> GO
> Now , loop throu the names and build a dynamix sql to insert the data , make
> sure that all tables have the same structure
>
>
> <joakim.andersson@.permobil.se> wrote in message
> news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...
> > Hi, I'm really new to SQL server so I need some assistance.
> >
> > I have an SQL server database where there are some tables that are
> > named like this:
> > XXCCYY
> > where XX is the table name, CC is a two digit company code to identify
> > the correct company (eg 01 or 12) and YY are the two last numbers in
> > the year (01 for 2001 etc).
> >
> > I would like to create a single table (or a view) in which I bring in
> > data from all tables that have a certain name (the XX part) and add a
> > column with the company code to all records.
> >
> > How can I do this?
> >|||Jaokim
Lookup CURSORS in the BOL, it has great examples there
"Joakim" <joakim.andersson@.permobil.se> wrote in message
news:1150888778.837850.12330@.c74g2000cwc.googlegroups.com...
> Hi Uri,
> That was really helpful and I've done the create table part and it
> worked like a charm. BUT I'm not good at all at the programming part so
> could you please give me a hint at how to make that loop as well. The
> SQL part I can handle - just not the programming.
>
> Uri Dimant skrev:
>> Well, start with
>> CREATE TABLE #TableNames (name AS SYSNAME)
>> GO
>> INSERT INTO TableNames
>> SELECT t1.name
>> FROM sysobjects t1
>> INNER JOIN sysindexes t2
>> ON t1.id = t2.id
>> WHERE t2.indid <= 1
>> AND t2.rows > 0
>> AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
>> AND t1.name LIKE 'XX%'
>> GO
>> Now , loop throu the names and build a dynamix sql to insert the data ,
>> make
>> sure that all tables have the same structure
>>
>>
>> <joakim.andersson@.permobil.se> wrote in message
>> news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...
>> > Hi, I'm really new to SQL server so I need some assistance.
>> >
>> > I have an SQL server database where there are some tables that are
>> > named like this:
>> > XXCCYY
>> > where XX is the table name, CC is a two digit company code to identify
>> > the correct company (eg 01 or 12) and YY are the two last numbers in
>> > the year (01 for 2001 etc).
>> >
>> > I would like to create a single table (or a view) in which I bring in
>> > data from all tables that have a certain name (the XX part) and add a
>> > column with the company code to all records.
>> >
>> > How can I do this?
>> >
>|||Thank you very much for the help. It took me a while to get it right
but now it's working fine.
Brgds Joakim
Uri Dimant skrev:
> Jaokim
> Lookup CURSORS in the BOL, it has great examples there
>
> "Joakim" <joakim.andersson@.permobil.se> wrote in message
> news:1150888778.837850.12330@.c74g2000cwc.googlegroups.com...
> > Hi Uri,
> >
> > That was really helpful and I've done the create table part and it
> > worked like a charm. BUT I'm not good at all at the programming part so
> > could you please give me a hint at how to make that loop as well. The
> > SQL part I can handle - just not the programming.
> >
> >
> > Uri Dimant skrev:
> >
> >> Well, start with
> >>
> >> CREATE TABLE #TableNames (name AS SYSNAME)
> >> GO
> >> INSERT INTO TableNames
> >> SELECT t1.name
> >> FROM sysobjects t1
> >> INNER JOIN sysindexes t2
> >> ON t1.id = t2.id
> >> WHERE t2.indid <= 1
> >> AND t2.rows > 0
> >> AND OBJECTPROPERTY(t1.id,'IsUserTable') = 1
> >> AND t1.name LIKE 'XX%'
> >>
> >> GO
> >>
> >> Now , loop throu the names and build a dynamix sql to insert the data ,
> >> make
> >> sure that all tables have the same structure
> >>
> >>
> >>
> >>
> >>
> >> <joakim.andersson@.permobil.se> wrote in message
> >> news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...
> >> > Hi, I'm really new to SQL server so I need some assistance.
> >> >
> >> > I have an SQL server database where there are some tables that are
> >> > named like this:
> >> > XXCCYY
> >> > where XX is the table name, CC is a two digit company code to identify
> >> > the correct company (eg 01 or 12) and YY are the two last numbers in
> >> > the year (01 for 2001 etc).
> >> >
> >> > I would like to create a single table (or a view) in which I bring in
> >> > data from all tables that have a certain name (the XX part) and add a
> >> > column with the company code to all records.
> >> >
> >> > How can I do this?
> >> >
> >