Hello,I am new to SQL. I have a VB and C background. I have 3000 users in a database. In this database I have a table (dbo.Users) in which each user record has columns for various user information that gets entered manually from a UI. One of these columns (GroupID) indicates whether or not a user belongs to a specific user group (designated 1, 2...). A user can belong to more than one user group. In this application I have about 40 users who presently belong to group 1. I need to add records for all of the other users to put them in group 2. I need to automate the process rather than manually enter 3000 new records. A user may have one or more records, with a different number in the GroupID column. I need to scan through dbo.User, and write a new record to the table in question with the new group # in the GroupID column for each user. I know how to do this in VB but I'm just learning T-SQL. Any suggestions on how to proceed? Thanks in advance!!

You said "add" records to the table. Did you really want to ADD or did you want to UPDATE the existing records which have no GroupID? The rest of this post assumes you want to update.

If a user has two records, both of which are currently NULL (i.e., not defined) do you want both of those records to be updated to have GroupId = 2? If that is the case, the update statement can be as simple as this:

UPDATE YourTable SET
GroupId = 2
WHERE
GroupId IS NULL;

Before you run this run a select statement

SELECT * FROM YourTable WHERE GroupId IS NULL;

That will tell you how many rows are going to be updated and which. If you are satisfied with that, then only run the update statement.

The first record shows a user who currently belongs to Group 1. The second record is a user (one of the 3000) that doesn't belong to a Group. The column displays a '-1'. The third record is what I would expect to see if I manually entered user (3030) into Group 2 as well as being in Group 1. Note that this table 'UserGroupFunctions' only contains the users that have been added to the table, i.e., this is the table that establishes what groupID the user now belongs to. One of my stumbling blocks is the list of users are in another table 'Users'. Thus UserGroupFunctions will grow from a couple records (rows) to 3000 or so records. Could I read the list from the first table, use this list to fill the CredentialID column, enter the Group # in the last column, and then go to the next user oon the list and repeat?

GroupID is manually associated with the user when the user is first entered into the system. Thereafter, any new group associations are also done manually except where in this case I have about 3000 users to add to a group all at one time.

I have a question about IDENTITY. In this app we've been discussing, a credentialID is automatically generated for each user via the IDENTITY statement. I understand the statement requires a seed and increment number. If I have a table (whose first column is credentialID - set up as: [CredentialID] [int] IDENTITY (1,1) NOT NULL) if I am creating the table for the first time I assume credentialID starts at 1. When I have a pre-existing column of credentialIDs, and I add to this table, does SQL look at the last credentialID entry and increment it by 1 for the next record? That seems to be what is happening. My concern is when I add the new records I want to make sure the proper credentialID number is generated.Thanks again

I have a question about IDENTITY. In this app we've been discussing, a credentialID is automatically generated for each user via the IDENTITY statement. I understand the statement requires a seed and increment number. If I have a table (whose first column is credentialID - set up as: [CredentialID] [int] IDENTITY (1,1) NOT NULL) if I am creating the table for the first time I assume credentialID starts at 1. When I have a pre-existing column of credentialIDs, and I add to this table, does SQL look at the last credentialID entry and increment it by 1 for the next record? That seems to be what is happening. My concern is when I add the new records I want to make sure the proper credentialID number is generated.Thanks again

yep..if you already have data then next inserted record will get next id value as per the increment value from last generated value

One more question - if I copy the desired column from the source table (list of 3000 users), and then use it as one of the columns in the destination table (the other columns will be hard-coded with info that is the same in each row), will Sql Server automatically index to the next row based on the rows of data in the source column? Here is what I'm trying do:

One more question - if I copy the desired column from the source table (list of 3000 users), and then use it as one of the columns in the destination table (the other columns will be hard-coded with info that is the same in each row), will Sql Server automatically index to the next row based on the rows of data in the source column? Here is what I'm trying do:

I'm having trouble getting the following code to work. Still workingon the task you helped me with. I get 'Invalid column name 'CredentialID' when I try to execute the code. If I hard code CredentialID is will write one row. I need it to write a new row in table2 for each value in the CredentialID column in table1. I can hard-code the last 3 columns but I need the info in the CredentialID column to match what is in table1: