Looping through tables

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:
[vbcol=seagreen]
> 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 , ma
ke
> sure that all tables have the same structure
>
>
> <joakim.andersson@.permobil.se> wrote in message
> news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...|||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:
>
>|||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:
[vbcol=seagreen]
> 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 , ma
ke
> sure that all tables have the same structure
>
>
> <joakim.andersson@.permobil.se> wrote in message
> news:1150879573.633142.259930@.g10g2000cwb.googlegroups.com...|||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:
>
>|||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:
[vbcol=seagreen]
> 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...sql