Insert With 1 Value Resulting From Another Query
Oct 22, 2007
I'm not exactly sure how to do this in MS SQL. Here is my current (broken) MSSQL insert statement:
Code:
cmd2.CommandText = "INSERT INTO User_Courses ('course_id','register_date','credit_date','userid') VALUES (" +
"@classID,@regdate,@creditDate," +
"(SELECT userid FROM Users WHERE guid=@guid2))";
Of course this doesn't work, as the SELECT statement might contain more than one value (which it doesn't, guid is a unique hash).
How do I execute this statment without breaking it into a separate command?
View 3 Replies
ADVERTISEMENT
Oct 23, 2014
I need to name make the name of a column the same as the name of a table from the result of a sql query.. here is the assignment question below..I can't figure out how to get the name of the table to be inputed as the column name..
Write a script that uses dynamic SQL to return a single column that represents the number of rows in the first table in the current database. The script should automatically choose the table that appears first alphabetically, and it should exclude tables named dtproperties and sysdiagrams. [highlight=#ffff11]Name the column CountOfTable, where Table is the chosen table name.[/highlight]
Hint: Use the sys.tables catalog view.
I can figure out the rest. and actually have alredy done it, but i cannot figure out how to do the part that is highlighted above. I looked at lots of things on google to figure it out but no luck..Can some just give me some directlon or an example..
View 14 Replies
View Related
Nov 25, 2005
have a SQL2K/VB.NET05 -based website that uses a complex search query, whose results will contain additional logic to be evaluated. There are thousands of records and growing, so it is not feasible to code this within the program...it must be evaluated inline or after the query, and it is also not feasible to set up additional fields and tables to handle the logic.
For a very general example: In the .NET code, the following variables are recognized:
sex="M"
Paid=4350.00
Outstanding=28000.50
One of the query result fields will contain the additional logic to evaluate and another will tell the type of expression..
EVAL EXPR
BOOL Sex='F' and Paid/Outstanding < 27.50
BOOL Sex='M' and Paid/Outstanding < 38 or Sex='F'
INT Paid*52.33
In other words..the thousands of records being returned have their own additional logic to evaluate. Is there a way this can be done by importing the variable into SQL server and testing it during the query?
If not, is there a way that I can run the code in the middle of .NET? I know I could run scripted code while in ASP, but ASP.NET is compiled, so I dont know if it can be done there....
View 3 Replies
View Related
Aug 30, 2007
Hello all:
Here is a sample query:
DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'
DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'
DECLARE @KeyCol NVARCHAR(50);
SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue NVARCHAR (1000)
SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +
' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '
The value resulting from Executing @KeyValue is an integer.
I want to store this value in a new variable say @VAR2
When I do this
DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue
its giving me an error.
can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?
View 3 Replies
View Related
Sep 20, 2007
Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks.
Here is my stored procedure:PROCEDURE [dbo].[spGetCompBulletins]
@Userid uniqueidentifier OUTPUT,@DisplayName varchar(200)
AS
SELECT *
FROM dbo.UserProfile INNER JOIN
dbo.bulletins ON dbo.UserProfile.UserId = dbo.bulletins.Userid INNER JOINdbo.Associations ON dbo.Associations.BusinessID = dbo.bulletins.Userid WHERE UserProfile.DisplayName=@DisplayName
and Userprofile.Userid = @Userid ORDER BY Bulletins.Bulletin_Date
Return
View 7 Replies
View Related
Oct 18, 2004
hi all,
I have a problem with SQL 2000 here...
I juz imported the same set of data to my database table and it gave me duplicated records as all the data is imported although there is same existing data.
Can anyone help me with this?
How can i import the data such that the data which already exists in the database will not be imported in again?
Thanks in advance.
View 4 Replies
View Related
Apr 1, 2004
I'm relatively new to SQL and I've come across something that doesn't seem quite right. When an insert becomes part of an transaction I notice an exclusive KEY lock in Enterprise Manager. The table in question was using a Clustered index but I changed that, dropped the table and brought it back in but I still get the lock which keeps all others out of the table. Is this the expected behavior or is there something I am missing? Could the size of the tabe affect things? This is a very small table currently. I'm using MSSQL 7 sp3.
Thanks,
John
View 10 Replies
View Related
Oct 19, 2007
I created a report that uses a parameter to return a record per page basically, but it doesn't return all records that match the parameter. Thoughts?
Specifically, I have a table that tracks various sites my engineers are responsible for; each have about twelve. The problem is that every time I select an engineer I only see six pages (records). When I select two or three, then I see 12 or 18 pages, but still just six records per engineer.
Any help is greatly appreciated! Neil
View 1 Replies
View Related
Oct 7, 2015
I have a report that is using the following expression for one of the fields:
=Split(Fields!FromAccount.Value, " ")(1)
The format of the number is xxxxx-xxx xxxxxxxxxxxx
So the function is to grab the second section of numbers after the space. The query for this report can bring back 1 to many results which equals one to many pages.
I have an issue where the query returns three results. The first page will display the correct number, the second page displays #Error and the third displays the number.
When I run the query from Management Studio I see the numbers are as follows:
xxxxx-xxx xxxxxxxxxxxx
xxxxx-xxx xxxxxxxxxxxx
xxxxx-xxx xxxxxxxxxxxx
Note the first and and last lines have extra spaces which I thought would be the cause of the problem though I would've expected the #Error to appear on the first and third page rather than the second one.
When I remove the extra space for the first and third number all three pages display their values correctly.
However, there are many, many numbers in the table that have one to two spaces. These numbers are from a spreadsheet that is imported biweekly. So it was either I fix the spreadsheet before every import or I come up with a new expression to check for one or two spaces. This is what I came up with:
=IIF (Fields!FromAccount.Value.Contains("Â "), Split(Fields!FromAccount.Value, "Â ")(1),Split(Fields!FromAccount.Value, " ")(1))
Now, the first page displays the number correctly while the second and third page displays with the #Error. Basically I want to say if the value contains two spaces then split at the two spaces otherwise split at the one space.
View 3 Replies
View Related
Jun 27, 2007
Hi €“ I hope someone can help,
I have developed a custom authentication extension to Reporting Services 2005, using Visual Studio 2005 C#.
In local integration tests the extension behaved as expected, honouring the role based security of our main system. Following the deployment steps laid out from numerous sources all worked perfectly. We were optimistic.
I've since deployed to a 'live' user acceptance staging server, using the same procedures used in Integration and it's behaving incorrectly.
Initially the log in page is displayed as expected, at this point SQL dumps occur in the RS Log directory, the same as is seen here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1750128&SiteID=1
Hopefully there'll be a resolution to that soon. As described in that topic, the main page of Report Manager appears.
After this it all goes wrong.
Sometimes we can browse to a folder and run a report. But after running the report, trying to run something else, or browse back to the containing folder we are presented with the error page with the message:
The permissions granted to user '' are insufficient for performing this operation.
Clicking on the home link brings up the top title and nothing else and you have no option but to close the site and re-login.
Sometimes you get the above message before you can even run a report. The error logs have the message listed at the end of this post.
Other times you log in to the website and the folders don't even appear, just the top title bar.
I have since added some verbose logging in each of the implemented methods in the extension DLL. The permission checking method return true and all the various data in each method appears as expected.
I have checked, checked again and re-checked all the configuration files, they match the local Integration files, barring the machine specific keys etc, they weren't just overwritten.
The location of the log directory where my internal logs are written is read from the configuration files via the SetConfiguration methods, if that wasn't set correctly then there wouldn't be any logs at all, so the configuration is being read correctly by the extension.
One other think that I've noticed is that the back door user that is configured in the config files works perfectly, but I can't see how this can make any sort of a difference as it returns the same result in the extension DLL as a 'normal' user does at the same point in the code.
Can someone please help me, and my poor scalp, it's losing hair at a rate of knots.
Set up:
Windows Server 2005
SQL Server 2005 Service Pack 2 (Developer)
IIS 6
Error message in the log files:
w3wp!library!1!27/06/2007-10:04:20:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation.
w3wp!ui!1!27/06/2007-10:04:20:: e ERROR: The permissions granted to user '' are insufficient for performing this operation.
w3wp!ui!1!27/06/2007-10:04:20:: e ERROR: HTTP status code --> 500
-------Details--------
Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user '' are insufficient for performing this operation.
w3wp!ui!1!27/06/2007-10:04:20:: e ERROR: Exception in ShowErrorPage: System.Threading.ThreadAbortException: Thread was being aborted.
at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg) at at System.Threading.Thread.AbortInternal()
at System.Threading.Thread.Abort(Object stateInfo)
at System.Web.HttpResponse.End()
at System.Web.HttpServerUtility.Transfer(String path, Boolean preserveForm)
at Microsoft.ReportingServices.UI.ReportingPage.ShowErrorPage(String errMsg)
View 3 Replies
View Related
May 16, 2007
Hello:
I have designed a CV database with complete CV stored in a TEXT field. There is a keyword search which queries the TEXT field also. The query conditions are defined in T-SQL submitted through an ASP page. There is about 20,000 records now. Now while querying the database for keyword search I am receiving time out errors. Is there any solution other than Index server to rectify this situation. How can I speed up the query execution time. Please advise.
Rgds
pooja
View 3 Replies
View Related
Jul 4, 2007
All,
I have two reports. One is the main/summary report and other one is drill through. When I pass the Start and End Date parameters from main to the drill, the original format of DateTime changes. For example, in main report the data is displayed for following date range:
4/7/2007 - 5/9/2007 (i.e 4 July 2007 to 5 Sept 2007)
which displays correct data.
However, when I click on the drill through link, it jumps to the drill through report but displays data for the following period:
7/4/2007 - 9/5/2007 (i.e. 7 Apr 2007 to 9 May 2007)
The reporting services is converting the value from one format to another of the report parameters when passing them from parent report to the drill through. When run individually, these two reports display data for correct date range. And you can imagine, the child report crashes with rsReportParameterTypeMismatch error if the start or end date had a day part greater than 12 (e.g 25/4/2007).
I can't understand what could be going wrong. All the parameters in both reports are datetime, so intrisically, it shouldn't matter even if the reporting services is converting or using different date formats as long as the data type remains the same. Is there a way to fix this and force the parameters to stay in the format they are provided in the main report?
Your help is much appreciated.
Thanks.
View 5 Replies
View Related
Dec 12, 2007
While recently working with several mining models, I came across something that struck me as pretty odd - and I'm hoping to find an explanation for the behavior.
Consider the following setup:
A single table in the relational database represents the only case table
A single, continuous column is the predictable
A mining structure has been created
The mining structure contains a single model, based on the MS Decision Trees algorithm
Input columns were selected for the model via the BI Studio wizard (i.e., those provided via the "Suggest" button)
The structure has been fully processed
Now, the interesting parts:
I view the scatterplot for the mining model, under the Mining Accuracy Chart tab
Back on the Mining Structure tab, I delete one of the input columns
I add the same column back into the structure
The structure is fully processed again
When I view the scatterplot for the mining model, under the Mining Accuracy Chart tab, a different set of data points are presented for the model predictions
A different set of decision trees under the Mining Model Viewer tab confirms thisHow could different patterns have been found this second time around, even though all of the input columns were the same (as well as the training cases)?
(Note: I encountered this situation while creating a new mining model that was identical to an existing one. Even though the models received the exact same inputs and training cases, they yielded different results. I was able to reproduce the behavior by using steps 1-6 above, though.)
Can someone provide some insight on this behavior, or some kind of explanation of what may be happening?
Thanks,
Joe Miller
View 3 Replies
View Related
Apr 11, 2008
Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
View 3 Replies
View Related
Mar 1, 2007
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end)
scenario
In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table.
I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
View 2 Replies
View Related
Jan 21, 2007
i want to implement something like let say i have 2 table...customer table and order table....order table has a foreign key of customer table (maybe the customer_id)...is there any way that let say, i want to insert a particular customer_id in the customer table. Then, it will insert the particular customer_id in the order table also. I want to make one statement query that can solve that situation?
View 4 Replies
View Related
Mar 15, 2007
my application will add and delete and update records in db
my problem is when to insert
I have one text box and one dropdownbox one to write the name of db and the dropdownbox to choose the holding server ..
this is the structure of each table >>
servers_tbl : SRV_ID,Server_Name
DB_tbl : DB_ID,DB_Name
srvdb_tbl : DB_ID,SRV_ID(forign keys from the previous tables)
so >>>
I want to add a new db to a server
so I am writing the new db name in the textbox and choose the server from the dropdownbox and press a button to add the db name in the DB_tbl.DB_Name and add the db id in the DB_tbl.DB_ID to the srvdb_tbl.DB_ID and server id in the Servers_tbl.SRV_ID
any one can help me ....
View 1 Replies
View Related
Jan 10, 2008
Hi All!
I really need to pick your brains for a moment. I am attempting to create a INSERT INTO query that will select recrods from another existing table. This part can figure out...
INSERT INTO "table1" ("column1", "column2")SELECT "column1", "column2"FROM "table2"
Easy enough, but my table1 contains additional fields that the select statement does not retrieve from table2 (lets say "column3" and "column4"). I want to statically assign values to column3 and 4 at the same time.
"column3" = Yes
"column4" = No
is there a way to do this within the same insert statement?
Thanks for all your help!
View 5 Replies
View Related
Apr 3, 2008
can somebody help me in this plz m new to .net...m using visual studio 2005 how do we write this query actually...Dim con As SqlConnection = New SqlConnection("Server=;uid=;pwd=;Database=") Dim ra As Integer con.Open() myCommand = New SqlCommand("Insert into Telecheck(DateCheck,Hospital,Ward,PhoneNumber,StaffName,StatusOfStaff,StatusOfPhone)values(getdate(),'" & ComboBox1.SelectedItem.text & "','" & ComboBox3.SelectedItem.text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & ComboBox4.SelectedItem.text & "','" & ComboBox2.SelectedItem.text & "') ", con) ra = myCommand.ExecuteNonQuery() MessageBox.Show("Record entered") con.Close() the above is the code for windows here i am getting an error which says "{"Public member 'text' on type 'String' not found."}"the type of database is sqlserver 2000 and the type of column are nvarchar,only date is datetime
View 3 Replies
View Related
Jun 14, 2008
Declare @UID intDeclare @FID varchar(50)set @UID = 1set @FID = '1,2,3'insert into table_name (UID,FID) values(@UID,'+@FID+') Can we have an insert query like above or is their another way to get the result like below the result should beUID FID 1 1 1 2 1 3
View 2 Replies
View Related
Dec 1, 2005
I am needing to insert two values from tables, one would be from a query, which is easy, one needs to be static, easy. The not easiness (not a word) comes from combining the two. Here would be an example of what I am trying to run...
Code:
insert into MEMBER_SUBSCRIPTION_CHANNEL_FORMAT (MEMBER_ID,SUBSCRIPTION_CHANNEL_FORMAT_ID)
(select m.MEMBER_ID from MEMBER m,MEMBER_SUBSCRIPTION ms where m.MEMBER_ID = ms.MEMBER_ID and m.SUBSCRIPTION_FORMAT_ID = 1 and ms.SUBSCRIPTION_ID = 1), 1
the last part of the code ', 1' would be the static part. Any ideas?
View 1 Replies
View Related
Dec 3, 2004
Hi guys I need to insert some updated Terms from another table but it keeps telling me that it cant because it would be creating duplicates. The funny thing about it is I did a comparison with the two tables one table is called UpdateForTerms Table and the other is called TERMINATION
SELECT UpdateForTerms.EMPLOYEE, UpdateForTerms.EMP_STATUS, UpdateForTerms.TERM_DATE, UpdateForTerms.DEPARTMENT, UpdateForTerms.LAST_NAME, UpdateForTerms.FIRST_NAME, UpdateForTerms.DESCRIPTION
FROM UpdateForTerms LEFT JOIN TERMINATION ON UpdateForTerms.EMPLOYEE = TERMINATION.[TM #]
WHERE (((TERMINATION.[TM #]) Is Null));
I made the results of this into a query so I could update the Termination table. The reason I created this query is so that I could weed out those records the Terminations table already had. Kind of a futile attempt it seems
View 5 Replies
View Related
Apr 28, 2008
Hi All,
I've been going around in circles and was wondering if anyone could help with this insert query. (I'm using SQL Server 2005).
I have two tables aspnet_Users and aspnet_Draws. They have a 1 to many relationship, with UserID being the PK in aspnet_Users and the FK in aspnet_Draws. Table structure is:
aspnet_Draws
ID (PK)
UserId
DrawDate
NumberOfEntries
aspnet_Users
UserId (PK)
UserName
I want to insert a row into aspnet_Draws. UserID needs to be taken from aspnet_Users where UserName is a particular value, e.g. NameXXX
DrawDate and NumberOfEntries do not allow nulls and also need to be updated, though these values will be taken from a webForm and not from aspnet_Users. ID is automatically incremented.
Any help or advice would be greatly appreciated.
Cheers,
Jon
View 11 Replies
View Related
May 1, 2008
Hi. I have 2 tables, this structure (basically);
Code Title
1 AAAAA
2 BBBBB
3 CCCCC
4 DDDDD
Code Title
1 AAAAA
2 BBBBB
Basically, what is the best method, ie. not in, left join, etc to write this insert query? I feel like I know this, but mostly I want to make sure I am using the most efficient way. There are 1.1M records in the first table, named Complete_Products, and 979K in tblProducts. I already ran the update query that selected on the ProductCode column in both that updated tblProduct for the records that were also in Complete_Products. That ran fine, a bit long, but it worked and updated the 805,273 records they had in common. Now I need to insert the new records that are in Complete_Products that are not in tblProducts.
Thanks so much for your help in advance.
View 3 Replies
View Related
Sep 1, 2005
Hello friends!
I want to know weather we can insert more than 1 records from a single INSERT query in query analyzer?
If yes please tell me how to..
Thankyou.
View 1 Replies
View Related
Sep 20, 2005
use Dacari
go
IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.InsITRRComments
IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.InsITRRComments >>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.InsITRRComments >>'
END
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.InsITRRComments
@CommentId int,
@DacType varchar(25),
@DacTypeId int,
@TypeOfComment varchar(50),
@Comments varchar(4000),
@RiskId_External_ID varchar(16),
@UserId varchar(25),
@FullUserName varchar(50),
@AmendedDateTime datetime,
@ProcessedFlag char(1)
AS
BEGIN
/*************************************************************************
*
* Name: InsITRRComments
*
* Description: Insert new Term Type item.
*
* Inputs:
* @CommentId
* @DacType
* @DacTypeId
* @TypeOfComment
* @Comments
* @RiskId_External_ID
* @UserId
* @FullUserName
* @AmendedDateTime
* @ProcessedFlag
*
* Outputs: None
*
*
* Notes:
*
* History:
* Date Author Changes
* ------------------------ -------------------------------------------
* 16 Sept 05xxxxxxxxxxxxxx Original
*************************************************************************/
---------------------------------------------------------------------------
INSERT INTO ITRRComments
VALUES (
@DacType,
@DacTypeId,
@TypeOfComment,
@Comments,
@RiskId_External_ID,
@UserId,
(select FullUserName from SecurityUsers where UserId=@UserId)
@AmendedDateTime,
'N'
)
END
GO
IF OBJECT_ID('dbo.InsITRRComments') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.InsITRRComments>>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.InsITRRComments>>>'
go
GRANT EXECUTE ON dbo.InsITRRComments TO AppWrite
go
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Subqueries are not allowed in this context. Only scalar expressions are allowed
This is the error that I'm getting, how do I get round the issue of a sub query problem
View 9 Replies
View Related
Feb 8, 2006
How do I use Enterprise manager to insert records from one table into a blank table ? I open my table (with data) as a query and change it to an Insert From query but it doesn't seem to build the query correctly.
View 15 Replies
View Related
Nov 11, 2006
Hello
I have a table consisting of Date,Particulars,Debit,Credit columns.I executed the following query to insert the values.
insert into balancesheet(Particulars,Credit) VALUES('Salary',4917.00)
Iam getting an error
String or binary data would be truncated.
The statement has been terminated.
Plz help me out
Thanks in advance
Poornima
View 4 Replies
View Related
Dec 21, 2006
i am running a pretty crazy query with a couple on inner joins
lets say it returns 20 results that i then want to insert into a different table
how do i do this -- is there a way to do it with out cursors?
View 15 Replies
View Related
Feb 26, 2007
Shiva writes "Hi
I want to insert a new row in a table after any specified row and when i open the table in enterprise manage that row must be physically located on specified position.
i want to do it only through the query. no stored procedures or triggers.
Can u help me out plz?"
View 1 Replies
View Related
Mar 23, 2007
I need three insert query into only one single query at a time .It's possible...
View 13 Replies
View Related
Apr 18, 2007
Hi
I want to create a temporary table from a select statement
but sql server 2005 give error - invalid object name?
like:
INSERT INTO #TempTable (SELECT f.code,c.name FROM FORMNAMEMASTER f inner join formname c on f.code=c.code)
Pls guide how to do this...
View 1 Replies
View Related
Dec 10, 2007
Hi i have a stored procedure, i need to find out if a record exsists then if not then insert so i have this code, just not sure of the syntax.
/****** MATCH UP ADDRESS ID TO THE F4 RENTAL ******/
SELECT COUNT(R_Web_Add_ID)AS NewR_Web_Add_ID
FROM TBL_RENTAL
WHERE R_Web_Add_ID = @R_Add_ID
/****** IF MATCH IS LT 1 THEN INSERT ADDRESS INTO RENTAL ******/
if NewR_Web_Add_ID < 1 then
insert statement here
end if
View 7 Replies
View Related
