Get back calculated column from insert

I have the following in my Stored Procedure, where I am just adding a record
that will set the ApplicantID to the largest number + 1 or 1000 if null.
INSERT INTO ftsolutions.dbo.Applicant
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
Select
@.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
How do I find out what the ApplicantID is? This is similar to what an
Identity column would do, but I need to do it manually.
Thanks,
TomYour table should have a natural key defined in addition to the
surrogate ApplicantID. Use the natural key to retrieve the last ID. For
example, if this is for an online application you might make the email
address a key, therefore:
SET @.last_id =
(SELECT applicantid
FROM dbo.Applicant
WHERE email = @.email)
If you don't declare a natural key then you can't guarantee uniqueness
and integrity and therefore you can't reliably retrieve the Applicantid
after insert.
David Portas
SQL Server MVP
--|||Why do you need to "do it manually"? What do you think is going to happen
if two transactions hit this at exactly the same time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OvU13sfFFHA.624@.TK2MSFTNGP15.phx.gbl...
> I have the following in my Stored Procedure, where I am just adding a
record
> that will set the ApplicantID to the largest number + 1 or 1000 if null.
> INSERT INTO ftsolutions.dbo.Applicant
>
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
> Select
> @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
> @.LastName,@.Email,getdate()
> from ftsolutions.dbo.Applicant
> where ClientID = @.ClientID
> How do I find out what the ApplicantID is? This is similar to what an
> Identity column would do, but I need to do it manually.
> Thanks,
> Tom
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ef63V1fFFHA.392@.TK2MSFTNGP09.phx.gbl...
> Why do you need to "do it manually"? What do you think is going to happen
> if two transactions hit this at exactly the same time?
Because there are cases where I will need to add another ApplicantID in the
insert statement.
The create is:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
GO
The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
What would happen here is I could add a record that has no ApplicantID
asssociated with it and the insert would have take the max and add 1 and the
Applicant Unique would be 1. If there is already an ApplicantID out there,
I would have the same ApplicantID with ApplicantUnique =
max(ApplicantUnique)+1. Since the ApplicantID would need to be in this
Insert statement, it can't be an Identity.
Actually, we originally designed this to only allow one ApplicantID per
person without the PositionID, but now we need to allow multiple PositionIDs
per ApplicationID's, so we changed ApplicantID to be a normal bigint.
Tom

>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:OvU13sfFFHA.624@.TK2MSFTNGP15.phx.gbl...
> record
> (ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,
Da
> tePosted)
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108760606.023053.73570@.l41g2000cwc.googlegroups.com...
> Your table should have a natural key defined in addition to the
> surrogate ApplicantID. Use the natural key to retrieve the last ID. For
> example, if this is for an online application you might make the email
> address a key, therefore:
> SET @.last_id =
> (SELECT applicantid
> FROM dbo.Applicant
> WHERE email = @.email)
> If you don't declare a natural key then you can't guarantee uniqueness
> and integrity and therefore you can't reliably retrieve the Applicantid
> after insert.
>
Actually, I do have key.
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
GO
The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
The insert statement that gets the ApplicantID is:
****************************************
************************************
***************
if not exists (Select ApplicantID from ftsolutions.dbo.Applicant where
ClientID = @.ClientID and
LastName = @.LastName and FirstName = @.FirstName and Email = @.Email)
INSERT INTO ftsolutions.dbo.Applicant
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
Select
@.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
****************************************
************************************
**************
If it does exist, then I would use the same ApplicantID and increase the
ApplicantUnique by one
I used to have ApplicantID as an identity (as I mentioned in my other post)
and after the insert I would do a:
Select SCOPE_IDENTITY() as ApplicantID
to pass the ApplicantID back.
I could do the same thing by doing another select (inside a transaction to
make sure no one else increases it before I get it back) and then pass it
back, but I was hoping there was a way to get it from the insert/select
statement.
Thanks,
Tom

> --
> David Portas
> SQL Server MVP
> --
>|||> The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
An artificial key should not be required to make a candidate key. You
haven't even got First Normal Form. Normalize your schema (apparently a
subset of these columns belongs in another table) and then your problem
will be solved.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108769600.895342.127230@.l41g2000cwc.googlegroups.com...
> An artificial key should not be required to make a candidate key. You
> haven't even got First Normal Form. Normalize your schema (apparently a
> subset of these columns belongs in another table) and then your problem
> will be solved.
I assume you mean something like:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ApplicantDetail] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
The problem is that this is just a temporary table which may not go any
farther. It may generate another record in another table which would be the
ClientID, ApplicantID,PositionID and ApplicantUnique.
I'm not sure what you mean by an Artificial key. The key is the Client
Identification, the Applicant Identification, and the Position
Identification. Are you saying I should generate another key?
Tom
> --
> David Portas
> SQL Server MVP
> --
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108769600.895342.127230@.l41g2000cwc.googlegroups.com...
> An artificial key should not be required to make a candidate key. You
> haven't even got First Normal Form. Normalize your schema (apparently a
> subset of these columns belongs in another table) and then your problem
> will be solved.
I assume you mean something like:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ApplicantDetail] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
The problem is that this is just a temporary table which may not go any
farther. It may generate another record in another table which would be the
ClientID, ApplicantID,PositionID and ApplicantUnique.
I'm not sure what you mean by an Artificial key. The key is the Client
Identification, the Applicant Identification, and the Position
Identification. Are you saying I should generate another key?
I have a Client Table that is the ClientID only. I also have a Postions
table which is ClientID and PositionID (could have the same PositionID for
different Clients). The we have the Applicant table, where we have multiple
Positions for each Client and each Applicant could have more than one
instance of the same position (which is why I need the ApplicantUnique).
Tom
> --
> David Portas
> SQL Server MVP
> --
>|||An artificial key is an arbitrary value, such as an incrementing count,
that is not a real attribute of the entity you are modelling. By
definition you are carrying redundant data if you don't have a Natural
key - ie. some subset of the attributes (real attributes) that is
unique.
It's far from clear to me what all these columns are but you seem to
have a high level of non-key dependencies in this table. Are you
familiar with the concept of Normalization? Normalization is a basic
design step in which you eliminate duplicated information by
decomposing data into appropriate tables based on functional
dependencies. If this process is performed correctly then it won't be
possible to end up with a structure such as you have. For example I
would not expect to see data about Applicants (actual names and email
addresses of people) in the same table as data about Applications
(positions applied for and who applied for them). Here's a wild guess,
not perfect, but all the tables have at least one natural key:
CREATE TABLE Applicants (applicant_id INTEGER PRIMARY KEY,
email_address VARCHAR(45) NOT NULL UNIQUE, firstname VARCHAR(30) NOT
NULL, lastname VARCHAR(30) NOT NULL)
CREATE TABLE Clients (client_id INTEGER PRIMARY KEY, client_name
VARCHAR(50) NOT NULL UNIQUE)
CREATE TABLE Positions (client_id INTEGER REFERENCES Clients
(client_id), client_job_code VARCHAR(20) NOT NULL, PRIMARY KEY
(client_id, client_job_code))
CREATE TABLE Applications (applicant_id INTEGER NOT NULL REFERENCES
Applicants (applicant_id), client_id INTEGER NOT NULL, client_job_code
VARCHAR(20) NOT NULL, FOREIGN KEY (client_id, client_job_code)
REFERENCES Positions (client_id, client_job_code), application_date
DATETIME NOT NULL, PRIMARY KEY (applicant_id, client_id,
client_job_code, application_date))
Regarding PositionUnique. I don't see any other information associated
with multiple instances of a Position for an Applicant. If there isn't
any such information then you don't actually need to add a new row. For
completeness I've added Application_Date into the key.

> The problem is that this is just a temporary table which may not go
any
> farther.
Then you surely won't need another artifical key at all. The only use
for the INSERT you originally asked for is if new rows of data are
being inserted. If you need more help then I expect you can get better
advice if you tell us what your actual *objective* is rather than
propose half a solution to a problem you haven't really explained. The
following article describes the best way to post your problem for the
group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108778173.839320.171900@.z14g2000cwz.googlegroups.com...
> An artificial key is an arbitrary value, such as an incrementing count,
> that is not a real attribute of the entity you are modelling. By
> definition you are carrying redundant data if you don't have a Natural
> key - ie. some subset of the attributes (real attributes) that is
> unique.
> It's far from clear to me what all these columns are but you seem to
> have a high level of non-key dependencies in this table. Are you
> familiar with the concept of Normalization? Normalization is a basic
> design step in which you eliminate duplicated information by
> decomposing data into appropriate tables based on functional
> dependencies.
I understand Normalization and do a certain amount of it but not completely
(to the consternation of many).

>If this process is performed correctly then it won't be
> possible to end up with a structure such as you have.
That's true.

>For example I
> would not expect to see data about Applicants (actual names and email
> addresses of people) in the same table as data about Applications
> (positions applied for and who applied for them).
Not true here.
I would agree with you in other cases, however. I might have a persons data
in a separate table than the application table. But in our case, the person
may want to have different basic information for each position. He will
typically gear the information to the position. We need to keep a snapshot
of each application of each person. So in our case, it is just easier to
keep the personal information along with the application data. We do have a
separate table for each position (whose key is the PostitionID).

>Here's a wild guess,
> not perfect, but all the tables have at least one natural key:
> CREATE TABLE Applicants (applicant_id INTEGER PRIMARY KEY,
> email_address VARCHAR(45) NOT NULL UNIQUE, firstname VARCHAR(30) NOT
> NULL, lastname VARCHAR(30) NOT NULL)
> CREATE TABLE Clients (client_id INTEGER PRIMARY KEY, client_name
> VARCHAR(50) NOT NULL UNIQUE)
> CREATE TABLE Positions (client_id INTEGER REFERENCES Clients
> (client_id), client_job_code VARCHAR(20) NOT NULL, PRIMARY KEY
> (client_id, client_job_code))
> CREATE TABLE Applications (applicant_id INTEGER NOT NULL REFERENCES
> Applicants (applicant_id), client_id INTEGER NOT NULL, client_job_code
> VARCHAR(20) NOT NULL, FOREIGN KEY (client_id, client_job_code)
> REFERENCES Positions (client_id, client_job_code), application_date
> DATETIME NOT NULL, PRIMARY KEY (applicant_id, client_id,
> client_job_code, application_date))
Your table is pretty close to mine. If I were to separate my tables the you
have, I would also need to have the ClientID in the Applicants table also -
as we need to keep our applicants for our clients separate from each other.
I did make some changes, after looking at your last post about artificial
keys. I realized that I do have another table where I keep summary data
(JobApplicant) for each application and gave that a specific ID by making it
an identity field. There is one JobApplicant record for each application
filed. So I replace the ApplicantUnique with JobApplicant as that would
uniquify the record. The JobApplicant key "artificial key" would be the
JobID which is an identity field.
Also, a person can apply to multiple positions (so for the same ApplicantID
there could be multiple PositionIDs). Also, a person could apply to the
same position more than once. This was why I had the ApplicantUnique
before. But since there is only one JobApplicant record for each Applicant
and each position applied for - I can replace it with that.
Here is what I came up with and how it is currently on my system (there are
more fields in the tables, but these are the pertinent data).
CREATE TABLE [dbo].[Client] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Division] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE TABLE [dbo].[Position] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PositionID] [int] IDENTITY (1, 1) NOT NULL ,
[JobTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatePosted] [datetime] NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Position] WITH NOCHECK ADD
CONSTRAINT [PK_Position] PRIMARY KEY CLUSTERED
(
[ClientID],
[PositionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
ALTER TABLE [dbo].[Position] ADD
CONSTRAINT [FK_Position_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
)
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Applicant] WITH NOCHECK ADD
CONSTRAINT [DF_Applicant_PositionID] DEFAULT (0) FOR [PositionID],
CONSTRAINT [DF_Applicant_ApplicantUnique] DEFAULT (0) FOR [JobID],
CONSTRAINT [DF_Applicant_ResumeUnique] DEFAULT (0) FOR [ResumeUnique],
CONSTRAINT [PK_Applicant] PRIMARY KEY CLUSTERED
(
[ClientID],
[ApplicantID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[JobApplicant] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Resume] [datetime] NULL ,
[Application] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[JobApplicant] WITH NOCHECK ADD
CONSTRAINT [DF_jobApplicant_Interviews] DEFAULT (0) FOR [Interviews],
CONSTRAINT [PK_jobApplicant] PRIMARY KEY CLUSTERED
(
[ClientID],
[ApplicantID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[ApplicantPosition] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] NOT NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiddleName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateApplied] [datetime] NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ApplicantPosition] WITH NOCHECK ADD
CONSTRAINT [DF_ApplicantPosition_JobID] DEFAULT (0) FOR [JobID],
CONSTRAINT [PK_ApplicantPosition] PRIMARY KEY CLUSTERED
(
[ApplicantID],
[ClientID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Tom

> Regarding PositionUnique. I don't see any other information associated
> with multiple instances of a Position for an Applicant. If there isn't
> any such information then you don't actually need to add a new row. For
> completeness I've added Application_Date into the key.
>
> any
> Then you surely won't need another artifical key at all. The only use
> for the INSERT you originally asked for is if new rows of data are
> being inserted. If you need more help then I expect you can get better
> advice if you tell us what your actual *objective* is rather than
> propose half a solution to a problem you haven't really explained. The
> following article describes the best way to post your problem for the
> group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>sql

Get all table names with a given column name

All, is there a way of getting all table names that contain a column
name?
I'm looking at a DB that has 125+ tables and I'm interested in finding
all table names that contain the column order_date.
How can I do it?
TIA,
SashiSelect table_name from information_Schema.columns where
column_name='column name'

Madhivanan

Sashi wrote:

Quote:

Originally Posted by

All, is there a way of getting all table names that contain a column
name?
I'm looking at a DB that has 125+ tables and I'm interested in finding
all table names that contain the column order_date.
How can I do it?
TIA,
Sashi

|||How can I do it?

One method is to query the INFORMATION_SCHEMA views:

SELECT
c.TABLE_SCHEMA,
c.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c ON
t.TABLE_SCHEMA = c.TABLE_SCHEMA AND
t.TABLE_NAME = c.TABLE_NAME
WHERE
t.TABLE_TYPE = 'BASE TABLE' AND
c.COLUMN_NAME = 'column order_date'
ORDER BY
c.TABLE_SCHEMA,
c.TABLE_NAME

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sashi" <smalladi@.gmail.comwrote in message
news:1152627046.364476.199010@.h48g2000cwc.googlegr oups.com...

Quote:

Originally Posted by

All, is there a way of getting all table names that contain a column
name?
I'm looking at a DB that has 125+ tables and I'm interested in finding
all table names that contain the column order_date.
How can I do it?
TIA,
Sashi
>

Get @@Identity from a remote SQL Server

I am using SQL 2000 server. I have a SP that insert a
record to a remote server table with Identity column as
the ID. After the insert, I try to use @.@.Identity to get
the newly inserted ID back. I got NULL. However, it works
when I run the same SP against a DB on the same server.
How can I get the ID back from a remote server?
Thank you for any inputTom
It works.Thank you a lot.
>--Original Message--
>Try:
>declare @.id int
>exec OtherServer.OtherDB.dbo.sp_executesql
> N'insert MyTable (ColA, ColB) values (@.a, @.b)
> select @.id = @.@.identity'
>, '@.a int, @.b int, @.id int'
>, @.a = 1, @.b = 2, @.id = @.id out
>--
> Tom
>----
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>..
>"omninews" <cxie@.omnichoice.com> wrote in message
news:015801c377f8$140a7410$a001280a@.phx.gbl...
> I am using SQL 2000 server. I have a SP that insert a
>record to a remote server table with Identity column as
>the ID. After the insert, I try to use @.@.Identity to get
>the newly inserted ID back. I got NULL. However, it works
>when I run the same SP against a DB on the same server.
> How can I get the ID back from a remote server?
> Thank you for any input
>|||Tom and others,
It works but sometimes I get dead lock. Because this
exec statement starts a new process on the remote server.
After this I have other update/delete might agaist the
same remote database objects which are within the same
procedure ,therefore the main thread. Looks like the exec
started process does not cleanup itself after it finishes
it's job. Is there a way to kill itself after it is done?
Thank you!
>--Original Message--
>Tom
> It works.Thank you a lot.
>
>>--Original Message--
>>Try:
>>declare @.id int
>>exec OtherServer.OtherDB.dbo.sp_executesql
>> N'insert MyTable (ColA, ColB) values (@.a, @.b)
>> select @.id = @.@.identity'
>>, '@.a int, @.b int, @.id int'
>>, @.a = 1, @.b = 2, @.id = @.id out
>>--
>> Tom
>>----
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>..
>>"omninews" <cxie@.omnichoice.com> wrote in message
>news:015801c377f8$140a7410$a001280a@.phx.gbl...
>> I am using SQL 2000 server. I have a SP that insert a
>>record to a remote server table with Identity column as
>>the ID. After the insert, I try to use @.@.Identity to get
>>the newly inserted ID back. I got NULL. However, it
works
>>when I run the same SP against a DB on the same server.
>> How can I get the ID back from a remote server?
>> Thank you for any input
>.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0090_01C3866C.3C5B1D20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Deadlocks are a bigger issue. You'll have to ensure that you keep your =transactions "narrow", i.e. do only the essential work inside the =transaction. Also, make sure you access the resources in the exact same =order in all transactions. You may want to consider doing the insert =via a stored proc on the remote server, instead of using sp_executesql.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"omninews" <cxie@.omnichoice.com> wrote in message =news:014f01c3842b$d18f3770$7d02280a@.phx.gbl...
Tom and others,
It works but sometimes I get dead lock. Because this exec statement starts a new process on the remote server. After this I have other update/delete might agaist the same remote database objects which are within the same procedure ,therefore the main thread. Looks like the exec started process does not cleanup itself after it finishes it's job. Is there a way to kill itself after it is done?
Thank you!
>--Original Message--
>Tom
> It works.Thank you a lot.
>
>>--Original Message--
>>Try:
>>declare @.id int
>>exec OtherServer.OtherDB.dbo.sp_executesql
>> N'insert MyTable (ColA, ColB) values (@.a, @.b)
>> select @.id =3D @.@.identity'
>>, '@.a int, @.b int, @.id int'
>>, @.a =3D 1, @.b =3D 2, @.id =3D @.id out
>>-- >> Tom
>>----
>>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>>SQL Server MVP
>>Columnist, SQL Server Professional
>>Toronto, ON Canada
>>www.pinnaclepublishing.com/sql
>>..
>>"omninews" <cxie@.omnichoice.com> wrote in message >news:015801c377f8$140a7410$a001280a@.phx.gbl...
>> I am using SQL 2000 server. I have a SP that insert a >>record to a remote server table with Identity column as >>the ID. After the insert, I try to use @.@.Identity to get >>the newly inserted ID back. I got NULL. However, it works >>when I run the same SP against a DB on the same server.
>> How can I get the ID back from a remote server?
>> Thank you for any input
>.
>
--=_NextPart_000_0090_01C3866C.3C5B1D20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Deadlocks are a bigger issue. =You'll have to ensure that you keep your transactions "narrow", i.e. do only the =essential work inside the transaction. Also, make sure you access the resources =in the exact same order in all transactions. You may want to consider =doing the insert via a stored proc on the remote server, instead of using sp_executesql.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"omninews" wrote in =message news:014f01c3842b$d1=8f3770$7d02280a@.phx.gbl...Tom and others, It works but sometimes I get dead lock. =Because this exec statement starts a new process on the remote server. After =this I have other update/delete might agaist the same remote database =objects which are within the same procedure ,therefore the main thread. Looks like =the exec started process does not cleanup itself after it finishes =it's job. Is there a way to kill itself after it is done? =Thank you! >--Original Message-->Tom> It works.Thank you a lot.>>>>--Original Message-->Try:>>declare @.id int>exec OtherServer.OtherDB.dbo.sp_executesql> =N'insert MyTable (ColA, ColB) values (@.a, @.b)> =select @.id =3D @.@.identity'>, '@.a int, @.b int, @.id =int'>, @.a =3D 1, @.b =3D 2, @.id =3D @.id =out>>-- > Tom>>---=-->Thomas A. Moreau, BSc, PhD, MCSE, MCDBA>SQL Server =MVP>Columnist, SQL Server Professional>Toronto, ON Canada>www.pinnaclepublishing.com/sql>..>>=;"omninews" =wrote in message >news:015801c377f8$140a7410$a001280a@.phx.gbl...> &=nbsp; I am using SQL 2000 server. I have a SP that insert a >record =to a remote server table with Identity column as >the ID. After =the insert, I try to use @.@.Identity to get >the newly inserted ID =back. I got NULL. However, it works >when I run the same SP =against a DB on the same server.> How can I get the ID back =from a remote server?>> Thank you for any input>>.>

---

--=_NextPart_000_0090_01C3866C.3C5B1D20--

get % distribution of invalid data?

We've got the requirement where we need to load data (usually dirty of course) from a flat file, and for every column, if the value is invalid, I'm putting a -1 in the field as it flows through the data stream - no problem. (I'm converting all invalids to -1 so they'll go into my eventual int column)

My question though is - at the end of the data flow, is there any smooth way to capture the % invalid values in each column. An example: If my table is as follows...where the # 1 signifies "clean data" and -1 signifies dirty.

col1 col2 col3 col4

1 1 1 1

1 -1 -1 1

-1 1 -1 1

-1 1 -1 1

I would want my % distribution query/ssis result to return:

col1 col2 col3 col4

.5 .25 .75 0

I do not want to do a select/ssis process for each column. Any way to do this all in one shot?

You could write a script component to track the number of good and bad values, and output them to an asynchronous output. Or execute a SQL statement after the data flow to get the statistics.|||

jwelch wrote:

execute a SQL statement after the data flow to get the statistics.

That's what I would try first....just to keep it simpler

Generic Stored procedure

Hi All!

i want to retrieve reults from table "tblCategory" by passing

search string as parameter and column name as parameter as well.

CREATE Procedure uspSearchCategory

(

@.Search nvarchar(255),

@.column varchar(100)

)

AS

SELECT

*

FROM

tblCategory

WHERE

@.column LIKE '%' + @.Search+'%'

This doesn't work as @.column in last line is incorrect. Can anybody tell me how can i achieve that.

If i write

name LIKE '%' + @.Search+'%' or

ID LIKE '%' + @.Search+'%'

it works.But can it works as general for ant column name i pass as @.column.

thanx

You need to turn your query into a string, then run the string using code like this:

exec sp_executesql @.query

where @.query is the nvarchar that contains the SQL to run

generation of sql for an alter column etc

Hi.

I have a database I need to supply something (I'm assuming a t-sql script..
maybe something else is better) to update customer tables with.
The operations include mostly changing varchar lengths, though a couple of
columns were renamed.

I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
to generate the scripts.

I can't just send alter table scripts because I'm involving all sorts of
constraints that have to be disabled/or dropped, the alter made, then have
them enabled/ or re-created.

Basically I'm hoping to get the tools to do the rather large amount of work
for me. I'm targetting sql server 2000.

Can someone make a knowledgeable suggestion?

Regards
Jeff KishJeff Kish wrote:
> Hi.
> I have a database I need to supply something (I'm assuming a t-sql script..
> maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple of
> columns were renamed.

Not a good idea IMHO although you can use sp_rename.

> I'd like to maybe figure out how to get Enterprise Manager or Query Analyzer
> to generate the scripts.
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.

Then generate the SQL for the target state and insert drops yourself.

> Basically I'm hoping to get the tools to do the rather large amount of work
> for me. I'm targetting sql server 2000.
> Can someone make a knowledgeable suggestion?

I don't think you will be able to get this out of EM - at least not
directly. It would basically mean to trace your operations and generate
SQL from that. I don't think EM will do that for such a complex set of
operations. You'll have to do some manual work.

Kind regards

robert|||Jeff Kish (jeff.kish@.mro.com) writes:
> I have a database I need to supply something (I'm assuming a t-sql
> script.. maybe something else is better) to update customer tables with.
> The operations include mostly changing varchar lengths, though a couple
> of columns were renamed.
> I'd like to maybe figure out how to get Enterprise Manager or Query
> Analyzer to generate the scripts.
> I can't just send alter table scripts because I'm involving all sorts of
> constraints that have to be disabled/or dropped, the alter made, then have
> them enabled/ or re-created.
> Basically I'm hoping to get the tools to do the rather large amount of
> work for me. I'm targetting sql server 2000.

Composing change scripts for production environments is not a task to
take lightly. Particularly not if you have to apply them while the system
is operating. (If the system closes for business, you may be able to repair
a disaster by restorin a backup.)

It requires good understanding of what can go wrong, and how to prevent
that. For instance, if you need to drop constraints to alter a column,
you should probably wrap that in a transaction, so you don't end up with
losing the constraint.

At the same time, ALTER operations that require changes to the physical
data pages, can take a huge toll on the transaction log, causing it to
grow rapidly. (Changing varchar lengths should be metadata so that should
be safe.)

You can use Enterprise Manager to have it to generate change scripts.
However, there are several flaw in those scripts, and you need to review
them carefully, and also make several changes to them. For instance, the
transaction scope in those scripts are wacko.

What may prove to be a show-stopper is that EM works with SQL 6.5 as its
target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
update as create new table and copy data over. Which sometimes is the right
thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.

There are other tools on the market. Red Gate's SQL Compare get a lot
of positive acclaim, but I have not used it myself.

One potential problem is that you don't know the name of the constraints,
because they were added without a name, so all there is a system-generated
name. In this case, you need to retrieve the name, and then run DROP
CONSTRAINT dynamically. I would suggest that you restore the constraints
with a given name.

Speaking of constraints, make sure that you re-add them WITH CHECK. The
script from EM uses WITH NOCHEK, which means that they are not checked. This
is a lot faster, but it also means that the optimizer will neglect them,
which can have expensive consequences.

Finally, before you run in production, test on a copy of production!

--
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|||On Fri, 24 Mar 2006 13:45:19 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

>Jeff Kish (jeff.kish@.mro.com) writes:
>> I have a database I need to supply something (I'm assuming a t-sql
<snip>
>Composing change scripts for production environments is not a task to
>take lightly. Particularly not if you have to apply them while the system
>is operating. (If the system closes for business, you may be able to repair
>a disaster by restorin a backup.)
>It requires good understanding of what can go wrong, and how to prevent
>that. For instance, if you need to drop constraints to alter a column,
>you should probably wrap that in a transaction, so you don't end up with
>losing the constraint.
ahh.. I had no idea that transactions could wrap/rollback ddl. I don't think
that is the case in Oracle.
>At the same time, ALTER operations that require changes to the physical
>data pages, can take a huge toll on the transaction log, causing it to
>grow rapidly. (Changing varchar lengths should be metadata so that should
>be safe.)
>You can use Enterprise Manager to have it to generate change scripts.
>However, there are several flaw in those scripts, and you need to review
>them carefully, and also make several changes to them. For instance, the
>transaction scope in those scripts are wacko.
Can you tell me how? I'm having some problem seeing how to get the equivalent
alter table etc scripts out of EM. I looked, honest. I'll even look some more.

>What may prove to be a show-stopper is that EM works with SQL 6.5 as its
>target DB (same in Mgmt Studio in SQL 2005, by the way). 6.5 did not
>have ALTER TABLE ALTER COLUMN, so I would guess that it implements the
>update as create new table and copy data over. Which sometimes is the right
>thing, but not when ALTER TABLE ALTER COLUMN is only a metadata change.
target is only sql server 2000 right now.
>There are other tools on the market. Red Gate's SQL Compare get a lot
>of positive acclaim, but I have not used it myself.
>One potential problem is that you don't know the name of the constraints,
>because they were added without a name, so all there is a system-generated
>name. In this case, you need to retrieve the name, and then run DROP
>CONSTRAINT dynamically. I would suggest that you restore the constraints
>with a given name.
mmm not sure I understand.. they are originally added specifically. can I just
disable them or do I need to drop them?

>Speaking of constraints, make sure that you re-add them WITH CHECK. The
>script from EM uses WITH NOCHEK, which means that they are not checked. This
>is a lot faster, but it also means that the optimizer will neglect them,
>which can have expensive consequences.
>Finally, before you run in production, test on a copy of production!
of course! the scars I have should remind me of that. :> )

thanks so much.
Jeff Kish|||Jeff Kish (jeff.kish@.mro.com) writes:
>>You can use Enterprise Manager to have it to generate change scripts.
>>However, there are several flaw in those scripts, and you need to review
>>them carefully, and also make several changes to them. For instance, the
>>transaction scope in those scripts are wacko.
>>
> Can you tell me how? I'm having some problem seeing how to get the
> equivalent alter table etc scripts out of EM. I looked, honest. I'll
> even look some more.

Right-click table and select Modify Table. As I said, it is not likely
it will generate ALTER TABLE commands, those you will have to write
yourself. But at least you will get some code to recreate constraints.
Just be sure to change WITH NOCHECK to WITH CHECK.

>>One potential problem is that you don't know the name of the constraints,
>>because they were added without a name, so all there is a system-generated
>>name. In this case, you need to retrieve the name, and then run DROP
>>CONSTRAINT dynamically. I would suggest that you restore the constraints
>>with a given name.
>>
> mmm not sure I understand.. they are originally added specifically. can
> I just disable them or do I need to drop them?

The error message from ALTER TABLE makes it clear that you have to
drop the constraint. Keep in mind that a FK column must match the
PK column it refers to, so if you change a PK/FK pair, you need to
drop constraints for both columns before changing.

Yes, it follows that from what I say that you should mainly generate
the script manually. This may seem like a crazy idea, but since it's
so important that you understand what the script does, it can be
dangerous to leave that in the hands of a tool.
--
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

Generating unique nvarchar unique key

Hi,
Would like to know whether it is possible to generate a unique key for a
nvarchar column. This key will need to generate by a T-SQL Stored Proc, the
format as follow:
XX-ZZZZ
XX is month, where I think it should be able to generate using DATEPART(mm,
GETDATE())
- is fixed delimiter
ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
So, everytime we run the Stored Proc to generate this key, the statement
will need to break the ZZZZ and increase by one in order to generate the new
id (concatenate with XX-).
But we run into duplication key issue when this Stored Proc was called by
several concurrent clients (like every few thousand records, duplication of
keys will occur once, but occurance totally random).
Would like to know whether there are any better ways to ensure the key
generated (with above format) will always be unique?
ThanksIf you plan to use this as a primary key, it's a very bad idea. The best
primary key is an auto-incremented integer. MSSQL will manage everything for
you with an identity field. If you absolutely need that field you're talking
about (XX-ZZZZ), then create a second field and populate it with a process
that runs every hour or every few minutes. And most importantly, don't make
it part of the primary key.
What you are suggesting is an invitation for deadlocks, and a whole bunch of
other problems. For instance, are you 150% sure that you won't have more
than 10 000 records per month?
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Thalia Mei" <thaliamei@.gmail.com> wrote in message
news:F165CCBF-5AC9-4BAF-B48F-477728F8A399@.microsoft.com...
> Hi,
> Would like to know whether it is possible to generate a unique key for a
> nvarchar column. This key will need to generate by a T-SQL Stored Proc,
> the format as follow:
> XX-ZZZZ
> XX is month, where I think it should be able to generate using
> DATEPART(mm, GETDATE())
> - is fixed delimiter
> ZZZZ is a running number, which will be reseted to 0000 when it reach 9999
> So, everytime we run the Stored Proc to generate this key, the statement
> will need to break the ZZZZ and increase by one in order to generate the
> new id (concatenate with XX-).
> But we run into duplication key issue when this Stored Proc was called by
> several concurrent clients (like every few thousand records, duplication
> of keys will occur once, but occurance totally random).
> Would like to know whether there are any better ways to ensure the key
> generated (with above format) will always be unique?
> Thanks

Generating SQL Database from Visio

I created a data model in Visio. When I try to generate a database in SQL
from the datamodel, the table and column descriptions do not tranfer.
I am using visio for Enterprise Architects (10.0.2705)Hi docsql,
Thank you for using the MSDN Managed Newsgroups.
This appears to be related to visio. This inquiry would best be posted to
the Microsoft.public.visio. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other customers who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us. Thank you for your
understanding.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Generating SQL Database from Visio

I created a data model in Visio. When I try to generate a database in SQL
from the datamodel, the table and column descriptions do not tranfer.
I am using Visio for Enterprise Architects (10.0.2705)
Hi docsql,
Thank you for using the MSDN Managed Newsgroups.
This appears to be related to visio. This inquiry would best be posted to
the Microsoft.public.visio. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other customers who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us. Thank you for your
understanding.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Generating SQL Database from Visio

I created a data model in Visio. When I try to generate a database in SQL
from the datamodel, the table and column descriptions do not tranfer.
I am using Visio for Enterprise Architects (10.0.2705)Hi docsql,
Thank you for using the MSDN Managed Newsgroups.
This appears to be related to visio. This inquiry would best be posted to
the Microsoft.public.visio. The reason why we recommend posting
appropriately is you will get the most qualified pool of respondents, and
other customers who read the newsgroups regularly can either share their
knowledge or learn from your interaction with us. Thank you for your
understanding.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Generating Sequence Number....

Hi,
I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.

Col1
--
1
1
1
1
2
2
3
3
3
3
3
4
5
6
6
7

And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.......

Col1 Col2
-- --
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1

Thanks In Advance,
Rahul JhaI thought this would be a fun one to try - so here's a very poor attempt using an icky cursor... I don't think I have to explain to a DBA such as yourself why you shouldn't implement this method ;)

DECLARE @.col1 int
DECLARE @.col2 int
DECLARE @.id int
DECLARE @.i int
DECLARE @.x int
DECLARE @.myTable table (
id int identity(1,1)
, col1 int
, col2 int
)

SET NOCOUNT ON

INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(1)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(2)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(3)
INSERT INTO @.myTable(col1) VALUES(4)
INSERT INTO @.myTable(col1) VALUES(5)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(6)
INSERT INTO @.myTable(col1) VALUES(7)

SET @.i = 1
SET @.x = 1

DECLARE theIckyCursor cursor
FOR
SELECT id
, col1
FROM @.myTable
ORDER
BY col1 ASC

OPEN theIckyCursor

FETCH NEXT FROM theIckyCursor INTO @.id, @.col1

WHILE @.@.Fetch_Status <> -1 BEGIN
IF @.x <> @.col1 BEGIN
SET @.x = @.x + 1
SET @.i = 1
END

UPDATE @.myTable
SET col2 = @.i
WHERE id = @.id

SET @.i = @.i + 1
FETCH NEXT FROM theIckyCursor INTO @.id, @.col1
END

CLOSE theIckyCursor
DEALLOCATE theIckyCursor

SELECT col1
, col2
FROM @.myTable

SET NOCOUNT OFF

You know... If you provide us with *real* data, rather than just T1, T2 etc then a better answer could be provided.

(Basically, what you're trying to do sounds very wrong!)|||thnkx georgy. Even I was trying on the same line :-). But what I am looking forward is some process (without cursor) or just the query that can do the needfull. there is a huge data migration to be done. hence suggest me some thing that doesn't eat much of the system resource.|||a table with only one column where there are duplicate values cannot possibly have a primary key, so you cannot expect a good solution

export your data to excel, use a macro to apply the sequence numbers

otherwise, tell us your real situation, not this fantasy of only one column|||Ok, let's go back to the real world scenario.
Can you provide proper column headings etc and describe WHY you want to add a sequence number?
If you explain your justification for this design; perhaps we can suggest a better solution :)
EDIT: Sniped!|||Goergy I won't able able to give you the real world schema as it's highly confidential. Scenario is exactly teh same as i said. the only difference is that there are more columns than I said in both the tables.

Why I need this, is because of i am migrating the data from and de-normalised database to a normalised one.

Thanks,
Rahul Jha|||can you use an ORDER BY to get the rows into the necessary sequence for numbering them?|||select col1,row_number()over(partition by col1 order by col1)col2 into t2 from t1|||row_number?

Can you make it more clear?

Thanks,
Rahul Jha|||row_number() (http://www.databasejournal.com/features/mssql/article.php/3572301)|||pdreyer's solution actually works. I have learned something new today :)|||IVON, can you paste the code for the same. I am not getting the meaning of row_number(). How to use this function.

I'll be obliged if any one let me know on the query that pdreyer has written.

Thanks,
Rahul Jha|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000. And my DB is in SS 2000.

Thanks,
Rahul Jha|||please answer the question in post #7|||This one ought to leave a mark!SELECT 1 AS ick
INTO #junque
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 6
UNION ALL SELECT 7

SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junque

UPDATE #foo
SET bat = (SELECT Count(*)
FROM #foo AS baz
WHERE baz.ick = #foo.ick
AND baz.bar <= #foo.bar)

SELECT ick, bat
FROM #foo

DROP TABLE #foo
DROP TABLE #junque-PatP|||ORDER BY on COL1 can be done......... Let me know if i hv cleared ur doubt or not............|||have you seen pats post on page 1|||This one ought to leave a mark!ouch!

[applies mark remover]|||I googled for ROW_NUMBER() and it seems that this is in SS 2005. This is not the part of SS 2000.
True, true.
And my DB is in SS 2000.
We didn't know that.|||ORDER BY on COL1 can be doneexcellent

here is your solution:

select ( select count(*) + 1
from daTable
where Col1 < T.Col1 ) as rownum
, Col1
from daTable as T
order
by Col1

rownum Col1
1 1
1 1
1 1
1 1
5 2
5 2
7 3
7 3
7 3
7 3
7 3
12 4
13 5
14 6
14 6
16 7
here is pdreyer's ROW_NUMBER() solution for comparison --

select row_number() over
( partition by Col1
order by Col1 ) as rownum
, Col1
from daTable
order
by Col1

rownum Col1
1 1
2 1
3 1
4 1
1 2
2 2
1 3
2 3
3 3
4 3
5 3
1 4
1 5
1 6
2 6
1 7
if you remove the PARTITION BY clause, then the result is

rownum Col1
1 1
2 1
3 1
4 1
5 2
6 2
7 3
8 3
9 3
10 3
11 3
12 4
13 5
14 6
15 6
16 7|||You guys rock............

Thanks for the solution.......... Thanks patty, r937 and pdreyer......... gr8 help that was.

Thanks,
Rahul Jha|||SELECT ick, Identity(INT, 1, 1) AS bar, 0 AS bat
INTO #foo
FROM #junqueI had no idea you could insert identity values that way.|||Thanks patty

*laughs out loud*|||:) :) Don Laugh Georgy............... :) :)

I really learnt few things thru this thread.........

Thanks guys once again...... You all are awesome......... Nothing to fear Rahul till the time you guys are there......... :) :) :)

Thanks,
Rahul Jha|||I had no idea you could insert identity values that way.Identity function - I think it is new to 2005. The devs here love it because they love select into too.|||that's a negative poots. the identity function is in SQL 2k. I have used it before.|||My mistake. I came across it about the time we migrated so probably got my wires crossed there.|||well, be careful for what you wish for

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx|||-- Prepare sample data
CREATE TABLE #Sample
(
Col1 INT,
Col2 INT
)

INSERT #Sample
(
Col1
)
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 6 UNION ALL
SELECT 7

-- Check precondition
SELECT * FROM #Sample

-- Setup staging mechanism
CREATE CLUSTERED INDEX IX_Sample ON #Sample (Col1)

DECLARE @.Index INT,
@.Col INT

SELECT TOP 1 @.Index = 0,
@.Col = Col1
FROM #Sample
ORDER BY Col1

UPDATE #Sample
SET @.Index = Col2 = CASE WHEN Col1 = @.Col THEN @.Index + 1 ELSE 1 END,
@.Col = Col1

DROP INDEX #Sample.IX_Sample

-- Check postcondition
SELECT * FROM #Sample

-- Clean up
DROP TABLE #Sample

generating seq no with in a main column

Hi all,
please see if some one can suggest a better approach for this.
i need to move 5 million rows from one table to another.
LoanID is the only clumn in Source table
Source table structure
=========-->
==============================
loanID
1
1
2
2
2
3
4
=====================================
target or detination table should look like this
=========================================
loanID Sqeuence_number_with_in_LoanID
1 1
1 2
2 1
2 2
2 3
3 1
4 1
=========================================

this is what i have tried so far
1)there are about 4 & 1/2 million out of 5 million which will appear
only once (this is one tine load) in source table.
so if i do this
----
sleect loanID
from SourceTable
group by loanID
having count(*)=1

and then i will hard code Sqeuence_number_with_in_LoanID to 1 .because
i know they will only come once.this is set based operation so this is
fast.

2)now,problem is with remainder of the 1/2 million rows.
i had to use cursor for this which is very-very slow.
other option i was thinking of is to pull it in the front end
(.net)and do it there.i am hoping that will be faster.
Database i am working on is sql server 2000.
if some could suggest me some better approach of handling this
remainder
1/2 milion rows.
thanks in advance
-siddYour target table doesn't look very meaningful as you've described it. If
you're not recording any extra information on each row then what's the point
of populating the table with lots of redundant data? Much more efficient
just to add a count column:

CREATE TABLE TargetTable (loanid INTEGER PRIMARY KEY, loancount INTEGER NOT
NULL CHECK (loancount>0))

INSERT INTO TargetTable (loanid, loancount)
SELECT loanid, COUNT(*)
FROM SourceTable
GROUP BY loanid

--
David Portas
----
Please reply only to the newsgroup
--|||Below is one method.

--create table of sequence numbers
DECLARE @.MaxCount int
SELECT @.MaxCount = MAX(loanCount)
FROM(
SELECT COUNT(*) AS loanCount
FROM SourceTable
GROUP BY loanID
) AS loanCounts
CREATE TABLE #SequenceNumbers
(
Sequence_number_with_in_LoanID int NOT NULL
PRIMARY KEY
)
WHILE @.MaxCount > 0
BEGIN
INSERT INTO #SequenceNumbers VALUES(@.MaxCount)
SET @.MaxCount = @.MaxCount - 1
END

-- load target table
SELECT loanID, Sequence_number_with_in_LoanID
INTO TargetTable
FROM (
SELECT loanID, COUNT(*) AS loanCount
FROM SourceTable
GROUP BY loanID) AS SourceTableCounts
JOIN #SequenceNumbers ON
#SequenceNumbers.Sequence_number_with_in_LoanID <=
SourceTableCounts.loanCount
DROP TABLE #SequenceNumbers
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"sidd" <siddharthkhare@.hotmail.com> wrote in message
news:af96a2d7.0312270944.2dd0ca8f@.posting.google.c om...
> Hi all,
> please see if some one can suggest a better approach for this.
> i need to move 5 million rows from one table to another.
> LoanID is the only clumn in Source table
> Source table structure
> =========-->
> ==============================
> loanID
> 1
> 1
> 2
> 2
> 2
> 3
> 4
> =====================================
> target or detination table should look like this
> =========================================
> loanID Sqeuence_number_with_in_LoanID
> 1 1
> 1 2
> 2 1
> 2 2
> 2 3
> 3 1
> 4 1
> =========================================
> this is what i have tried so far
> 1)there are about 4 & 1/2 million out of 5 million which will appear
> only once (this is one tine load) in source table.
> so if i do this
> ----
> sleect loanID
> from SourceTable
> group by loanID
> having count(*)=1
> and then i will hard code Sqeuence_number_with_in_LoanID to 1 .because
> i know they will only come once.this is set based operation so this is
> fast.
> 2)now,problem is with remainder of the 1/2 million rows.
> i had to use cursor for this which is very-very slow.
> other option i was thinking of is to pull it in the front end
> (.net)and do it there.i am hoping that will be faster.
> Database i am working on is sql server 2000.
> if some could suggest me some better approach of handling this
> remainder
> 1/2 milion rows.
> thanks in advance
> -sidd|||>> LoanID is the only column in Source table <<

Then this is not a table by definition. Please post DDL in the
future, so we do not have to make guesses. Build a table of
sequential numbers; this is a standard SQL programming trick.

CREATE TABLE Foobar (loan_id, seq_nbr)
SELECT DISTINCT S1.loan_id, seq
FROM Source S1, Sequence
WHERE seq <= (SELECT COUNT(*)
FROM SOurce AS S2
WHERE S1.loan_id = S2.loan_id);

But this is not a good design.|||Dan,
it works great!!
thanks
siddsql

generating auto sequential numbers

I am using sybase aSE12.5. I have a table with only one column with
datatype char(2). when i query this table to select all the records, i
should get these records in the ascending order and they should be numbered
, i.e, the o/p should look something like this
column_name
-- ---
1 AB
2 AC
3 AD
and so on.

I cannot add an extra column and i need this to be done in a single query.

--
Message posted via http://www.sqlmonster.comPraveen D'Souza via SQLMonster.com (forum@.nospam.SQLMonster.com) writes:
> I am using sybase aSE12.5. I have a table with only one column with
> datatype char(2). when i query this table to select all the records, i
> should get these records in the ascending order and they should be
> numbered , i.e, the o/p should look something like this
> column_name
> -- ---
> 1 AB
> 2 AC
> 3 AD
> and so on.
> I cannot add an extra column and i need this to be done in a single query.

If you are using Sybase, you should be posting to
comp.databases.sybase, or whatever that groups goes by at SQLMonster.

However, this solution should work on Sybase as well:

SELECT col, (SELECT COUNT(*) FROM tbl b WHERE b.col >= a.col)
FROM tbl a
ORDER BY col

For large amount of data this may not be very performant, though.
SQL 2005, currently in beta, offers a ROW_NUMBER() function to
handle this a little more effeciently. I don't know if Sybase offers
something similar.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In a tiered architecture display is done in the front end and not the
database.

Generated sprocs of VS2005

Can someone explain the generated sprocs of VS2005 if one column can be nullable

DependentOfSeqID = @.Original_DependentOfSeqID OR ((@.IsNull_DependentOfSeqID = 1) AND (DependentOfSeqID IS NULL))

In VS2003 the generated sprocs would be

DependentOfSeqID = @.Original_DependentOfSeqID OR ((@.Original_DependentOfSeqID IS NULL) AND (DependentOfSeqID IS NULL))

Which is the best?

I guess the 2 statements are the same: the first 1 uses a variable @.IsNull_DependentOfSeqID to determine whther the @.Original_DependentOfSeqID is null; while the 2nd directly compare it to null.

Generate Multiple Tables from Column data

I have a large table with 30000+ rows. Most of the rows can be group by Column 1.

Say you had 10000 people and numbers 1 through 3. Every person would be linked to 1, 2 & 3. hence 30000 rows.

What i need to do is create a table for each number. i.e. Table 1 where column 1 is 1 (then it lists 10000 rows)

Table 2 where column 1 is 2 (another 10000 rows) and so on.

I need to create just under 300 tables. Is there a quicker way of doing that than this 300 times?

I'll try and put it into an example of what i'm looking for

Original table

Column 1 Column 2 Column 3 etc...

-

1 a b .....

2 a b .....

1 c d .....

2 c d .....

AND THEN TURN IT INTO

Table name '1'

Column 1 Column 2 Column 3 etc...

-

1 a b .....

1 c d .....

Table name '2'

Column 1 Column 2 Column 3 etc...

-

2 a b .....

2 c d .....

Using one piece of code and the data in Column 1 as the table names?

Thanks

it worked like this

Select *into 1 from Original Table where column1 = 1

Select *into 2 from Original Table where column1 = 2

All i did was copy the column names into excell and duplicate the rest and run seperate select queries.

Generate Column List

Is it possible to generate a list of all column names in a table seperated
by a comma?
I want to do this because I am creating some triggers on a large number of
tables, with a large number of fields in each, and rather than type out my
list of fields I wanted to generate a list so I could just copy/past into my
triggers.
Thanks
Hi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.

Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>
|||Or a SELECT TOP 0 * would do ;-)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ugGZPWQWEHA.3988@.tk2msftngp13.phx.gbl...
Hi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.

Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>
|||Using the object browser window in SQL Query Analyzer may help you. Try it:
- Start Query Analyzer, connect to your database server.
- Click from the Menus: Tools | Object Browser | Show/Hide (or just hit F8).
This brings up the Object Browser pane.
- Navigate database object tree in Object Browser pane to table of interest.
- Right-click on that table and pick Script Object to Clipboard as Select |
Insert | Update | Delete as appropriate.
- Click in your query window and hit Control/V to paste the SQL statement.
You get a complete SQL statement with placeholders for things you will need
to supply.
A friend pointed this out to me a while back. Looks like there may be other
tricks available via the templates tab but I've not explored the
possibilities.
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>

Generate Column List

Is it possible to generate a list of all column names in a table seperated
by a comma?
I want to do this because I am creating some triggers on a large number of
tables, with a large number of fields in each, and rather than type out my
list of fields I wanted to generate a list so I could just copy/past into my
triggers.
ThanksHi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.

Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Or a SELECT TOP 0 * would do ;-)
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ugGZPWQWEHA.3988@.tk2msftngp13.phx.gbl...
Hi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.

Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Using the object browser window in SQL Query Analyzer may help you. Try it:
- Start Query Analyzer, connect to your database server.
- Click from the Menus: Tools | Object Browser | Show/Hide (or just hit F8).
This brings up the Object Browser pane.
- Navigate database object tree in Object Browser pane to table of interest.
- Right-click on that table and pick Script Object to Clipboard as Select |
Insert | Update | Delete as appropriate.
- Click in your query window and hit Control/V to paste the SQL statement.
You get a complete SQL statement with placeholders for things you will need
to supply.
A friend pointed this out to me a while back. Looks like there may be other
tricks available via the templates tab but I've not explored the
possibilities.
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>

Generate Column List

Is it possible to generate a list of all column names in a table seperated
by a comma?
I want to do this because I am creating some triggers on a large number of
tables, with a large number of fields in each, and rather than type out my
list of fields I wanted to generate a list so I could just copy/past into my
triggers.
ThanksHi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.
:)
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Or a SELECT TOP 0 * would do ;-)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:ugGZPWQWEHA.3988@.tk2msftngp13.phx.gbl...
Hi,
Simple way is:
In query Analyzer ,
Go to Tools menu -- Options -- Result Tab -- Results Output format --
Select " Comma Delimited (CSV)".
Now excute the below command in text output mode (Query menu -- click --
Results in Text):-
set rowcount 1
select * from table_name
This will give the output in comma delimited. The first row is column names.
:)
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>|||Using the object browser window in SQL Query Analyzer may help you. Try it:
- Start Query Analyzer, connect to your database server.
- Click from the Menus: Tools | Object Browser | Show/Hide (or just hit F8).
This brings up the Object Browser pane.
- Navigate database object tree in Object Browser pane to table of interest.
- Right-click on that table and pick Script Object to Clipboard as Select |
Insert | Update | Delete as appropriate.
- Click in your query window and hit Control/V to paste the SQL statement.
You get a complete SQL statement with placeholders for things you will need
to supply.
A friend pointed this out to me a while back. Looks like there may be other
tricks available via the templates tab but I've not explored the
possibilities.
"Keith" <@..> wrote in message news:uuNjPFQWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Is it possible to generate a list of all column names in a table seperated
> by a comma?
> I want to do this because I am creating some triggers on a large number of
> tables, with a large number of fields in each, and rather than type out my
> list of fields I wanted to generate a list so I could just copy/past into
my
> triggers.
> Thanks
>