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 a return value from an insert without using a stored proc.

hi all,

lets say i have this insert command being executed from C# to a SQL Db.

//store transaction logSqlCommand cmdStoreTrans =new SqlCommand("INSERT into Transactions(ImportID,ProfileID,RowID) values (@.ImportID,@.ProfileID,@.RowID);",conn);cmdStoreTrans.Parameters.Add("@.ImportID",importId);cmdStoreTrans.Parameters.Add("@.ProfileID",profileId);cmdStoreTrans.Parameters.Add("@.RowID",i);try{conn.Open();cmdStoreTrans.ExecuteNonQuery();conn.Close();}catch(SqlException ex){throw(ex);}

I need the new Identity number of that record added. how can i get that within THIS Sqlcommand. Currently im closing the connection, creating a new command with 'SELECT MAX(id) from transactions" and getting the value that way, im sure there is a easier way keeping it all within one command object? someone mentioned using something liek @.@.Identity

any help appreciated

TIA, mcm

Try doing a SELECT SCOPE_IDENTITY() at the end of your INSERT statement. and use ExecuteScalar instead of ExecuteNonQuery.|||

thanks,

will do.

mcm

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--

generating insert scripts

Hi,

I have a table with 25 columns and 100 rows of data. I want to generate Insert scripts with data. Sql 2005 does not provide any option out of the box to do so.

Has anyone tried this/ used any other tool for the same.

Please share the solution.

Thanks.

hi,
you can use a third party tool.. there's a lot of them.. or you just write a query something like

select 'insert into tblTable (col1, col2, col3.......)'
select 'select '''+ col1 + ''',''' + col2 + ''''.......... + ' union all' from tblTable

- clintz|||

you can use this script:

declare @.Columns varchar(5000),@.TableName varchar(50),@.Condition varchar(500), @.ColumnVal varchar(8000)
set @.TableName='table1'
set @.Condition='1=1'
set @.Columns=''
set @.ColumnVal=''
select @.Columns =@.Columns + c.Column_name + ',' ,@.ColumnVal=@.ColumnVal +
case c.data_type
when 'varchar' then '''''''+'
when 'datetime' then '''''''+ cast('
else '+ cast(' end +
c.Column_name +
case c.data_type
when 'varchar' then '+ '''''
when 'datetime' then ' as varchar(50)) + '''''
else ' as varchar(50)) +' end + ''','
from INFORMATION_SCHEMA.columns c where c.table_name=@.TableName
set @.Columns= substring(@.Columns,1,len(@.Columns)-1)
set @.ColumnVal= substring(@.ColumnVal,1,len(@.ColumnVal)-1)

exec( 'select ''insert into ' + @.TableName + '('+ @.Columns + ') values (''' + @.ColumnVal + ')'' from ' + @.TableName)

please note that all datatypes are not handled . you will have to modify the case part for other datatypes

|||

You can use one of these excellent options:

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

DDL –Script Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en

|||Thanks Arnie, clintz, mobin

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong'
--
Ranbdy KrofickI don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:

>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter
"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong'

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong'
--
Ranbdy KrofickI don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:
>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong'

generated insert statement

using ms sql 2005 express,
created a simple two field table
tried to configure the data adapter in vb 2003 and the wizard returns
"Generated INSERT statement. There were errors configuring the data adapter"
No description of the error. The insert, update and delete procs are not
generated.
What am I doing wrong?
Ranbdy Krofick
I don't think that wizard works with 2005 - it reads the
meta data tables which are different in SQL Server 2005 than
they were in previous versions of SQL Server. You'll need to
write your own code - and you'll be better off anyway.
-Sue
On Mon, 5 Mar 2007 15:33:03 -0800, Randy Krofick
<randyk@.nwinds.com> wrote:

>using ms sql 2005 express,
>created a simple two field table
>tried to configure the data adapter in vb 2003 and the wizard returns
>"Generated INSERT statement. There were errors configuring the data adapter"
>No description of the error. The insert, update and delete procs are not
>generated.
>What am I doing wrong?

generate update statements for existing data

Does anyone know how to write scripts for generating update statements for
existing data?
I found a stored procedure online that generates INSERT statements for a
given table, I was wondering if anyone has worked on a UPDATE generatorMike
What do you mean by "generating update"?
Do you want to build a script that does updateting?
declare @.sql varchar(400)
set @.sql='update '+ @.tablename+' set col'..........
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:9E10B2B9-80CC-4E50-9E34-5865A0C3BA1D@.microsoft.com...
> Does anyone know how to write scripts for generating update statements for
> existing data?
> I found a stored procedure online that generates INSERT statements for a
> given table, I was wondering if anyone has worked on a UPDATE generator|||I have a tables with about 1000 records and if I make changes I would like t
o
generate script (UPDATE scripts) for the existing data that I could run on
another server or ship it to a client with the latest changes instead of a
restore.
there is a good insert script available at
http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
similar for UPDATE
"Uri Dimant" wrote:

> Mike
> What do you mean by "generating update"?
> Do you want to build a script that does updateting?
>
> declare @.sql varchar(400)
> set @.sql='update '+ @.tablename+' set col'..........
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:9E10B2B9-80CC-4E50-9E34-5865A0C3BA1D@.microsoft.com...
>
>|||Mike
Do you consider using Replication/Triggers ? It will be much better than
generating SQL script.
If you use SQL Server 2005 you can create a SNAPSHOP database ( ah i forgot
, it should be on the same server),sorry.
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:00EF43C1-EBF7-4422-99D0-A1979F3500A0@.microsoft.com...
>I have a tables with about 1000 records and if I make changes I would like
>to
> generate script (UPDATE scripts) for the existing data that I could run on
> another server or ship it to a client with the latest changes instead of a
> restore.
> there is a good insert script available at
> http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
> similar for UPDATE
> "Uri Dimant" wrote:
>|||Mike wrote:
> I have a tables with about 1000 records and if I make changes I would like
to
> generate script (UPDATE scripts) for the existing data that I could run on
> another server or ship it to a client with the latest changes instead of a
> restore.
> there is a good insert script available at
> http://vyaskn.tripod.com/code.htm#inserts , I am looking for something
> similar for UPDATE
>
http://www.red-gate.com/products/SQ...mpare/index.htm
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Try www.sqlscripter.com to generate data scripts. All types are supported
(Insert, Update, Delete).
It's free.
Thomas
"Mike" wrote:

> Does anyone know how to write scripts for generating update statements for
> existing data?
> I found a stored procedure online that generates INSERT statements for a
> given table, I was wondering if anyone has worked on a UPDATE generator

generate the insert script from table of SQL Server

Dear All,

I would like to generate the insert scripts of the 500 records for the later software initialization. Could you suggest some software to do this? Thanks.

Alex

For information

the environment are SQL Server 2000

Try Squirrel.

http://search400.techtarget.com/tip/1,289483,sid3_gci1165699,00.html

Generate SQL insert statement fro SQL Server database

Good day,

I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.

Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?

Please someone help, this is driving me insane.

ThanksFound the answer, great sacript.

SQL Insert generator script - http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=1052&lngWId=5|||I wrote this a while back for my own use
Call this sp in a loop over SELECT TABLE_NAME FROM INFORMATION_SCHEMA.tables

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE spGenerateInsertStatement
(
@.TableName varchar(255),
@.SQL_INSERT varchar(8000) OUTPUT
)
AS

DECLARE @.current_column varchar(255)
DECLARE @.current_ordinal int
DECLARE @.max_ordinal int

--pre assign some values for safety
SET @.current_column = ''
SET @.current_ordinal = 0
SET @.max_ordinal = 0

SET @.SQL_INSERT = 'INSERT INTO [' + @.TableName + '] ('

--get the max ordinal for the table you're inserting into
SELECT @.max_ordinal = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = @.TableName

--create a cursor of column names and ordinals
DECLARE cInsert CURSOR
FOR
SELECT COLUMN_NAME,
ORDINAL_POSITION
FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = @.TableName
ORDER BY ORDINAL_POSITION

OPEN cInsert

--fetch the next record
FETCH NEXT FROM cInsert INTO @.current_column,@.current_ordinal

WHILE(@.@.FETCH_STATUS = 0)
BEGIN


IF(@.current_ordinal != @.max_ordinal)
BEGIN
--append "column," to the insert statement
SET @.SQL_INSERT = @.SQL_INSERT + '[' +@.current_column + '],' + CHAR(13) + CHAR(10)
END
ELSE
BEGIN
--append "column)" to the insert statement
SET @.SQL_INSERT = @.SQL_INSERT + '[' + @.current_column + '])' + CHAR(13) + CHAR(10)
END

--fetch the next record
FETCH NEXT FROM cInsert INTO @.current_column,@.current_ordinal
END

--cleanup cursor
CLOSE cInsert
DEALLOCATE cInsert

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Hi! I'm on your side now!|||Hi! I just joined the forum to learn more about sql server 2000 and make friends too!|||Raski, what do you mean you are on our side? Where you from?

Generate Scripts for existing records in a table

I can use Generate Scripts tool to get scripts for tables, procedures etc. But now I have records in tables which I want all "Insert into ..." queries from sqlserver. My co-worker said I have to manually type all these lines for my data.sql. Do I have to? I guess there must be a way...

Thanks,

LiliTry using DTS (Data Transformation services) which will allow you to recreate and / or rename the table and also lets you populate with original data.

The fastest way to recreate tables as you can recreate your entire database if you want to.

:p|||DTS is awsome :) but I need to deliver data.sql (of course with all other *.sql files) to the client so that they can install and drop database any time they mess it up :P|||It sounds like you want to give the client some base data. Could you not just BCP the data out into files and then create a command file that would truncate the tables and then BCP the data back in?

Or do you need to create the INSERT statements?
Example:INSERT myTbl (col1,col2,col3) VALUES ('xxx','aaa',123)|||Yes! base data. I need to insert statements. I just typed up some, but I'm wondering whether I can use a tool to make life easier :)

and guru, could you read my post about "Create My Own Log (for functionality) "? Thank you!

Lili|||Following sql statement will help you to generate INSERT STATEMENT for your base table

Table Name : BaseTable
fields
BName varchar(20)
dt smalldatetime
val int

select 'Insert into BaseTable VALUES (' + '''' + bname + '''' + ','
+ '''' + convert(varchar(10),dt,101) + '''' + ','
+ '''' + convert(varchar(10),val) + '''' + ')'
from basetable

handle NULL part by using ISNULL function

All the best|||Wow! Good idea. Silly me :p Thanks,

Lili|||You know sometimes I get carried away. I wrote a stored procedure that will generate all the INSERT statements for a given table. I've attached the proc, it worked for me on 2000.

Example on pubs
exec usp_CreateInsert discounts

OUTPUT:
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Initial Customer',NULL,NULL,NULL,10.50)
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Volume Discount',NULL,100,1000,6.70)
INSERT discounts ( discounttype, stor_id, lowqty, highqty, discount)
VALUES ('Customer Discount','8042',NULL,NULL,5.00)|||//@.@. where is the stored procedure?|||It must have stoppped me because the extension of the file was '.sql', so I had to change it to '.txt'|||wow, you are real sql guru //admire

thank you for your help and sharing :)

Have a nice weekend!

Lili

Generate Script and Insert Data

I have a question regarding generating SQL script from a database.If you use SQL Server Management Express, it lets you generate SQLscript that can be used to recreate table structure, stored procedures,etc. But it does not include the actual data stored in the tables.

Is it possible to generate SQL to *INSERT* data as well? If yes, then how?

You can get a template query for inserting data to 1 table by right clicking the table in Object Explorer->choose Script Table As->INSERT To. However there is no such generated script to do actual INSERTs and it is not necessary, because there are serveral easy way to transfer data into the new table, for example you can just use such INSERT command:

INSERT INTO newTable SELECT * FROM oldTable

|||

Here is software that does exactly what you want:http://www.denovations.com/products/sqlpopulator/

I'm using it by myself while creating database installation script and should say this solution matched all my requirements.

Generate multiple rows for insert from single row

Dear all,

I have a package in which, when a Cost Center has X as a value, I must insert not X but many different Y value, which are associated with X. How can I gather and treat those Y values? Only using a Script Component?

Regards,

Pedro Martins

Are the Y values stored anywhere? Can you not merge the two data sets?

Generate insert statement for table

I have data in a table that I wish to transfer to a remote system (both SQL
2000).
Is there an easy way to generate a series of insert statements from my
database so I can transfer this text file to my remote system. If possible
I like to be able to exclude data with a where statement.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004http://vyaskn.tripod.com/code.htm#inserts
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...

quote:

---

> I have data in a table that I wish to transfer to a remote system (both

---

SQL

quote:

---

> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If

---

possible

quote:

---

> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>

---

|||Here's a PERL script for generating SQL INSERT statements.
The script deals with all data types, except image and
sql_variant, but including the user defined data types.
#############Begin script###########
# Created by: Linchi Shea
use strict;
use Data::Dumper;
use Win32::ODBC;
use Getopt::Std;
my %opts;
getopts('S:d:t:', \%opts);
my ($server, $dbName, $tbName) = ($opts{S}, $opts{d}, $opts
{t});
(defined $server && defined $dbName && defined $tbName) or
printUsage();
Main: {
my $connStr = "Driver={SQL Server};Server=$server;" .
"Database=$dbName;Trusted_Connection=yes";
my $conn = new Win32::ODBC($connStr) or
die "***Err: " . Win32::ODBC::Error();
my ($columnRef, $attribRef) = getColumnProperties
($tbName, $conn);
my $sql = constructINSERT($columnRef, $attribRef,
$conn);
print $sql;
$conn->Close();
} # Main
############################
sub getColumnProperties {
my($tbName, $conn) = @._;
my @.columns;
my %attrib;
if (! $conn->Sql("select * from $tbName where 1=2") ) {
1 while $conn->FetchRow();
# first get the data type for each column
my @.fields = $conn->FieldNames();
%attrib = $conn->ColAttributes($conn-

quote:

---

>SQL_COLUMN_TYPE_NAME, @.fields);

---

# in case the data type is user defined, we need
# the real data type to help us decide how to handle
# the retrieved data in an INSERT statement
foreach my $field (@.fields) {
if (! $conn->Sql("sp_help '$attrib{$field}'") )
{
while($conn->FetchRow()) {
my ($type) = $conn->Data("Storage_type");
$attrib{$field} = $type;
}
}
if ($attrib{$field} =~ /^(image|sql_variant)$/i) {
die "***Err: data type $attrib{$field} not
supported.\n";
}
push @.columns, $field if lc($attrib{$field})
ne 'timestamp';
}
}
else {
die "***Err: failed to run select * from $tbName
where 1=2.\n";
}
return (\@.columns, \%attrib);
} # getColumnProperties
########################
sub constructINSERT {
my($columnRef, $attribRef, $conn) = @._;
(scalar @.$columnRef && scalar %$attribRef) or
die "Err: \$columnRef or \$attribRef is empty.\n";
my $sql;
if (! $conn->Sql("select * from $tbName") ) {
# now get the data values for each row
while ($conn->FetchRow()) {
$sql .= "INSERT $tbName (" . join(',',
@.$columnRef) . ")\n";
my @.values = ();
my %data = $conn->DataHash();
# decide how to handle the VALUE clause of the
INSERT
foreach my $column (@.$columnRef) {
# the values of these data types can be used
as is
if ($attribRef->{$column}
=~ /int|smallint|bigint|tinyint|
bit|decimal|numeric|money|
smallmoney|float|real
/ix) {
if (defined $data{$column}) {
push @.values, $data{$column};
}
else {
push @.values, 'NULL';
}
}
# the values of these types must be quoted
with a pair of
# single quotation marks
elsif ($attribRef->{$column}
=~ /datetime|smalldatetime|
char|varchar|nchar|nvarchar|
text|ntext|uniqueidentifier
/ix) {
if (defined $data{$column}) {
$data{$column} =~ s/'/''/g;
push @.values, "'$data{$column}'";
}
else {
push @.values, 'NULL';
}
}
# the binary data must be converted to a HEX
string format
elsif ($attribRef->{$column}
=~ /binary|varbinary
/ix) {
if (defined $data{$column}) {
push @.values, '0x' . unpack("H*", $data
{$column});
}
else {
push @.values, 'NULL';
}
}
else {
print "***Assert: invalid code path. Skip
this row.\n";
next;
}
}
$sql .= "VALUES (" . join(',', @.values) . ")\n";
}
}
return $sql;
} # construtcINSERT
###################
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl GeneratedataInserts.pl -S <SQL server or
instance>
-d <database name>
[ -t <table name> ]
--Usage--
exit;
} # printUsage
##############End script###############

quote:

---

>--Original Message--
>I have data in a table that I wish to transfer to a

---

remote system (both SQL

quote:

---

>2000).
>Is there an easy way to generate a series of insert

---

statements from my

quote:

---

>database so I can transfer this text file to my remote

---

system. If possible

quote:

---

>I like to be able to exclude data with a where statement.
>Regards
>Jeff
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.564 / Virus Database: 356 - Release Date:

---

19/01/2004

quote:

---

>
>.
>

---

|||If that's your sole purpose, you can get by more easily with
1. creating a separate db
2. put the data in a table in that db
3. backup that db
4. do the reverse on the remove server.
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...

quote:

---

> I have data in a table that I wish to transfer to a remote system (both

---

SQL

quote:

---

> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If

---

possible

quote:

---

> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>

---

Generate insert statement for table

I have data in a table that I wish to transfer to a remote system (both SQL
2000).
Is there an easy way to generate a series of insert statements from my
database so I can transfer this text file to my remote system. If possible
I like to be able to exclude data with a where statement.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004http://vyaskn.tripod.com/code.htm#inserts
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
> I have data in a table that I wish to transfer to a remote system (both
SQL
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>|||Here's a Perl script for generating SQL INSERT statements.
The script deals with all data types, except image and
sql_variant, but including the user defined data types.
#############Begin script###########
# Created by: Linchi Shea
use strict;
use Data::Dumper;
use Win32::ODBC;
use Getopt::Std;
my %opts;
getopts('S:d:t:', \%opts);
my ($server, $dbName, $tbName) = ($opts{S}, $opts{d}, $opts
{t});
(defined $server && defined $dbName && defined $tbName) or
printUsage();
Main: {
my $connStr = "Driver={SQL Server};Server=$server;" .
"Database=$dbName;Trusted_Connection=yes";
my $conn = new Win32::ODBC($connStr) or
die "***Err: " . Win32::ODBC::Error();
my ($columnRef, $attribRef) = getColumnProperties
($tbName, $conn);
my $sql = constructINSERT($columnRef, $attribRef,
$conn);
print $sql;
$conn->Close();
} # Main
############################
sub getColumnProperties {
my($tbName, $conn) = @._;
my @.columns;
my %attrib;
if (! $conn->Sql("select * from $tbName where 1=2") ) {
1 while $conn->FetchRow();
# first get the data type for each column
my @.fields = $conn->FieldNames();
%attrib = $conn->ColAttributes($conn-
>SQL_COLUMN_TYPE_NAME, @.fields);
# in case the data type is user defined, we need
# the real data type to help us decide how to handle
# the retrieved data in an INSERT statement
foreach my $field (@.fields) {
if (! $conn->Sql("sp_help \'$attrib{$field}\'") )
{
while($conn->FetchRow()) {
my ($type) = $conn->Data("Storage_type");
$attrib{$field} = $type;
}
}
if ($attrib{$field} =~ /^(image|sql_variant)$/i) {
die "***Err: data type $attrib{$field} not
supported.\n";
}
push @.columns, $field if lc($attrib{$field})
ne 'timestamp';
}
}
else {
die "***Err: failed to run select * from $tbName
where 1=2.\n";
}
return (\@.columns, \%attrib);
} # getColumnProperties
########################
sub constructINSERT {
my($columnRef, $attribRef, $conn) = @._;
(scalar @.$columnRef && scalar %$attribRef) or
die "Err: \$columnRef or \$attribRef is empty.\n";
my $sql;
if (! $conn->Sql("select * from $tbName") ) {
# now get the data values for each row
while ($conn->FetchRow()) {
$sql .= "INSERT $tbName (" . join(',',
@.$columnRef) . ")\n";
my @.values = ();
my %data = $conn->DataHash();
# decide how to handle the VALUE clause of the
INSERT
foreach my $column (@.$columnRef) {
# the values of these data types can be used
as is
if ($attribRef->{$column}
=~ /int|smallint|bigint|tinyint|
bit|decimal|numeric|money|
smallmoney|float|real
/ix) {
if (defined $data{$column}) {
push @.values, $data{$column};
}
else {
push @.values, 'NULL';
}
}
# the values of these types must be quoted
with a pair of
# single quotation marks
elsif ($attribRef->{$column}
=~ /datetime|smalldatetime|
char|varchar|nchar|nvarchar|
text|ntext|uniqueidentifier
/ix) {
if (defined $data{$column}) {
$data{$column} =~ s/\'/\'\'/g;
push @.values, "\'$data{$column}\'";
}
else {
push @.values, 'NULL';
}
}
# the binary data must be converted to a HEX
string format
elsif ($attribRef->{$column}
=~ /binary|varbinary
/ix) {
if (defined $data{$column}) {
push @.values, '0x' . unpack("H*", $data
{$column});
}
else {
push @.values, 'NULL';
}
}
else {
print "***Assert: invalid code path. Skip
this row.\n";
next;
}
}
$sql .= "VALUES (" . join(',', @.values) . ")\n";
}
}
return $sql;
} # construtcINSERT
###################
sub printUsage {
print << '--Usage--';
Usage:
cmd>perl GeneratedataInserts.pl -S <SQL server or
instance>
-d <database name>
[ -t <table name> ]
--Usage--
exit;
} # printUsage
##############End script###############
>--Original Message--
>I have data in a table that I wish to transfer to a
remote system (both SQL
>2000).
>Is there an easy way to generate a series of insert
statements from my
>database so I can transfer this text file to my remote
system. If possible
>I like to be able to exclude data with a where statement.
>Regards
>Jeff
>
>--
>Outgoing mail is certified Virus Free.
>Checked by AVG anti-virus system (http://www.grisoft.com).
>Version: 6.0.564 / Virus Database: 356 - Release Date:
19/01/2004
>
>.
>|||If that's your sole purpose, you can get by more easily with
1. creating a separate db
2. put the data in a table in that db
3. backup that db
4. do the reverse on the remove server.
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:Oxo8g173DHA.540@.tk2msftngp13.phx.gbl...
> I have data in a table that I wish to transfer to a remote system (both
SQL
> 2000).
> Is there an easy way to generate a series of insert statements from my
> database so I can transfer this text file to my remote system. If
possible
> I like to be able to exclude data with a where statement.
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19/01/2004
>

Generate insert script

does anyone know if there is a way to generate an insert script based
on the records in a table? I want to distribute the contents of a
couple of tables to customers without having to use BCP or DTS.

Greetings Sjaak"Sjaak van Esdonk" <sjaakvanesdonk@.hotmail.com> wrote in message
news:7479e65c.0404050347.1105649b@.posting.google.c om...
> does anyone know if there is a way to generate an insert script based
> on the records in a table? I want to distribute the contents of a
> couple of tables to customers without having to use BCP or DTS.
> Greetings Sjaak

http://vyaskn.tripod.com/code.htm#inserts
http://www.rac4sql.net/objectscriptr_main.asp

Simon|||Thanks Simon, thats exactly what i needed! This is going to save me lots of time !|||sjaakvanesdonk@.hotmail.com (Sjaak van Esdonk) wrote in message news:<7479e65c.0404050347.1105649b@.posting.google.com>...
> does anyone know if there is a way to generate an insert script based
> on the records in a table? I want to distribute the contents of a
> couple of tables to customers without having to use BCP or DTS.
> Greetings Sjaak

Hi,
use SQL Scripter (http://www.sqlscripter.com) to create Insert, Update
or Delete scripts ...

Thomas