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 2012 Forums
 Transact-SQL (2012)
 Adding records to table

Author  Topic 

samyer
Starting Member

15 Posts

Posted - 2013-02-05 : 13:36:32
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!!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 14:23:12
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.
Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-05 : 15:08:55
I think your suggestion might work. Here is the info:
table: dbo.UserGroupFunctions
example record:

FunctionID CredentialID Function Section GroupID
3138 3030 1 10 1
3150 3031 1 10 -1
3138 3030 1 10 2

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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 01:03:39
you can. you just need to use INSERT...SELECT syntax to insert users from other table.

Is GroupID values generated on the fly or is there a master table for them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-06 : 08:24:12
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 appreciate your help!!! Thanks!!
Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-06 : 08:26:16
@JamesK

THANKS
Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-06 : 08:45:08
visakh16,

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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 11:13:54
quote:
Originally posted by samyer

visakh16,

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-06 : 14:08:15
thanks visakh16

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:

use MainDB;
go

select column1
from table1
insert into table2 ([Field1] [int] IDENTITY (1,1), Column1, "1","14","10"

-- where 1 & 14 are always the same for each record, and '10' is the new UserGroup number that the 3000 will have.

Question - when it writes to the desitnation table will it automatically create the next subsequent record with the next corresponding column1 number?

Thanks in advance!
Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-06 : 14:09:19
A note on the previous post - I'm sure my syntax is incorrect
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-06 : 22:49:40
quote:
Originally posted by samyer

thanks visakh16

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:

use MainDB;
go

select column1
from table1
insert into table2 ([Field1] [int] IDENTITY (1,1), Column1, "1","14","10"

-- where 1 & 14 are always the same for each record, and '10' is the new UserGroup number that the 3000 will have.

Question - when it writes to the desitnation table will it automatically create the next subsequent record with the next corresponding column1 number?

Thanks in advance!



are you trying to copy column1 values as is or generate next sequence of numbers based on column1?


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-07 : 07:54:01
Column1 values as is

thanks in advance
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 09:24:45
then you need to SET IDENTITY_INSERT <tablename> ON before you do insertion and turn it OFF after

then next insertion will start with next sequential value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-07 : 09:33:16
THANKS!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 09:41:20
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-11 : 16:29:27
visakh16,

I'm having trouble getting the following code to work. Still working
on 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:


use MyDB;
go

declare @Function int
declare @Zone int
declare @GroupID int

set @Function = 1
set @Zone = 14
set @GroupID = 10

select CredentialID
from table1
insert into [dbo].[table2]
([CredentialID],[Function],[Zone],[GroupID])
values
(CredentialID
,@Function
,@Zone
,@GroupID)

go

Thanks in advance!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-11 : 22:53:44
the last insert should be



insert into [dbo].[table2]
([CredentialID],[Function],[Zone],[GroupID])
select CredentialID
,@Function
,@Zone
,@GroupID
from table1


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-12 : 08:05:53
Thanks! I'll give that a try!
Go to Top of Page

samyer
Starting Member

15 Posts

Posted - 2013-02-12 : 09:03:32
IT WORKED!! THANKS!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-12 : 09:54:02
Welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -