Inserting values

Hello,
I have an table with fromdate and todate columns in experince table.
fromdate todate
01/10/2000 01/10/2001
10/10/2001 11/11/2003
12/11/2003 09/12/2005
i want to insert values
fromdate todate
10/10/2005 3/10/2006 as fromdate todate experinece dates.
i am using sql server as backend and asp as frontend.
before inserting i want is any experience available with this values
in the above three rows.
Please help me in this scenario
Regards,
Rama Kishore,
ramakishoreiic@.gmail.com
Hi
I'm confused
Can you explain more what are you trying to achivie?
IF NOT EXISTS ( SELECT * FROM Table WHERE ...)
--Inserting here
ELSE
something else
<ramakishoreiic@.gmail.com> wrote in message
news:1143029185.205342.166360@.i40g2000cwc.googlegr oups.com...
> Hello,
> I have an table with fromdate and todate columns in experince table.
> fromdate todate
> 01/10/2000 01/10/2001
> 10/10/2001 11/11/2003
> 12/11/2003 09/12/2005
>
> i want to insert values
> fromdate todate
> 10/10/2005 3/10/2006 as fromdate todate experinece dates.
> i am using sql server as backend and asp as frontend.
> before inserting i want is any experience available with this values
> in the above three rows.
> Please help me in this scenario
> Regards,
> Rama Kishore,
> ramakishoreiic@.gmail.com
>

Inserting values

Hello,
I have an table with fromdate and todate columns in experince table.
fromdate todate
01/10/2000 01/10/2001
10/10/2001 11/11/2003
12/11/2003 09/12/2005
i want to insert values
fromdate todate
10/10/2005 3/10/2006 as fromdate todate experinece dates.
i am using sql server as backend and asp as frontend.
before inserting i want is any experience available with this values
in the above three rows.
Please help me in this scenario
Regards,
Rama Kishore,
ramakishoreiic@.gmail.comHi
I'm confused
Can you explain more what are you trying to achivie?
IF NOT EXISTS ( SELECT * FROM Table WHERE ...)
--Inserting here
ELSE
something else
<ramakishoreiic@.gmail.com> wrote in message
news:1143029185.205342.166360@.i40g2000cwc.googlegroups.com...
> Hello,
> I have an table with fromdate and todate columns in experince table.
> fromdate todate
> 01/10/2000 01/10/2001
> 10/10/2001 11/11/2003
> 12/11/2003 09/12/2005
>
> i want to insert values
> fromdate todate
> 10/10/2005 3/10/2006 as fromdate todate experinece dates.
> i am using sql server as backend and asp as frontend.
> before inserting i want is any experience available with this values
> in the above three rows.
> Please help me in this scenario
> Regards,
> Rama Kishore,
> ramakishoreiic@.gmail.com
>

Inserting values

Hello,
I have an table with fromdate and todate columns in experince table.
fromdate todate
01/10/2000 01/10/2001
10/10/2001 11/11/2003
12/11/2003 09/12/2005
i want to insert values
fromdate todate
10/10/2005 3/10/2006 as fromdate todate experinece dates.
i am using sql server as backend and asp as frontend.
before inserting i want is any experience available with this values
in the above three rows.
Please help me in this scenario
Regards,
Rama Kishore,
ramakishoreiic@.gmail.comHi
I'm confused
Can you explain more what are you trying to achivie?
IF NOT EXISTS ( SELECT * FROM Table WHERE ...)
--Inserting here
ELSE
something else
<ramakishoreiic@.gmail.com> wrote in message
news:1143029185.205342.166360@.i40g2000cwc.googlegroups.com...
> Hello,
> I have an table with fromdate and todate columns in experince table.
> fromdate todate
> 01/10/2000 01/10/2001
> 10/10/2001 11/11/2003
> 12/11/2003 09/12/2005
>
> i want to insert values
> fromdate todate
> 10/10/2005 3/10/2006 as fromdate todate experinece dates.
> i am using sql server as backend and asp as frontend.
> before inserting i want is any experience available with this values
> in the above three rows.
> Please help me in this scenario
> Regards,
> Rama Kishore,
> ramakishoreiic@.gmail.com
>

Inserting Unique Records

Hello,

I have a table with sixty columns in it, five of which define uniqueness for the records. Currently there are 190,775 records in the table. One of the records is a duplicate. I need to insert only the unique records from this table (all columns) into another table. I cannot use a unique nonclistered index with IGNORE_DUP_KEY in the destination table because of a problem I am having with the 'duplicate key was ignored' message. The destination table has a primary key with a clustered index on the same five columns.

How can I put together a SELECT statement that will give me all of the columns in the source table based on uniqueness of the five key columns?

Does my request make sense? Please let me know if you have questions.

Thank you for your help!

CSDunnThe one way that I know to do this would be to use MAX on all but the key fields in the Select statement of the source table, and group by the key fields.

Is there another way?|||Are you simply trying to ID (in order to eliminate) the one record that is a duplicate?

You might try:

SELECT
col1
, col2
, col3
, col4
, col5
FROM
dbo.MyTable
GROUP BY
col1
, col2
, col3
, col4
, col5
HAVING COUNT(*) > 1

Then copy 1 row with the duplicate data to another table (identically defined with no primary key). Delete the duplicate records from the source table and then re-import the one record from the export table.

Otherwise, yes, you could use MAX for all but the five primary key columns to insert into your other table. Just be sure that it's MAX that you want and not MIN (or some other function).

Regards,

hmscott|||Thanks for your help!

cdun2

Inserting records and running a stored procedure at the same time?

Hi there..
The target table got these two 'must be set columns', one called DATASET, an
d
one called LXBENUMMER.
Now, when I want to insert data into this target table I need to fill those
two
columns with the new records I'm inserting. DATASET is easy as it'll always
be a
fixed value, however, LXBENUMMER must be set with a number that comes from a
stored procedure.
If I do an exec sp_xal_seqno 1, 'DAT' I get a number back, which must be put
in
LXBENUMMER with the new record.
An example-
insert tbl1
(dataset,lxbenummer,col1,col2,col3,etc)
select 'DAT',**value from stored procedure**, col1, col2, col3, etc
from tbl2
How do I go about this?
I doubt, therefore I might be.Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Also, you do not know that a column is not anything like a field and
that a row is not a record. After 20+ years of doing SQL and charging
a lot of money for consulting work in correctly schemas, this is a
signal that you are really screwed up.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129947794.955269.92220@.g43g2000cwa.googlegroups.com

> Also, you do not know that a column is not anything like a field and
> that a row is not a record. After 20+ years of doing SQL and charging
Well everywhere I look, row and record is the same and is used interchangeab
le.
Why should I think different? I don't see where I wrote field and compared i
t
with a column; but well you do know better.

> a lot of money for consulting work in correctly schemas, this is a
> signal that you are really screwed up.
Dude, you need to take a break from time to time, instead of throwing dirt a
t
those to aren't "worthy" compared to you. I'd see your point better if you h
ad
kept it nicer.
I'm screwed up if I write nutty SQL? Man, if you think that, you /seriously/
need to take a break from anything relating to computers.
Anyway, I shall post again when I have a DDL ready.
--
I doubt, therefore I might be.|||Kim Noer (kn@.nospam.dk) writes:
> The target table got these two 'must be set columns', one called
> DATASET, and one called LXBENUMMER.
> Now, when I want to insert data into this target table I need to fill
> those two columns with the new records I'm inserting. DATASET is easy as
> it'll always be a fixed value, however, LXBENUMMER must be set with a
> number that comes from a stored procedure.
> If I do an exec sp_xal_seqno 1, 'DAT' I get a number back, which must be
> put in LXBENUMMER with the new record.
General comment: don't use sp_ as the leading charcters in the name of
your objects. This prefix is reserved for SQL Server, and SQL Server will
first look in master for these objects.

> An example-
> insert tbl1
> (dataset,lxbenummer,col1,col2,col3,etc)
> select 'DAT',**value from stored procedure**, col1, col2, col3, etc
> from tbl2
> How do I go about this?
There are number of options, of which some requires you to change
the procedure. I happen to have an article about this on my web site:
http://www.sommarskog.se/share_data.html.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||From your attitude i'd be suprised if anybody outside of education has hired
you in the past couple of years!
You need to bring your skills up-to-date with what business wants now, not
15 years ago.
Times have significantly changed and you seem to have been left behind.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129947794.955269.92220@.g43g2000cwa.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> Also, you do not know that a column is not anything like a field and
> that a row is not a record. After 20+ years of doing SQL and charging
> a lot of money for consulting work in correctly schemas, this is a
> signal that you are really screwed up.
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129947794.955269.92220@.g43g2000cwa.googlegroups.com
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
CREATE TABLE [tbl1] (
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[LXBENUMMER] [int] NOT NULL ,
[col1] [varchar] (30) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [XALSEQ] (
[SEQID] [int] NOT NULL ,
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[SEQNO] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO XALSEQ
VALUES (0,'DAT',1000)
GO
INSERT INTO tbl1
VALUES ('DAT',1000,'somerandomtext')
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_xal_seqno @.increment INT, @.dataset CHAR(3) AS
BEGIN TRAN
UPDATE XALSEQ SET SEQNO = SEQNO + @.increment
WHERE DATASET = @.dataset AND SEQID = 0
SELECT SEQNO - @.increment FROM XALSEQ
WHERE DATASET = @.dataset AND SEQID = 0
COMMIT TRAN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I doubt, therefore I might be.|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96F7D40BB5DE7Yazorman@.127.0.0.1

> General comment: don't use sp_ as the leading charcters in the name of
> your objects. This prefix is reserved for SQL Server, and SQL Server
> will first look in master for these objects.
Aye, I name all those I create differently, but alas, I did not get to chose
the
name of the stored procedure, this one comes from MBS themself.

> There are number of options, of which some requires you to change
> the procedure. I happen to have an article about this on my web site:
> http://www.sommarskog.se/share_data.html.
In my second post to Celko, I've included the structure of the tables, the S
P
and tiny amount of sample data, if that helps point me in the correct direct
ion
(I'm going to read up on your article anyway though).
Basically, the whole purpose is to make sure that every single row (or is it
record?) get their very own unique number. It would be exceedingly lovely if
any
solution to this particular problem could be made generic if possible.
Thanks in advance.
--
I doubt, therefore I might be.|||Kim Noer (kn@.nospam.dk) writes:
> Aye, I name all those I create differently, but alas, I did not get to
> chose the name of the stored procedure, this one comes from MBS
> themself.
MBS? That's some third-party software?

> Basically, the whole purpose is to make sure that every single row (or
> is it record?) get their very own unique number. It would be exceedingly
> lovely if any solution to this particular problem could be made generic
> if possible.
It would certainly be more convenient if it was an OUTPUT parameter
rather than a result ser. If you can't change the procedure, you will
have to use INSERT EXEC.
Besides this looks funny:
CREATE TABLE [XALSEQ] (
[SEQID] [int] NOT NULL ,
[DATASET] [varchar] (3) COLLATE SQL_Danish_Pref_CP1_CI_AS NOT NULL ,
[SEQNO] [int] NOT NULL
) ON [PRIMARY]
GO
It doesn't have a primary key?
If there are no indexes on the table, this means that SQL Server will
have to take a out a table lock to give you a sequence number, and thus
no other will be able to get a sequence number simultaneously.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:

> MBS? That's some third-party software?
Microsoft Business Solution, they bought a company named Navision, which
makes applications that primarily talks flat fileish. In my case the
application uses cursors, a /lot/ of cursors. They do plan to change this,
but that's 2+ years in the future atleast.
I'm trying to insert data from the "outside" of this application, which
means that I can use all kinds of RDBMS tricks.

> It would certainly be more convenient if it was an OUTPUT parameter
> rather than a result ser. If you can't change the procedure, you will
> have to use INSERT EXEC.
I'm sure can write a new, as long as I retain the functionality. Which one
of your methods would you use then?

> It doesn't have a primary key?
> If there are no indexes on the table, this means that SQL Server will
> have to take a out a table lock to give you a sequence number, and
> thus no other will be able to get a sequence number simultaneously.
Maybe they forgot, or the application might not be able to handle such a
situation.
Necessity is the plea for every infringement of human freedom. It is
the argument of tyrants; it is the creed of slaves. -- William Pitt,
1783|||Kim Noer (kn@.nospam.dk) writes:
> I'm sure can write a new, as long as I retain the functionality. Which one
> of your methods would you use then?
OUTPUT parameter.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Inserting NULL when foreign key gets deleted.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now
what I need to do is that when I delete a row in the "Department" table, the
n
the rows in "Student" table where the to be deleted "Department ID" occurs
must *not* get deleted. Instead NULL should be inserted there.
Can this be achieved through database schema? Or is it something that needs
to be done through triggers?
A.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriIn 2005, you can define SET DEFAULT and SET NULL for a foreign key reference
(and of course CASCADE
and restrict). In 200, you need to write code for this(trigger, for example)
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> what I need to do is that when I delete a row in the "Department" table, t
hen
> the rows in "Student" table where the to be deleted "Department ID" occurs
> must *not* get deleted. Instead NULL should be inserted there.
> Can this be achieved through database schema? Or is it something that need
s
> to be done through triggers?
> A.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Thanks a lot Tibor for the quick reply. In the meanwhile I also searched a
bit more for the solution to my problem. I got to this article on MSDN -
http://msdn.microsoft.com/library/d...fintegrity.asp.
It explains as to how to implement the "ON DELETE INSERT NULL" functionality
as TRIGGER in SQL server 2000.
A.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
"Tibor Karaszi" wrote:

> In 2005, you can define SET DEFAULT and SET NULL for a foreign key referen
ce (and of course CASCADE
> and restrict). In 200, you need to write code for this(trigger, for exampl
e).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
> news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
>
>

Inserting NULL when foreign key gets deleted.

I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now
what I need to do is that when I delete a row in the "Department" table, then
the rows in "Student" table where the to be deleted "Department ID" occurs
must *not* get deleted. Instead NULL should be inserted there.
Can this be achieved through database schema? Or is it something that needs
to be done through triggers?
A.
--
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriIn 2005, you can define SET DEFAULT and SET NULL for a foreign key reference (and of course CASCADE
and restrict). In 200, you need to write code for this(trigger, for example).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> what I need to do is that when I delete a row in the "Department" table, then
> the rows in "Student" table where the to be deleted "Department ID" occurs
> must *not* get deleted. Instead NULL should be inserted there.
> Can this be achieved through database schema? Or is it something that needs
> to be done through triggers?
> A.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Thanks a lot Tibor for the quick reply. In the meanwhile I also searched a
bit more for the solution to my problem. I got to this article on MSDN -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_refintegrity.asp.
It explains as to how to implement the "ON DELETE INSERT NULL" functionality
as TRIGGER in SQL server 2000.
A.
--
Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
"Tibor Karaszi" wrote:
> In 2005, you can define SET DEFAULT and SET NULL for a foreign key reference (and of course CASCADE
> and restrict). In 200, you need to write code for this(trigger, for example).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
> news:0F995DF3-EFED-475B-9F22-E96302A58F0B@.microsoft.com...
> >I have a couple of tables in my database (SQL server 2000)
> > 1. Student -- Columns are as
> > "StudentID" -- INT
> > "StudentName" -- NVARCHAR
> > "DepartmentID" -- INT
> > 2. Department -- Columns are as
> > "DepartmentID" -- INT
> > "DepartmentName" -- NVARCHAR
> >
> > So in the table "Student", the field "DepartmentID" is a foreign key. Now
> > what I need to do is that when I delete a row in the "Department" table, then
> > the rows in "Student" table where the to be deleted "Department ID" occurs
> > must *not* get deleted. Instead NULL should be inserted there.
> > Can this be achieved through database schema? Or is it something that needs
> > to be done through triggers?
> >
> > A.
> >
> > --
> > Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
>
>

Inserting NULL values on Date Fields trhough DAL

I am using a DAL and i want to insert a new row where one of the columns is DATE and itcanbe 'NULL'.

I am assigning SqlTypes.SqlDateTime.Null.

But when the date is saved in the database, i get the minvalue (1/01/1900) . Is there a way to put the NULL value in the database using DAL??

how can i put an empty date in the database?

THANK YOU!!!

check ifthishelps.|||

That does not work. Besides, in your example you are NOT using DAL. You are inserting directly to the DB, through a SQL statement.

Thannks,

JeffKish

|||

The code in the link below uses ADO.NET parameters. Hope this helps.

http://www.c-sharpcorner.com/Code/2003/Sept/EnterNullValuesForDateTime.asp