February 5, 2010 at 8:04 am
Hi
I am trying to insert two variables and some set values into a table. It's a user table which contains details such as id (unique identifier of the attribute) and userID. The id increments by one each time, so I have to check the maximum value in this table and add one then insert that. Also need to select userID from a list.
UserID seems to be ok but can't get the ID part to work. I'm trying to use 2 cursors but not sure Im doing this the right way.
I'd like to set my variables to values like below:
id....|userid
1.....|usera
2.....|userb
3.....|userc
then take these values and set id - 1 , userid - usera and insert. loop through and do this for all users.
Not sure if Im going about this the right way and would appreciate any help. My code so far is below.
Thanks! 🙂
DECLARE@myuserid numeric
DECLARE@myid numeric
DECLAREc1 CURSOR
FOR
SELECTuserID
FROMTable1
WHEREuserID IN
(
....
)
OPEN c1
FETCH NEXT FROM c1
INTO @myuserid
DECLAREc2 CURSOR
FOR
SELECT MAX(id) FROM Table2
OPEN c2
FETCH NEXT FROM c2
INTO @myid
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert statement will go here
--Just trying to print for now
PRINT @myuserid
PRINT @id+1
FETCH NEXT FROM c1
INTO @myuserid
FETCH NEXT FROM c2
INTO @myid
END
CLOSE c1
DEALLOCATE c1
CLOSE c2
DEALLOCATE c2
February 5, 2010 at 8:44 am
At first look at your code, I really don't think you need any cursors at all. But to proceed further, you need to provide some more information such as table structure for table1 and table2, sample data and the required output.
--Ramesh
February 5, 2010 at 9:14 am
Thanks for the response.:-)
Ok...
I have the following tables:
User - contains id and name of user
Group - contains id and name of group
Membership - contains the unique id of user and group
UserAttributes - contains information about user
I join user, group and membership to find out which users are in a particular group. This will return the userId of X users and I need to loop through them.
User attribute contains a uniqueID which is the primary key. It also contains the userID and a value field.
I plan to insert the uniqueID, value and userID into the userattribute table. As it loops through the userIDs, it must check the maximum value of the ID field so it can do +1 then insert. Each entry in this table has a different ID (increments by 1 every time)
Hope that makes sense?
I'd have an insert statement like this:
INSERT INTO...... (@id+1, @userid, 'a pre-set value')
e.g.
User
ID | Name
--------------
001 | UserA
002 | UserB
Group
ID | Name
--------------
001 | Group1
002 | Group2
Membership
GroupID | UserID
---------------------
001 | 001
001 | 002
002 | 001
UserAttribute
uniqueID | userID | Value
------------------------------
1234 | 001 | testing
1235 | 001 | xxxx
1236 | 001 | yyyyy
February 5, 2010 at 9:37 am
Chris,
Close, but you haven't given us quite enough information yet. Please provide DDL (CREATE TABLE) for the tables, and DML (INSERT INTO) statements to make and populate the tables. See the first link in my signature for more information on how to do this.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
February 5, 2010 at 4:56 pm
Also think about why you need to have an incrementing value for the ID in that table. Does it absolutely require an incrementing value with no gaps? Or do you just need an increasing value that is unique?
If the latter - then look at IDENTITY instead and you can forget about looping. Just insert the data and it will be given a unique ID, but it isn't guaranteed to have no gaps.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 6, 2010 at 12:58 am
As Jeffrey said, if you just need incremental values without bothering about gaps then an IDENTITY column would do the best job. All you have to do is mark the uniqueID column of UserAttributes table as an IDENTITY column.
--Ramesh
February 8, 2010 at 3:49 am
Ramesh Saive (2/6/2010)
As Jeffrey said, if you just need incremental values without bothering about gaps then an IDENTITY column would do the best job. All you have to do is mark the uniqueID column of UserAttributes table as an IDENTITY column.
Hi Folks, thanks for the responses.
I am trying to achieve what the "Is Identity" option does - but via my script. The company who develop the database do not recommend we make changes although I can enquire about turning on the identity option for this. It would certainly make life easier.
However, at the moment, is there a way to produce this without having to turn that on for this column?
Thanks.
February 8, 2010 at 4:40 am
chris.mcbain (2/8/2010)
......However, at the moment, is there a way to produce this without having to turn that on for this column?
.....
It can done, but unless you help us with some required information such as CREATE TABLE, INSERT TABLE scripts and the required output.
However, here is one of the recent posts that looks similar to the one you described.
http://www.sqlservercentral.com/Forums/Topic860500-392-1.aspx#bm860509
--Ramesh
February 9, 2010 at 2:34 am
Hi
Thanks again for responses. Code below should create tables and insert some sample data.
Notes
- ID of User and Group tables correspond with User & Group in Attributes and Membership tables
- IDs are primary keys but "Is Identity" is not turned on
I want to insert into the Attributes table. The name, value and group will be constant but the ID and User will be variables. I have a query which produces a list of the userIDs I need to add in here. I want to set the user variable to those IDs (will require loop?) but for each one I'll also require an attribute ID. Not sure how to get both of these....
Please advise 🙂
USE MyDB
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
[Id] [numeric](19, 0) NOT NULL,
[name] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL
)
CREATE TABLE [dbo].[Group](
[Id] [numeric](19, 0) NOT NULL,
[description] [ntext] COLLATE Latin1_General_CI_AS NULL,
[name] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL
)
CREATE TABLE [dbo].[Membership](
[Group] [numeric](19, 0) NOT NULL,
[User] [numeric](19, 0) NOT NULL
)
CREATE TABLE [dbo].[Attributes](
[Id] [numeric](19, 0) NOT NULL,
[User] [numeric](19, 0) NULL,
[name] [nvarchar](128) COLLATE Latin1_General_CI_AS NULL,
[Value] [ntext] COLLATE Latin1_General_CI_AS NULL,
[Group] [numeric](19, 0) NULL
)
INSERT INTO MyDB.[dbo].[Group]
([ID]
,[description]
,[name])
VALUES
('001', 'First Group', 'Group 1')
INSERT INTO MyDB.[dbo].[Group]
([ID]
,[description]
,[name])
VALUES
('002', 'Second Group', 'Group 2')
INSERT INTO MyDB.[dbo].[User]
([ID]
,[name])
VALUES
('1111', 'User1')
INSERT INTO MyDB.[dbo].[User]
([ID]
,[name])
VALUES
('2222', 'User2')
INSERT INTO MyDB.[dbo].[Membership]
([Group]
,[User])
VALUES
('001', '1111')
INSERT INTO MyDB.[dbo].[Membership]
([Group]
,[User])
VALUES
('001', '2222')
INSERT INTO MyDB.[dbo].[Membership]
([Group]
,[User])
VALUES
('002', '2222')
INSERT INTO MyDB.[dbo].[Attributes]
([ID]
,[User]
,[name]
,[Value]
,[Group])
VALUES
('98765', '1111', 'Attribute A', 'Value 1', '001')
INSERT INTO MyDB.[dbo].[Attributes]
([ID]
,[User]
,[name]
,[Value]
,[Group])
VALUES
('98766', '1111', 'Attribute B', 'Value 2', '001')
INSERT INTO MyDB.[dbo].[Attributes]
([ID]
,[User]
,[name]
,[Value]
,[Group])
VALUES
('98767', '1111', 'Attribute C', 'Value 1', '002')
INSERT INTO MyDB.[dbo].[Attributes]
([ID]
,[User]
,[name]
,[Value]
,[Group])
VALUES
('84001', '2222', 'Attribute A', 'Value 1', '001')
INSERT INTO MyDB.[dbo].[Attributes]
([ID]
,[User]
,[name]
,[Value]
,[Group])
VALUES
('84002', '2222', 'Attribute B', 'Value 2', '002')
February 9, 2010 at 2:51 am
Thank you for providing the scripts, good work.
Well, the technique is to use ROW_NUMBER() function to generate sequential numbers. And by adding the last Id value to this row number column will generate unique sequential numbers.
DECLARE @LastId NUMERIC(19,0)
SELECT@LastId = MAX( Id ) FROM dbo.Attributes
SELECT@LastId = COALESCE( @LastId, 0 )
--INSERTdbo.Attributes( Id, [User], [name], Value, [Group] )
SELECT@LastId + ROW_NUMBER() OVER( ORDER BY ( SELECT 1 ) ) AS RowNumber,
M.[User], 'Attribute 1', 'Value 1', M.[Group]
FROMdbo.Membership M
INNER JOIN dbo.[User] U ON M. = U.Id
INNER JOIN dbo.[Group] G ON M.[Group] = G.ID
--Ramesh
February 9, 2010 at 3:25 am
Hi
Thank you for the SQL to generate the attribute ID. Not sure if I've been completely clear on this...
My user IDs come from another query. I need to insert each user id into the attribute table along with my other variable, attribute ID.
I get my userID from the query below. This returns a list.
SELECTUSER
FROMAttributes a
WHEREUser IN
(
SELECTu.ID
FROMMyDB.dbo.Membership m
INNER JOINMyDB.dbo.User u
ONm.User = u.ID
INNER JOINMyDB.dbo.Group g
ONm.Group = g.ID
WHEREg.ID = '001'
ANDu.ID IN
(
SELECTUser
FROMAttributes a
WHEREa.name = 'App1 username'
ANDValue is not null
)
)
ANDa.name = 'Appl1 username'
ANDValue like 'admin.%'
ORValue like 'super.%'
February 9, 2010 at 4:05 am
I really don't think so I've understood your requirement completely. Can you provide the sample output required for the provided data?
--Ramesh
February 9, 2010 at 4:38 am
Yes.
First, I'll declare all columns in the attribute table as variables.
DECLARE@xid numeric
DECLARE@xGroup numeric
DECLARE@xname varchar(10)
DECLARE@xValue varchar(10)
DECLARE@xuserID numeric
I set them to the values I want. Some are constant values while the others are variables (ID and userID).
SET @xgroup = '001'
SET @xname = 'test'
SET @xValue = 'value abc'
The userID will be contained in the query I posted above (last post). This returns a list of userIDs. I would have a variable which goes through each row in my list of user IDs returned.
So somewhere I'd need:
SET @userid = .......
The other variable is ID (of attributes). This would be generated using the row number example you have posted above.
So I need to get an insert statement that will check the maximum attributeID and set my variable to that + 1. It will have to do this for each userID in my list returned from previous query. All other variables remain the same as declared above.
Does that make sense??
My attribute table would look like this:
(note ID / user change but the rest of the values are constant)
| ID | User | Name | Value | Group
--------------------------------------
|120| 1111| test | value abc | 001 |
|121| 3333| test | value abc | 001 |
|122| 4444| test | value abc | 001 |
|123| 5555| test | value abc | 001 |
|124| 6666| test | value abc | 001 |
|125| 7777| test | value abc | 001 |
February 9, 2010 at 4:52 am
So, you just wanted a sequential numbering for the resultset returned by your query, just add the ROW_NUMBER function in the same query itself.
Though this should work, but I really want to why you are doing it this way, because I believe this can be done in a much simpler way.
And also, I think the OR operator at the end of query (i.e. Value like 'admin.%' OR Value like 'super.%') should be enclosed within parenthesis, otherwise it would return all the rows where Value is either 'admin.%' OR 'super.%' irrespective of other conditions.
DECLARE @LastId NUMERIC(19,0)
SELECT @LastId = MAX( Id ) FROM dbo.Attributes
SELECT @LastId = COALESCE( @LastId, 0 )
SELECT@LastId + ROW_NUMBER() OVER( ORDER BY ( SELECT 1 ) ) AS AttributeID, USER
FROMAttributes a
WHEREUser IN
(
SELECTu.ID
FROMdbo.Membership m
INNER JOIN dbo.User u ON m.User = u.ID
INNER JOIN dbo.Group g ON m.Group = g.ID
WHERE g.ID = '001'
AND u.ID IN
(
SELECTUser
FROMAttributes a
WHEREa.name = 'App1 username'
AND Value is not null
)
)
AND a.name = 'Appl1 username'
AND Value like 'admin.%'
OR Value like 'super.%'
--Ramesh
February 9, 2010 at 5:03 am
If you believe this can be done in a much simpler way, I'd really like to know how! :w00t:
Thanks for the solution above. I will test this out.
Appreciate it.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply