inserting <NULL>

how do you write an insert into statement and keep <NULL> in the null cells?
i was trying something like this BUT when you try to write an IS NULL statement it doesnt work.
ISNULL(dbo.TRUNK02_LastVersion_PayableClaims_01.MO D1, NULL) AS Mod1,ISNULL(dbo.TRUNK02_LastVersion_PayableClaims_01.MO , NULL) AS Mod1,|||how is this any different?

ISNULL(dbo.qry_TRUNK02_LastVersion_PayableClaims01 .MOD1, NULL) AS Mod1

after i truncate and insert into my table i need to be able to query with an IS NULL statement.

select mod1
from table
where mod1 is null|||Books online: ISNULL Replaces NULL with the specified replacement value.
So what is the point of ISNULL([A], Null)?

Please explain more clearly what you are trying to do, and give some sample data.|||Yeah, what The Blind One said...

are you just meaning to insert a NULL into a column of a table?

as in:
INSERT INTO dbo.TRUNK02_LastVersion_PayableClaims_01 (Mod1, ...)
VALUES (NULL, ...)???

INSERT UPDATE Trigger Question

How would I write a trigger that updates the values of a Description
column to upper case for even IDs and to lower case for odd IDs? I
need this trigger to fire for INSERT and UPDATE events.Hi

Try something like:

CREATE TABLE Test ( id int not null identity (1,1), Description char(10) )

CREATE TRIGGER Test_Insert ON Test FOR INSERT AS
UPDATE TEST SET Description = CASE Id%2 WHEN 0 THEN UPPER(Description) ELSE
LOWER (Description) END

INSERT INTO TEST ( Description ) VALUES ('One')
INSERT INTO TEST ( Description ) VALUES ('Two')
INSERT INTO TEST ( Description ) VALUES ('Three')
INSERT INTO TEST ( Description ) VALUES ('four')
INSERT INTO TEST ( Description ) VALUES ('five')
INSERT INTO TEST ( Description ) VALUES ('SIX')
INSERT INTO TEST ( Description ) VALUES ('SEVEN')

SELECT * from Test

John

<imani_technology@.yahoo.com> wrote in message
news:f9208446.0309011615.5f269625@.posting.google.c om...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.|||imani_technology@.yahoo.com wrote in message news:<f9208446.0309011615.5f269625@.posting.google.com>...
> How would I write a trigger that updates the values of a Description
> column to upper case for even IDs and to lower case for odd IDs? I
> need this trigger to fire for INSERT and UPDATE events.

You might want to consider formatting the text on the client, when you
retrieve it from the database - presentation tasks don't really belong
in a database. But if you want to do it using a trigger, something
like this should work (assuming your 'ID' is an integer key column):

create trigger ATR_UI_MyTable
on dbo.MyTable after insert, update
as
update dbo.MyTable
set DescriptionColumn =
case i.IDColumn % 2
when 1 then lower(i.DescriptionColumn)
when 0 then upper(i.DescriptionColumn)
end
from dbo.MyTable t
join inserted i
on t.IDColumn = i.IDColumn

Simon

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank youOn Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:

>I am new to writting triggers. I am trying to write a trigger that adds up
4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank you
On Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:

>I am new to writting triggers. I am trying to write a trigger that adds up 4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

insert Trigger for summation

I am new to writting triggers. I am trying to write a trigger that adds up 4
numeric fields and writes it in a seperste field in the row within the same
table.
Example:
col 1 col2 col3 col4 sum
1 0 5 0 6
5 2 0 8 15
So when ever a value is added in col1,col2,col3,col4 I want it summed up in
'sum'
This table is actually linked to a AccessDB, where the 4 fields are entered
in and I need the sum field for reporting purposes
Any help would be appreciated
Thank youOn Wed, 23 Nov 2005 12:21:02 -0800, Amit wrote:
>I am new to writting triggers. I am trying to write a trigger that adds up 4
>numeric fields and writes it in a seperste field in the row within the same
>table.
>Example:
>col 1 col2 col3 col4 sum
>1 0 5 0 6
>5 2 0 8 15
>So when ever a value is added in col1,col2,col3,col4 I want it summed up in
>'sum'
>This table is actually linked to a AccessDB, where the 4 fields are entered
>in and I need the sum field for reporting purposes
>Any help would be appreciated
>Thank you
Hi Amit,
Instead of using a trigger, use a view to calculate the total when you
are reading the data. Or add a computed column to the table:
CREATE TABLE YourTable
(.....
Col1 int NOT NULL,
Col2 int NOT NULL,
Col3 int NOT NULL,
Col4 int NOT NULL,
TheSum AS Col1 + Col2 + Col3 + Col4,
PRIMARY KEY (...)
)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Insert Trigger and Updating a view

I am just getting back to SqlServer and TSQL after a 4 year hiatus.
I want to write a trigger to update a view with the same record that is
being inserted into a table. I have a trigger bound to the table to be
inserted and since it is a simple process, I will probably forgoing using a
stored proc.
In my trigger I want to essentially do:
On Insert....
Update MyView
Set Col A = NewCol A Value,
Col B = NewCol B Value,
Col C = NewCol C Value
The NewCol x Value values are the insert values of the record being posted
to the table being inserted.
Interbase has New property. Can anyone provide the syntac to accomplish my
task?
TIA
LarryLarry,
There are two special tables accessible within a trigger,
inserted and deleted. They hold the new rows (for inserts
and updates) and the old rows (for deletes and updates)
of the target table with respect to the statement that fired
the trigger. Note that a trigger fires only once, whether the
triggering statement affects multiple rows or not, and so the
inserted and deleted tables can have more than one row.
It sounds like your triggering statement will be affecting
only one row, but it is still a good idea to consider making
sure of that by checking @.@.rowcount at the very beginning
of the trigger.
Your trigger will probably look something like this:
create trigger... as
if @.@.rowcount <> 1 begin
raiserror (as appropriate)
rollback transaction -- or return, or whatever you need
update MyView set
ColA = i.ColA,
ColB = i.ColB,
. and so on
where MyView.viewKey = i.ColumnIdentifyingViewRowToUpdate
If you want, post CREATE TABLE statement and sample data for an
example and we can try to help more specifically to your case. You
can also find out more about the special tables inserted and deleted
in Books Online.
Steve Kass
Drew University
DelphiGuy wrote:

>I am just getting back to SqlServer and TSQL after a 4 year hiatus.
>I want to write a trigger to update a view with the same record that is
>being inserted into a table. I have a trigger bound to the table to be
>inserted and since it is a simple process, I will probably forgoing using a
>stored proc.
>In my trigger I want to essentially do:
>On Insert....
>Update MyView
>Set Col A = NewCol A Value,
> Col B = NewCol B Value,
> Col C = NewCol C Value
>
>The NewCol x Value values are the insert values of the record being posted
>to the table being inserted.
>Interbase has New property. Can anyone provide the syntac to accomplish my
>task?
>TIA
>Larry
>
>

Insert Trigger

Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
VensiaTry:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia

Insert Trigger

Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia
Try:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) !=
(SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =
inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia

Insert Trigger

Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode = inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode = inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
VensiaTry:
IF EXISTS
(
SELECT
*
FROM
inserted i
WHERE NOT EXISTS
(
SELECT
*
FROM
tblDept d
WHERE
d.DeptCode = i.DeptCode
)
)
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
That said, why don't you just put a FOREIGN KEY constraint on your table
that references the tblDept table, and you won't need a trigger?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Vensia" <vensia2000_nospam@.yahoo.com> wrote in message
news:OCboBxTQHHA.3460@.TK2MSFTNGP03.phx.gbl...
Hello,
I want to write trigger for Insert event, but I don't know how to write the
SQL statement.
Let say I have table called tblSalaryMain and tblSalaryMain has field
DeptCode.
The SQL trigger maybe like this :
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
But I want the above trigger is executed only if there is a value
tblSalaryMain.DeptCode (tblSalaryMain.DeptCode Is Not Null)
How do I write SQL statement to check the null value of
tblSalaryMain.DeptCode ?
The complete code maybe like this :
IF inserted.DeptCode Is Not Null Then
IF (SELECT COUNT(*) FROM inserted) != (SELECT COUNT(*) FROM tblDept, inserted WHERE (tblDept.DeptCode =inserted.DeptCode))
BEGIN
RAISERROR(778625, 16, 1)
ROLLBACK TRANSACTION
END
Please help me change the first line.
Thanks.
Vensia

Insert Trigger

Hi,
I am new to triggers and would like your help
I am trying to write an insert trigger on a table, so that when a record is
inserted in table1 a dummy record is inserted in table2
eg: table "master" has a record with fields "1', "Honda", "1998"
When this record is inserted into the "master" table, I need it to insert
another record in the "userlog" table, with the following fields "1",
"Honda", "datatimestamp". The trigger inserts the record when I use
hardcoded values. However, I do not know how to reference the values that
were inserted into the "master" table and then insert those values into the
"userlog" table.
Please help
Thanks
- RichIn triggers, there are 2 "special" tables in memory, that
exist for use only inside triggerville, called INSERTED or
DELETED. These tables are maintained for you by SQL
Server, and the layout of columns, datatypes matches back
exactly to the "master" table. Refer to these like other
tables (e.g. select * from INSERTED) INSIDE the trigger of
the table being modified...
On an INSERT, the new values inserted are stored in
INSERTED only.
On an UPDATE, the old values are stored in DELETED, and
new values are stored in INSERTED.
On a DELETE, the old values are stored in DELETED only.
Remember that a trigger is executed once per SQL action
against that table, so if your statement inserts 100 rows
to table_X, the INSERT trigger for table_X is fired ONCE,
not 100 times...
Bruce
>--Original Message--
>Hi,
>I am new to triggers and would like your help
>I am trying to write an insert trigger on a table, so
that when a record is
>inserted in table1 a dummy record is inserted in table2
>eg: table "master" has a record with
fields "1', "Honda", "1998"
> When this record is inserted into the "master" table, I
need it to insert
>another record in the "userlog" table, with the following
fields "1",
>"Honda", "datatimestamp". The trigger inserts the record
when I use
>hardcoded values. However, I do not know how to
reference the values that
>were inserted into the "master" table and then insert
those values into the
>"userlog" table.
>Please help
>Thanks
>- Rich
>
>.
>|||Bruce
Thanks you very much. This helped
- Rich
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:097b01c3adfb$5205bc50$a601280a@.phx.gbl...
> In triggers, there are 2 "special" tables in memory, that
> exist for use only inside triggerville, called INSERTED or
> DELETED. These tables are maintained for you by SQL
> Server, and the layout of columns, datatypes matches back
> exactly to the "master" table. Refer to these like other
> tables (e.g. select * from INSERTED) INSIDE the trigger of
> the table being modified...
> On an INSERT, the new values inserted are stored in
> INSERTED only.
> On an UPDATE, the old values are stored in DELETED, and
> new values are stored in INSERTED.
> On a DELETE, the old values are stored in DELETED only.
> Remember that a trigger is executed once per SQL action
> against that table, so if your statement inserts 100 rows
> to table_X, the INSERT trigger for table_X is fired ONCE,
> not 100 times...
> Bruce
> >--Original Message--
> >Hi,
> >I am new to triggers and would like your help
> >
> >I am trying to write an insert trigger on a table, so
> that when a record is
> >inserted in table1 a dummy record is inserted in table2
> >
> >eg: table "master" has a record with
> fields "1', "Honda", "1998"
> > When this record is inserted into the "master" table, I
> need it to insert
> >another record in the "userlog" table, with the following
> fields "1",
> >"Honda", "datatimestamp". The trigger inserts the record
> when I use
> >hardcoded values. However, I do not know how to
> reference the values that
> >were inserted into the "master" table and then insert
> those values into the
> >"userlog" table.
> >Please help
> >Thanks
> >- Rich
> >
> >
> >.
> >sql

insert stored procedure

i want to insert values into a table select one field value from other table
(want to write select statement into insert statement but not knows how to
do it)
how can i implement it
the stored procedure is as given below
ALTER PROCEDURE dbo.usp_Insert
(
@.AccountName [NVARCHAR] (50)
, @.AccountSite [NVARCHAR] (50)
, @.LeadName [NVARCHAR] (50)
)
AS
BEGIN
SELECT Lead_ID into @.Lead_ID from Lead where Leadname=@.Leadname
INSERT INTO [Account]
(
[AccountName]
, [AccountSite]
, [Lead_ID]
)
VALUES
(
@.AccountName
, @.AccountSite
, @.Lead_ID
)
RETURN (@.@.IDENTITY)
END
ALTER PROCEDURE dbo.usp_Insert
(
@.AccountName [NVARCHAR] (50)
, @.AccountSite [NVARCHAR] (50)
, @.LeadName [NVARCHAR] (50)
)
AS
BEGIN
INSERT INTO [Account] ([AccountName], [AccountSite], [Lead_ID])
SELECT @.AccountName, @.AccountSite, b.Lead_ID
FROM [Lead]
WHERE [LeadNane] = @.Leadname
RETURN SCOPE_IDENTITY()
END
Andrew J. Kelly SQL MVP
"news.microsoftnews" <sapk81@.yahoo.com> wrote in message
news:erADAHSrEHA.332@.TK2MSFTNGP14.phx.gbl...
> i want to insert values into a table select one field value from other
table
> (want to write select statement into insert statement but not knows how to
> do it)
> how can i implement it
> the stored procedure is as given below
> ALTER PROCEDURE dbo.usp_Insert
> (
> @.AccountName [NVARCHAR] (50)
> , @.AccountSite [NVARCHAR] (50)
> , @.LeadName [NVARCHAR] (50)
>
> )
>
> AS
> BEGIN
> SELECT Lead_ID into @.Lead_ID from Lead where Leadname=@.Leadname
> INSERT INTO [Account]
> (
> [AccountName]
> , [AccountSite]
> , [Lead_ID]
> )
> VALUES
> (
> @.AccountName
> , @.AccountSite
> , @.Lead_ID
> )
> RETURN (@.@.IDENTITY)
> END
>

INSERT statement; only 1 column in table.. that too identity

need to write an insert statement to a table with only identity column
without using IDENTITY_INSERT option
create table t (id int identity(1,1) primary key)
RakeshRakesh
create table test(id int identity)
insert test default values
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DB21864-6422-485C-8D26-BCC16C261076@.microsoft.com...
> need to write an insert statement to a table with only identity column
> without using IDENTITY_INSERT option
> create table t (id int identity(1,1) primary key)
> Rakesh|||Thanx
"Uri Dimant" wrote:

> Rakesh
> create table test(id int identity)
> insert test default values
>
>
> "Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
> news:8DB21864-6422-485C-8D26-BCC16C261076@.microsoft.com...
>
>|||create table t (id int identity(1,1) primary key)
INSERT INTO t default values
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:8DB21864-6422-485C-8D26-BCC16C261076@.microsoft.com...
> need to write an insert statement to a table with only identity column
> without using IDENTITY_INSERT option
> create table t (id int identity(1,1) primary key)
> Rakesh

Insert statement with todays date in one of the field

How do I write an Insert SQL statement with a default today's date inserted into one of the field?

Help is apreciated.

INSERT INTO [TableName]

(

DateField

)

VALUES

(

getdate()

)

|||

sorry didn't see the word default

you would set a paramter of type date and set the default value to getdate()

@.DateField as DateTime = getdate()

If you pass a paramter it will override this default value

|||

Thank you so much for the immediate response. Actually it was an update not an insert but it is similar. Here's what I've tried.

UpdateCommand="UPDATE [myAlumni] SET [constID] = @.constID, [hideState] = @.hideState, [userName] = @.userName, [lstName] = @.lstName, [mdnName] = @.mdnName, [fstName] = @.fstName, [mdlName] = @.mdlName, [nckName] = @.nckName, [classOf] = @.classOf, [semester] = @.semester, [address] = @.address, [city] = @.city, [state] = @.state, [zip] = @.zip, [country] = @.country, [phone] = @.phone, [address2] = @.address2, [email1] = @.email1, [email2] = @.email2, [email3] = @.email3, [website] = @.website, [mdfyDate] =<%# DateTime.Now%> WHERE [dirID] = @.dirID">

I am using SqlDataSource for this. The error I got from the above statement is:

Exception Details:System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '<'.

So I tried this:

UpdateCommand="UPDATE [myAlumni] SET [constID] = @.constID, [hideState] = @.hideState, [userName] = @.userName, [lstName] = @.lstName, [mdnName] = @.mdnName, [fstName] = @.fstName, [mdlName] = @.mdlName, [nckName] = @.nckName, [classOf] = @.classOf, [semester] = @.semester, [address] = @.address, [city] = @.city, [state] = @.state, [zip] = @.zip, [country] = @.country, [phone] = @.phone, [address2] = @.address2, [email1] = @.email1, [email2] = @.email2, [email3] = @.email3, [website] = @.website, [mdfyDate] =<%# getDate()%> WHERE [dirID] = @.dirID">

And then in the getDate method, I do this:

protected string getDate() {string strDate = Convert.ToString(DateTime.Now);return strDate; }

But I still get the same error.|||You don't need the <%# %> tags. getdate() is a function inside sql, not c#|||Thank you so much! That works great.|||

I am trying to get this to work as well without much success. I want to add today's date in a field called "Created". This field is not used in the form but should add the date created automatically.

InsertCommand="INSERT INTO [Courses] ([Course], [Course_Code], [Curriculum_Area_ID], [Centre_ID], [Course_Level], [Entry_Requirements], [Application_Method], [Structure_and_Content], [Assessment], [Employment], [Additional_Information], [Tutor], [Contact_Number], [E_mail], [Contact], [Keywords], [Mode], [Created]) VALUES (@.Course, @.Course_Code, @.Curriculum_Area_ID, @.Centre_ID, @.Course_Level, @.Entry_Requirements, @.Application_Method, @.Structure_and_Content, @.Assessment, @.Employment, @.Additional_Information, @.Tutor, @.Contact_Number, @.E_mail, @.Contact, @.Keywords, @.Mode, getDate())"

getDate() function in script

protectedstring getDate()

{

string strDate =Convert.ToString(DateTime.Now);return strDate;

}

|||

I am trying to get this to work as well without much success. I want to add today's date in a field called "Created". This field is not used in the form but should add the date created automatically.

InsertCommand="INSERT INTO [Courses] ([Course], [Course_Code], [Curriculum_Area_ID], [Centre_ID], [Course_Level], [Entry_Requirements], [Application_Method], [Structure_and_Content], [Assessment], [Employment], [Additional_Information], [Tutor], [Contact_Number], [E_mail], [Contact], [Keywords], [Mode], [Created]) VALUES (@.Course, @.Course_Code, @.Curriculum_Area_ID, @.Centre_ID, @.Course_Level, @.Entry_Requirements, @.Application_Method, @.Structure_and_Content, @.Assessment, @.Employment, @.Additional_Information, @.Tutor, @.Contact_Number, @.E_mail, @.Contact, @.Keywords, @.Mode, getDate())"

getDate() function in script

protectedstring getDate()

{

string strDate =Convert.ToString(DateTime.Now);return strDate;

}

Undefined function 'getDate' in expression

|||

You need to seperate the getdate function from the string

InsertCommand="INSERT INTO [Courses] ([Course], [Course_Code], [Curriculum_Area_ID], [Centre_ID], [Course_Level], [Entry_Requirements], [Application_Method], [Structure_and_Content], [Assessment], [Employment], [Additional_Information], [Tutor], [Contact_Number], [E_mail], [Contact], [Keywords], [Mode], [Created]) VALUES (@.Course, @.Course_Code, @.Curriculum_Area_ID, @.Centre_ID, @.Course_Level, @.Entry_Requirements, @.Application_Method, @.Structure_and_Content, @.Assessment, @.Employment, @.Additional_Information, @.Tutor, @.Contact_Number, @.E_mail, @.Contact, @.Keywords, @.Mode, " + getdate() + ")"

|||

Sorry for the earlier double post. I needed Date() function as it is an access database.

Insert Statement Help

I need to insert over 600 similar records in a table. The only way I know is to write insert into <table> command 600 times. Is there another way which is easier?
Thanks in advance,
SauravFirst create this interger table

CREATE TABLE Numbers(
Number INT NOT NULL,
CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED (Number)
WITH FILLFACTOR = 100)
INSERT INTO Numbers
SELECT
(a.Number * 256) + b.Number AS Number FROM
(SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number <= 255)a (Number),
(SELECT number
FROM master..spt_values
WHERE type = 'P'
AND number <= 255)b (Number)
GO


now run this

CREATE TABLE test(
name VARCHAR(20),
age INT)

INSERT INTO test VALUES('joy',30)

INSERT INTO test

SELECT name,age FROM test
CROSS JOIN Numbers
WHERE number<601|||Thank you Rudra for the reply. However, I need to insert similar values (actually not the same values). Values in certain columns are same and in others different.

Thanks,
Saurav|||Thank you Rudra for the reply. However, I need to insert similar values (actually not the same values). Values in certain columns are same and in others different.

Thanks,
Saurav

Please give some more info,I mean examples of your table and data.Then it would be easy for us to help you.Please read the sticky at the top most post.|||Where is this data now?|||Where's the data now?|||What is the location and format of the data at present?

(just thought I'd change it up a bit ;) )|||If the data is some sort of file you should create a DTS package and import the data. It would be a lot easier than creating some sort of BULK insert statement|||When nothing has been done with the data,then it should there where it was earlier...so don't worry be happy ;)|||Yes, my fuzzy friend, but we were not able to glimpse the location of the data earlier.

And what you say is not always true, glasshoppa...sometimes doing nothing can cause loss of data, which would mean it is not where it was...and further cause a great deal of debate over whether it ever was.|||Welcome back Paul,I missed you a lot ...;)|||i think the real issue should not be locating this data. Rather it's the logic our friend requires to sort out his difficulty.|||i think the real issue should not be locating this data. Rather it's the logic our friend requires to sort out his difficulty.So the logic is independent of whether the data is handwritten on some forms on his desk, contained in qualitative text in a word document or normalised and typed in an Oracle database?|||i think the real issue should not be locating this data. Rather it's the logic our friend requires to sort out his difficulty.Yes, as Pootie has so well highlighted, perhaps the logic our friend requires to sort out his difficulty is rooted in the location of his data. In fact, one might argue that at least on the surface, the origin of data is one of the cornerstones of database analysis and design.

In fact, I put forth for your consideration the assertation that without knowing the origin of one's data, the manipulation of said data is perhaps nearly impossible.

Or, as Grandma used to say, one cannot hope to successfully build a relational database for the future without knowing intimately the data around which the database is to be built, and this knowledge is largely based upon knowing the past of one's data.

She usually followed up this bit of sage advice with an often lengthy tirade against the use of cursors, and sometimes followed that with a treatise on the evils of using sweet apples in an apple pie...but that's a discussion for a different thread.

Insert row in Query Analyzer

Hi there !

I have a table called customer which got
id = int
name = varchar
address = varchar
email= varchar

can you please write the syntax that insert the below data in the table using query Analyzer

id = 1
name = fadil
Address = London
email = fadil1977@.hotmail.com

Thank you for your time and help.INSERT INTO Customer
VALUES (1, 'fadil', 'London', 'fadil1977@.hotmail.com')

INSERT RECORD with PDF WOED files

Hi guys!

I've made a simple INSERT form write some records in a database...

then I need to associate to every record a PDF FILE or a WORD FILE..

so who (a user) insert a record should upload a file ...

How could associate the record to the file that an user upload?

classical article pubblication problem...do you know some tutorial?

3rdEyed

Hi 3rdEyed,

There are 2 ways that come to my mind in doing this.

1. Add a VarChar field in your table which stores the path and file name of the PDF or DOC file. You can later get this file from the file system and do whatever you like.

2. Use a FileStream to get file in a byte array. You can store the byte array in a binary field in database table.

I will recommend the first way, since it will not give much overhead to database.

|||

Kevin Yu - MSFT:

Hi 3rdEyed,

There are 2 ways that come to my mind in doing this.

1. Add a VarChar field in your table which stores the path and file name of the PDF or DOC file. You can later get this file from the file system and do whatever you like.

2. Use a FileStream to get file in a byte array. You can store the byte array in a binary field in database table.

I will recommend the first way, since it will not give much overhead to database.

Hi thanks for the answer..did you some example SCRIPT or TUTORIAL?

Insert Record Logic Needed

Im trying to write some sql which inserts these rows into another table
called move. Unfortunately i am struggling trying to get the insert statemen
t
not to insert record 5 because the ToURN has been used before in a previous
record(1). Does anyone know how I would write the sql to do this.
RecNO MergeFromURN MergeToURN MergeDateMerged
1 100 200 15/06/1982
2 200 300 15/06/1982
3 300 400 15/06/1982
4 500 600 15/06/1982
5 700 100 15/06/1982
6 100 100 15/06/1982
7 NULL 100 15/06/1982
8 700 0 15/06/1982
So far I have the following sql but need to go that step further to stop
record 5 being inserted because 100 already has been inserted as a from urn
in record 1.
INSERT INTO MOVE (MOVEFROMURN, MOVETOURN, MOVEDATEMERGED)
SELECT MergeFromURN, MergeToURN, MIN(MergeDateMerged)
from myTable
where MergeFromURN is not null and MergeToURN is not null
and MergeFromURN <> 0 and MergeToURN <> 0 and
MergeFromURN <> MergeToURN and
(MergeFromURN not in (select MoveFromURN from Move) and MergeToURN not in
(select MoveToURN from Move))
GROUP BY MergeFromURN, MergeToURN
Order by MergeFromURN
while @.@.ROWCOUNT > 0
begin
update A set MoveToURN = B.MoveToURN
from Move A
inner join Move B on A.MoveToURN=B.MoveFromURN
end
Can anyone help me with this.Are you aware of the fact that at least once several people hav tried to hel
p
you?
Maybe you should read their responses...
http://msdn.microsoft.com/newsgroup...92-6b6386cb16a1
ML

Insert query firing Insert & Update trigger at the same time.

Hello All,
I have a table on which I have created a insert,Update and a Delete trigger. All these triggers write a entry to another audit table with the unique key for each table and the timestamp.

Insert and Update trigger work fine when i have only one of them defined.

However when I have all the 3 triggers in place and when i try to fire a insert query on the statement. It triggers both insert and update trigger at the same time and has the same timestamp in the audit table.

Insert trigger goes as
CREATE TRIGGER InsRecord ON [dbo].[tableA]
AFTER INSERT
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Insert',GetDate() from inserted

Update trigger goes as
CREATE TRIGGER UpdRecord ON [dbo].[tableA]
FOR UPDATE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Update',GetDate() from inserted

Delete Trigger goes as
CREATE TRIGGER delRecord ON [dbo].[tableA]
FOR DELETE
AS
insert Audit(change_id,change_table,change_type,date_chan ge)
select uniqueid, srctable,'Delete',GetDate() from deleted

Note:This tableA has relations with 2 other tables on 1 field each from each table but i don't think it should matter.

Please advise how to prevent it.CREATE TRIGGER alteredRecord ON [dbo].[tableA]
FOR INSERT, UPDATE, DELETE
AS
BEGIN

...declare lngIns & lngDel

SELECT lngIns=count(col1)
from inserted

select lngDel=count(col1)
from deleted

IF lngIns>0 and lngDel=0
...inserted
else if lngIns>0 and lngDel>0
...updated
else if lngIns=0 and lngDel>0
...deleted
end

END

insert query ?

my application will add and delete and update records in db

my problem is when to insert

I have one text box and one dropdownbox one to write the name of db and the dropdownbox to choose the holding server ..

this is the structure of each table >>

servers_tbl : SRV_ID,Server_Name

DB_tbl : DB_ID,DB_Name

srvdb_tbl : DB_ID,SRV_ID(forign keys from the previous tables)

so >>>

I want to add a new db to a server

so I am writing the new db name in the textbox and choose the server from the dropdownbox and press a button to add the db name in the DB_tbl.DB_Name and add the db id in the DB_tbl.DB_ID to the srvdb_tbl.DB_ID and server id in the Servers_tbl.SRV_ID

any one can help me ...

You need a stored procedure along the lines of

CREATE PROCEDURE dbo.AddDbServer ( @.DB_Name VARCHAR(50), @.SRV_ID INT) AS

DECLARE @.DB_ID INT

IF NOT EXISTS(SELECT * FROMDB_tbl WHERE DB_NAME = @.DB_NAME)

BEGIN INSERT INTO DB_tbl (DB_NAME) VALUES (@.DB_Name)

SELECT @.DB_ID = SCOPE_IDENTITY

END

ELSE

SELECT @.DB_ID = SELECT DB_ID FROMDB_tbl WHERE DB_NAME =@.DB_Name

END

INSERT INTOsrvdb_tbl(DB_ID,SRV_ID) VALUES (@.DB_ID , @.SRV_ID)

You will need to test the stored procedure before incorporating it into your program.

Insert Proc With Both Select And Values

I'm trying to write a Stored Proc to Insert records into a table in SQL Server 2000. The fields in the records to be inserted are from another table and from Parameters. I can't seem to figure out the syntax for this.

I created a test in MS Access and it loooks like this:

INSERT INTO PatientTripRegionCountry_Temp ( CountryID, RegionID, Country, PatientTripID )
SELECT Country.CountryID, Country.RegionID, Country.Country, 2 AS PatientTripID
FROM Country

This works great in Access but not in SQL Server. In SQL Server 2 = @.PatientTripID

ANY SUGGESTIONS ON HOW TO HANDLE THIS?Hey, I tested your script. It works for me. Could you specify the error message and under what circumstance you are running this command and fail?|||Are you looking for something more like:CREATE PROCEDURE dbo.s2164
@.piPatientTripID INT
AS

INSERT INTO PatientTripRegionCountry_Temp (
CountryID, RegionID
, Country, PatientTripID)
SELECT Country.CountryID, Country.RegionID
, Country.Country, @.PatientTripID
FROM Country

RETURN-PatP|||This is my Stored Proc. It executes but the field PatientTripID is set to <Null>

CREATE PROCEDURE [dbo].[sp_PatientTripRegionCountryTemp_Insert_ForRegionID ]
@.RegionID int,
@.PatientTripID int,
@.PatientID int
AS
INSERT INTO PatientTripRegionCountry_Temp ( CountryID, Country, RegionID, PatientTripID )
SELECT C.CountryID, C.Country, C.RegionID, @.PatientTripID
FROM Country C
WHERE (RegionID=@.RegionID)
GO

Any Suggestions?|||When you execute it from Query Analyzer, it should show "N row(s) affected" when it executes. Zero would be a bad thing in this case.

-PatP|||?? How are you calling the procedure? Can you give a couple examples?|||Thanks for all your help

Don't ask me why, but I retried the versions shown in #4 above and this time it worked.|||Way more gooder yet even! Glad you are back in business.

-PatP