How to insert more than one value into one field in the database?
-
Hi,
I need to know how to insert more than one value from a sproc into one table that has two fields..
for example i need to insert a baker
this baker... cooks...cleans....preps......and at the same time this baker has one UserID... THE table that i need to inset this info into has only two columns... userid and Jobs...
So my question is how do i do a insert like this?
thanks,
erik..
Dam again!
-
You don't. You normalise your database schema so that a single field stores a single value.
In your example use 3 tables. 1 to store details of the user. (id = 1, title = baker, name= Bob, etc)
A second table stored jobs (eg cook, clean, bake, prepare, sell) and a third is a joining table between them (eg userid = 1, jobid=1, userid=1, jobid=3)
If you have no option (ie existing database and you can't change design) then you could concatinate the values together into a comma-delimited list and store that. That option does however violate the first rule of database normalisation.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
awww.. not a concatenated list...
that will be absolutely horrible to search through...I'd go for the userid + job instead, one row for each job that the userid has. This would of course mean that the PK must also be userid + job... but I think I prefer that instead of having userid + job1, job2, job3....
/Kenneth
-
awww.. not a concatenated list...
that will be absolutely horrible to search through...Search through? How about updating to remove a job. That goes several miles beyond horrible.
Hence the comments about normalising the database.
I was working on the assumption (probably bad) that userID was unique.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Oh, yes, you're absolutely right. The step beyond horrible is ever so short.
I too assumed that userId was unique, hence the note about userId + job must then become the PK...
/Kenneth
-
ok i am lost here... what exactly is it that i should do?
Dam again!
-
Could you give us a little bit broader description of tables involved and what fields are unique?
Ideally you shouldn't put more than one value in a field (doing so violates the basic rules of relational databases), so if you give us a better idea of the overall problem, we can sugest somethng useful.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
i need to insert a new user.....and then insert all of the areas that this user specialezes in...... so that is my userspecialty table and this is the one to many...
i will look at the xml bols.... to see how to do this....
got an example?
thanks.
erik..
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[is_performed_by]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserSpecialty] DROP CONSTRAINT is_performed_by
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[purchases_items_via]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Orders] DROP CONSTRAINT purchases_items_via
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[offers]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Product] DROP CONSTRAINT offers
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[specializes_in]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[UserSpecialty] DROP CONSTRAINT specializes_in
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Specialty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Specialty]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UserSpecialty]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[UserSpecialty]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO
CREATE TABLE [dbo].[Specialty] (
[SpecialtyCD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SpecialtyName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[UserSpecialty] (
[UserID] [int] NOT NULL ,
[SpecialtyCD] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[Users] (
[UserID] [int] IDENTITY (1, 1) NOT NULL ,
[OComp] [int] NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyName] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ActiveInd] [int] NULL ,
[Zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WkPhone] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HmPhone] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Website] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UserType] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StateCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryCD] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyPhoto] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyDescr] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Password] [binary] (24) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Dam again!
-
oop's... i can see that i have not asked my question correctly. (((( Ideally you shouldn't put more than one value in a field ))))) i need to insert many records into one talbe (userspecialty) that have the same userid..
so when i insert a new user into the users table...then i insert all of the userspecialty (user may specialize in one are or 20 areas....) would like to wrap this up in one querry.
Dam again!
-
This is where i need to pass in one to (how ever many) after i am able to retrieve the newly inserted user
SET
@userid = SCOPE_IDENTITY()
INSERT INTO
UserSpecialty
(SpecialtyCD, UserID)
VALUES (@UserSpecialtyCD, @userid)
========================================
sos,
erik
Here is the stored prcedure that i am building...
==================================================
ALTER PROCEDURE
Admin_Insert_NewUser
@Admin_Email
Varchar (200), /*Admin Check */
@Admin_FName
Varchar (30), /*Admin Check */
@Admin_UserType
Varchar (20), /*Admin Check */
@FName [varchar](30),
@LName [varchar](30),
@CompanyName [varchar](200) =
NULL,
@Address1 [varchar](75),
@Address2 [varchar](75),
@City [varchar](75),
@Zip [varchar](10),
@WkPhone [varchar](12),
@HmPhone [varchar](12),
@Fax [varchar](12),
@Email [varchar](200),
@website [varchar](200) =
NULL,
@UserType [varchar](20),
@Title [varchar](100),
@Note [text],
@StateCD [char](2),
@CountryCD [char](2),
@CompanyPhoto [varchar](50) =
NULL,
@CompanyDescr [varchar](2000)=
NULL,
@UserSpecialtyCD [varchar](5)=
NULL,
@Password [binary](24)
AS
DECLARE
@user-id INT
IF EXISTS
(SELECT Email, FirstName, UserType FROM Users
WHERE
Email = @Admin_Email AND
FirstName = @Admin_FName
AND
UserType = @Admin_UserType)
BEGIN
BEGIN TRANSACTION
INSERT INTO Users
( [FirstName],
[LastName],
[CompanyName],
[Address1],
[Address2],
[City],
[Zip],
[WkPhone],
[HmPhone],
[Fax],
[Email],
[Website],
[UserType],
[Title],
[Note],
[StateCD],
[CountryCD],
[CompanyPhoto],
[CompanyDescr],
[Password])
VALUES
( @FName,
@LName,
@CompanyName,
@Address1,
@Address2,
@City,
@Zip,
@WkPhone,
@HmPhone,
@Fax,
@Email,
@UserType,
@Title,
@Note,
@StateCD,
@CountryCD,
@CompanyPhoto,
@CompanyDescr,
@Password)
IF (@@ERROR <> 0) GOTO on_error
SET
@userid = SCOPE_IDENTITY()
INSERT INTO
UserSpecialty
(SpecialtyCD, UserID)
VALUES (@UserSpecialtyCD, @userid)
IF (@@ERROR <> 0) GOTO on_error
COMMIT TRANSACTION
-- return 0 to signal success
RETURN (0)
on_error:
ROLLBACK TRANSACTION
-- return 1 to signal failure
RETURN (1)
END
ELSE
BEGIN
DECLARE
@Admin_Warning VARCHAR (30)
SET
@Admin_Warning = 'You are not signed in as an Administrator! You must be signed in order to update a users credentials.'
Select
@Admin_Warning as AdminWarning
END
Dam again!
-
Erik - if I've understood the requirements correctly, I think what you want to do is this...
INSERT INTO UserSpecialty SELECT @UserId, UserSpecialtyCD FROM Specialty WHERE SpecialtyName IN ('Butcher', 'Baker', 'Candlestick Maker')
**ASCII stupid question, get a stupid ANSI !!!** -
I have a form that a new user fills out decribing their business and the different areas of building they specialize in...
all of the fields that the new user fills out is one value... what i need for the areas that the user specializes in..is a way for the user to make a Multiple selection and pass that back to a stored procdure... i think i am going to need some sort of xml..
what do you think?
erik
Dam again!
-
One alternative is to have a drop-down box that displays the values from your "Specialty" table where the user can select multiple rows and then you insert based on the selection...
actually, that's what I thought you were doing when I posted that query...
**ASCII stupid question, get a stupid ANSI !!!** -
Think you mean a list box
Yes add a list box to the form, populate the values into the list box based on your Specialty table.
Make it multiselectable.
When your form gets submitted to the DB, create a loop for a count of items selected and just call your proc each time to insert into UserSpecialty.
Dont see this a SQL problem though, maybe the wrong forum
-
Here is what i finally was able to come up with.
I did use a listbox so that i can let the user make more than one slection... when i got to this part in my application i was a little dumbfounded because it never dawned on me that i would need to insert more than one record for one userid at a time...So in order to keep my app looking pro i had to come up with something...and o'll xml to the resuce...
This is not the exact piece that i have in my app; however, this is what i used to test with and build, and this works great... this will allow the user to insert as many records from a listbox as they want to..
OPENXML.... to the resuce.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strSqldoc As String = "<ROOT>"
Dim Li As ListItem
Dim LVHolder As String
'
For Each Li In Me.ListBox1.Items
'
If Li.Selected = True Then
LVHolder = Me.ListBox1.SelectedItem.Value
strSqldoc &= "<UserSpecialty SpecialtyCD='" & LVHolder & "'" & "/>"
Li.Selected = False
End If
'
Next Li
'
strSqldoc &= "</ROOT>"
'
sendmyxml(strSqldoc)
'
End Sub
===========================================================================
'
Private Sub sendmyxml(ByVal strSqldoc As String)
Dim cnn As New SqlConnection("Data source=(local);initial catalog= test; user id = erik; password=xxx")
Dim cmd As New SqlCommand
With cmd
.Parameters.Add("@Sqldoc", SqlDbType.VarChar, 1000).Value = strSqldoc
.Parameters.Add("@UserID", 5)
.CommandText = "testSql"
.CommandType = CommandType.StoredProcedure
.Connection = cnn
End With
cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
'
End Sub
===========================================================================
ALTER Procedure testSql
@user-id int,
@Sqldoc varchar (1000)
as
DECLARE @idoc int
--Create an internal representation of the XML document
EXEC sp_xml_preparedocument @idoc OUTPUT, @Sqldoc
insert into TableTest1
( [ID], Test)
-- Execute a SELECT statement that uses the OPENXML rowset provider
SELECT @user-id, *
FROM OPENXML (@idoc, '/ROOT/UserSpecialty',1)
WITH (SpecialtyCD varchar(5))
Dam again!
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply