'Insert Into..' Select Outputput From Stored Procedure
Dec 11, 2007
In a stored procedure, I've created a temp table, and am trying to populate it using:
insert #myTable
exec p_someProc
p_someProc returns 2 record sets, the first is the one I want to stuff into #myTable, the second is a single field.
I think that is the reason that I'm getting:
"
Insert Error: Column name or number of supplied values does not match table definition.
"
#myTable has the same structure as the first record set. Is that second recordset causing the error, if so, how to I either incorporate it into the temp table or just ignore it?
So I have simply procedure: Hmmm ... tak sobie myślę i ...
Czy dałoby radę zrobić procedurę składową taką, że pobiera ona to ID w ten sposob co napisalem kilka postow wyzej (select ID as UID from aspnet_users WHERE UserID=@UserID) i nastepnie wynik tego selecta jest wstawiany w odpowiednie miejsca dalszej procedury ?[Code SQL]ALTER procedure AddTopic @user_id int, @topic_katId int, @topic_title nvarchar(256), @post_content nvarchar(max)as insert into [forum_topics] (topic_title, topic_userId,topic_katId) values (@topic_title, @user_id, @topic_katId) insert into [forum_posts] (topic_id, user_id,post_content) values (scope_identity(), @user_id, @post_content)Return And I want to make something more. What I mean - in space of red "@user_id" I want something diffrent. I make in aspnet_Users table new column uID which is incrementing (1,1). In this way I have short integer User ID (not heavy GUID, but simply int 1,2,3 ...). I want to take out this uID by this :SELECT uID from aspnet_Users WHERE UserId=@UserId.I don't know how can I put this select to the stored procedure above. So I can have a string (?) or something in space of "@user_id" - I mean the result of this select query will be "@user_id". Can anyone help me ? I tried lots of ways but nothing works.
hi need help do this i have table that generate Serial i get the code from this link http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2734123&SiteID=1
and i must to get the same sn + date so whan i do this
Code Snippet select * from tbl_1a WHERE (CONVERT(DATETIME, CONVERT(VARCHAR(16), dateinb, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(16), CONVERT(DATETIME, '05/05/2008 14:51'), 120))) and sn=0000009 select * from tbl2_2B WHERE (CONVERT(DATETIME, CONVERT(VARCHAR(16), dateinb, 120)) = CONVERT(DATETIME, CONVERT(VARCHAR(16), CONVERT(DATETIME, '05/05/2008 14:51'), 120))) and sn=0000009
i get the current state DATE + SN
i need to insert into other 2 table this Serial+date like this --need help fix this code
Code Snippet DECLARE @sn DECLARE @date set @date =Getdate() -- insert 1 new records tb sn INSERT INTO ConfirmationNumber([ConfirmationDate]) SELECT GETDATE() ---select last value sn set @sn = SELECT top 1 SequenceNumber FROM vw_ConfirmationNumber ---- get the last sn order by SequenceNumber desc --insert into tb1 insert into tbl_1a (fld1,fld2,sn,date) select from tbl_2a fld1,fld2,@sn,@date --insert into tb2 insert into tbl2_2B (f1,f2,date) select from tbl2_1B f1,f2,@sn,@date
I have a list of articles stored in my sqlserver 2000 db. To get all the articles I have a stored procedure which just contains a simple select statement. When a user click to download an article, I want to increase the download count. I can do this with a separate stored procedure with an insert statement and passing in the document id, but I'm sure I could do it within one stored procedure, just not sure how. This is my current sp for getting the articles.CREATE PROCEDURE [sp_GetArticles] ASSELECT [DocumentID], [Title], [Description], [Downloads], [UploadDate], [Filesize] FROM Documents WHERE CategoryID = 1
Hi All, As known its recommended to use stored procedures when executing on database for perfermance issue. I am thinking to create 4 stored procedures on my database (spSelectQuery, spInsertQuery, spUpdateQuery, spDeleteQuery) that accept any query and execute it and return the result, rather than having a number of stored procedures for all tables? create PROCEDURE spSelectQuery ( @select_query nvarchar(500) ) as begin
exec sp_executesql @select_query, N'@col_val varchar(50) out', @col_val out
I would like to have a stored procedure executed once a week via a DTS package. The data I would like inserted into Table_2, which is the table where the DTS is being executed on, comes from a weekly dump from Oracle into a Table_1 via another DTS package.
I would like to only import data since the last import so I was thinking of my logic to be like this:
INSERT INTO Table_2 (Field1, Field2, ... , FieldN) VALUES (SELECT Field1, Field2, ... , FieldN FROM Table_1 WHERE ThisDate > MAX(Table_2.ThatDate))
Does this make sense? Or do you all suggest a different mannger of accomplishing this?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
Hi, i'm an SQL newbie. I'm trying to figure out if there's an easy way to take a single field record set from a SELECT statement and then do an INSERT using that record set, all in one single Stored Procedure.
Here's what i tried to do, but this returns an error "The name "phonenumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.".
The common field in both SELECT and INSERT is phonenumber.
quote:PROCEDURE [dbo].[usp_select_and_insert]
@name varchar(20),
AS
SELECT phonenumber FROM USERLIST where OWNERNAME LIKE @name INSERT INTO LOGLOG (destination,content) values(phonenumber,'hello world');
GO
Hope that one of you can be kind enough to give me some guidance. Appreciate in advance. :)
Hi, I am having trouble inserting 2 fields in a row using a stored procedure. This works fine: Exec ('Insert Into NumbersPull (Number)'+ @SQL) but when I try to insert another value into field 2 it errors out: I try this: Exec ('Insert Into NumbersPull (Number,resultID) Select ('+ @SQL + '),' + @resultID' ) and get this error: ERROR: Line 2: Incorrect syntax near ')'. Thanks, Doug
Having problem do INSERT values to my SQL DB with an StoredProcedure. SELECT works fine. My StoredProcedure : CREATE PROCEDURE insert_test @id int ,@Rubrik char(25), @Info char(60) , @Datum datetime ASINSERT INTO test_news(ID, Rubrik, Info, Datum) VALUES (@id, @Rubrik, @Info, @Datum)GO The StoredProcedure works fine in the SQL Query Analyzer
My Code; int num= 1234; string rub = "KLÖKÖLKÖLKÖL"; string ino = "slökdjfkasdkfjsdakf";SqlConnection myConnection = new SqlConnection("server='SOLDANER\DAER'; trusted_connection=true; database='SeWe'"); SqlCommand myCommand = new SqlCommand("insrt_test", myConnection); myCommand.CommandType = CommandType.StoredProcedure; myCommand.Parameters.Add( "@id",num);myCommand.Parameters.Add( "@Rubrik",rub );myCommand.Parameters.Add( "@Info", ino);myCommand.Parameters.AddWithValue( "@Datum", DateTime.Now ); Even tried AddWithValues Dont get any error.....
OK I have a stored procedure that inserts information into a database table. Here is what I have so far: I think I have the proper syntax for inserting everything, but I am having problems with two colums. I have Active column which has the bit data type and a Notify column which is also a bit datatype. If I run the procedure as it stands it will insert all the information correctly, but I have to manually go in to change the bit columns. I tried using the set command, but it will give me a xyntax error implicating the "=" in the Active = 1 How can I set these in the stored procedure?1 SET ANSI_NULLS ON 2 GO 3 SET QUOTED_IDENTIFIER ON 4 GO 5 -- ============================================= 6 -- Author:xxxxxxxx 7 -- Create date: 10/31/07 8 -- Description:Insert information into Registration table 9 -- ============================================= 10 ALTER PROCEDURE [dbo].[InsertRegistration] 11 12 @Name nvarchar(50), 13 @StreetAddress nchar(20), 14 @City nchar(10), 15 @State nchar(10), 16 @ZipCode tinyint, 17 @PhoneNumber nchar(20), 18 @DateOfBirth smalldatetime, 19 @EmailAddress nchar(20), 20 @Gender nchar(10), 21 @Notify bit 22 23 AS 24 BEGIN 25 -- SET NOCOUNT ON added to prevent extra result sets from 26 -- interfering with SELECT statements. 27 SET NOCOUNT ON; 28 29 INSERT INTO Registration 30 31 (Name, StreetAddress, City, State, ZipCode, PhoneNumber, DateOfBirth, EmailAddress, Gender, Notify) 32 33 VALUES 34 35 (@Name, @StreetAddress, @City, @State, @ZipCode, @PhoneNumber, @DateOfBirth, @EmailAddress, @Gender, @Notify) 36 37 --SET 38 --Active = 1 39 40 END 41 GO
I'm trying to make sure that a user does not allocate more to funds than they have to payments. Here is what my stored procedure looks like now: I listed th error below ALTER PROCEDURE [dbo].[AddNewFundAllocation] @Payment_ID Int,@Receipt_ID Int,@Fund_ID Int,@Amount_allocated money,@DateEntered datetime,@EnteredBy nvarchar(50)ASSELECT (SUM(tblReceiptsFunds.Amount_allocated) + @Amount_allocated) AS total_allocations, Sum(tblReceipts.AmountPaid) as total_paymentsFROM tblReceiptsFunds INNER JOIN tblReceipts ON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Payment_ID=@Payment_IDIF (total_allocations<total_payments)INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)
ELSE BEGIN PRINT 'You are attempting to allocate more to funds than your total payment.' END I get the following error when I try and save the stored procedure: Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26 Invalid column name 'total_allocations'. Msg 207, Level 16, State 1, Procedure AddNewFundAllocation, Line 26 Invalid column name 'total_payments'.
I'm trying to insert the details in the "registration form" using stored procedure to my table. My stored procedure is correct, but I dunno what is wrong in my code or I dunno whether I've written correct code. My code is below. Please let me know what is wrong in my code or my code is itself wrong..... protected void RegisterSubmitButton_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Server=ACHUTHAKRISHNAN; Initial Catalog=classifieds;Integrated Security=SSPI"); SqlCommand cmd; cmd = new SqlCommand("registeruser", conn); SqlParameter par = null; par= cmd.Parameters.Add("@fname", SqlDbType.VarChar, 30); par.Value = RegisterFirstNameTextBox; par = cmd.Parameters.Add("@lname", SqlDbType.VarChar, 30); par.Value = RegisterLastNameTextBox; par = cmd.Parameters.Add("@uname", SqlDbType.VarChar, 30); par.Value = RegisterUserNameTextBox; par = cmd.Parameters.Add("@pwd", SqlDbType.VarChar, 20); par.Value = RegisterPasswordTextBox; par = cmd.Parameters.Add("@email", SqlDbType.VarChar, 40); par.Value = RegisterEmailAddressTextBox; par = cmd.Parameters.Add("@secque", SqlDbType.VarChar, 50); par.Value = RegisterSecurityQuestionDropDownList; par = cmd.Parameters.Add("@secans", SqlDbType.VarChar, 40); par.Value = RegisterSecurityAnswerTextBox; try { conn.Open(); cmd.ExecuteNonQuery(); } catch (Exception ex) { throw new Exception("Execption adding account. " + ex.Message); } finally { conn.Close(); } }
Hi, I have two tables "people' and "dept". What I need is a stored procedure to insert into both tables. I need first, insert into "people" table, and then, insert into "dept" table since the first insert returns people id(peo_id), which is an input parameter for "dept" table. Here is my stored procedure, but I got error:Create PROCEDURE [dbo].[insert_people] @peo_last_name varchar(35), @peo_mid_initial varchar(1) = null,@peo_first_name varchar(10), @peo_address1 varchar(50) = null, @peo_city varchar(30) = null, @peo_state varchar(2) = null, @peo_zip varchar(10) = null, @peo_ph1 varchar(30) = null, @peo_ph2 varchar(30) = null, @peo_email varchar(40) = null, @dept_id int, @peo_id int
AS SET @peo_id = (INSERT INTO people (peo_last_name, peo_mid_initial, peo_first_name, peo_address1, peo_city, peo_state, peo_zip, peo_ph1, peo_ph2, peo_email) VALUES (@peo_last_name, @peo_mid_initial, @peo_first_name, @peo_address1, @peo_city, @peo_state, @peo_zip, @peo_ph1, @peo_ph2, @peo_email)) INSERT INTO dept (dept_id, peo_id) VALUES (@dept_id, @peo_id) GO Could somebody help out? Thanks a lot!
I have a table with UserID, UserName, UserPassword I have a stored procedure as follows:ALTER PROCEDURE UserInsert @UserName varchar(50), @UserPassword varchar(50) AS BEGIN INSERT Users (UserName, UserPassword) VALUES (@UserName, @UserPassword) END I have a GridView bound to the Users Table and seperate textboxes (UserName, UserPassword) on a webform. Couple of Questions... 1. how and/or what code do I use to execute the Stored Procedure to insert what is in the textboxes into the Table using a button click event? 2. Since UserID is autogenerated on new records....does UserID need to be in the code? Many Thanks
Hi, I need to insert a new user if the user (user_login) does not exist in the table (abcd_user) using a stored procedure. I created a stored procedure called "insert_into_abcd_user". Here is the complete strored procedure... CREATE PROCEDURE [dbo].[insert_into_abcd_user] ( @first_name [VARCHAR](30), @last_name [VARCHAR](30), @email [VARCHAR](60), @user_login [VARCHAR](50)) AS INSERT INTO [dbo].[abcd_USER] ([first_name], [last_name], , [user_login])VALUES (@first_name, @last_name, @email, @user_login) I need to to insert a new user if the user (user_login) does not exist int the table (abcd_User). Any one shade on my code? I appreciate your help in advance.
INSERT INTO Table_1 SELECT Source_Table.Field_1, Source_Table.Field_2, Source_Table.Field_3, ???? OUTPUT parameter returnet from a stored procedure ??????? FROM Source_Table
I have created a stored procedure which simply inserts two records into a table. Here is my stored procedure:- //BEGIN ALTER PROCEDURE [dbo].[pendingcol] @cuser varchar(100) AS Declare @sqls nvarchar(1000)
This is the code i am using to call my stored procedure using VB.NET:- //BEGIN 'variables Dim user As String user = Profile.UserName
'connection settings Dim cs As String cs = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|friends.mdf;Integrated Security=True;User Instance=True" Dim scn As New SqlConnection(cs)
'parameters Dim cmd As New SqlCommand("pendingcol", scn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@cuser", SqlDbType.VarChar, 1000) cmd.Parameters("@cuser").Value = user
'execute scn.Open()
cmd.ExecuteNonQuery()
scn.Close() //END
Now when i execute this code i get an error point to cmd.ExecuteNonQuery() that says " The name "recordone" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. "
As far as i can see theres nothing wrong with the VB code, im guessing that the problem lies somewhere in my stored proc!
Can anyone please enlighten me on where i may be going wrong? Cheers
Hi, I want to set the max and min of a query to the variable @Value1 and @Value2. However, when I do the following, both @Value1 and @Value2 pointed to the latest data. Do I have to use a subquery to do that or is that possible to do it directly? DECLARE @Value1 DECIMAL(7,2) DECLARE @Value2 DECIMAL(7,2) SELECT TOP 20 @Value1=MIN(Value1), @Value2=MAX(Value2)FROM OrdersGROUP BY OrderID, Date
I have the stored procedure snipet below and it does not return anything. What am I doing wrong here? I works ok if I need all the records for the dataset and don't use the WHERE and LIKE. CREATE PROCEDURE dbo.search @Field VARCHAR (20), @KeyWord VARCHAR (200), @errCode INT OUTPUT ASBEGIN DECLARE @guid uniqueidentifier DECLARE @subject NVARCHAR SELECT @guid = guid, @subject = subjectFROM myTable WHERE @Field LIKE @KeyWord SELECT guid, subjectFROM myTable WHERE @Field LIKE @KeyWord
In Informix you can use a stored procedure as part of a SELECT statement:
SELECT ord_num, ord_date, ship_date, business_day_diff(ord_date, ship_date) FROM order_table
business_day_diff is a stored procedure that uses a custom calendar to compute business days. In fact, Informix stored procedures can return any data type. Apparently SQL Server stored procedures cannot be used in a SELECT like this.
Does anyone know of a straightforward way to accomplish the same purpose?
How do you use the value of one select statement within another select statement inside the same stored proc?
Here is my scenario: I need a row id from one select statement to make another select statement. I figured instead of making two calls from within my code I could just call one stored procedure which would have to be faster!
I have written plenty of select statement sp's but this is the first time I ever needed to do something like this.
How could I use SELECT TOP in stored procedure For ex I want convert this line to Stored procedure
"Select Top "&intArticles&" * From tblArticles WHERE published = 1 ORDER BY Adate DESC, Atime DESC;"
I dont want use ROWCOUNT because its not working good it will not show some data of some fields for example if I have Subject and Body fields, in results it does not show body content, and it will show just subject :confused:
this is my current code
CREATE PROCEDURE [dbo].[SP_Articles] ( @Articles int ) AS SET ROWCOUNT @Articles SELECT tblArticles.* FROM tblArticles WHERE published = 1 ORDER BY Adate DESC; SET ROWCOUNT 0 GO
Hey all! I have a quick question. Is it possible do formulate a select query where one of the tables is a recordset returned from a stored procedure? If so, what's the syntax?
In other words, if I have a stored proc whose last command is:
selectt.[Region_Code], t.[Country_Code], sum([Total]) as [Total] from#tmp t group by[Region_Code], [Country_Code] order by[Region_Code], [Country_Code]
Then can I somehow get a handle on that cursor and join it to another table etc? Thanks!
I have a stored procedure that has many select statements in it. I want to call and execute the procedure in my c# code and put data returned by each select statement into separate grids. I could just write the select into the c# code and then execute as a dataset making that dataset the datasource for whatever grid but that defeats the purpose of stored procedures. Can I capture the data returned by each select in the stored procedure and have it put into a grid when the c# code is ran.
HelloI have many different tables, that most have the same structure.. they allhave some primary key, some fields and mylinkid ... so what I want to makeis to create a stored procedure, where I can pass the table-name, primarykey, and the new mylinkid, what should happen is, that the procedure copiesthe entrie, where i passed the id, to a new row and the mylink-id should getthe new value...example:mytable 1id=1 (primary key autoincrement)mylinkid=233field1=asdffield2=blablai call my procedure like: duplicate_entry 'mytable',1,4the result should then beid=2 (primary key/autincrement)mylinkid=4field1=asdffield2=blablaI was thinking to make a Stored Procedure, that copies the entier row with aselect * into mytable (SELECT * from mytable where id=1) and afterwards iupdate the mylinkid ..... but there i have problem with the primary key.The other solution that will work, but I won't like is to get with thesp_fields all fields from that specific table, build the select into withthe correct fields without the id-field... that should work, but was askingmyself if there is somethign better...Has someone a good idea for that?Thanks
I have code simililar to the following in a stored procedure:
CREATE PROCEDURE GetGroupMembers ( @GroupID INT )
AS SELECT TMembers.MemberID, VCurrentMemberGroups.GroupID, THonorsMembers.HonorID FROM THonorsMembers LEFT OUTER JOIN TMembers ON THonorsMembers.MemberID= TMembers.MemberID LEFT OUTER JOIN VCurrentMemberGroups ON TMembers.MemberID= VCurrentMemberGroups.MemberID WHERE (VCurrentMemberGroups.GroupID = @intGroupID) AND TMembers.DeleteDate IS NULL ORDER BY TMembers.MemberID
RETURN GO
When I call this stored procedure in Query Analyzer, it takes two minutes to execute. The web pages that rely on it time out before that. If I copy just the select statement and substitute the group I'm working with for @GroupID, it takes less than a second for the statement to run and display results.
Can anyone tell me why a select statement called through a stored procedure would take minutes longer than one ran from Query Analyzer? I have many other stored procedures that are just select statements like this with a variable or three and they have no problems completing execution in a timely manner.
I have a complex stored procedure that returns one or more nvarchar values given a syntax similar to this:
sp_MyProcedure 'Param1', 'Param2'
My issue is that the end goal is to get these values into a SQL query that I can call directly from something like Excel (no VBA or anything can be used). So far, I have come up with this:
INSERT INTO [#tmpTable] EXEC sp_MyProcedure 'Param1', 'Param2'
SELECT * FROM AnotherTable
WHERE (AnotherTable.ID IN (select * from [#tmpTable])) AND (AnotherTable.Year>2007)
drop table [#tmpTable] ********************************************************************************************************
That works, but it is incredibly sloppy once I have need to make 4 or 5 temp tables like that and then select from all of them inside the same query. So does anyone have any suggestions? Can I use a function to help out?
