IF EXISTS statement in my perl program

I am having trouble finishing my query.

This is what I have:

IF EXISTS(Select ApplicationID from Application Where Application = '&_')
Insert Into PCApp(ApplicationID, SystemNetName)
Values( , $HoH->{Host}{SystemNetName})

I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.

Any suggestions would be appreciated.

Thanks,
LauraUse a SELECT statement instead of the VALUES clause.

-PatP|||And using a select statement will still insert the values into the table?|||In an INSERT statement, you can use the VALUES clause for a list of constants, or a SELECT clause for a list of expressions. The SELECT can include multiple rows and/or contain UNION operators to create multiple row inserts using just one INSERT statement. The SELECT buys you the ability to use expressions (including function calls), generate multiple rows, etc.

-PatP|||Ok I think I got it. Off the subject, can the IF...ELSE Contain an embedded IF...ELSE? Is it ok to have two inserts with the if section?

For example,

IF NOT EXISTS (Select ApplicationID from Application Where (Application = '$_' ))
INSERT INTO Application(Application)
Values('$_')

INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')

ELSE
INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";

Thanks for your help.
-Laura|||I think what you meant was:IF NOT EXISTS (Select ApplicationID
FROM Application Where (Application = '$_' ))
BEGIN
INSERT INTO Application(Application)
Values('$_')

INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_'
AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')
END
ELSE
INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_'
AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";Note the addition of the BEGIN...END (in red) to your code.

-PatP|||CROSS JOIN?

And what's with the double quote on the end?|||Ok, so the cross join isn't the way that I'd approach it, but it would work... Kind of like the way you had to construct joins using the pre-SQL-89 syntax. Ugly, but adequate to the job!

The double quote hanging off of the end is actually because Laura is taking this SQL out of the middle of her Perl code. It isn't really part of the SQL syntax at all.

-PatP|||Really...OK

Laura...start writting stored procedures and execute them instead....

Never did learn Pearl...though we did use it for an Oracle project once...

I gotta find a Rexx interpreter for Windoze....|||Originally, I used a left join but when I tested my query in sql server's enterprise manager it automatically changed it to cross join.

The begin and end worked. Thanks for the help.

-Laura|||I gotta find a Rexx interpreter for Windoze....I've never tried it, but I've heard that Reginald (http://www.borg.com/~jglatt/rexx/win32/rxusrw32.htm) isn't too bad.

-PatP|||Yeah, I am pretty new at SQL Server. Just started learning it last month because I am going to be the database administrator. So, I have not begun using stored procedures yet, but I will.

Thanks,
Laura|||Hold the phone...Enterprise Manager?

Do you mean Query Analyzer?

And Pat....RxSocks....Not that I'll find a practical application for it...(Well maybe I could replace DTS), but I bet I can get it to talk to SQL Server...

Very cool

Thanks|||Enterprise Manager -> Opened the table view to see the records that were stored -> on the top toolbar there is an sql button that I tested my statements. It probably isn't the best way, but I wanted to have quick access to my table design and query results.

I am aware of Query Analyzer but I have not used it much.

-Laurasql

If anybody like know...

i do not understand.

I have this code:

#import "C:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF", "EndOfFile")

...

_ConnectionPtr Conn;

...

CoInitialize(NULL);

HRESULT hr = Conn.CreateInstance(__uuidof(Connection));

Conn->Provider = "Microsoft.Jet.OLEDB.4.0";

Conn->ConnectionString="Data Source=c:\\mydb.mdb;";

hr = Conn->Open( "","Admin","", adConnectUnspecified);

Whith My account this code work good, but when i try to start my program whith another user login, i have this error:

hr=0x800a0e7a -> Provider cannot be found. It may not be properly installed.

Why by my login program run normal, but by another login failed? OS Xpsp2 whith standart MDAC 2.8 installed.

Tthis is bug: sum keys in registry have no permissions for another users in system.

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[tablename] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.
You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call that
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
David Portas
SQL Server MVP

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[table
name] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call tha
t
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
David Portas
SQL Server MVP
--

IDENTITY Insert doesnt work for Linked servers

Hi Everybody,
In my program, I am inserting rows from the source database to the
destination database which are on different servers. From source database,
with the help of the link server, I am connecting to the destination server.
In the destination table(destination db), I have one identity column which
is a primary key. But in that table, I need to insert the rows with the same
values as in the source table(source db). So i am using
SET IDENTITY_INSERT [linked server].[database].[user].[tablename] ON
But this when executed shows me an error - "Table does not exist or cannot
be opened for SET operation."
Please tell me how to make the identity insert ON on the table through the
linked server. I can execute the same command on any table on the same
database server, but not on destination database server.
Thanks.You can't. You'll have to do it on the destination server - for instance you
can create a SP on the target server that inserts the data and then call that
SP remotely. Or use DTS for example.
Why do you care that the IDENTITY value is preserved in the destination DB?
This rather defeats the point of an IDENTITY column. Allow the server to
assign a new IDENTITY value and then assign the new ID to any referencing
rows as you insert them. For queries across the two servers relate the data
on a natural key, not on the IDENTITY.
--
David Portas
SQL Server MVP
--