Question: Heyo folks,
Just looked on Google and around on this site, and there's very little information.
Given a query which is `"SELECT * FROM Table WHERE (various)"`, I'm putting in a BCP utility query like thus, called from C# (.net 3.5, but can be raised).

```
xp_cmdshell 'bcp "SELECT * FROM [DB].dbo.[table]  " queryout "\\networkpath\bla\file.csv" -N -S localhost -T -E -m0'

```

However, when I run this query in SQL Management Studio, it helpfully keeps supplying me with "1000 rows were copied." messages. I want to get those messages in my C# application.

I've tried several C# methods, including SqlDependency, SqlConnection.InfoMessage, BeginExecuteNonQuery(), etc; hence the lack of C# code.

What would be the best method to do this?

Cheers

Answer: 
See [Five Simple Database Design Errors You Should Avoid](https://www.simple-talk.com/sql/database-administration/five-simple--database-design-errors-you-should-avoid/).

What you're trying to do is their point #1. 

It *sounds* like a great idea - at first. Having just one table instead of many ... 

But it's really not, precisely because it defeats the main purpose of a lookup tables - being able to **enforce referential integrity**. 

Don't do this - use a separate lookup table for each category and use **proper referential integrity**! 

The only way you could make this work would be to have a `INT IDENTITY` on this "global" lookup table - but that ID would be "global" across all categories. Then you could reference this global lookup table based just on that one ID. The downside is that your categories won't all have nice consecutive numbers - it'll be all over the place.
So, you have a "one big lookup table"? 

To create a FK (and there is no other declarative way to restrict values based on lookup table, otherwise you can use triggers) you have to add another column to the `main_table` ie the `lookup_category` column, and draw the reference using both columns to the primary key in the lookup table.