Insert/Update statements or Stored Procs

When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.
thanksStored procs...but who's going to write them?|||Use ADO from VB for insert and update|||Originally posted by Brett Kaiser
Stored procs...but who's going to write them?

wouldn't i just code the Insert/Update statement within the stored proc, then pass the value's to the stored proc. That sounds like alot of parameters to be dealing with for larger tables.|||Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.|||Originally posted by Brett Kaiser
Alot of parameters...perhaps...but there are performance gains by having a compiled and in cache sproc...

Also you isolate all of the buseness rules to the server, not the code...

More control that way.

Thanks Brett, one more quick question. Within the stored proc, i need to check if the record already exists before inserting or updating. Can you paste a small code sample to give me an idea of how i would ideally do that.

thanks alot|||I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP|||USE Northwind
GO

CREATE TABLE myTable99(Col1 int PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 1,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'C' UNION ALL
SELECT 4,'D'
GO

CREATE PROC mySproc99
@.Action Char(1)
, @.Col1 int
, @.Col2 char(1) = Null
AS
-- File: {\\tsstrv03\ESolutions}:
-- Date: May 1st, 2002
-- Author: Brett Kaiser
-- Server:
-- Database: TaxReconDB
-- Login: sa
-- Description: myTable99 Maint sproc
--
--
-- The stream will do the following:
--
-- 1.
--
-- Tables Used: myTable99
--
-- Tables Created: None
--
--
-- Row Estimates:
-- name rows reserved data index_size unused
-- ------- ---- ------ ------ ------ ------
-- Ledger_Detail 76779 17160 KB 17040 KB 64 KB 56 KB
-- ATS_SignOff_Entity 3316 512 KB 504 KB 16 KB -8 KB
-- tblAcct_LedgerBalance 11691 3848 KB 3792 KB 8 KB 48 KB
--
--Change Log
--
-- UserId Date Description
-- ---- ----- ---------------------------
-- x002548 05/23/2002 1. Initial release
--
--
--

Declare @.error_out int, @.Result_Count int, @.Error_Message varchar(255), @.Error_Type int, @.Error_Loc int, @.RC int

SET NOCOUNT ON

SELECT @.rc = 0

BEGIN TRAN

IF @.Action NOT IN ('S','I','U','D')
BEGIN
SELECT @.Error_Loc = 1
SELECT @.Error_Message = 'Incorrect Request. Must be S,I,U or D. Paramter was: "' + @.Action + '"'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END

IF @.Action = 'S'
BEGIN
SELECT Col1, Col2 FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 2
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 2
SELECT @.Error_Message = 'myTable99 Returned zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'D'
BEGIN
DELETE FROM myTable99 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 3
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 3
SELECT @.Error_Message = 'An Attempted DELETE from myTable99 affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'I'
BEGIN
INSERT INTO myTable99(Col1,Col2) SELECT @.Col1, @.Col2

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 4
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 4
SELECT @.Error_Message = 'An Attempted INSERT to myTable99 did not insert anything'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

IF @.Action = 'U'
BEGIN
UPDATE myTable99 SET Col2=@.Col2 WHERE Col1 = @.Col1

SELECT @.Result_Count = @.@.ROWCOUNT, @.error_out = @.@.error

If @.Error_Out <> 0
BEGIN
Select @.Error_Loc = 5
Select @.Error_Type = 50001
GOTO mySproc99_Error
END

If @.Result_Count = 0
BEGIN
SELECT @.Error_Loc = 5
SELECT @.Error_Message = 'An Attempted UPDATE of myTable99 Affected zero rows'
SELECT @.Error_Type = 50002
GOTO mySproc99_Error
END
END

COMMIT TRAN

mySproc99_Exit:

SET NOCOUNT OFF

RETURN @.rc

mySproc99_Error:

ROLLBACK TRAN

IF @.Error_Type = 50001
BEGIN
Select @.error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' @.@.ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @.error_out)
END
IF @.Error_Type = 50002

BEGIN
Select @.Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@.Error_Loc))
+ ',"' + ' Severity: UserLevel '
+ ',"' + ' Message: ' + ',"' + RTrim(@.Error_Message)
END

SELECT @.rc = -1

RAISERROR @.Error_Type @.Error_Message

GOTO mySproc99_Exit
GO

DECLARE @.RC int

EXEC @.RC = mySproc99 'X',1,'A'

SELECT @.RC

EXEC @.RC = mySproc99 'S',4

SELECT @.RC

EXEC @.RC = mySproc99 'I',5,'E'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'U',5,'F'

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'D',5

SELECT @.RC

EXEC @.RC = mySproc99 'S',5

SELECT @.RC

EXEC @.RC = mySproc99 'I',4,'F'

SELECT @.RC
GO

DROP PROC mySproc99
GO
DROP TABLE myTable99
GO|||Originally posted by Pat Phelan
I am definitely with Brett on this one. The executable should be "lookie no touchie" in my opinion, it should be able to SELECT as it needs to, but I don't think it should change anything except through a stored procedure. At the very least, all updates should be done via RPC calls and those should only be allowed under duress.

-PatP
Actually, any communication with the server should be done through stored procedure, including SELECT.|||Originally posted by rdjabarov
Actually, any communication with the server should be done through stored procedure, including SELECT. I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||here's the man of so many virtues|||Originally posted by ms_sql_dba
here's the man of so many virtues

You sure s/he's a man?

Pat, you lost me...

we're talking about an app right? Not ad-hoc/dba maint issues? right?|||Originally posted by ms_sql_dba
here's the man of so many virtues Are you accusing me of having virtues ? I may wear many hats, but that is due to having a huge head. It has nothing to do with virtues of any kind!

-PatP|||Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?)

suave is the only word I can think of...

You must be a ladies man....

:D

Does anyone use anything like the template posted..or is it 1 sproc per operation?|||Originally posted by rdjabarov
Yeah, Pat, are you trying to confuse us? App is an app, and stored procedure should be the way to go. If you are a developer (are you?) then you have developer rights...but only in Development environment. If you're a DBA (are you really?) then you need to be associated with SYSADMIN server role, unless you are a junior (I get it, is that one of your hats?) Heck, I thought that confusion was a consequence of working with databases!

Nah, I don't really have any of those titles, but they sounded cool next to my actual titles (International super-spy, Bon-Vivant, and Ultra-cool geek about town). I'll try to behave better from now on!

-PatP|||Originally posted by Brett Kaiser
suave is the only word I can think of... Nope, Suave is one of our DataWarehousing consultants. He lives somewhere in Jersey and flies out to come play when we need him.
Originally posted by Brett Kaiser
You must be a ladies man.... Just one lady, although I do flirt outrageously. I used to send people around the bend when I'd dial our last TAM and ask "So how are you, other than obviously devastatingly gorgeous?" To which she'd often reply "Gee, you've just GOT to call more often."
Originally posted by Brett Kaiser
Does anyone use anything like the template posted..or is it 1 sproc per operation? Nope, to me that smacks of bad design. At least in my book, cross-tabs should be done on the client side or in the data warehouse, not from an OLTP system.

-PatP|||That's all VERY funny...

but what do you mean cross tabs?

And I definetly have to use that line...

Well, at least on the wife anyway...|||Originally posted by Brett Kaiser
but what do you mean cross tabs?
Whoops! Brain fart on my part, wrong thread!

-PatP|||International super-spy? You took my title!!! I demand it back...or a cig!|||You're really having trouble with the cig thing, but it is worth the fight. There aren't enough "bright boys" around, and we can't afford to loose any!

Anywho, the title isn't exclusive. When they awarded me the title, none of the previous users lost their permission to use it!

-PatP|||>For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand".

Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools. You better get your facts right...pb8 > 9704 would "compile" and give SQL error codes whenever embedded sql is given in code and it adds to ease of use for developers...[no doubt agrees for SP approch being the better one].

moreover, PB supports all 4 levels of dynamic SQL superbly and I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...

my 2 cents

WS [wizardofnet-at-yahoo]

Originally posted by Pat Phelan
I'm certainly good with that, but it means that many of the new "data aware" tools will effectively cease to function. For example, you can't use PowerBuilder very well if it can't do at least basic SELECT operations "on demand". None of the ETL tools or report writers that I've used work worth diddly either, although some will struggle gamely.

While wearing my dba hat, I argee that all access to the server should be via stored procedures. While wearing my developer hat, I need at least basic SELECT privleges to get my job done efficiently. While wearing my manager hat, I have to side with getting the job done, even though it makes the dba hat uncomfortable.

-PatP|||Originally posted by mell
I use them successfully in my code to query oracle dynamically even when i dont know table names or column list...


[as he types falling out of chair]
Really?
[/as he types falling out of chair]|||Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN|||Originally posted by MCrowley
Sorry, Brett, but in the few projects I have had any control over, I went with a stored procedure per action. Makes for a ton of stored procedures, but the front end code seems to be more readable. Haven't gone for many updates, as yet, so I don't know how many stored procedures I will be touching then. But that is just my 0.02 USD=0.219430 MXN

Ya lost me on that one...you mean make 4 out of the one I posted?

It's all a matter of methodolgy...

Pick 1 and stick eith it...no thinking involved...same thing for naming comventions...

make it so you don't have to look anything up...

But I like the part about not knowing the names of columns or tables...

must make for some very interesting code...no?|||Originally posted by mell
Well. You could use embedded SQL inside Powerbuilder and its a far superior tool among all the popular tools.[wizardofnet-at-yahoo] 'splain dis one again for me... In the scenario I described, you don't have SELECT permissions, so you can't see any tables. You can't open the DataWindow painter. You can't generate any dynamic SQL...

What exactly can you do again?

-PatP|||Hey Lucy.....what did you did you do with the permissions this time?|||Yep. As near as I understand, when a procedure runs for the first time with it's first parameters, a query plan is born. SQL Server tries to use that query plan for each successive run of the stored procedure. Writing an all in one procedure is good if the procedure is not run very often, but for a website where a stored procedure can be run many many times, you don't want to wait around for the query optimizer to try to figure out it needs to recompile all of a sudden. Clear as mud?|||woooooosh...

and huh?

wouldn't the plan just stay in cache?

Got to get that internals book...|||Here is a classic example. Get on a server that has been around and been backing up databases regularly. Then make this stored procedure:

create procedure testproc (@.start int, @.end int)
as

select *
from msdb..backupset
where backup_set_id > @.start
and backup_set_id < @.end
go

Then run this:
testproc 1, 2

Get the execution plan, then run this:
testproc 1, 40000

and check out that execution plan

It should use the index for both, even though a table scan would be better for the second query.

EDIT: Hmm. Having trouble with the reverse of the logic in this example. I can not get the stored proc to do anything but use the index. Anyway, complex queries can get hit pretty hard by this fact. Something to keep in mind.|||Originally posted by Sammy_S
When working from within VB, should i be using Insert or Update statements, or should i pass the values to a stored proc that does it for me.

thanks

It's definitely better to use sprocs. In this way you separate the different layers in your application (something, which you may have missed to consider during the development). Later if you need changes you will need just to change the sprocs, without any modifications in the VB code. Also consider that the sprocs syntax is being validated during creation and they're compiled. So in all cases it's better to use them instead of raw hard-coded statements.

Martin Markov

insert...returning / select

Hi

I have a JSP/JDBC program that processes sql statements from other JSPs to an Oracle 8.16 database. I need to modify this program to retreive the data that was just inserted (from different tables, with different keys and different INSERT statement structures).

The best for me would be to perform a SELECT *... for the inserted record... but I just can't figure out how to retreive this record and it's becoming frustrating !!

I've been searching for a way to do this with PL/SQL 'INSERT... RETURNING...' but everything I found on the web isn't clear and i'm quite new to SQL and JDBC.

Could someone PLEASE clearly explain to me if it's possible and HOW... if not, is there any way I can ever achieve this without having to tear down the INSERT statement and build some sort of a SELECT statement out of it ??Hi,

the syntax is

insert into table (column1,column2,column3)
values(1,2,3)
returning column1,column2 into variable1,variable2

but it doesn't work with multitable-inserts and it is really slowly (on 9i)
the faster way is

select primary_key from sequence.nextval into variable...
then insert with this pk an reselect the values inserted...

good luck|||In general it shouldn't be necessary to SELECT to find out what you just inserted - you know what you just inserted! The only exceptions are values set by DEFAULT clauses or triggers.

It would be a good idea (good practice) to take the insert statements out of the JSP code and put them in PL/SQL packaged procedures. These procedures can then have OUT arguments to return the required data. However, I imagine that is a big change from where you are now.|||I see no problem with the performance of INSERT RETURNING on 9i database. It appears to perform rather better that SELECT then INSERT even WITHOUT further SELECT to retrieve row.

Frankly I'm disappointed when I see people making assertions of this kind without any evidence. There's enough Oracle misconceptions floating around without adding to the steaming pile.

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> CREATE TABLE table_name (column_name NUMBER (10));

Table created.

SQL> CREATE UNIQUE INDEX index_name ON table_name (column_name);

Index created.

SQL> CREATE SEQUENCE sequence_name INCREMENT BY 1 CACHE 10000;

Sequence created.

SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
2 v_start_time INTEGER := 0;
3 v_column_name NUMBER (10);
4 v_sequence_no NUMBER (10);
5 v_iterations INTEGER := 10000;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE ('Case 1: SELECT then INSERT');
8 v_start_time := DBMS_UTILITY.GET_TIME;
9 FOR i IN 1..v_iterations LOOP
10 SELECT sequence_name.NEXTVAL
11 INTO v_sequence_no
12 FROM dual;
13 INSERT INTO table_name (column_name)
14 VALUES (v_sequence_no);
15 END LOOP;
16 DBMS_OUTPUT.PUT_LINE ('Hsecs: ' ||
17 (DBMS_UTILITY.GET_TIME - v_start_time));
18
19 DBMS_OUTPUT.PUT_LINE ('Case 2: INSERT RETURNING');
20 v_start_time := DBMS_UTILITY.GET_TIME;
21 FOR i IN 1..v_iterations LOOP
22 INSERT INTO table_name (column_name)
23 VALUES (sequence_name.NEXTVAL)
24 RETURNING column_name INTO v_column_name;
25 END LOOP;
26 DBMS_OUTPUT.PUT_LINE ('Hsecs: ' ||
27 (DBMS_UTILITY.GET_TIME - v_start_time));
28 END;
29 /
Case 1: SELECT then INSERT
Hsecs: 202
Case 2: INSERT RETURNING
Hsecs: 129

PL/SQL procedure successfully completed.

SQL> /
Case 1: SELECT then INSERT
Hsecs: 196
Case 2: INSERT RETURNING
Hsecs: 153

PL/SQL procedure successfully completed.

SQL> /
Case 1: SELECT then INSERT
Hsecs: 199
Case 2: INSERT RETURNING
Hsecs: 118

PL/SQL procedure successfully completed.

SQL>

Insert, Update queries

Is there any way to use a graphical designer to build your insert & update SQL statements in Enterprise manager? I mean Access has an EASY way to build them, surely SQL does too?

I would just build them in Access and copy the SQL, but then I'm stuck replacing all the "dbo_" with "dbo." and other little nuances.No graphical way. Lots of people use Access exactly like you mentioned. Another way is with Query Analyzer. Right click the table in the object browser and you'll have options for scripting INSERTS, UPDATES, DELETES, etc. Not graphical, but handy to eliminate some typing and spelling mistakes.|||Thanks for the reply!

You know, in many ways Access is superior to SQL Server. Easy interface for designing and building any types of queries and forms, easy to link tables to any form of database (Oracle, SQL Server, DBF), Great reporting tool, cheap, and so on and so forth...

if only it was more stable and faster for use in a larger corporate setting with many users hitting it constantly, it would be my #1 choice for database development.|||Access gets some bad press, but it's good at what it's meant to be. Doesn't hold a candle to SQL Server for what it's not meant to be. Just a case of the right tool for the job.

Insert values in Primary key field

Hi,

Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.

Thanks in advance for any reply.

I've found the answer myself if anyone interested!

SET IDENTITY_INSERT table_name ON \g

Insert transaction batch size

I have insert statements that inserts 1.7 million rows from one server to
another. Half way through the insert, the connection gets lost. I am thinking
that because of the batch size the connection gets cut-off. How can I
implement a commit in T-SQL after say every 500 rows inserted ?
Thanks.
DXC,
You will have to manage this yourself in a stored procedure or sql batch.
Have you considered DTS - you can set the commit batch size there.
-- Bill
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:B8409B51-1A56-4CB5-946B-234102EDFA27@.microsoft.com...
>I have insert statements that inserts 1.7 million rows from one server to
> another. Half way through the insert, the connection gets lost. I am
> thinking
> that because of the batch size the connection gets cut-off. How can I
> implement a commit in T-SQL after say every 500 rows inserted ?
> Thanks.

Insert transaction batch size

I have insert statements that inserts 1.7 million rows from one server to
another. Half way through the insert, the connection gets lost. I am thinkin
g
that because of the batch size the connection gets cut-off. How can I
implement a commit in T-SQL after say every 500 rows inserted ?
Thanks.DXC,
You will have to manage this yourself in a stored procedure or sql batch.
Have you considered DTS - you can set the commit batch size there.
-- Bill
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:B8409B51-1A56-4CB5-946B-234102EDFA27@.microsoft.com...
>I have insert statements that inserts 1.7 million rows from one server to
> another. Half way through the insert, the connection gets lost. I am
> thinking
> that because of the batch size the connection gets cut-off. How can I
> implement a commit in T-SQL after say every 500 rows inserted ?
> Thanks.

Insert statements, primary keys and relationships

I have a number of tables (30+) all collecting info about societies. I have a primary key (soc_ID) in all my tables and a number of one-to-one relationships. All the tables are joined.

Q1 : If I add into the first row in one table, do I HAVE to insert the PK in EVERY other table its related to? Q2:is it wise to have foreign keys nullable?
My MS SQL SERVER statement::

insert into society(soc_id) values ('1');

ERROR: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Society_EDU_materials_targeted". The conflict occurred in database "mem_soc", table "dbo.EDU_materials_targeted", column 'soc_id'.
The statement has been terminated.

I understand the error. Is there a short way to insert the PK in all related tables automatically otherwise my insert statement will be HUGE.
This method seems REALLY long winded...I'm 99% sure that you've got the primary and foreign keys reversed between your EDU_materials_targeted and society tables. At least as I understand it, the soc_id should be the primary key in the society table, and it should be the foreign key in the dbo.EDU_materials_targeted table.

A foreign key should allow NULL values if the relationship has a cardinality of "1 to zero or more". In other words, if the child table might not have a matching row in the parent table, then the FK should allow NULL values.

Normally I'd move this discussion to the Microsoft SQL Server (http://www.dbforums.com/forumdisplay.php?f=7) forum, but since it still is a relatively pure SQL issue I'm Ok with leaving it here in the SQL forum.

-PatP

-PatP|||Thanks a lot! You're 100% correct!

Insert statement with multiple select statements

hi

first of all is it possible? if so, what am i doing wrong with this

INSERT into TB2

(

ClientCode,
EngagementCode,
EngagementDescription

)

SELECT
(SELECT dbo.tarCustomer.CustID
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey),

NULL,

SELECT
(SELECT dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175 INNER JOIN
dbo.tarCustomer ON dbo.tPA00175.CustKey = dbo.tarCustomer.CustKey INNER JOIN
dbo.tPA00007 ON dbo.tPA00175.intJobKey = dbo.tPA00007.intJobKey)

the first select statement for works fine, but the second one and all after i get a syntax error near 'select'.

this is just a shortened version of the statement. how would i run select statements for a table to be inserted into with different column names. also with items that are hard coded like the 'null'. thanks

tiborINSERT
into TB2
( ClientCode
, EngagementCode
, EngagementDescription )
SELECT dbo.tarCustomer.CustID
, NULL
, dbo.tPA00175.chrJobNumber
FROM dbo.tPA00175
INNER
JOIN dbo.tarCustomer
ON dbo.tarCustomer.CustKey = dbo.tPA00175.CustKey
INNER
JOIN dbo.tPA00007
ON dbo.tPA00007.intJobKey = dbo.tPA00175.intJobKey|||Thanks alot.. Something so simple but yet such a pain.

have a good one|||Ok the syntax works great but its not doing the insert. i built the select statement with a view so i know all the links are nice and strong and such. it doesnt seem to like the 'from' i think. any suggestions?|||Are you getting any error messages? I don't see anything wrong with r937's code (other than the goofy formatting ;) ).
If you run just the SELECT portion without the insert, does it return records? Are there any constraints on the target table that would prevent inserts (unique indexes, foreign keys...)?|||i apologize for not paying attention. the second part of my select statement had a join that returned nothing so i had to alter that. now its working but im getting the String or binary data would be truncated error. After i figure out which field is causing that, it should be all good. but stay tuned in case something else comes up, haha.

tibor

INSERT Statement Question

Hello everyone,
I have a general question about INSERT statements. Is it possible to
insert fields into a table both through a SELECT statement and
explicitly in the same INSERT statement? For example, if I have the
following part of an INSERT statement:
INSERT INTO table1 (column1, column2)
Is it possible to insert "column1" using a SELECT statement and
"column2" explicitly? What I am trying to do is insert multiple rows
into a table where some of the field information I need is located in
another table and some I need to insert explicitly. Thanks for your
replies in advance.This would work..
INSERT INTO table1 (column1, column2)
select au_lname, au_fname from authors
UNION
SELECT 'Fred','Bloggs'
HTH. Ryan
"schoultzy" <schoultzy@.yahoo.com> wrote in message
news:1150120091.014547.51090@.u72g2000cwu.googlegroups.com...
> Hello everyone,
> I have a general question about INSERT statements. Is it possible to
> insert fields into a table both through a SELECT statement and
> explicitly in the same INSERT statement? For example, if I have the
> following part of an INSERT statement:
> INSERT INTO table1 (column1, column2)
> Is it possible to insert "column1" using a SELECT statement and
> "column2" explicitly? What I am trying to do is insert multiple rows
> into a table where some of the field information I need is located in
> another table and some I need to insert explicitly. Thanks for your
> replies in advance.
>|||>Is it possible to insert "column1" using a SELECT statement and
>"column2" explicitly?
Yes
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"schoultzy" <schoultzy@.yahoo.com> wrote in message
news:1150120091.014547.51090@.u72g2000cwu.googlegroups.com...
> Hello everyone,
> I have a general question about INSERT statements. Is it possible to
> insert fields into a table both through a SELECT statement and
> explicitly in the same INSERT statement? For example, if I have the
> following part of an INSERT statement:
> INSERT INTO table1 (column1, column2)
> Is it possible to insert "column1" using a SELECT statement and
> "column2" explicitly? What I am trying to do is insert multiple rows
> into a table where some of the field information I need is located in
> another table and some I need to insert explicitly. Thanks for your
> replies in advance.
>|||I think i mis-understood.
Try this :-
DECLARE @.Constant varchar(50)
SET @.Constant = 'Constant Value'
INSERT INTO table1 (column1, column2)
SELECT Col001, @.Constant FROM tablename
HTH. Ryan
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23K9KYfijGHA.4044@.TK2MSFTNGP03.phx.gbl...
> This would work..
> INSERT INTO table1 (column1, column2)
> select au_lname, au_fname from authors
> UNION
> SELECT 'Fred','Bloggs'
> --
> HTH. Ryan
>
> "schoultzy" <schoultzy@.yahoo.com> wrote in message
> news:1150120091.014547.51090@.u72g2000cwu.googlegroups.com...
>|||Thanks to everyone for your replies. It seams as though using a
variable to hold the constant information will be the best solution for
me. Thank you for holding my hand. My novice status with SQL was on
full display with this post.|||No worries, we all need to start somewhere.
"schoultzy" <schoultzy@.yahoo.com> wrote in message
news:1150121330.164136.315130@.h76g2000cwa.googlegroups.com...
> Thanks to everyone for your replies. It seams as though using a
> variable to hold the constant information will be the best solution for
> me. Thank you for holding my hand. My novice status with SQL was on
> full display with this post.
>

Insert Statement

Hi Friends,
In my SP i have 3 insert statements that inserts record into 3 different
tables. If any of the inserts fail, I want to roll back any other inserts
that is executed. how to do this. please give me an example.
example:
insert into abc values('ert','ert')
insert into xyz values('rtert','rtyrty')
if the insert operation fails for the table xyz then the insert for abc
should not be commited.
thnks
vanithaput your sql statement in a transaction
begin transaction
insert 1....
insert 2....
insert 3.......
commit transaction
<hr>
MCP #2324787
"Vanitha" wrote:

> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||CREATE PROCC dbo.ProcedureName
(paramlist)
AS
BEGIN
BEGIN TRAN [tranname]
INSERT INTO First table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
INSERT INTO second table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
INSERT INTO third table ...
IF @.@.ERROR > 0
BEGIN
ROLLBACK TRAN [tranname]
RETURN [Errorcode]
END
COMMIT TRAN [tranname]
END
GO
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||This involves error handling as well as transaction handling. It is a large
topic, so some
background reading will sort this out for you. The best reference to this to
pic, IMO, is the error
handling articles at:
http://www.sommarskog.se/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
> Hi Friends,
> In my SP i have 3 insert statements that inserts record into 3 different
> tables. If any of the inserts fail, I want to roll back any other inserts
> that is executed. how to do this. please give me an example.
> example:
> insert into abc values('ert','ert')
> insert into xyz values('rtert','rtyrty')
> if the insert operation fails for the table xyz then the insert for abc
> should not be commited.
> thnks
> vanitha|||Just adding BEGIN TRAN and COMMIT TRAN will not cut it. The first might be O
K, the second fail and
the third OK. So the first and the third are performed but not the second. Y
ou need error handling
as well (or SET XACT_ABORT ON, but almost no-one in the SQL Server community
uses this setting).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:80DA1215-F68E-4FA3-BE39-3B08BD39473A@.microsoft.com...
> put your sql statement in a transaction
>
> begin transaction
> insert 1....
> insert 2....
> insert 3.......
> commit transaction
>
> --
>
> <hr>
> MCP #2324787
>
> "Vanitha" wrote:
>|||Thanks
"Tibor Karaszi" wrote:

> This involves error handling as well as transaction handling. It is a larg
e topic, so some
> background reading will sort this out for you. The best reference to this
topic, IMO, is the error
> handling articles at:
> http://www.sommarskog.se/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
> news:08B477A8-5335-45EB-BAC2-B76B2B2A5563@.microsoft.com...
>

Insert Problem in MS SQL

hey, I have written a bunch of insert statements and basically all I am trying to do is fill up columns of one table with values from columns of the second table.

My insert statement looks like:

INSERT table1(column)
SELECT column
FROM table2

Now, this works for one column , but when I run another statement like this trying to update another column, the second time around it does not work.
It does not error out, it shows that it runs fine, but the data is not shown on the table. Some of the data which is shown removes the data from the first column in the adjacent row. I am sure I am missing something here, but not able to figure it out, please HELP .Does your code look like this.

INSERT INTO Table1
( Col1, Col2, Col3....Etc)
SELECT col1, Col2, Col3
FROM Table2

If not then it won't work. Post your code and I'll have a look

Cheers
C|||Hi!
I think you have to do some change.you may use following codes,else email me your total code .Than i heartly solve thats....
INSERT INTO Table1
( Col1, Col2, Col3....Etc)
VALUES(SELECT col1, Col2, Col3....etc
FROM Table2)

Ok...bye