Fairly New User - 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 2005 Forums

Transact-SQL (2005)

Fairly New User

Author

Topic

scooternm
Starting Member

8 Posts

Posted - 2008-07-01 : 12:14:51

Hello All!
I'm using the integration services to import a bunch of flat files into SQL Server 2005. I got that working fine.Here is what I'm wondering how to do, if even possible:

Every table's first column shares the same name, say FirstColumn. Is there an Update query or function or something that will update every row in each table with the name of the table?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-07-01 : 12:44:29

Why would you want to do something like this?

=======================================
Some people walk in the rain, others just get wet. -Roger Miller, musician (1936-1992)

scooternm
Starting Member

8 Posts

Posted - 2008-07-01 : 12:57:25

Good Question! Maybe I don't. I'm not sure. I'll explain...but, note I am importing from txt files so my tables are really perfect, but they will work.

I want to eventually generate a report based on pretty much the fist three columns of each table.

The first column in each table currently contains a "Y" (this is the value I wanted to replace with the name of the table.

The second column in each table will contain either a 'W' or an 'E'.

The third column will contain a three digit number (ie. 502, 510)

What I want in the end is a report that lists:
-the name of the table
-the number of unique values between the second and third columns

For example:
If my database has two table, Log1 and Log2.

Log1 contains:
Y W 502
Y W 502
Y E 502
Y E 510

Log2 contains:
Y W 514
Y E 540
Y E 540
Y W 520

My output would be:
LOG1 W502 2
LOG1 E502 1
LOG1 E510 1
LOG2 W514 1
LOG2 W520 1
LOG2 E540 2

I guess I was thinking I could make the first column the name of the log and then combine all the tables and run a query to count the unique values. Any suggestions are greatly appreciated.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-07-01 : 18:41:43

How would you generate this report if the name of the table was in the first column? I assume it would involve some type of UNION ALL operator. If so, just introduce the name of the table as a constant along with the rest of the table data.

=======================================
Some people walk in the rain, others just get wet. -Roger Miller, musician (1936-1992)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources