I have a SP that I am trying to finalize however; my inexperience is showing itself on this one.
History:
3 Tables: Tooldb - Employee - ToolUserdb
Scenario:
I have a webform in c# that gathers data concerning internal tools(applications) that are written in-house. One of the fields is a listbox of names pulled from the Employee table called Creator. When the form is submitted I need to have the list of selected employees published to the ToolUserdb.
My SP:
ALTER PROCEDURE dbo.InsertTool
(
@.ToolName nvarchar(250),
@.Platform nvarchar(250),
@.Vendor nvarchar(250),
@.Subplatform nvarchar(250),
@.Submitter nvarchar(250),
@.Finders nvarchar(250),
@.LTD nvarchar(50),
@.JobArea nvarchar(250),
@.Func nvarchar(250),
@.Owners nvarchar(250),
@.Active nvarchar(250),
@.Version numeric,
@.Build numeric,
@.CWSTD nvarchar(50),
@.Status nvarchar(250),
@.Cost numeric,
@.Notes nvarchar(250),
@.Keywords nvarchar(250),
@.Links nvarchar(250),
@.Paths nvarchar(250),
@.eid int,
@.tdbid int,
@.Creator nvarchar(250)
)
AS
INSERT INTO [ToolDB] (ToolName, Platform, Vendor, Subplatform, Submitter, Finders, LTD, JobArea, Func, Owners, Active, Version, Build, CWSTD, Status, Cost, Notes, Keywords, Links, Paths)
VALUES
(@.ToolName, @.Platform, @.Vendor, @.Subplatform, @.Submitter, @.Finders, @.LTD, @.JobArea, @.Func, @.Owners, @.Active, @.Version, @.Build, @.CWSTD, @.Status, @.Cost, @.Notes, @.Keywords, @.Links, @.Paths)
INSERT INTO ToolUsersdb (@.Creator) SELECT + @.eid + ',[ID] FROM Employee + @.tdbid + ',[ID] FROM ToolDB IN (@.Creator)
Error:
Incorrect syntax near keywork IN (referring to last insert statement).
Can someone tell me how I can get this multi-insert stmt. to work?
Thank you!
TimWhat's the DDL for ToolUsersDB table?
INSERT INTO ToolUsersdb (EmpID, ToolID, Creator) values (@.eid, @.tdbid, @.Creator) ?|||if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ToolUsersdb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ToolUsersdb]
GO
CREATE TABLE [dbo].[ToolUsersdb] (
[id] [int] NOT NULL ,
[eid] [int] NULL ,
[tdbid] [int] NULL
) ON [PRIMARY]
GO|||After examining my SP closer I have made the following changes. I think this is closer to the end result. The problem is that I am getting an error:
The select list for the insert statement contains fewer items than the insert list. Not sure why this is.
ALTER PROCEDURE dbo.InsertTool
(
@.ToolName nvarchar(250),
@.Platform nvarchar(250),
@.Vendor nvarchar(250),
@.Subplatform nvarchar(250),
@.Submitter nvarchar(250),
@.Finders nvarchar(250),
@.LTD nvarchar(50),
@.JobArea nvarchar(250),
@.Func nvarchar(250),
@.Owners nvarchar(250),
@.Active nvarchar(250),
@.Version numeric,
@.Build numeric,
@.CWSTD nvarchar(50),
@.Status nvarchar(250),
@.Cost numeric,
@.Notes nvarchar(250),
@.Keywords nvarchar(250),
@.Links nvarchar(250),
@.Paths nvarchar(250),
@.eid int,
@.tdbid int,
@.Creator nvarchar(250)
)
AS
INSERT INTO [ToolDB] (ToolName, Platform, Vendor, Subplatform, Submitter, Finders, LTD, JobArea, Func, Owners, Active, Version, Build, CWSTD, Status, Cost, Notes, Keywords, Links, Paths)
VALUES
(@.ToolName, @.Platform, @.Vendor, @.Subplatform, @.Submitter, @.Finders, @.LTD, @.JobArea, @.Func, @.Owners, @.Active, @.Version, @.Build, @.CWSTD, @.Status, @.Cost, @.Notes, @.Keywords, @.Links, @.Paths)
INSERT INTO [ToolUsersdb] (eid, tdbid) select e.id from employee e where e.id in (@.Creator) select t.id from Tooldb t|||Huh?
INSERT INTO [ToolUsersdb] (eid, tdbid)
select e.id from employee e where e.id in (@.Creator) select t.id from Tooldb t
What this?
You can't do that|||Brett,
Could you expand on your thoughts?
Do you have any ideas on how this can be done. I am sure that I am not the first person to be presented with a one to many realtionship between tables.
Even a link would be helpful if you know of one.
Thanks,
Tim|||It looks like, and correct me if I'm wrong, and INSERT statement with 2 SELECTs...
I guess you could do it this way...
INSERT INTO [ToolUsersdb] (eid, tdbid)
SELECT e.id, t.id
FROM employee e JOIN Tooldb t
ON e.id = t.id
WHERE e.id = @.Creator
The IN error message is erroneous...|||Brett,
Thanks for helping out. You thoughts are appreciated.
Here is the modification I have made (with your thoughts in mind):
INSERT INTO [ToolUsersdb] (eid, tdbid)
SELECT e.id FROM employee e WHERE e.id = (@.Creator)
UNION
SELECT t.id FROM Tooldb t
I put the UNION in place because my understanding is that without it, the second SLELECT statement would be ignored.
With the above script I am getting the following error:
The Select list for the insert statment contains fewer items than the Insert list.
There are only two columns in the ToolUsersdb outside of the ID column.
Any additional ideas?
Have I explained what I am trying to do clearly enough in the beginning? Let me know if you need any further clarification.
Thanks again.
Tim|||Concepts!
You're mixing rows with columns.
For example, your INSERT expects 2 columns per row.
Your result set is producing 1 column per row.
If you want e.id and t.id on the same row, you have to relate them somehow.
2012年3月8日星期四
2012年2月25日星期六
Any limit of the length of the characters in table name ?
Hi,
We are a team that are developing a projet which has in itself a good number
of tables in sql server (not temporary ones !). The entire team is pretty
knowledgeable in MSSQL server and in SQL programming in general... However
we kind of want to double check what are the limitations of naming tables
(not temporary ones ! "#"). ?
e.g
1. Any limit of the length of the characters in table name ?
2. Any special characters which are not allowed or is not advised to be in a
table name ?
3. or any other constrains or limitations I can't think of now.
Thank you very much in advance,
G.Y
Software Engineer,
QuadraMed, Reston VA
> 1. Any limit of the length of the characters in table name ?
They are stored using SYSNAME datatype, which is NVARCHAR(128).
DECLARE @.s VARCHAR(255)
SET @.s = 'CREATE TABLE '+REPLICATE('x', 129)+' (i INT)'
EXEC(@.s)
Yields:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'xxxxxxxxxxxxxxxxxxxxxx[...]' is too long.
Maximum length is 128.
> 2. Any special characters which are not allowed or is not advised to be in
> a table name ?
Spaces. Punctuation. Accent characters. Do not start a table name with a
number or a dash. Underscores are the only non-alphanumeric that I allow,
only if deemed absolutely necessary by someone else
(ThisCaseWorksFineForMe), and only if they are at a logical place (not at
the beginning). In fact even numbers are questionable, but it really
depends on the entity you are trying to model.
Here are my thoughts on SQL Server naming conventions, maybe useful to you,
maybe not:
http://www.aspfaq.com/2538
> 3. or any other constrains or limitations I can't think of now.
http://www.aspfaq.com/2345
We are a team that are developing a projet which has in itself a good number
of tables in sql server (not temporary ones !). The entire team is pretty
knowledgeable in MSSQL server and in SQL programming in general... However
we kind of want to double check what are the limitations of naming tables
(not temporary ones ! "#"). ?
e.g
1. Any limit of the length of the characters in table name ?
2. Any special characters which are not allowed or is not advised to be in a
table name ?
3. or any other constrains or limitations I can't think of now.
Thank you very much in advance,
G.Y
Software Engineer,
QuadraMed, Reston VA
> 1. Any limit of the length of the characters in table name ?
They are stored using SYSNAME datatype, which is NVARCHAR(128).
DECLARE @.s VARCHAR(255)
SET @.s = 'CREATE TABLE '+REPLICATE('x', 129)+' (i INT)'
EXEC(@.s)
Yields:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'xxxxxxxxxxxxxxxxxxxxxx[...]' is too long.
Maximum length is 128.
> 2. Any special characters which are not allowed or is not advised to be in
> a table name ?
Spaces. Punctuation. Accent characters. Do not start a table name with a
number or a dash. Underscores are the only non-alphanumeric that I allow,
only if deemed absolutely necessary by someone else
(ThisCaseWorksFineForMe), and only if they are at a logical place (not at
the beginning). In fact even numbers are questionable, but it really
depends on the entity you are trying to model.
Here are my thoughts on SQL Server naming conventions, maybe useful to you,
maybe not:
http://www.aspfaq.com/2538
> 3. or any other constrains or limitations I can't think of now.
http://www.aspfaq.com/2345
Any limit of the length of the characters in table name ?
Hi,
We are a team that are developing a projet which has in itself a good number
of tables in sql server (not temporary ones !). The entire team is pretty
knowledgeable in MSSQL server and in SQL programming in general... However
we kind of want to double check what are the limitations of naming tables
(not temporary ones ! "#"). ?
e.g
1. Any limit of the length of the characters in table name ?
2. Any special characters which are not allowed or is not advised to be in a
table name ?
3. or any other constrains or limitations I can't think of now.
Thank you very much in advance,
G.Y
Software Engineer,
QuadraMed, Reston VA> 1. Any limit of the length of the characters in table name ?
They are stored using SYSNAME datatype, which is NVARCHAR(128).
DECLARE @.s VARCHAR(255)
SET @.s = 'CREATE TABLE '+REPLICATE('x', 129)+' (i INT)'
EXEC(@.s)
Yields:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'xxxxxxxxxxxxxxxxxxxxxx[...]' is too lon
g.
Maximum length is 128.
> 2. Any special characters which are not allowed or is not advised to be in
> a table name ?
Spaces. Punctuation. Accent characters. Do not start a table name with a
number or a dash. Underscores are the only non-alphanumeric that I allow,
only if deemed absolutely necessary by someone else
(ThisCaseWorksFineForMe), and only if they are at a logical place (not at
the beginning). In fact even numbers are questionable, but it really
depends on the entity you are trying to model.
Here are my thoughts on SQL Server naming conventions, maybe useful to you,
maybe not:
http://www.aspfaq.com/2538
> 3. or any other constrains or limitations I can't think of now.
http://www.aspfaq.com/2345
We are a team that are developing a projet which has in itself a good number
of tables in sql server (not temporary ones !). The entire team is pretty
knowledgeable in MSSQL server and in SQL programming in general... However
we kind of want to double check what are the limitations of naming tables
(not temporary ones ! "#"). ?
e.g
1. Any limit of the length of the characters in table name ?
2. Any special characters which are not allowed or is not advised to be in a
table name ?
3. or any other constrains or limitations I can't think of now.
Thank you very much in advance,
G.Y
Software Engineer,
QuadraMed, Reston VA> 1. Any limit of the length of the characters in table name ?
They are stored using SYSNAME datatype, which is NVARCHAR(128).
DECLARE @.s VARCHAR(255)
SET @.s = 'CREATE TABLE '+REPLICATE('x', 129)+' (i INT)'
EXEC(@.s)
Yields:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'xxxxxxxxxxxxxxxxxxxxxx[...]' is too lon
g.
Maximum length is 128.
> 2. Any special characters which are not allowed or is not advised to be in
> a table name ?
Spaces. Punctuation. Accent characters. Do not start a table name with a
number or a dash. Underscores are the only non-alphanumeric that I allow,
only if deemed absolutely necessary by someone else
(ThisCaseWorksFineForMe), and only if they are at a logical place (not at
the beginning). In fact even numbers are questionable, but it really
depends on the entity you are trying to model.
Here are my thoughts on SQL Server naming conventions, maybe useful to you,
maybe not:
http://www.aspfaq.com/2538
> 3. or any other constrains or limitations I can't think of now.
http://www.aspfaq.com/2345
Any limit of the length of the characters in table name ?
Hi,
We are a team that are developing a projet which has in itself a good number
of tables in sql server (not temporary ones !). The entire team is pretty
knowledgeable in MSSQL server and in SQL programming in general... However
we kind of want to double check what are the limitations of naming tables
(not temporary ones ! "#"). ?
e.g
1. Any limit of the length of the characters in table name ?
2. Any special characters which are not allowed or is not advised to be in a
table name ?
3. or any other constrains or limitations I can't think of now.
Thank you very much in advance,
G.Y
Software Engineer,
QuadraMed, Reston VA> 1. Any limit of the length of the characters in table name ?
They are stored using SYSNAME datatype, which is NVARCHAR(128).
DECLARE @.s VARCHAR(255)
SET @.s = 'CREATE TABLE '+REPLICATE('x', 129)+' (i INT)'
EXEC(@.s)
Yields:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'xxxxxxxxxxxxxxxxxxxxxx[...]' is too long.
Maximum length is 128.
> 2. Any special characters which are not allowed or is not advised to be in
> a table name ?
Spaces. Punctuation. Accent characters. Do not start a table name with a
number or a dash. Underscores are the only non-alphanumeric that I allow,
only if deemed absolutely necessary by someone else
(ThisCaseWorksFineForMe), and only if they are at a logical place (not at
the beginning). In fact even numbers are questionable, but it really
depends on the entity you are trying to model.
Here are my thoughts on SQL Server naming conventions, maybe useful to you,
maybe not:
http://www.aspfaq.com/2538
> 3. or any other constrains or limitations I can't think of now.
http://www.aspfaq.com/2345
We are a team that are developing a projet which has in itself a good number
of tables in sql server (not temporary ones !). The entire team is pretty
knowledgeable in MSSQL server and in SQL programming in general... However
we kind of want to double check what are the limitations of naming tables
(not temporary ones ! "#"). ?
e.g
1. Any limit of the length of the characters in table name ?
2. Any special characters which are not allowed or is not advised to be in a
table name ?
3. or any other constrains or limitations I can't think of now.
Thank you very much in advance,
G.Y
Software Engineer,
QuadraMed, Reston VA> 1. Any limit of the length of the characters in table name ?
They are stored using SYSNAME datatype, which is NVARCHAR(128).
DECLARE @.s VARCHAR(255)
SET @.s = 'CREATE TABLE '+REPLICATE('x', 129)+' (i INT)'
EXEC(@.s)
Yields:
Server: Msg 103, Level 15, State 7, Line 1
The identifier that starts with 'xxxxxxxxxxxxxxxxxxxxxx[...]' is too long.
Maximum length is 128.
> 2. Any special characters which are not allowed or is not advised to be in
> a table name ?
Spaces. Punctuation. Accent characters. Do not start a table name with a
number or a dash. Underscores are the only non-alphanumeric that I allow,
only if deemed absolutely necessary by someone else
(ThisCaseWorksFineForMe), and only if they are at a logical place (not at
the beginning). In fact even numbers are questionable, but it really
depends on the entity you are trying to model.
Here are my thoughts on SQL Server naming conventions, maybe useful to you,
maybe not:
http://www.aspfaq.com/2538
> 3. or any other constrains or limitations I can't think of now.
http://www.aspfaq.com/2345
订阅：
博文 (Atom)