insert or replace: named

Showing posts with label named. Show all posts

Monday, March 12, 2012

Insert statement help needed

I have set up a trace, and have the following statement to insert the trace
results into a table named DurationTrace.
If my server were named MyServer and table DurationTrace were to have its
last column named ServerName, is there any way to include the name of the
server [MyServer] as part of the following insert statement? Or would I have
to perform the following insert, followed by an update statement?
INSERT DurationTrace
SELECT *
FROM ::fn_trace_gettable(@.path_file_ext, default)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1Following good preactoces and always name the columns in the SELECT list as well as the INSERT
statement makes this a breeze:
INSERT INTO DurationTrace(col1, col2, ..., MachineName)
VALUES SELECT col1, col2, ... 'MyMachine'
FROM ::fn_trace_gettable(@.path_file_ext, default)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:5de2a069eedbd@.uwe...
>I have set up a trace, and have the following statement to insert the trace
> results into a table named DurationTrace.
> If my server were named MyServer and table DurationTrace were to have its
> last column named ServerName, is there any way to include the name of the
> server [MyServer] as part of the following insert statement? Or would I have
> to perform the following insert, followed by an update statement?
> INSERT DurationTrace
> SELECT *
> FROM ::fn_trace_gettable(@.path_file_ext, default)
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1|||Tibor
> INSERT INTO DurationTrace(col1, col2, ..., MachineName)
> VALUES SELECT col1, col2, ... 'MyMachine'
> FROM ::fn_trace_gettable(@.path_file_ext, default)
We do not need VALUES here.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBwb0ZbUGHA.1564@.TK2MSFTNGP11.phx.gbl...
> Following good preactoces and always name the columns in the SELECT list
> as well as the INSERT statement makes this a breeze:
> INSERT INTO DurationTrace(col1, col2, ..., MachineName)
> VALUES SELECT col1, col2, ... 'MyMachine'
> FROM ::fn_trace_gettable(@.path_file_ext, default)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
> news:5de2a069eedbd@.uwe...
>>I have set up a trace, and have the following statement to insert the
>>trace
>> results into a table named DurationTrace.
>> If my server were named MyServer and table DurationTrace were to have its
>> last column named ServerName, is there any way to include the name of the
>> server [MyServer] as part of the following insert statement? Or would I
>> have
>> to perform the following insert, followed by an update statement?
>> INSERT DurationTrace
>> SELECT *
>> FROM ::fn_trace_gettable(@.path_file_ext, default)
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
>|||> We do not need VALUES here.
Correct. That for catching that. Uri.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:Oqd2L$jUGHA.1304@.tk2msftngp13.phx.gbl...
> Tibor
>> INSERT INTO DurationTrace(col1, col2, ..., MachineName)
>> VALUES SELECT col1, col2, ... 'MyMachine'
>> FROM ::fn_trace_gettable(@.path_file_ext, default)
> We do not need VALUES here.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uBwb0ZbUGHA.1564@.TK2MSFTNGP11.phx.gbl...
>> Following good preactoces and always name the columns in the SELECT list as well as the INSERT
>> statement makes this a breeze:
>> INSERT INTO DurationTrace(col1, col2, ..., MachineName)
>> VALUES SELECT col1, col2, ... 'MyMachine'
>> FROM ::fn_trace_gettable(@.path_file_ext, default)
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:5de2a069eedbd@.uwe...
>>I have set up a trace, and have the following statement to insert the trace
>> results into a table named DurationTrace.
>> If my server were named MyServer and table DurationTrace were to have its
>> last column named ServerName, is there any way to include the name of the
>> server [MyServer] as part of the following insert statement? Or would I have
>> to perform the following insert, followed by an update statement?
>> INSERT DurationTrace
>> SELECT *
>> FROM ::fn_trace_gettable(@.path_file_ext, default)
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
>|||Tibor
> Correct. That for catching that. Uri.
Did you mean thanks for catching that. Uri.?:-))))))))
It is morning here and such things happend to me too a lot
Have a nice day
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
news:ONZGsykUGHA.4340@.TK2MSFTNGP10.phx.gbl...
>> We do not need VALUES here.
> Correct. That for catching that. Uri.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:Oqd2L$jUGHA.1304@.tk2msftngp13.phx.gbl...
>> Tibor
>> INSERT INTO DurationTrace(col1, col2, ..., MachineName)
>> VALUES SELECT col1, col2, ... 'MyMachine'
>> FROM ::fn_trace_gettable(@.path_file_ext, default)
>> We do not need VALUES here.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uBwb0ZbUGHA.1564@.TK2MSFTNGP11.phx.gbl...
>> Following good preactoces and always name the columns in the SELECT list
>> as well as the INSERT statement makes this a breeze:
>> INSERT INTO DurationTrace(col1, col2, ..., MachineName)
>> VALUES SELECT col1, col2, ... 'MyMachine'
>> FROM ::fn_trace_gettable(@.path_file_ext, default)
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
>> news:5de2a069eedbd@.uwe...
>>I have set up a trace, and have the following statement to insert the
>>trace
>> results into a table named DurationTrace.
>> If my server were named MyServer and table DurationTrace were to have
>> its
>> last column named ServerName, is there any way to include the name of
>> the
>> server [MyServer] as part of the following insert statement? Or would I
>> have
>> to perform the following insert, followed by an update statement?
>> INSERT DurationTrace
>> SELECT *
>> FROM ::fn_trace_gettable(@.path_file_ext, default)
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
>>
>|||> Did you mean thanks for catching that. Uri.?:-))))))))
Yep. It was morning here too when I wrote that. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23PKlY3kUGHA.5884@.TK2MSFTNGP14.phx.gbl...
> Tibor
>> Correct. That for catching that. Uri.
> Did you mean thanks for catching that. Uri.?:-))))))))
> It is morning here and such things happend to me too a lot
> Have a nice day
>
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ONZGsykUGHA.4340@.TK2MSFTNGP10.phx.gbl...
>> We do not need VALUES here.
>> Correct. That for catching that. Uri.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:Oqd2L$jUGHA.1304@.tk2msftngp13.phx.gbl...
>> Tibor
>> INSERT INTO DurationTrace(col1, col2, ..., MachineName)
>> VALUES SELECT col1, col2, ... 'MyMachine'
>> FROM ::fn_trace_gettable(@.path_file_ext, default)
>> We do not need VALUES here.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:uBwb0ZbUGHA.1564@.TK2MSFTNGP11.phx.gbl...
>> Following good preactoces and always name the columns in the SELECT list as well as the INSERT
>> statement makes this a breeze:
>> INSERT INTO DurationTrace(col1, col2, ..., MachineName)
>> VALUES SELECT col1, col2, ... 'MyMachine'
>> FROM ::fn_trace_gettable(@.path_file_ext, default)
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:5de2a069eedbd@.uwe...
>>I have set up a trace, and have the following statement to insert the trace
>> results into a table named DurationTrace.
>> If my server were named MyServer and table DurationTrace were to have its
>> last column named ServerName, is there any way to include the name of the
>> server [MyServer] as part of the following insert statement? Or would I have
>> to perform the following insert, followed by an update statement?
>> INSERT DurationTrace
>> SELECT *
>> FROM ::fn_trace_gettable(@.path_file_ext, default)
>> --
>> Message posted via SQLMonster.com
>> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200603/1
>>
>>
>

Insert statement help needed

Sunday, February 19, 2012

insert or replace

Hi,
I'm porting an application using an SQLite database. In SQLite there is
a sql-statement named "INSERT OR REPLACE" which insert a record if it's
not already exist, and update it if it's already there. Is there
something similar in SQL Server? Or do I have to first do a SELECT to
see if the record already exist and then do either an INSERT or UPDATE?Hi,
In SQL Server you will have to use IF Exists. Use the below syntax...
IF Exists(Select statement)
Update
Else
Insert
Thanks
Hari
SQL Server MVP
<nyhetsgrupper@.gmail.com> wrote in message
news:1159729668.855789.212000@.h48g2000cwc.googlegroups.com...
> Hi,
> I'm porting an application using an SQLite database. In SQLite there is
> a sql-statement named "INSERT OR REPLACE" which insert a record if it's
> not already exist, and update it if it's already there. Is there
> something similar in SQL Server? Or do I have to first do a SELECT to
> see if the record already exist and then do either an INSERT or UPDATE?
>|||On 1 Oct 2006 12:07:48 -0700, nyhetsgrupper@.gmail.com wrote:
>Hi,
>I'm porting an application using an SQLite database. In SQLite there is
>a sql-statement named "INSERT OR REPLACE" which insert a record if it's
>not already exist, and update it if it's already there. Is there
>something similar in SQL Server? Or do I have to first do a SELECT to
>see if the record already exist and then do either an INSERT or UPDATE?
Hi nyhetsgrupper,
There is (unfortunately) no single keyword for INSERT OR UPDATE. I am
one of the many people who'd love to see Microsoft implement a MERGE or
UPSERT operation.
For now, we have to make do with one of these techniques:
A) For processing multiple (zero, one, or more) rows:
UPDATE d
SET DataCol1 = s.DataCol1,
DataCol2 = s.DataCol2
FROM Source AS s
INNER JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key,
-- or else unexpected things might happen.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT KeyCol1, KeyCol2, DataCol1, DataCol2
FROM Source AS s
LEFT JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key above.
-- Only one of the key columns suffices below.
WHERE d.KeyCol1 IS NULL;
B) For processing a single row if most rows are expected to be inserted.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT @.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2
WHERE NOT EXISTS
(SELECT *
FROM Dest
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2);
IF @.@.ROWCOUNT = 0
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
C) For processing a single row if most rows are expected to be updated.
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
IF @.@.ROWCOUNT = 0
INSERT INTO Dest
(KeyCol1, KeyCol2, DataCol1, DataCol2)
VALUES (@.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2);
Hugo Kornelis, SQL Server MVP

insert or replace

Hi,
I'm porting an application using an SQLite database. In SQLite there is
a sql-statement named "INSERT OR REPLACE" which insert a record if it's
not already exist, and update it if it's already there. Is there
something similar in SQL Server? Or do I have to first do a SELECT to
see if the record already exist and then do either an INSERT or UPDATE?
Hi,
In SQL Server you will have to use IF Exists. Use the below syntax...
IF Exists(Select statement)
Update
Else
Insert
Thanks
Hari
SQL Server MVP
<nyhetsgrupper@.gmail.com> wrote in message
news:1159729668.855789.212000@.h48g2000cwc.googlegr oups.com...
> Hi,
> I'm porting an application using an SQLite database. In SQLite there is
> a sql-statement named "INSERT OR REPLACE" which insert a record if it's
> not already exist, and update it if it's already there. Is there
> something similar in SQL Server? Or do I have to first do a SELECT to
> see if the record already exist and then do either an INSERT or UPDATE?
>
|||On 1 Oct 2006 12:07:48 -0700, nyhetsgrupper@.gmail.com wrote:

>Hi,
>I'm porting an application using an SQLite database. In SQLite there is
>a sql-statement named "INSERT OR REPLACE" which insert a record if it's
>not already exist, and update it if it's already there. Is there
>something similar in SQL Server? Or do I have to first do a SELECT to
>see if the record already exist and then do either an INSERT or UPDATE?
Hi nyhetsgrupper,
There is (unfortunately) no single keyword for INSERT OR UPDATE. I am
one of the many people who'd love to see Microsoft implement a MERGE or
UPSERT operation.
For now, we have to make do with one of these techniques:
A) For processing multiple (zero, one, or more) rows:
UPDATE d
SET DataCol1 = s.DataCol1,
DataCol2 = s.DataCol2
FROM Source AS s
INNER JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key,
-- or else unexpected things might happen.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT KeyCol1, KeyCol2, DataCol1, DataCol2
FROM Source AS s
LEFT JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key above.
-- Only one of the key columns suffices below.
WHERE d.KeyCol1 IS NULL;
B) For processing a single row if most rows are expected to be inserted.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT @.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2
WHERE NOT EXISTS
(SELECT *
FROM Dest
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2);
IF @.@.ROWCOUNT = 0
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
C) For processing a single row if most rows are expected to be updated.
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
IF @.@.ROWCOUNT = 0
INSERT INTO Dest
(KeyCol1, KeyCol2, DataCol1, DataCol2)
VALUES (@.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2);
Hugo Kornelis, SQL Server MVP

insert or replace

Hi,
I'm porting an application using an SQLite database. In SQLite there is
a sql-statement named "INSERT OR REPLACE" which insert a record if it's
not already exist, and update it if it's already there. Is there
something similar in SQL Server? Or do I have to first do a SELECT to
see if the record already exist and then do either an INSERT or UPDATE?Hi,
In SQL Server you will have to use IF Exists. Use the below syntax...
IF Exists(Select statement)
Update
Else
Insert
Thanks
Hari
SQL Server MVP
<nyhetsgrupper@.gmail.com> wrote in message
news:1159729668.855789.212000@.h48g2000cwc.googlegroups.com...
> Hi,
> I'm porting an application using an SQLite database. In SQLite there is
> a sql-statement named "INSERT OR REPLACE" which insert a record if it's
> not already exist, and update it if it's already there. Is there
> something similar in SQL Server? Or do I have to first do a SELECT to
> see if the record already exist and then do either an INSERT or UPDATE?
>|||On 1 Oct 2006 12:07:48 -0700, nyhetsgrupper@.gmail.com wrote:

>Hi,
>I'm porting an application using an SQLite database. In SQLite there is
>a sql-statement named "INSERT OR REPLACE" which insert a record if it's
>not already exist, and update it if it's already there. Is there
>something similar in SQL Server? Or do I have to first do a SELECT to
>see if the record already exist and then do either an INSERT or UPDATE?
Hi nyhetsgrupper,
There is (unfortunately) no single keyword for INSERT OR UPDATE. I am
one of the many people who'd love to see Microsoft implement a MERGE or
UPSERT operation.
For now, we have to make do with one of these techniques:
A) For processing multiple (zero, one, or more) rows:
UPDATE d
SET DataCol1 = s.DataCol1,
DataCol2 = s.DataCol2
FROM Source AS s
INNER JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key,
-- or else unexpected things might happen.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT KeyCol1, KeyCol2, DataCol1, DataCol2
FROM Source AS s
LEFT JOIN Dest AS d
ON d.KeyCol1 = s.KeyCol1
AND d.KeyCol2 = s.KeyCol2;
-- Make sure to include ALL columns of the primary key above.
-- Only one of the key columns suffices below.
WHERE d.KeyCol1 IS NULL;
B) For processing a single row if most rows are expected to be inserted.
INSERT INTO Dest (KeyCol1, KeyCol2, DataCol1, DataCol2)
SELECT @.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2
WHERE NOT EXISTS
(SELECT *
FROM Dest
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2);
IF @.@.ROWCOUNT = 0
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
C) For processing a single row if most rows are expected to be updated.
UPDATE Dest
SET DataCol1 = @.DataCol1,
DataCol2 = @.DataCol2
WHERE KeyCol1 = @.KeyCol1
AND KeyCol2 = @.KeyCol2;
IF @.@.ROWCOUNT = 0
INSERT INTO Dest
(KeyCol1, KeyCol2, DataCol1, DataCol2)
VALUES (@.KeyCol1, @.KeyCol2, @.DataCol1, @.DataCol2);
Hugo Kornelis, SQL Server MVP

Monday, March 12, 2012

Insert statement help needed

Insert statement help needed

Insert statement help needed

Sunday, February 19, 2012

insert or replace

insert or replace

insert or replace

insert or replace

Blog Archive

About Me