March 31, 2012 at 9:50 pm
Someone help me understand this,I've got solutions to insert into two tables having same columns but my issue here is I've 2 different tables each holding different columns.I need to insert into my 1st(parent) table and just insert the pk of my table1 into my 2'nd table.
My parent table has columns id (pk),Name,description and my table2 has columns table1-id(FK),id(PK),directorypath,upload date.I need to insert into table1 values name and description and just insert the tabel1 -ID(FK),directorypath,upload date into tabel 2.Help me understand is it possible using my below query.
my table 2 has foreign key(table1 PK), table 2 primary key.
ALTER PROCEDURE InsertResource
(
@ResourceName nvarchar(256),
@Description nvarchar(256),
@DirectoryPathToFile nvarchar(256),
@UploadDate datetime2(7)
)
AS
declare @FileType nvarchar(max),
@FileName nvarchar(max),
@Proceed bit
select @FileName = dbo.GetFileName (@DirectoryPathToFile , '\')
select @FileType = reverse(SUBSTRING(REVERSE(@FileName), 0, CHARINDEX('.',REVERSE(@FileName))))
SELECT @Proceed = CASE WHEN EXISTS (select 1 from ImageItems where DirectoryPathToFile = @DirectoryPathToFile) THEN 0
WHEN EXISTS (SELECT 1 from DocumentItems where DirectoryPathToFile = @DirectoryPathToFile) THEN 0
WHEN EXISTS (SELECT 1 FROM VideoItems where DirectoryPathToFile = @DirectoryPathToFile) THEN 0 ELSE 1 END
IF @Proceed = 1
BEGIN
IF @FileType IN ('doc','pdf','txt')
INSERT INTO dbo.Resources(ResourceName, DescriptionOfResource)
OUTPUT Inserted.ResourceDatabaseID,@DirectoryPathToFile, @UploadDate INTO
dbo.DocumentItems
VALUES (@ResourceName,@Description)
end
April 1, 2012 at 6:45 am
Wrap the whole thing in a transaction first. Then, use the OUTPUT clause to output to a temp table or a table variable (a table variable would probably work well in this instance) and then insert from there. You can't do it all as a single step. Finally, make sure you wrap it in TRY/CATCH statements so you can rollback the transaction in the event of an error.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2012 at 9:24 pm
My Table schema is shown below.On executing my SP i get error msg saying USerid is Null.How do I insert my Userid (foreign key) .Please help me with my SP.
Resource Table
ResourceDatabaseID (PK)
Userid (FK)
Resourcename
Descriptionofresource
DocumentItem Table
DocumentItemID (PK)
ResourceDatabaseID (FK)
DirectoryFilePath
Uploaddate
I'm trying the below stored procedure.
ALTER PROCEDURE InsertResource
(
@ResourceName nvarchar(256),
@Description nvarchar(256),
@DirectoryPathToFile nvarchar(256),
@UploadDate datetime2(7),
@resourcestatus nvarchar(256),
@Username nvarchar(256)
)
AS
declare @FileType nvarchar(max),
@FileName nvarchar(max),
@Proceed bit,
@resourceid uniqueidentifier,
@userid uniqueidentifier
set @userid =(select UserId from aspnet_Users where UserName= @Username)
IF @Proceed = 1
BEGIN
IF @FileType IN ('doc','pdf','txt')
insert into Resources
(
ResourceDatabaseID,
UserId,
ResourceName,
DescriptionOfResource,
ResourceStatus
)
values ( NEWID(),@UserId,@ResourceName,@Description,@resourcestatus)
SET @resourceid = (SELECT resourcedatabaseid FROM Resources WHERE ResourceName = @ResourceName)
insert into Documentitems
(
ResourceDatabaseID,
DocumentitemDatabaseID,
DirectoryPathToFile,
UploadDate
)
values (@resourceid,NEWID(),@DirectoryPathToFile,@UploadDate)
end
April 1, 2012 at 11:08 pm
I am not sure this will solve your problem because if @userid is NULL it can only mean you're not getting a match when you query it out of your users database.
However I would rewrite the SP this way to avoid the readback of the newly generated UID from your first table. You also seem to have forgotten that you need a BEGIN after your second IF statement as I believe you want to execute both INSERTs on the file type condition.
ALTER PROCEDURE InsertResource
(
@ResourceName nvarchar(256),
@Description nvarchar(256),
@DirectoryPathToFile nvarchar(256),
@UploadDate datetime2(7),
@resourcestatus nvarchar(256),
@Username nvarchar(256)
)
AS
declare @FileType nvarchar(max),
@FileName nvarchar(max),
@Proceed bit,
@resourceid uniqueidentifier,
@userid uniqueidentifier
select @resourceid = NEWID()
,@UserId = UserId from aspnet_Users where UserName= @Username
IF @Proceed = 1
BEGIN
IF @FileType IN ('doc','pdf','txt')
BEGIN
insert into Resources
(
ResourceDatabaseID,
UserId,
ResourceName,
DescriptionOfResource,
ResourceStatus
)
values ( @resourceid,@UserId,@ResourceName,@Description,@resourcestatus)
insert into Documentitems
(
ResourceDatabaseID,
DocumentitemDatabaseID,
DirectoryPathToFile,
UploadDate
)
values (@resourceid,NEWID(),@DirectoryPathToFile,@UploadDate)
END
end
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 2, 2012 at 4:52 am
If UserID is coming up NULL, then your problem is here:
set @userid =(select UserId from aspnet_Users where UserName= @Username)
Not with the other statements.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 2, 2012 at 8:32 am
Thank you.Your solution worked.Thank you so much.
April 3, 2012 at 12:54 pm
I"m back here.When I execute my SP I get it inserted but it throws me msg saying
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." on line56
insert into Resources(ResourceDatabaseID, UserId,ResourceName,DescriptionOfResource,ResourceStatus)
values (@resourceid,@UserId,@ResourceName,@Description,@resourcestatus)
SET @resourceid = (SELECT resourcedatabaseid FROM Resources WHERE ResourceName = @ResourceName)
insert into Documentitems(ResourceDatabaseID,DocumentitemDatabaseID,DirectoryPathToFile,UploadDate)
values (@resourceid,NEWID(),@DirectoryPathToFile,@UploadDate)
end
I've tried to fix it but no luck with that.
April 3, 2012 at 1:04 pm
bplvid (4/3/2012)
I"m back here.When I execute my SP I get it inserted but it throws me msg saying"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." on line56
insert into Resources(ResourceDatabaseID, UserId,ResourceName,DescriptionOfResource,ResourceStatus)
values (@resourceid,@UserId,@ResourceName,@Description,@resourcestatus)
SET @resourceid = (SELECT resourcedatabaseid FROM Resources WHERE ResourceName = @ResourceName)
insert into Documentitems(ResourceDatabaseID,DocumentitemDatabaseID,DirectoryPathToFile,UploadDate)
values (@resourceid,NEWID(),@DirectoryPathToFile,@UploadDate)
end
I've tried to fix it but no luck with that.
Well that message is pretty clear. It means that you have more than 1 row in your Resources table with the same name. Maybe you can change your subquery to use top 1? Maybe you need a better way to identify which row you want?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2012 at 1:28 pm
bplvid (4/3/2012)
I"m back here.When I execute my SP I get it inserted but it throws me msg saying"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." on line56
insert into Resources(ResourceDatabaseID, UserId,ResourceName,DescriptionOfResource,ResourceStatus)
values (@resourceid,@UserId,@ResourceName,@Description,@resourcestatus)
SET @resourceid = (SELECT resourcedatabaseid FROM Resources WHERE ResourceName = @ResourceName)
insert into Documentitems(ResourceDatabaseID,DocumentitemDatabaseID,DirectoryPathToFile,UploadDate)
values (@resourceid,NEWID(),@DirectoryPathToFile,@UploadDate)
end
I've tried to fix it but no luck with that.
Why are you querying for the resourcedatabaseid when you already have that value in @resourceid that you used in your first insert into statement?
April 3, 2012 at 1:39 pm
Lynn Pettis (4/3/2012)
bplvid (4/3/2012)
I"m back here.When I execute my SP I get it inserted but it throws me msg saying"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." on line56
insert into Resources(ResourceDatabaseID, UserId,ResourceName,DescriptionOfResource,ResourceStatus)
values (@resourceid,@UserId,@ResourceName,@Description,@resourcestatus)
SET @resourceid = (SELECT resourcedatabaseid FROM Resources WHERE ResourceName = @ResourceName)
insert into Documentitems(ResourceDatabaseID,DocumentitemDatabaseID,DirectoryPathToFile,UploadDate)
values (@resourceid,NEWID(),@DirectoryPathToFile,@UploadDate)
end
I've tried to fix it but no luck with that.
Why are you querying for the resourcedatabaseid when you already have that value in @resourceid that you used in your first insert into statement?
hehe I hadn't looked that closely. But now that I have...why are you using uniqueidentifier as the datatype for your primary key? I am guessing that is also your clustered index? This will cause you some performance problems as the tables get larger.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2012 at 1:43 pm
I'm a beginner in sql.Help me refine my SP.I know it has some issues in it.If there's a better soulution plz help me out here.
April 3, 2012 at 1:47 pm
Sure there are lots of people around here willing to help. First however you need to help us help you. We need ddl, sample data and desired output. Details about posting this can be found by reading the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 3, 2012 at 1:55 pm
IGNORE, I need to make a few more changes.
Here is my rewrite:
ALTER PROCEDURE InsertResource
(
@ResourceName nvarchar(256),
@Description nvarchar(256),
@DirectoryPathToFile nvarchar(256),
@UploadDate datetime2(7),
@resourcestatus nvarchar(256),
@Username nvarchar(256)
)
AS
BEGIN
declare @FileType nvarchar(max),
@FileName nvarchar(max),
@Proceed bit,
@resourceid uniqueidentifier,
@userid uniqueidentifier
set @userid =(select UserId from aspnet_Users where UserName= @Username)
IF @Proceed = 1
BEGIN -- Never get here, @Proceed is never initialiaze to anything.
IF @FileType IN ('doc','pdf','txt')
BEGIN
insert into Resources(
ResourceDatabaseID,
UserId,
ResourceName,
DescriptionOfResource,
ResourceStatus
)
output inserted.ResourceDatabaseID into @resourceid
values (
NEWID(),
@ResourceName,
@Description,
@resourcestatus
);
insert into Documentitems(
ResourceDatabaseID,
DocumentitemDatabaseID,
DirectoryPathToFile,
UploadDate
)
values (
@resourceid,
NEWID(),
@DirectoryPathToFile,
@UploadDate
);
END
END
END
April 3, 2012 at 2:06 pm
Updated code:
ALTER PROCEDURE InsertResource
(
@ResourceName nvarchar(256),
@Description nvarchar(256),
@DirectoryPathToFile nvarchar(256),
@UploadDate datetime2(7),
@resourcestatus nvarchar(256),
@Username nvarchar(256)
)
AS
BEGIN
declare @FileType nvarchar(max),
@FileName nvarchar(max),
@Proceed bit,
@resourceid uniqueidentifier,
@userid uniqueidentifier;
declare @ResourceData table (resourceid uniqueidentifier);
set @userid =(select UserId from aspnet_Users where UserName= @Username)
IF @Proceed = 1
BEGIN -- Never get here, @Proceed is never initialiaze to anything.
IF @FileType IN ('doc','pdf','txt')
BEGIN
insert into Resources(
ResourceDatabaseID,
UserId,
ResourceName,
DescriptionOfResource,
ResourceStatus
)
output inserted.ResourceDatabaseID into @ResourceData(resourceid)
values (
NEWID(),
@ResourceName,
@Description,
@resourcestatus
);
select @resourceid = resourceid from @ResourceData;
insert into Documentitems(
ResourceDatabaseID,
DocumentitemDatabaseID,
DirectoryPathToFile,
UploadDate
)
values (
@resourceid,
NEWID(),
@DirectoryPathToFile,
@UploadDate
);
END
END
END
Of course, this still needs work as you can see from a comment in the code.
April 3, 2012 at 2:43 pm
Lynn Pettis :
Thank you so much for the valuable help.It really helped me.Now I understood what went wrong.
Thanks to all who came forward to help me.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply