Adding rows to all tables

Question

Post reply

Adding rows to all tables

andrew.banwell

SSC Rookie

Points: 38
More actions
April 8, 2008 at 8:37 am

#185118

Sorry but I am a SQL virgin, I want to add a new row (GroupMembership) to all tables in my database. Is there an easy way to do this?
Also the table names all reflect the name of the group membership that needs to populate each of these new rows that I want to create. Any easy way to accomplish that?
Kind Regards
Bigandy :crazy:

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 1

That just doesn't make sense. You want to add new records, of the same information, to each table?

That sounds like your database needs to be redesigned. You should never have duplicate data like you are describing.

Andrew SQLDBA

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 2

I agree with Andrew that this doesn't make sense. Can you better explain, or give an example of what you mean? Maybe show a few tables.

Also, are you sure you mean row and not column?

andrew.banwell SSC Rookie Points: 38 More actions · Answer 3

The database is an import of part of our active directory, so I didn't design it and all I want to do is when required produce a report of usernames,real name by group membership, but I am not a programmer nor do I wish to become one.

I am enjoying learning about SQL server though 😀

Regards

Bigandy

PS I probably mean Colum :blush:

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

I am sorry, but are you trying to retrieve the data or insert the data ? If you are trying to generate a report for Username realname by groupmembership you just need to write a SELECT Query from respective tables using group by groupmembership.

Sorry if I am missing anything.

Prasad Bhogadi
www.inforaise.com

andrew.banwell SSC Rookie Points: 38 More actions · Answer 5

As an example All tables have the colums 'UserName' 'LastName' 'FirstName'

I want to add 'GoupMembership' as a colum to allow me to produce reports by groupmembership.

I would like to add that this is just for me to learn at the moment and the so I have restricted the data just to simple things.

Eventually I would like to be able to produce quite complex reports from the active directory via SQL server.

Regards

BigAndy

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 6

That would mean that you as an AD User, can only be a member of one group. You need a table that lists all the Group Names, and then an associative table that lists all the UserIDs and GroupIDs. That way, you can be in more than one AD group. Every User will be in that table, and pretty much every GroupID will be in there as well.

You would never, ever add the same column to every table in your database, that is just totally whacked. You talk about a nightmare to try and keep up.

Think about that, for one, you could have only one GroupMembership, and then for another, if you wanted to add another groupmembership, you would have to go back and add that column again to each and every table. That is totally insane.

I am a member of close to 300 Groups in our company, how are you going to handle something like that? Are you going to add 300 columns to every table? What about the users that are in only say, 5 or 6 groups. There will be 295 empty columns in their record, in each table.

Andrew

SQLDBA

The Wisenut Right there with Babe Points: 756 More actions · Answer 7

Not sure what you are trying to achieve. In my db i have Audit columns on all tables to determine who created the data and who modified it along with the timestamps. Now if all you want is to add a same column "GroupMembership" in all the tables in your database, this is a way to do it

sp_msforeachtable "ALTER TABLE ? ADD GroupMembership VARCHAR(20) NULL "

The above command will alter all the tables in your database to add a new column with the name GroupMembership with the datatype VARCHAR(20) which will accept NULL values.

Not sure whether this is what you intend.

In case you want to remove it, issue this command

sp_msforeachtable "ALTER TABLE ? DROP COLUMN GroupMembership"

Thanks

Vinoj

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 8

Andrew,

If you can describe what you want to achieve, not necessarily what you think you ought to do, we can perhaps give you better advice.

As a start, in this database, how many tables are there? You should be able to use Management Studio to connect to the server, expand the database, then the Tables folder.

andrew.banwell SSC Rookie Points: 38 More actions · Answer 9

In an ideal world I would like to be able to export our Active directory to SQL server and then produce reports as required by different departments as needed.

I.E. I have been asked if I could get a list of all users in the finance OU's of the company and present this in a report in group order with Logon names, First names, Last names and Group Title.

I know I can already do this using a VB script run manually on each group but would like to use SQL server to automate the process and possibly improve my knowledge of databases.

At present I have run CSVDE on a domain controller to produce a csv file of the active directory and then imported that into SQL server 2005 (not with total success I must admit).

I do realise that the majority of users on the forum are serious DBA's and Developers so if you could fit your answers in around more important posts I would appritiate it.:D

Regards

Bigandy

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 10

That does, and as along as a person can be in only one OU, you're ok. Not sure how much you want to do with this, meaning do you want to put all OUs in SQL Server (for reporting) and handle nesting? Those queries get complicated.

If you are adding a primary OU and everyone is only in one, then you'd want to add this to the user table only, not all tables. The way you proposed by adding a "GroupMembership" column to this table is ok, and you can use SSMS (MAnagement Studio), pick the table, right click, design, add it.

The thing you should consider is that AD tends to change frequently and modeling all the relationships in SQL Server means you're essentially redesigning a new copy of the AD database. Not sure I'd do that, especially as new versions of Windows might really mess you up.

If you can do this in VBScript, I'd actually do it there. You have lots of flexibility to loop and create interesting joins. You could always make a 1 or 2 column table, one character, one date or numerical), and insert your report rows in there if you want to report using something like Reporting Services or a web page. That way you could "store" your report.