How to save the SP result?

Hi!
How can i save the resultant recordset generated by an Stored Procedure to
another table (with the same structure) ?
I think its some like INSERT * INTO tmptable FROM **Exec mySProc**
Can you help my?
Thanxs, Gabriel.Hi Gabriel
You're close...
You just don't need the * or the FROM.
Make sure the Stored Proc only returns one set of rows.
INSERT INTO tmptable
EXEC myProc
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gabriel South" <gsouth@.hotmail.com> wrote in message
news:Ore5y9iBFHA.2984@.TK2MSFTNGP11.phx.gbl...
> Hi!
> How can i save the resultant recordset generated by an Stored Procedure to
> another table (with the same structure) ?
> I think its some like INSERT * INTO tmptable FROM **Exec mySProc**
> Can you help my?
> Thanxs, Gabriel.
>
>

How to save contents of Text box to database?

Hi,

For some reason I can't use the edit, update or insert features on my remote shared server, so I am looking to create a web page that has text boxes on it, that I can enter data into, that will be saved into my database.

This is opposed to entering the data directly into the database itself. I want to be able to use a webpage, for simply adding new data, and saving it so that the new data updates and saves over the top of the old data.

What are the steps involved in doing this?

Any example code for just one text box would be appreciated, I could then extend it to suit my needs. Tia.

As I understand you want to get data in text box in you website and then update or insert to the database table. Here I wrote a very simple sample in C#:

protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = @."Data Source=Confute\SQL2000;Initial Catalog=tempdb;Integrated Security=SSPI;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
// Connect to the database then retrieve the schema information.

SqlCommand cmd = new SqlCommand("sp_UpdateMytable", connection);
connection.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.id", txtBox_ID.Text);
cmd.Parameters.Add("@.name", txtBox_Name.Text);
int i = cmd.ExecuteNonQuery();

}

And the storedprocedure sp_UpdateMytable will update a table t1(id int, name varchar(30)) in this way:

create proc sp_UpdateMyTable @.id int,@.name varchar(30)
as
if exists (select * from t1 whereid=@.id)
update t1 setname=@.namewhere id= @.id
else insert into t1 select @.id,@.name
go

|||

Thanks Lori_Jay,

I actually resolved the issue and did switch the answered tag on this thread. I am sure your solution would work, however mine was a more simple issue, actually I will mention it here for other poor souls who struggle with the same issue I did.

Basically, I did not have a Primary Key set in my table of my database. Firstly, I was taught that although its a good idea to have a PK, it's not absolutely necessary. Because I had one table, with one column and one row, Idecided not to have one.

If you don't have one, then in Visual Studio 2005, you cannot access the Advanced SQL options, which are INSERT, UPDATE & DELETE, this seems to be a major fault if you ask me because there is ZERO error reporting and ZERO documentation about it.

I got lucky when I did a Google for it (after a week of endless suffering) to find one site in the entire world, written in Russian (which I had to translate very poorily), which stated that you need a PK. I quickly added a PK to my table and it worked instantly.

Perhaps the powers that be whom monitor these forums, can look into this and document it so that others are spared the same distress.

Regards.

How to run trigger against already-filled-table?

Hi all,
I am creating a trigger for table A. This trigger would insert some values
to table B. Problem is, the apss that uses this database is already running
for months by now and table A has filled with transaction values.
Question:
Is there any way that I could run the trigger (thus automatically insert
appropriate values to table B) based on the data that is _alread_ on table
B?
Looking forward to hearing from all of you!
TIA,
WilliantoI expect you can just run the trigger code in Query Analyzer. Just
paste in the code and then replace references to the INSERTED and
DELETED tables with the name of Table A.
David Portas
SQL Server MVP
--sql

How to run an insert without returning anything

I need to run a select statement in a sproc and at the end insert into a
history table without having the insert return anything to the sproc is is
embedded in. How do I do this? Thank you.If that is all the proc does, you can say:
INSERT INTO HistoryTable EXEC myProcedure;
"JT" <xtf@.microsoft.com> wrote in message
news:eUw3771VGHA.4952@.TK2MSFTNGP09.phx.gbl...
>I need to run a select statement in a sproc and at the end insert into a
>history table without having the insert return anything to the sproc is is
>embedded in. How do I do this? Thank you.
>|||Problem is that I do a select first and then insert into history and I am
getting the reults of my insert not my select. Thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u9cLMC2VGHA.2444@.TK2MSFTNGP14.phx.gbl...
> If that is all the proc does, you can say:
> INSERT INTO HistoryTable EXEC myProcedure;
>
>
> "JT" <xtf@.microsoft.com> wrote in message
> news:eUw3771VGHA.4952@.TK2MSFTNGP09.phx.gbl...
>|||> Problem is that I do a select first and then insert into history and I am
> getting the reults of my insert not my select. Thanks.
I don't know what all of this means. Could you provide some real code, a
simple repro, and explain in detail what you want to really happen? All
these word problems are not very easy to follow.|||JT wrote:
> I need to run a select statement in a sproc and at the end insert into a
> history table without having the insert return anything to the sproc is is
> embedded in. How do I do this? Thank you.
CREATE PROC usp_insert
AS
SET NOCOUNT ON ;
INSERT INTO tbl (col1, col2, ...)
SELECT col1, col2, ...
FROM ... ;
GO
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||JT (xtf@.microsoft.com) writes:
> Problem is that I do a select first and then insert into history and I am
> getting the reults of my insert not my select. Thanks.
If this is a quiz, my guess is that your table has a trigger with a
SELECT statement in it.
If it not a quiz, please be more detailed...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

How to run a query file

I have a query file, test.sql, in the root of my C drive. It is
400,000 lines worth of insert into statements and is so big i cannot
load in into management studio. How can I run this?On Wed, 31 Oct 2007 17:02:36 -0700, "mitchman10@.gmail.com"
<mitchman10@.gmail.comwrote:

Quote:

Originally Posted by

>I have a query file, test.sql, in the root of my C drive. It is
>400,000 lines worth of insert into statements and is so big i cannot
>load in into management studio. How can I run this?

Add a line with nothing but a GO every, oh say after every 100 INSERT
commands. Then you could TRY to execute the file using the command
line utilities OSQL or SQLCMD. I'm not sure if they will choke on a
file that size as I've never had reason to test them in that way.

Roy Harvey
Beacon Falls, CT|||Roy Harvey (SQL Server MVP) wrote:

Quote:

Originally Posted by

On Wed, 31 Oct 2007 17:02:36 -0700, "mitchman10@.gmail.com"
<mitchman10@.gmail.comwrote:
>

Quote:

Originally Posted by

>I have a query file, test.sql, in the root of my C drive. It is
>400,000 lines worth of insert into statements and is so big i cannot
>load in into management studio. How can I run this?

>
Add a line with nothing but a GO every, oh say after every 100 INSERT
commands. Then you could TRY to execute the file using the command
line utilities OSQL or SQLCMD. I'm not sure if they will choke on a
file that size as I've never had reason to test them in that way.

Failing that, split it up into multiple files (by whatever means you
like) and execute them individually.|||On Wed, 31 Oct 2007 18:38:12 -0700, Ed Murphy <emurphy42@.socal.rr.com>
wrote:

Quote:

Originally Posted by

>Roy Harvey (SQL Server MVP) wrote:
>

Quote:

Originally Posted by

>On Wed, 31 Oct 2007 17:02:36 -0700, "mitchman10@.gmail.com"
><mitchman10@.gmail.comwrote:
>>

Quote:

Originally Posted by

>>I have a query file, test.sql, in the root of my C drive. It is
>>400,000 lines worth of insert into statements and is so big i cannot
>>load in into management studio. How can I run this?

>>
>Add a line with nothing but a GO every, oh say after every 100 INSERT
>commands. Then you could TRY to execute the file using the command
>line utilities OSQL or SQLCMD. I'm not sure if they will choke on a
>file that size as I've never had reason to test them in that way.

>
>Failing that, split it up into multiple files (by whatever means you
>like) and execute them individually.

Which might be no more work than adding the GO lines.

Roy Harvey
Beacon Falls, CT

How to run a insert script in SQL Server

MS SQL Server 2005 on Win2003 EE.

I am new to SQL Server.. Can someone let me know how to run a script file from sqlcmd prompt in sql server?

I tried this command and got an error: label names must be unique within a query or stored procedure.

sqlcmd -U sa -P sa -d Test -i c:\tmp\t1.sql -o c:\tmp\1.log

Table: t1
c1 nchar(10)
c2 nchar(10)

t1.sql
insert into t1 (c1, c2) values ('1', '2');
GO

Thanks in advance.If the file says only:

insert into test..t1 (c1, c2) values ('1', '2')
go

it should insert the tuple (1,2) into the table t1 in the database test, using the default schema for the user (so dbo will be used I guess) ...|||No I am not seeing the results. Following error is shown:

The label 'c' has already been declared. Label names must be unique within a query batch or stored procedure.|||Have you installed the SQL server Client Tools?|||I did not install the suite.. but when I look into the folder, I see MS SQL Server Management Studio, DTS etc. I am running the command from command prompt using sqlcmd...

I know in SQL2000 we use to have SQL Query Analyzer however, not sure if they have changed in SQL2005!?

Thanks.|||SQL Server Mangement Studio is the new EM/QA

If you've done QA before, how come you are doing command ine?

Oracle fiend?|||How about:

insert into test..t1 ([c1], [c2]) values ('1', '2')
go|||The reason for trying out from the command line is that we obtained a script with insert statements and this needs to be run using command line!!

Tried what MCrowley suggested.. but no luck.. same error gets reported in the sqlcmd window.|||Ok, let's take it from the top here ... Do you even get connected to a SQL Server ?
Try:
sqlcmd -U sa -P sa -S <ServerName>

If all goes well, you should see the '1>' prompt and from there you should be able to do SQL from there. Something like 'select getdate()' (followed by 'go') should give some result.

Does that work ?

Gr,
Yveau

How to return large amount of data in the XML format

I have SQL 2000 and need to retrieve fairly large amout of data (~
50.000 characters) in XML format and then insert it into the field of
the text type.
As 'FOR XML' can't be used with either local variables, INSERT INTO or
SELECT INTO this makes "XML support" quite useless in many aspects.

Can anyone please help me in solving this.
Thanks a lot for your help and time.

PavelPavel (p.golobokov@.ausbulk.com.au) writes:
> I have SQL 2000 and need to retrieve fairly large amout of data (~
> 50.000 characters) in XML format and then insert it into the field of
> the text type.
> As 'FOR XML' can't be used with either local variables, INSERT INTO or
> SELECT INTO this makes "XML support" quite useless in many aspects.

You can try:

INSERT tbl (xmlcol)
SELECT * FROM OPENQUERY(LOCALSVR, 'SELECT ... FOR XML')

Where LOCALSVR has been created as

EXEC sp_addlinkedserver
@.server = 'LOCALVR',
@.srvproduct = '',
@.provider = 'MSDASQL',
@.datasrc = 'LocalServer'

That is, you use the deprecated OLE DB over ODBC provider. This works
so far that you get XML back. However, you may find that the text
has been broken into many rows. (If you would use SQLOLEDB, the real
SQL Server provider, you get a blob back.)

If this does not work out, you will have a find a client to pick up the
XML and send it back.

In SQL 2005, the XML support is considerably enhanced, and you should
be able to do this without weird workarounds.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

how to return event description from stored proceedure?

I am using C# to insert the form details and passing event id (numeric) to the same stored procedure in my eror handler and need to retrieve the description from event_db to display in MessageBox..

can the stored proceedure send the text?

Could you describe this abit in detail and send your DDL over ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to retrieve values from one db to another db

Hi
Could someone please give me the correct TSQL statement, for the below
"pseudo".
USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
given that tbl_1 and tbl_2 is identical.
Any hints appreciated
Regrds.
Mr. SmithAssuming both tables are owned by dbo:
INSERT INTO db_a.dbo.tbl_1
SELECT * FROM db_b.dbo.tbl_2
Jacco Schalkwijk
SQL Server MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Hi,
No need of USE Database command here. you could use:-
Insert into database1.tableowner.tablename select * from
database2.tableowner.tablename
Thanks
Hari
SQL Server MVP
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||INSERT INTO DB1.tbl_1
SELECT * from DB2.tbl_2
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Mr. Smith" <nospam@.blindfolded.gone> schrieb im Newsbeitrag
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Thanks all of you! Hari, Jacco and Jens for a quick and easy answer.
Regards
Mr. Smith
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>|||Sorry, forgot the owner
INSERT INTO DB1.dbo.tbl_1
SELECT * from DB2.dbo.tbl_2
Jens Smeyer.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> schrieb
im Newsbeitrag news:ur9bJ6ARFHA.3288@.TK2MSFTNGP14.phx.gbl...
> INSERT INTO DB1.tbl_1
> SELECT * from DB2.tbl_2
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Mr. Smith" <nospam@.blindfolded.gone> schrieb im Newsbeitrag
> news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Hi,
Try using OpenRowSet.
User db_a
INSERT INTO tbl('col1','col2')
SELECT a.col1,a.col2
FROM OPENROWSET('SQLOLEDB','servername';'user
id';'password','SELECT
col1,col2 FROM db_b.dbo.tbl_2') a
Hope this helps.
Regards,
Sambath
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:efdpI0ARFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> Could someone please give me the correct TSQL statement, for the below
> "pseudo".
> USE db_a INSERT INTO tbl_1 VALUES(USE db_b SELECT * FROM tbl_2)
> given that tbl_1 and tbl_2 is identical.
> Any hints appreciated
> Regrds.
> Mr. Smith
>

How to retrieve the GUID value of a SQL NewID() identity column after an insert ?

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

no, you have to turn around and requery the database. another option if you are using CF2 is to create the GUID in your mobile app code and use it in your INSERT statement instead of NewID(). Then you know what it is without the extra database roundtrip.

-Darren

How to retrieve the GUID value of a SQL NewID() identity column after an insert ?

Hello,

In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.

Is it possible to retreive the GUID column of this new record (without requerying the table) ?

I'm using EVC, Sql Mobile 3.0 and OLE DB interface.

Thanks in advance.

no, you have to turn around and requery the database. another option if you are using CF2 is to create the GUID in your mobile app code and use it in your INSERT statement instead of NewID(). Then you know what it is without the extra database roundtrip.

-Darren

How to retrieve last inserted ID(Auto-Number) then perform another Insert Statement?

Hi All,

I hope you could help me in retrieving the last inserted ID(Auto-Number) then perform another Insert Statement?

I would really much appreciate it. I am coding in VB and am uisng

Visual Web Developer 2005 Express Edition and Microsoft SQL Sever

Management Studio Express.

Thanks alot.

-- Sam

My Codes:

Dim SQLStr5 As String = "INSERT INTO

NotesDetails(Notes_Level,Notes_Subject,Notes_Type,Notes_Year,Notes_Desc)

VALUES ('" & ddl_level.SelectedValue & "','" &

ddl_sub.SelectedValue & "','" & rbl_type.SelectedValue &

"','" & ddl_year.SelectedValue & "','" & tb_desc.Text &

"')"

Dim con5 As New SqlConnection(connstring)

con5.Open()

Dim cmd5 As New SqlCommand(SQLStr5, con5)

cmd5.ExecuteNonQuery()

con5.Close()

'' Need to get last inserted ID to Insert into the next Statement. (Notes_ID)

Dim SQLStr5a As String = "INSERT INTO

NotesComments(Notes_ID,Notes_Comments) VALUES ('" &

ddl_level.SelectedValue & "','" & tb_comments.Text & "')"

Dim con5a As New SqlConnection(connstring)

con5a.Open()

Dim cmd5a As New SqlCommand(SQLStr5a, con5a)

cmd5a.ExecuteNonQuery()

con5a.Close()

The last identity can be fetched using the SCOPE_IDENTITY() function, be aware that you have to do that within the same scope. As an additional new feature of SQL Server 2005 you could use the OUTPUT clause which give you the availbility to return values within the same DML statement. See more informations and samples in the BOL.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de