Help with SP

I have a webform that I need to insert data into a db. I would like to have a stored procedure handle the insert for me.

Here are the data fields from the form:

<td><asp:textbox id="f_name" runat="server"></asp:textbox></td>
<td><asp:textbox id="l_name" runat="server"></asp:textbox></td>
<td><asp:textbox id="emp_num" runat="server"></asp:textbox></td>
<td><asp:textbox id="email" runat="server"></asp:textbox></td>
<td><asp:dropdownlist id=dd1 runat="server" DataMember="Line_Item" DataSource="<%# dsLineItem1 %>" DataTextField="LI_ID" DataValueField="ID"></asp:dropdownlist></td>
<td><asp:dropdownlist id=dd2 runat="server" DataMember="Component" DataSource="<%# dsComponent1 %>" DataTextField="Component" DataValueField="ID"></asp:dropdownlist></td>
<td><asp:dropdownlist id=dd3 runat="server" DataMember="Activity" DataSource="<%# dsActivity1 %>" DataTextField="Abbrev" DataValueField="ID"></asp:dropdownlist></td>


Needed Solution:
I need to create a stored procedure that will insert the data from the above fields into the following database:

ProfileDB
Table columns:
id
f_name
l_name
emp_num
email
line_item
component
activity

I am sure that there are some rules for this I am unaware of (such as field names must match table column names etc.).

Thank you for your help.

BTW,

My version of SQL is 2000.

Thank you again.

Sincerely,

TimI have constucted a stored procedure that is at the least error free:
If you have any comments or suggestions regarding this sp I would greatly appreciate your input since I am new to SQL.

CREATE PROCEDURE dbo.InsertProfile
(
@.F_Name [varchar] (100),
@.L_Name [varchar] (100),
@.Emp_Num [numeric] (9),
@.Email [varchar] (250),
@.Line_Item [varchar] (250),
@.Component [varchar] (250),
@.Activity [varchar] (250)
)

AS

Insert into [dbo.InsertProfile]
(
[F_Name],
[L_Name],
[Emp_Num],
[Email],
[Line_Item],
[Component],
[Activity]
)

Values

(@.F_Name, @.L_Name, @.Emp_Num, @.Email, @.Line_Item, @.Component, @.Activity)

GO

Thank you.|||OK

You might want to check for errors

DECLARE @.Error, @.Rowcount

...sql statement

SELECT @.Error = @.@.ERROR, @.RowCount = @.@.Rowcount

Then interogate thos values...if @.Error is other than 0, then you have a problem...

And if @.Rowcount doesn't = 1 the that's a problem as well (don't know how that would ever happen, but it's a check)

Help with simple SP (newbie)

Hi. I'm writing my firsts stored procedures.
I'm trying to get a row from one table and insert the results in other
table. By now I have this:
create proc registerTable
@.email varchar(30)
as
select name,email
from users
where email=@.email
go
insert into employees
exec registerTable 'users'
go
But I need to include the insert in the procedure. How can I do that?
Regards,
Diego F.
What do you mean by that ?
--But I need to include the insert in the procedure. How can I do that?
Do you want to execute this recursive ?
Jens.
"Diego F." wrote:

> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
>
|||No. I mean that I need all done into the SP. Now I select the row and
outside I make the insert. I want to call the SP and have all done.
BTW, I put that in the wrong group, sorry.
Regards,
Diego F.
"Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> escribi en el
mensaje news:19270E44-FE1A-4030-AABE-9BD4ECFF7838@.microsoft.com...[vbcol=seagreen]
> What do you mean by that ?
> --But I need to include the insert in the procedure. How can I do that?
> Do you want to execute this recursive ?
> Jens.
> "Diego F." wrote:
|||I dont quite get your question. Is this what you were expecting?
create proc registerTable
@.email varchar(30)
as
insert into employees
select name,email
from users
where email=@.email
go
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
"Diego F." <diegofrNO@.terra.es> wrote in message
news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
|||Yes, it was exactly that :-)
Regards,
Diego F.
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> escribi en el mensaje
news:db85pe$rc0$1@.news01.intel.com...
>I dont quite get your question. Is this what you were expecting?
> create proc registerTable
> @.email varchar(30)
> as
> insert into employees
> select name,email
> from users
> where email=@.email
> go
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
>

Help with simple SP (newbie)

Hi. I'm writing my firsts stored procedures.
I'm trying to get a row from one table and insert the results in other
table. By now I have this:
create proc registerTable
@.email varchar(30)
as
select name,email
from users
where email=@.email
go
insert into employees
exec registerTable 'users'
go
But I need to include the insert in the procedure. How can I do that?
--
Regards,
Diego F.What do you mean by that ?
--But I need to include the insert in the procedure. How can I do that?
Do you want to execute this recursive ?
Jens.
"Diego F." wrote:
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
>|||No. I mean that I need all done into the SP. Now I select the row and
outside I make the insert. I want to call the SP and have all done.
BTW, I put that in the wrong group, sorry.
--
Regards,
Diego F.
"Jens Süßmeyer" <JensSmeyer@.discussions.microsoft.com> escribió en el
mensaje news:19270E44-FE1A-4030-AABE-9BD4ECFF7838@.microsoft.com...
> What do you mean by that ?
> --But I need to include the insert in the procedure. How can I do that?
> Do you want to execute this recursive ?
> Jens.
> "Diego F." wrote:
>> Hi. I'm writing my firsts stored procedures.
>> I'm trying to get a row from one table and insert the results in other
>> table. By now I have this:
>> create proc registerTable
>> @.email varchar(30)
>> as
>> select name,email
>> from users
>> where email=@.email
>> go
>> insert into employees
>> exec registerTable 'users'
>> go
>> But I need to include the insert in the procedure. How can I do that?
>> --
>> Regards,
>> Diego F.
>>
>>|||I dont quite get your question. Is this what you were expecting?
create proc registerTable
@.email varchar(30)
as
insert into employees
select name,email
from users
where email=@.email
go
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
"Diego F." <diegofrNO@.terra.es> wrote in message
news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>|||Yes, it was exactly that :-)
--
Regards,
Diego F.
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> escribió en el mensaje
news:db85pe$rc0$1@.news01.intel.com...
>I dont quite get your question. Is this what you were expecting?
> create proc registerTable
> @.email varchar(30)
> as
> insert into employees
> select name,email
> from users
> where email=@.email
> go
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
>> Hi. I'm writing my firsts stored procedures.
>> I'm trying to get a row from one table and insert the results in other
>> table. By now I have this:
>> create proc registerTable
>> @.email varchar(30)
>> as
>> select name,email
>> from users
>> where email=@.email
>> go
>> insert into employees
>> exec registerTable 'users'
>> go
>> But I need to include the insert in the procedure. How can I do that?
>> --
>> Regards,
>> Diego F.
>>
>>
>

Help with simple SP (newbie)

Hi. I'm writing my firsts stored procedures.
I'm trying to get a row from one table and insert the results in other
table. By now I have this:
create proc registerTable
@.email varchar(30)
as
select name,email
from users
where email=@.email
go
insert into employees
exec registerTable 'users'
go
But I need to include the insert in the procedure. How can I do that?
Regards,
Diego F.What do you mean by that ?
--But I need to include the insert in the procedure. How can I do that?
Do you want to execute this recursive ?
Jens.
"Diego F." wrote:

> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>
>|||No. I mean that I need all done into the SP. Now I select the row and
outside I make the insert. I want to call the SP and have all done.
BTW, I put that in the wrong group, sorry.
Regards,
Diego F.
"Jens Smeyer" <JensSmeyer@.discussions.microsoft.com> escribi en el
mensaje news:19270E44-FE1A-4030-AABE-9BD4ECFF7838@.microsoft.com...[vbcol=seagreen]
> What do you mean by that ?
> --But I need to include the insert in the procedure. How can I do that?
> Do you want to execute this recursive ?
> Jens.
> "Diego F." wrote:
>|||I dont quite get your question. Is this what you were expecting?
create proc registerTable
@.email varchar(30)
as
insert into employees
select name,email
from users
where email=@.email
go
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Diego F." <diegofrNO@.terra.es> wrote in message
news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
> Hi. I'm writing my firsts stored procedures.
> I'm trying to get a row from one table and insert the results in other
> table. By now I have this:
> create proc registerTable
> @.email varchar(30)
> as
> select name,email
> from users
> where email=@.email
> go
> insert into employees
> exec registerTable 'users'
> go
> But I need to include the insert in the procedure. How can I do that?
> --
> Regards,
> Diego F.
>
>|||Yes, it was exactly that :-)
Regards,
Diego F.
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> escribi en el mensaje
news:db85pe$rc0$1@.news01.intel.com...
>I dont quite get your question. Is this what you were expecting?
> create proc registerTable
> @.email varchar(30)
> as
> insert into employees
> select name,email
> from users
> where email=@.email
> go
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Diego F." <diegofrNO@.terra.es> wrote in message
> news:uQNgczRiFHA.3216@.TK2MSFTNGP10.phx.gbl...
>sql

Help with simple insert, how to use primary key?

Ive added a primary key called ID to my table, now my insert stored procedure dont no longer work.

i want an unique identifier for each row.

heres my stored procedure:

CREATE PROCEDURE composeMessage

-- Add the parameters for the stored procedure here

@.username varchar(24),

@.sender varchar(24),

@.date dateTime,

@.subject varchar(255),

@.message varchar(2500)

AS

BEGIN

insert into Messages(

"Username",

"Sender",

"Date",

"Subject",

"Message"

)

values (

@.username,

@.sender,

@.date,

@.subject,

@.message

)

END

GO

heres my sqlcreate table:

USE [Messenger]

GO

/****** Object: Table [dbo].[Messages] Script Date: 09/12/2006 15:13:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Messages](

[Username] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,

[Sender] [varchar](24) COLLATE Latin1_General_CI_AS NOT NULL,

[Subject] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,

[Message] [varchar](2500) COLLATE Latin1_General_CI_AS NOT NULL,

[Date] [datetime] NOT NULL,

[ID] [int] NOT NULL,

CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

As primary keycan't be null, what do i put for primary key for my insert to work?

hope you understand what i mean?

Am i right that i have to set the table designer/identity column to my primary key?

It generates an unique incresing number, so doi i use that?

|||

If you want to have an increasing value, you will have to switch on the identity property on your column.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql

Help with select

Hi
I have a table with year and date values
create table year_mon
(year char (6),
mon char(2)
)
insert into year_mon values ('2003',12)
insert into year_mon values ('2004',12)
insert into year_mon values ('2005',12)
insert into year_mon values ('2003',3)
insert into year_mon values ('2003',6)
insert into year_mon values ('2003',9)
insert into year_mon values ('2004',3)
insert into year_mon values ('2004',6)
How can I insert a 0 value before the mon where mon is 3,6 or 9
select year, mon from year_mon
go
gives me
2003 12
2004 12
2005 12
2003 3 --> would like the values to be displayed as 2003 03
2003 6 --> would like the values to be displayed as 2003 06
2003 9 --> would like the values to be displayed as 2003 09
etc ...
AHi,
Try the below statement
select year, right(('0'+ltrim(rtrim(mon))),2) from year_mon
Thanks
Hari
SQL Server MVP
"ajmister" <ajmister@.optonline.net> wrote in message
news:%23hNQxFwWFHA.2700@.TK2MSFTNGP12.phx.gbl...
> Hi
> I have a table with year and date values
> create table year_mon
> (year char (6),
> mon char(2)
> )
> insert into year_mon values ('2003',12)
> insert into year_mon values ('2004',12)
> insert into year_mon values ('2005',12)
> insert into year_mon values ('2003',3)
> insert into year_mon values ('2003',6)
> insert into year_mon values ('2003',9)
> insert into year_mon values ('2004',3)
> insert into year_mon values ('2004',6)
> How can I insert a 0 value before the mon where mon is 3,6 or 9
> select year, mon from year_mon
> go
> gives me
> 2003 12
> 2004 12
> 2005 12
> 2003 3 --> would like the values to be displayed as 2003 03
> 2003 6 --> would like the values to be displayed as 2003 06
> 2003 9 --> would like the values to be displayed as 2003 09
> etc ...
> A
>|||Try,
update year_mon
set mon = '0' + ltrim(cast(month as int))
where len(month) = 1;
AMB
"ajmister" wrote:

> Hi
> I have a table with year and date values
> create table year_mon
> (year char (6),
> mon char(2)
> )
> insert into year_mon values ('2003',12)
> insert into year_mon values ('2004',12)
> insert into year_mon values ('2005',12)
> insert into year_mon values ('2003',3)
> insert into year_mon values ('2003',6)
> insert into year_mon values ('2003',9)
> insert into year_mon values ('2004',3)
> insert into year_mon values ('2004',6)
> How can I insert a 0 value before the mon where mon is 3,6 or 9
> select year, mon from year_mon
> go
> gives me
> 2003 12
> 2004 12
> 2005 12
> 2003 3 --> would like the values to be displayed as 2003 03
> 2003 6 --> would like the values to be displayed as 2003 06
> 2003 9 --> would like the values to be displayed as 2003 09
> etc ...
> A
>
>|||Hi
There are several ways, this is one
select year, right('0'+rtrim(MON),2) from year_mon
go
John
"ajmister" wrote:

> Hi
> I have a table with year and date values
> create table year_mon
> (year char (6),
> mon char(2)
> )
> insert into year_mon values ('2003',12)
> insert into year_mon values ('2004',12)
> insert into year_mon values ('2005',12)
> insert into year_mon values ('2003',3)
> insert into year_mon values ('2003',6)
> insert into year_mon values ('2003',9)
> insert into year_mon values ('2004',3)
> insert into year_mon values ('2004',6)
> How can I insert a 0 value before the mon where mon is 3,6 or 9
> select year, mon from year_mon
> go
> gives me
> 2003 12
> 2004 12
> 2005 12
> 2003 3 --> would like the values to be displayed as 2003 03
> 2003 6 --> would like the values to be displayed as 2003 06
> 2003 9 --> would like the values to be displayed as 2003 09
> etc ...
> A
>
>|||Sorry.
select [year], right('0' + ltrim(cast(month as int)), 2) as [month]
from year_mon
AMB
"Alejandro Mesa" wrote:
> Try,
> update year_mon
> set mon = '0' + ltrim(cast(month as int))
> where len(month) = 1;
>
> AMB
> "ajmister" wrote:
>|||Thank you all. I was able to get the output using
select year,
right("0" + convert(varchar(2),mon) as mon.
from year_mon
Aj
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:BAA3A38D-E203-4D5D-B39E-C97330F83F84@.microsoft.com...
> Sorry.
> select [year], right('0' + ltrim(cast(month as int)), 2) as [month]
> from year_mon
>
> AMB
> "Alejandro Mesa" wrote:
>|||You have missed the point of SQL.
The language has temporal data types, so you use them for temporal
data. Look up the concept of proper domains for data. This is not
COBOL any more; we do not use strings and numerics for this. The
second fundamental thing that you missed is that time is always modeled
as durations. The third thing is that in a tiered architecture display
and formatting are never done in the database, but belongs in the front
end.
CREATE TABLE MonthlyCalendar
(year_month CHAR (7) NOT NULL PRIMARY KEY,
month_start_date DATETIME NOT NULL,
month_end_date DATETIME NOT NULL
CHECK (month_start_date < month_end_date));|||> The
> second fundamental thing that you missed is that time is always modeled
> as durations.
I disagree. According to Snodgrass (Developing Time-Oriented Database
Applications in SQL), time data types include Instance, Interval and Period.
His
storage could be commensurate with an Interval (e.g. March 2005, May 2004).
Granted, even intervals of this nature can be stored using standard DateTime
data types.

> The third thing is that in a tiered architecture display
> and formatting are never done in the database, but belongs in the front
> end.
Agreed. The reporting engine should be doing the formatting.
Thomassql

Help with Query - Insert multiple rows and link between tables.

I am trying to do the following:

Insertn rows into A Table calledEAItems. For each row that is inserted intoEAItemsI need to take thatItemID(PK) and insert a row intoEAPackageItems.

I'm inserting rows from a Table calledEATemplateItems.

So far I have something like this: (I have the PackageID already at the start of the query).

 INSERT INTO EAItems(Description, Recommendation, HeadingID)
SELECT Description, Recommendation, HeadingID
FROM EATemplateItemsWHERE EATemplateItems.TemplateID = @.TemplateID
INSERT INTO EAPackageItems(ItemID, PackageID) ...

I have no idea how to grab each ITemID as it's created, and then put it into the EAPackageItems right away.Any Advice / help would rock! Thanks

I think you will want to do this as a stored procedure. As you insert an individual row you can use the @.@.IDENTITY variable for the last inserted row. You could save that to a variable and insert the record to the second table. In your first query you could adjust it to select the rows into a table variable and then loop over the rows in the table variable and use that loop to take care of your individual inserts.

The T-SQL snippet below is the basic structure for what I am describing.

DECLARE @.MyTableTABLE(IDint IDENTITY,Name varchar(20))INSERT INTO @.MyTable (Name)SELECT NameFROM OtherTableDECLARE @.CurIDintDECLARE @.MaxIDintDECLARE @.RowIDintSET @.MaxID = (SELECT MAX(ID)FROM @.MyTable )SET @.CurID = 1WHILE (@.CurID <= @.MaxID)BEGIN-- use CurID to access the row in @.MyTable-- do your insert-- get the @.@.IDENTITY-- use that value for the next insert-- be sure to increment the @.CurID to the next rowSET @.CurID = @.CurID + 1END

|||

Thanks for the reply.

I'll work with that when I get to work - it seems logically straight forward. The script you put down can work in both SQL 2000 and SQL 2005 right? I hope so :D

|||Yes, there is nothing specific in there for SQL Server 2005.

Help with query

I want to take this code and insert data on a monthly basis. For example all the data for month of August.

INSERT INTO IISLOG
( ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department) SELECT
ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department
FROM IISLOG.DBO.IISLOG
WHERE LogTime BETWEEN '2004-01-01' AND GetDate()-1
GO

ThanksChange your where clause

WHERE LogTime BETWEEN '2004-01-01' AND GetDate()-1

To this

WHERE MONTH(LogTime)=8|||If you can ride an index, I'd use:INSERT INTO IISLOG
( ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department) SELECT
ClientHost, Username
, Logtime, Service, Machine
, ServerIP, Processingtime, Bytesrecvd
, BytesSent, ServiceStatus, Win32status
, Operation, Target, Parameters
, Department
FROM IISLOG.DBO.IISLOG
WHERE '2004-08-01' <= LogTime
AND LogTime < '2004-09-01'
GOThis lets you ride an index if one exists on LogTime, which can improve your performance by literally orders of magnitude (things can take much less than one tenth as long as not riding the index).

-PatP

Help with OpenXML

Hi,
I am trying to insert an XML document into into 3 tables which match the
hierachy of the xml and which appears to be working. This data could be an
update or an insert so for simplicity I have an initial procedure which
clears down the existing data using a cascading delete. This too appears to
be working.
However, when I check the tables there appears to be hundreds of duplicate
rows ? Has anybody come across anything similar. The tables do have an
identity field within them, which might be causing an issue I guess.
I am using SQL Server 2000 (sp3) on a Windows 2000 Server box.
Grateful for any helpJust realised we are actually using sp4 which might explain the problem, as
I
am using @.mp/@.parentid ?
"Redowl" wrote:

> Hi,
> I am trying to insert an XML document into into 3 tables which match the
> hierachy of the xml and which appears to be working. This data could be a
n
> update or an insert so for simplicity I have an initial procedure which
> clears down the existing data using a cascading delete. This too appears
to
> be working.
> However, when I check the tables there appears to be hundreds of duplicate
> rows ? Has anybody come across anything similar. The tables do have an
> identity field within them, which might be causing an issue I guess.
> I am using SQL Server 2000 (sp3) on a Windows 2000 Server box.
> Grateful for any help

Help with Insert/Trigger

Just curious if there's anything in SQL comparable to a FOR EACH ROW trigger in Oracle.
I'm doing an import of a bunch of rows into a parent table using an INSERT/SELECT statement and I have triggers that fire pulling data from the same row in the source table into the child tables and creating the foreign key references. The problem lies in
the fact that the triggers only fire once seeing as how it reads the INSERT statement as one statement and not row by row.
Any thoughts?
Thanks.
Pete
There is no equivalent. You can search the newsgroups (.programming) for
many examples. In general, set-based solutions are faster and more
efficient for any sql programming.
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:3287C30A-AAF3-408C-83F9-9F72DC78ADAD@.microsoft.com...
> Just curious if there's anything in SQL comparable to a FOR EACH ROW
trigger in Oracle.
> I'm doing an import of a bunch of rows into a parent table using an
INSERT/SELECT statement and I have triggers that fire pulling data from the
same row in the source table into the child tables and creating the foreign
key references. The problem lies in the fact that the triggers only fire
once seeing as how it reads the INSERT statement as one statement and not
row by row.
> Any thoughts?
> Thanks.
> Pete

Help with INSERT TRIGGER - fails with error.

I need a trigger (well, I don't *need* one, but it would be optimal!)
but I can't get it to work because it references ntext fields.

Is there any alternative? I could write it in laborious code in the
application, but I'd rather not!

DDL for table and trigger below.

TIA

Edward

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblMyTable]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tblMyTable]
GO

CREATE TABLE [dbo].[tblMyTable] (
[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable

FOR INSERT

AS

BEGIN

IF UPDATE(fldKBSubject)
BEGIN

UPDATE
tblMyTable
SET
fldSubject = i.fldKBSubject
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID

END

IF UPDATE (fldKBDescription)
BEGIN
UPDATE
tblMyTable
SET
fldDescription = i.fldKBDescription
FROM
inserted i INNER JOIN
tblMyTable ON i.fldCSID = tblMyTable.fldCSID
END
ENDOn 17 Mar 2006 06:24:01 -0800, teddysnips@.hotmail.com wrote:

>I need a trigger (well, I don't *need* one, but it would be optimal!)
>but I can't get it to work because it references ntext fields.
>Is there any alternative? I could write it in laborious code in the
>application, but I'd rather not!
>DDL for table and trigger below.

Hi Edward,

Thanks for providing the DDL!

I'll come to your problem later, but first some comments.

>CREATE TABLE [dbo].[tblMyTable] (
>[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
>[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
>GO

You didn't declare any PRIMARY KEY in this database. I think you
intended to make the column fldCSID a PRIMARY KEY, but you didn't
declare it as such.

After that, you should also declare some other column (or combination of
columns) as UNIQUE. With this design, there's nothing to prevent you
from accidentally inserting the same data twice.

Does the fldSCID column really have to be uniqueidentifier? If you
choose to use surrogate keys, then IDENTITY should be the regular
choice; situations that call for uniqueidentifier are very rare.

Apart from the uniqueidentifier column, all your columns accept NULLs.
Do you really want to accept rows with just NULLs in your database?
Nullable columns should be the exception, not the rule.

Are you sure that all these columns need to be ntext? I can somewhat
imagine having descriptions of over 4,000 characters - but subjects? I
think that you should probably define Subject and KBSubject ar nvarchar
with an appropriate maximum length (hopefully less than 100, but I don;t
know your business of course). You might also want to rethiink the
choice of ntext/nvarchar over text/varchar - unless you really need to
store characters from non-Western alphabets or other characters that are
only available in unicode, there's no reason to use double the space
taken.

On to the trigger (I removed the empty lines for readability)

>CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable
>FOR INSERT
>AS
>BEGIN
>IF UPDATE(fldKBSubject)
>BEGIN
>UPDATE
>tblMyTable
>SET
>fldSubject = i.fldKBSubject
>FROM
>inserted i INNER JOIN
>tblMyTable ON i.fldCSID = tblMyTable.fldCSID
>END
>IF UPDATE (fldKBDescription)
>BEGIN
>UPDATE
>tblMyTable
>SET
>fldDescription = i.fldKBDescription
>FROM
>inserted i INNER JOIN
>tblMyTable ON i.fldCSID = tblMyTable.fldCSID
>END
>END

In an INSERT trigger, you don't need IF UPDATE(). It only makes sense in
an UPDATE trigger; for an INSERT, the IF UPDATE() will be true for each
column in the table.

There's also no need to use two seperate update statements. You can
combine these into one and gain some performance.

But the most important question, I think, is why you want to do this. If
the KBSubject and KBDescription are always a copy of the Subject and
Description columns, why have them?

Anyway, back to your question:

>I need a trigger (well, I don't *need* one, but it would be optimal!)
>but I can't get it to work because it references ntext fields.

You can't reference ntext columns in the inserted column. But you can
join to the base table and get the data from there. (Or you could
convert the trigger to an instead of trigger, in which case the ntext
data *WILL* be available in the inserted table - but that's not the
easiest solution in this case).

CREATE TRIGGER PrepopulateKBFieldsFromQuery
ON dbo.tblMyTable
FOR INSERT
AS
UPDATE MyTable
SET Subject = KBSubject,
Description = KBDescription
WHERE EXISTS
(SELECT *
FROM inserted AS i
WHERE i.CSID = MyTable.CSID)
go

--
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> After that, you should also declare some other column (or combination of
> columns) as UNIQUE. With this design, there's nothing to prevent you
> from accidentally inserting the same data twice.

I would guess that one of the subjects are intended to be a key of some
sort, but since it's probably a free-text column, a PK/UNIQUE constraint
only gives you half protection, as it will not catch variations due to
typos and spaces.

> Does the fldSCID column really have to be uniqueidentifier? If you
> choose to use surrogate keys, then IDENTITY should be the regular
> choice; situations that call for uniqueidentifier are very rare.

Unless you are into replication. GUIDs are also popular among web
programmers, because they can save a roundtrip to get the key value.
I've seen more than one URL with GUIDs in them.

> You might also want to rethiink the choice of ntext/nvarchar over
> text/varchar - unless you really need to store characters from
> non-Western alphabets or other characters that are only available in
> unicode, there's no reason to use double the space taken.

Not sure I agree. The cost for a change when a requirement to support,
say, Japanese, comes can prove to be prohibitive.

> But the most important question, I think, is why you want to do this. If
> the KBSubject and KBDescription are always a copy of the Subject and
> Description columns, why have them?

The trigger name says "prepopulate". I guess Edward is setting an initial
default.

--
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|||Hugo Kornelis wrote:
> On 17 Mar 2006 06:24:01 -0800, teddysnips@.hotmail.com wrote:
[...]
> >CREATE TABLE [dbo].[tblMyTable] (
> >[fldCSID] uniqueidentifier ROWGUIDCOL NOT NULL ,
> >[fldSubject][ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldKBSubject] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >[fldKBDescription] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> >GO
> You didn't declare any PRIMARY KEY in this database. I think you
> intended to make the column fldCSID a PRIMARY KEY, but you didn't
> declare it as such.

Weird, it IS the PK! An error with the script, or maybe I was a bit
eager on the editing.

> Does the fldSCID column really have to be uniqueidentifier? If you
> choose to use surrogate keys, then IDENTITY should be the regular
> choice; situations that call for uniqueidentifier are very rare.

At one time the database was replicated.

> Apart from the uniqueidentifier column, all your columns accept NULLs.
> Do you really want to accept rows with just NULLs in your database?
> Nullable columns should be the exception, not the rule.

Couldn't agree more - not my DB design!

> Are you sure that all these columns need to be ntext? I can somewhat
> imagine having descriptions of over 4,000 characters - but subjects? I
> think that you should probably define Subject and KBSubject ar nvarchar
> with an appropriate maximum length (hopefully less than 100, but I don;t
> know your business of course). You might also want to rethiink the
> choice of ntext/nvarchar over text/varchar - unless you really need to
> store characters from non-Western alphabets or other characters that are
> only available in unicode, there's no reason to use double the space
> taken.

The ntext come from the Access upsizing wizard. The original designer
simply left the default values (it would have been memo columns in
Access)

> On to the trigger (I removed the empty lines for readability)
> >CREATE TRIGGER PrepopulateKBFieldsFromQuery ON dbo.tblMyTable
> >FOR INSERT
> >AS
> >BEGIN
> >IF UPDATE(fldKBSubject)
> >BEGIN
> >UPDATE
> >tblMyTable
> >SET
> >fldSubject = i.fldKBSubject
> >FROM
> >inserted i INNER JOIN
> >tblMyTable ON i.fldCSID = tblMyTable.fldCSID
> >END
> >IF UPDATE (fldKBDescription)
> >BEGIN
> >UPDATE
> >tblMyTable
> >SET
> >fldDescription = i.fldKBDescription
> >FROM
> >inserted i INNER JOIN
> >tblMyTable ON i.fldCSID = tblMyTable.fldCSID
> >END
> >END
> In an INSERT trigger, you don't need IF UPDATE(). It only makes sense in
> an UPDATE trigger; for an INSERT, the IF UPDATE() will be true for each
> column in the table.

Yes, I realise that now - thanks.

> There's also no need to use two seperate update statements. You can
> combine these into one and gain some performance.

I tend to be very "belt and braces" with my code.

> But the most important question, I think, is why you want to do this. If
> the KBSubject and KBDescription are always a copy of the Subject and
> Description columns, why have them?

I want to do it because the underlying application is a query system.
Some queries will form part of a Knowledge Base system. The client
wants the (QUERY)Subject and Description columns to be mirrored by the
KBSubject and KBDescription fields, at least initially. Only the KB
versions will be exposed to the customer.

> Anyway, back to your question:
> >I need a trigger (well, I don't *need* one, but it would be optimal!)
> >but I can't get it to work because it references ntext fields.
> You can't reference ntext columns in the inserted column. But you can
> join to the base table and get the data from there. (Or you could
> convert the trigger to an instead of trigger, in which case the ntext
> data *WILL* be available in the inserted table - but that's not the
> easiest solution in this case).
> CREATE TRIGGER PrepopulateKBFieldsFromQuery
> ON dbo.tblMyTable
> FOR INSERT
> AS
> UPDATE MyTable
> SET Subject = KBSubject,
> Description = KBDescription
> WHERE EXISTS
> (SELECT *
> FROM inserted AS i
> WHERE i.CSID = MyTable.CSID)
> go

And that is absolutely spot on! Many thanks.

Edward

Help with insert stored procedure

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_IDInt,
@.Receipt_IDInt,
@.Fund_IDInt,
@.Amount_allocatedmoney,
@.DateEntereddatetime,
@.EnteredBynvarchar(50)

AS

SELECT (SUM(tblReceiptsFunds.Amount_allocated) +@.Amount_allocated)AStotal_allocations,Sum(tblReceipts.AmountPaid)astotal_payments
FROM tblReceiptsFundsINNERJOIN
tblReceiptsON tblReceiptsFunds.Receipt_ID = tblReceipts.Receipt_ID
WHERE tblReceipts.Payment_ID=@.Payment_ID

IF (total_allocations<total_payments)

INSERT INTO tblReceiptsFunds ([Receipt_ID],[Fund_ID],[Amount_allocated],DateEntered,EnteredBy)Values (@.Receipt_ID,@.Fund_ID,@.Amount_allocated,@.DateEntered,@.EnteredBy)ELSE BEGINPRINT'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'.

Try this or something like it. I'm not sure if the data types are correct.

ALTER PROCEDURE [dbo].[AddNewFundAllocation] @.Payment_IDInt,@.Receipt_IDInt,@.Fund_IDInt,@.Amount_allocatedmoney,@.DateEntereddatetime,@.EnteredBynvarchar(50)ASDeclare @.total_allocationsdecimal(18,2)Declare @.total_paymentsdecimal(18,2)SELECT @.total_allocations = (SUM(tblReceiptsFunds.Amount_allocated) + @.Amount_allocated), @.total_payments =Sum(tblReceipts.AmountPaid)FROM tblReceiptsFundsINNERJOIN tblReceiptsON 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)Values (@.Receipt_ID,@.Fund_ID,@.Amount_allocated,@.DateEntered,@.EnteredBy)ELSE BEGINPRINT'You are attempting to allocate more to funds than your total payment.'END

|||

John,

That worked with one exception, my doing. Do you know how I could get the PRINT (PRINT'You are attempting to allocate more to funds than your total payment.'
) to show on my web page?

I have a try catch like this:

Try

Con.Open()

intInsertCount = cmdInsert.ExecuteNonQuery()

Con.Close()

gvFundAllocations.DataBind()

Catch exAs Exception

Response.Write(ex.Message)

Finally

Con.Close()

Me.txtFundAmount.Text = 0

Me.FundDrop.SelectedValue =""

EndTry

|||

Yes there are 2 ways. 1 set and output parameter, and 2 have that message return in the result set like this.

ALTER PROCEDURE [dbo].[AddNewFundAllocation] @.Payment_IDInt,@.Receipt_IDInt,@.Fund_IDInt,@.Amount_allocatedmoney,@.DateEntereddatetime,@.EnteredBynvarchar(50)ASDeclare @.total_allocationsdecimal(18,2)Declare @.total_paymentsdecimal(18,2)SELECT @.total_allocations = (SUM(tblReceiptsFunds.Amount_allocated) + @.Amount_allocated), @.total_payments =Sum(tblReceipts.AmountPaid)FROM tblReceiptsFundsINNERJOIN tblReceiptsON 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)Values (@.Receipt_ID,@.Fund_ID,@.Amount_allocated,@.DateEntered,@.EnteredBy)Select'All OK!'as messageELSE BEGINSelect'You are attempting to allocate more to funds than your total payment.'as message--PRINT 'You are attempting to allocate more to funds than your total payment.'END

|||

John,

So sorry I just cannot get the message to showup on the web page. Any suggestions?

The stored procedure is doing what it should, if the amount is to high it terminates.

|||

It is not throwing an exception to the web page when the amount is to high, but the stored procedure is terminating. I don't understand this.

|||

The stored procedure is returning a result set. This result being of one row and one column named "message".

A result set can be read using the SqlDataReader or the ExecuteScalar method of the command object.

Do some reading on binding a SqlDataReader to your control, or in using the ExecuteScalar method and you should be OK.

Let me know how you do.

Help with Insert statemnt selecting from Access database

Hi, I need to select and later Update a database with information I select
from a Access 2000 database. In the following T-SQL, I want to select only
one record for each VName not already exists in my database. Since each
VName have many records in the table, I'm only looking with the record that
has the most recenst date-time, d_DateTime. I keep getting error with this
though. Appreciate it if someone can help me out here.
Thanks, AlphaOops, forgot to past the script.
INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading )
SELECT DISTINCT [Data].[d_RemoteName], [Data].[d_DateTime],
[Data].[d_OdometerTenths]
FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
and ([Data].[d_DateTime]=select Max([Data].[d_DateTime])
from [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA)
ORDER BY [d_RemoteName]
"Alpha" wrote:

> Hi, I need to select and later Update a database with information I select
> from a Access 2000 database. In the following T-SQL, I want to select onl
y
> one record for each VName not already exists in my database. Since each
> VName have many records in the table, I'm only looking with the record tha
t
> has the most recenst date-time, d_DateTime. I keep getting error with thi
s
> though. Appreciate it if someone can help me out here.
> Thanks, Alpha|||Alpha,
Try:
SELECT VName, MAX(d_DateTime)
FROM yourtable
GROUP BY VName
HTH
Jerry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@.microsoft.com...
> Hi, I need to select and later Update a database with information I select
> from a Access 2000 database. In the following T-SQL, I want to select
> only
> one record for each VName not already exists in my database. Since each
> VName have many records in the table, I'm only looking with the record
> that
> has the most recenst date-time, d_DateTime. I keep getting error with
> this
> though. Appreciate it if someone can help me out here.
> Thanks, Alpha|||Why not just use DISTINCT and remove the datetime criteria after NOT IN?
HTH
Jerry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:4ACAE646-A41C-432D-9C65-3B28C3446C6C@.microsoft.com...
> Oops, forgot to past the script.
> INSERT INTO VehDetail ( VName, LastOdometerDate, LastOdometerReading )
> SELECT DISTINCT [Data].[d_RemoteName], [Data].[d_DateTime],
> [Data].[d_OdometerTenths]
> FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
> WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
> and ([Data].[d_DateTime]=select Max([Data].[d_DateTime])
> from [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA)
> ORDER BY [d_RemoteName]
> "Alpha" wrote:
>|||I got error message that it doesn't like the database in "From". Do you kno
w
how to specify a tabe from Access database?
SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]),
[Data].[d_OdometerTenths]
FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
GROUP BY [Data].[d_RemoteName]
Thank you,
Alpha
"Jerry Spivey" wrote:

> Alpha,
> Try:
> SELECT VName, MAX(d_DateTime)
> FROM yourtable
> GROUP BY VName
> HTH
> Jerry
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:A06A1EE4-8320-4C5F-A2C7-A124371BB832@.microsoft.com...
>
>|||I usually add a linked server definition for the Access database. Take a
look at sp_addlinkedserver in the SQL Server Books Online - there is an
example of how to create one there.
HTH
JErry
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:C57EA186-1559-4E8F-B85D-3634AEC858A6@.microsoft.com...
>I got error message that it doesn't like the database in "From". Do you
>know
> how to specify a tabe from Access database?
> SELECT [Data].[d_RemoteName],MAX([Data].[d_DateTime]),
> [Data].[d_OdometerTenths]
> FROM [MS Access;DATABASE=C:\VMS\VMSDB\Ats20050830
Db.mdb;].DATA
> WHERE [Data].[d_RemoteName] NOT IN(SELECT VNAME FROM VehDetail)
> GROUP BY [Data].[d_RemoteName]
> Thank you,
> Alpha
> "Jerry Spivey" wrote:
>|||Is link server the only option to get the Access data? My application looks
in a directory where each day a new Access file is created with file name
inlcuding the date. I use the most recent file each time the user start my
application and needs to update my database. So you see, the link server
won't work for me because it needs to specify the file location. Unless, I
would delete the link server and create a new one each time my application
starts. But that seems odd and is there even a way to delete the
linkedserver?
Thanks, Alpha.
"Jerry Spivey" wrote:

> I usually add a linked server definition for the Access database. Take a
> look at sp_addlinkedserver in the SQL Server Books Online - there is an
> example of how to create one there.
> HTH
> JErry
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:C57EA186-1559-4E8F-B85D-3634AEC858A6@.microsoft.com...
>
>|||Great, that works...... Except that I don't think it's getting me the
records that I want. I need to select for oen distinct VNAME record that ha
s
the most recent d_DateTime. I think the script I have below just select a
distinct VName and then plug in the MAx DATE and then the Max Odometer which
each can come from different records with the same VNAME. Anyway to do what
I want.
Thanks a lot, Alpha
"Jerry Spivey" wrote:

> Alpha,
> Try OPENROWSET. From SQL Server BOL:
> C. Use the Microsoft OLE DB Provider for Jet
> This example accesses the orders table in the Microsoft Access Northwind
> database through the Microsoft OLE DB Provider for Jet.
>
> Note This example assumes that Access is installed.
>
> USE pubs
> GO
> SELECT a.*
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
> AS a
> GO
> HTHJerry"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:1876CCCD-5CC0-4185-85FA-A755936B7C62@.microsoft.com...
>
>

Help with Insert Statement!!

Can somebody help me with an Insert statement. I'm trying to insert data from one table into another table.
My structure that i wrote isn't working at all.
Please Helpif both tables have the same structure:
insert into Another_Table
select * from One_Table

if you wish to only insert data from a few attributes:
insert into Another_Table (First, Last, Middle)
select FirstName, LastName, MiddleName from One_Table

Of course you can add a where clause when needed

Help with insert sql statements...

Hi guys! I have these commands that insert into two tables, if condition 1 is met, it will insert into the first table, if the second condition is met, it will insert into the second table.

Is there a way for the insert statements to be merged so that I won't be executing two statements?

Dim update_phase_before As New SqlCommand("INSERT INTO TE_shounin_todokesho_jizen (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='before'", cnn)

Dim update_phase_after As New SqlCommand("INSERT INTO TE_shounin_todokesho_jigo (syain_No,date_kyou,time_kyou) SELECT syain_No,date_kyou,time_kyou FROM TE_todokesho WHERE TE_todokesho.b_a='after'", cnn)

Thanks.

If you really need to have it in one statement, you could put all of your logic into a stored procedure and simply call it from you application.

help with Insert SQL Query

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 makeone statement query that can solve that situation?

Hello,

if you create the id yourself then you can insert it into the second table. With SQL Server you can send two queries in one command, separated by a;. But if you have the first table set up with an auto incrementing identity then you will first have to find out the id that the database has created for you. This can be done with the SCOPE_IDENTITY() function in SQL Server.

Good luck!

|||

i create the id yourself...thanx for your help...anyway, is there any other way? Because actually i have to add the id from 1st table to many other tables...maybe 5 6 tables...i think a lot of sql query i have to execute if i have to add so many ids from 1st table...

|||

Hi,

As far as I know, there is no other ways. Multiple SQL Statement can be wrapped in a single SqlCommand. However, these muliple statements have to be written manually. The SQL Server itself will not do this for you. In this case, you may need to write 5-6 queries and wrap them in one SqlCommand.

HTH.

|||

I think that you have to look on the structure of your database if you have to insert the same ID to multiple data tables. Other tables should have it as foreign key so it should be inserted only when you add new data to table linked to you main table, and in this case you have to get your ID and insert it together with record data. The best way to do this is stored procedure with included transaction if you need it.

If it is true that you create ID yourself maybe you can use identity column in your main table to do it automatically?

Thanks

JPazgier

Help with INSERT query

I need to append records into a table with a two column primary key from a table that contains many records that already exist in the target table. How do I separate out the records in the source table that don't exist in the target?

When I used to do this in Access, I could write a simple append query that would automatically skip records in source that violated key constraints in the target. I'm trying to duplicate that funcionality.

Thanks.

INSERT INTO Target (field1, field2)
SELECT
field1, field2
FROM Source
WHERE Source.field NOT IN (SELECT Field FROM TARGET)

If you want to do a multi-field check...

INSERT INTO Target (field1, field2)
SELECT
field1, field2
FROM Source
LEFT OUTER JOIN Target
ON Target.field = Source.Field
WHERE Target.field IS NULL

|||

Thanks so much for the quick reply.

I need to check both Source.field1 and Source.field2 against their counterparts in Target since it's the combination that provides the primary key in Target.

What I've tried that gets me a duplicate key error is

INSERT INTO Target (field1, field2)
SELECT field1, field2
FROM Source
LEFT OUTER JOIN Target
ON Target.field1 = Source.Field1 AND Target.field2 = Source.Field2
WHERE Target.field1 IS NULL AND Target.field2 IS NULL

It looks like it should work, but it doesn't.

Kato

|||

In your where clause, all you need to do is check the nullability of one field from the Target table. If the join fails, every field in your left table will be null.

If you get a duplicate key error, try using a SELECT DISTINCT instead of just a select.

If you still get the error, what's your primary key / unique index on in Target?

Help with insert query

TblA TblB
---
ColA int Identity ColB char(5)
ColB char(5) FK
ColC char(6)
ColD char(50)
I would like to add rows to TblA where TblA/ColB would be populated
from TblB and ColC and ColD would be literals for each row added. ColC
= 'XXXX' and ColD = 'Suspense'
ColB and ColC in TblA combine to make a referenced key. There are
already some rows which are in TblA.
For example,
INSERT INTO TblA
(ColB, ColC, ColD)
VALUES ((
SELECT ColB
FROM TblB
WHERE ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX'
)), 'XXXX', 'Suspense')
Is there any way to do this?
TIA Lars> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
>
INSERT INTO TblA(ColB, ColC, ColD)
SELECT ColB, 'XXXX', 'Suspense'
FROM TblB
WHERE ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX')
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com|||Hope i understood you right:
Insert Into TblA
Select 'SomethingforA',
ColB,
'XXXX',
'Suspense'
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"larzeb" <larzeb@.community.nospam> schrieb im Newsbeitrag
news:df286192dm1u9468c1i6bqfkulc5pfmoh5@.
4ax.com...
> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
> Is there any way to do this?
> TIA Lars|||Try,
INSERT INTO TblA (ColB, ColC, ColD)
SELECT ColB, 'XXXX', 'Suspense'
FROM TblB
WHERE
ColB NOT IN (
SELECT ColB
FROM TblA
WHERE ColC = 'XXXX'
);
AMB
"larzeb" wrote:

> TblA TblB
> ---
> ColA int Identity ColB char(5)
> ColB char(5) FK
> ColC char(6)
> ColD char(50)
> I would like to add rows to TblA where TblA/ColB would be populated
> from TblB and ColC and ColD would be literals for each row added. ColC
> = 'XXXX' and ColD = 'Suspense'
> ColB and ColC in TblA combine to make a referenced key. There are
> already some rows which are in TblA.
> For example,
> INSERT INTO TblA
> (ColB, ColC, ColD)
> VALUES ((
> SELECT ColB
> FROM TblB
> WHERE ColB NOT IN (
> SELECT ColB
> FROM TblA
> WHERE ColC = 'XXXX'
> )), 'XXXX', 'Suspense')
> Is there any way to do this?
> TIA Lars
>

Help with INSERT Procedure

Hello,

I have a procedure which INSERTS a new record in two tables [Content] and [ContentLocalized] given [ContentName] and [ContentCulture].

Here are the table structures:

<Content>
|-- [ContentId] Type=UniqueIdentifier PK
| [ContentName] Type=NVarChar(100)
|
| <ContentLocalized>
| [ContentLocalizedId] Type=UniqueIdentifier PK
| ---> [ContentId] Type=UniqueIdentifier FK
| [ContentCulture] Type=NVarChar(5)
| [ContentHtml] Type=NVarChar(MAX)

WHAT I AM MISSING:

> If in <Content> THERE IS a record with the same [ContentName] then this record will be used AND:

If in <ContentLocalized> for the given [ContentName] THERE IS NO such [ContentCulture] then a new will be created with [ContentCulture] and [ContentHtml]

If in <ContentLocalized> for the given [ContentName]THERE IS such [ContentCulture] then its [ContentHtml] will be replaced by the given [ContentHtml]

> If in <Content> THERE IS NOT a record with the same [ContentName] then:

A new <Content> record will be created with [ContentName] and a new <ContentLocalized> record will be created with [ContentCulture] and [ContentHtml].

I know I didn't get there yet.

Could somebody help em out?

I am posting the INSERT Store Procedure as I have now:

1SET ANSI_NULLSON2GO3SET QUOTED_IDENTIFIERON4GO5ALTER PROCEDURE [dbo].[Content_CreateContentByNameAndCulture]6 @.ContentNameNVARCHAR(100),7 @.ContentCultureNVARCHAR(5),8 @.ContentHtmlNVARCHAR(MAX)9AS10BEGIN11 SET NOCOUNT ON;12DECLARE @.ContentIdUNIQUEIDENTIFIER;13SET @.ContentId =NEWID();14INSERT dbo.Content15 (16 ContentName17 )18SELECT19 @.ContentName;20SELECT @.ContentId;21INSERT dbo.ContentLocalized22 (23 ContentId,24 ContentCulture,25 ContentHtml26 )27SELECT28 @.ContentId,29 @.ContentCulture,30 @.ContentHtml;31END32GO333435

Thanks,

Miguel

Hello:

Here is one example involving the uniqueidentifier column:

ALTERPROCEDURE [dbo].[test_sp]

@.Captionnvarchar(50),

@.IsPublic

bit

AS

INSERTINTO [Albums]([c_id], [Caption],[IsPublic])VALUES(NEWID(), @.Caption, @.IsPublic)

RETURN

--Thid is the table I copied for you to test:

CREATE

TABLE [dbo].[Albums](

[AlbumID] [int]

IDENTITY(1,1)NOTNULL,

[Caption] [nvarchar]

(50)NOTNULL,

[IsPublic] [bit]

NOTNULL,

[c_id] [uniqueidentifier]

NULL)|||

SET ANSI_NULLSONGOSET QUOTED_IDENTIFIERONGOALTER PROCEDURE [dbo].[Content_CreateContentByNameAndCulture] @.ContentNameNVARCHAR(100), @.ContentCultureNVARCHAR(5), @.ContentHtmlNVARCHAR(MAX)ASBEGINSET NOCOUNT ON;DECLARE @.ContentIdUNIQUEIDENTIFIER,@.ExistingContentNameNVARCHAR(100)SELECT @.ExistingContentName = ContentNameFROM ContentWHERE ContentName = @.ContentName;IF (@.ExistingContentName =null)BEGININSERT INTO dbo.Content (ContentId, ContentName)VALUES (NEWID(), @.ContentName);ENDENDGO

Its not yet complete but i think its a start, does anyone knows how to make a statement that will return FALSE if the select statement returned no data and TRUE if there is >= 1?