Hello everyone,
I have a general question about INSERT statements. Is it possible to
insert fields into a table both through a SELECT statement and
explicitly in the same INSERT statement? For example, if I have the
following part of an INSERT statement:
INSERT INTO table1 (column1, column2)
Is it possible to insert "column1" using a SELECT statement and
"column2" explicitly? What I am trying to do is insert multiple rows
into a table where some of the field information I need is located in
another table and some I need to insert explicitly. Thanks for your
replies in advance.This would work..
INSERT INTO table1 (column1, column2)
select au_lname, au_fname from authors
UNION
SELECT 'Fred','Bloggs'
HTH. Ryan
"schoultzy" <schoultzy@.yahoo.com> wrote in message
news:1150120091.014547.51090@.u72g2000cwu.googlegroups.com...
> Hello everyone,
> I have a general question about INSERT statements. Is it possible to
> insert fields into a table both through a SELECT statement and
> explicitly in the same INSERT statement? For example, if I have the
> following part of an INSERT statement:
> INSERT INTO table1 (column1, column2)
> Is it possible to insert "column1" using a SELECT statement and
> "column2" explicitly? What I am trying to do is insert multiple rows
> into a table where some of the field information I need is located in
> another table and some I need to insert explicitly. Thanks for your
> replies in advance.
>|||>Is it possible to insert "column1" using a SELECT statement and
>"column2" explicitly?
Yes
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
"schoultzy" <schoultzy@.yahoo.com> wrote in message
news:1150120091.014547.51090@.u72g2000cwu.googlegroups.com...
> Hello everyone,
> I have a general question about INSERT statements. Is it possible to
> insert fields into a table both through a SELECT statement and
> explicitly in the same INSERT statement? For example, if I have the
> following part of an INSERT statement:
> INSERT INTO table1 (column1, column2)
> Is it possible to insert "column1" using a SELECT statement and
> "column2" explicitly? What I am trying to do is insert multiple rows
> into a table where some of the field information I need is located in
> another table and some I need to insert explicitly. Thanks for your
> replies in advance.
>|||I think i mis-understood.
Try this :-
DECLARE @.Constant varchar(50)
SET @.Constant = 'Constant Value'
INSERT INTO table1 (column1, column2)
SELECT Col001, @.Constant FROM tablename
HTH. Ryan
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23K9KYfijGHA.4044@.TK2MSFTNGP03.phx.gbl...
> This would work..
> INSERT INTO table1 (column1, column2)
> select au_lname, au_fname from authors
> UNION
> SELECT 'Fred','Bloggs'
> --
> HTH. Ryan
>
> "schoultzy" <schoultzy@.yahoo.com> wrote in message
> news:1150120091.014547.51090@.u72g2000cwu.googlegroups.com...
>|||Thanks to everyone for your replies. It seams as though using a
variable to hold the constant information will be the best solution for
me. Thank you for holding my hand. My novice status with SQL was on
full display with this post.|||No worries, we all need to start somewhere.
"schoultzy" <schoultzy@.yahoo.com> wrote in message
news:1150121330.164136.315130@.h76g2000cwa.googlegroups.com...
> Thanks to everyone for your replies. It seams as though using a
> variable to hold the constant information will be the best solution for
> me. Thank you for holding my hand. My novice status with SQL was on
> full display with this post.
>
Showing posts with label toinsert. Show all posts
Showing posts with label toinsert. Show all posts
Monday, March 12, 2012
Sunday, February 19, 2012
Insert or update depending on existence of record
Hello
I'm using SQL Server 2000, Windows 2000.
I'm writing a SP to check for the existence of records in a table, and to
insert or update based on this existence. In other words, I have 2 tables
- source and destination. If a record from the source table already exists
in the destination table I want to update it, if not, I want to insert the
record. At them moment I am using 'If Exists', but I don't know how to
loop through all the records in the source table and check for each one.
Because the SP finds at least one record in both tables, Exists is true and
the SP never reaches the 'Else'.
Here's what I have so far (apologies for the formatting - it's gone a bit
mad):-
CREATE PROCEDURE UpdateClient
AS
Begin
if exists(select a.MembershipNo from tblDataDest2 a, tblDataDest b
where a.MembershipNo = b.MembershipNo)
Begin
Update tblDataDest2
set NINumber = a.NINumber, Firstname = a.Firstname, Surname = a.Surname,
Title = a.Title,
Initials = a.Initials, Address1 = a.Address1, Address2 = a.Address2,
Address3 = a.Address3,
Town = a.Town, County = a.County, Postcode = a.Postcode, Location =
a.Location,
CurrentSalary = a.CurrentSalary, NRA = a.NRA, DOB = a.DOB,
StateRetirementAge = a.StateRetirementAge,
Sex = a.Sex, ServiceStartDate = a.ServiceStartDate, FullTimeIndicator =
a.FullTimeIndicator,
PlanType = a.PlanType, OldPlanBasisAtNRA5 = a.OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4 = a.OldPlanBasisAtNRA4, OldPlanBasisAtNRA3 =
a.OldPlanBasisAtNRA3,
OldPlanBasisAtNRA2 = a.OldPlanBasisAtNRA2, OldPlanBasisAtNRA1 =
a.OldPlanBasisAtNRA1,
OldPlanBasisAtNRA = a.OldPlanBasisAtNRA, OldDBBenefitAtNRA5 =
a.OldDBBenefitAtNRA5,
OldDBBenefitAtNRA4 = a.OldDBBenefitAtNRA4, OldDBBenefitAtNRA3 =
a.OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2 = a.OldDBBenefitAtNRA2, OldDBBenefitAtNRA1 =
a.OldDBBenefitAtNRA1,
OldDBBenefitAtNRA = a.OldDBBenefitAtNRA, CAREAtNRA5 = a.CAREAtNRA5,
CAREAtNRA4 = a.CAREAtNRA4, CAREAtNRA3 = a.CAREAtNRA3, CAREAtNRA2 =
a.CAREAtNRA2,
CAREAtNRA1 = a.CAREAtNRA1, CAREAtNRA = a.CAREAtNRA, DC@.11AtNRA5 =
a.DC@.11AtNRA5,
DC@.11AtNRA4 = a.DC@.11AtNRA4, DC@.11AtNRA3 = a.DC@.11AtNRA3,
DC@.11AtNRA2 = a.DC@.11AtNRA2, DC@.11AtNRA1 = a.DC@.11AtNRA1,
DC@.11AtNRA = a.DC@.11AtNRA, S2P = a.S2P, S2POld = a.S2POld, BatchID =
a.BatchID
From tblDataDest a
where tblDataDest2.MembershipNo = a.MembershipNo
End
Else
Begin
Insert into tblDataDest2 (MembershipNo, NINumber, Firstname, Surname,
Title,
Initials, Address1, Address2, Address3, Town, County, Postcode, Location,
CurrentSalary, NRA,
DOB, StateRetirementAge, Sex, ServiceStartDate, FullTimeIndicator,
PlanType, OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4, OldPlanBasisAtNRA3, OldPlanBasisAtNRA2,
OldPlanBasisAtNRA1,
OldPlanBasisAtNRA, OldDBBenefitAtNRA5, OldDBBenefitAtNRA4,
OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2, OldDBBenefitAtNRA1, OldDBBenefitAtNRA, CAREAtNRA5,
CAREAtNRA4,
CAREAtNRA3, CAREAtNRA2, CAREAtNRA1, CAREAtNRA, DC@.11AtNRA5, DC@.11AtNRA4,
DC@.11AtNRA3, DC@.11AtNRA2, DC@.11AtNRA1, DC@.11AtNRA, S2P, S2POld, BatchID)
Select MembershipNo, NINumber, Firstname, Surname, Title, Initials,
Address1, Address2,
Address3, Town, County, Postcode, Location, CurrentSalary, NRA, DOB,
StateRetirementAge, Sex,
ServiceStartDate, FullTimeIndicator, PlanType, OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4,
OldPlanBasisAtNRA3, OldPlanBasisAtNRA2, OldPlanBasisAtNRA1,
OldPlanBasisAtNRA,
OldDBBenefitAtNRA5, OldDBBenefitAtNRA4, OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2,
OldDBBenefitAtNRA1, OldDBBenefitAtNRA, CAREAtNRA5, CAREAtNRA4,
CAREAtNRA3,
CAREAtNRA2, CAREAtNRA1, CAREAtNRA, DC@.11AtNRA5, DC@.11AtNRA4,
DC@.11AtNRA3,
DC@.11AtNRA2, DC@.11AtNRA1, DC@.11AtNRA, S2P, S2POld, BatchID from
tblDataDest
End
End
GO
Can anyone suggest how I can tweak this in order to have it run through
each record in the source and check it against the destination?
Many thanks
DeniseWhat about updating first all rows and then add the non existing ?
UPDATE tblDataDest2
SET (YourUpdatesetlist)
FROM tblDataDest2 T1
INNER JOIN tblDataDest T2
ON T1.MembershipNo = T2.MembershipNo
and then
INSERt INTO tblDataDest2
(Columnlisthere)
SELECT (Columnlisthere)
FROM tblDataDest T1
WHERE NOT EXISTS
(
SELECT * FROM tblDataDest2 T2
WHERE T1.MembershipNo = T2.MembershipNo
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks Jens. That worked perfectly. And so simple!
On 3 May 2006 03:15:25 -0700, Jens wrote:
> What about updating first all rows and then add the non existing ?
> UPDATE tblDataDest2
> SET (YourUpdatesetlist)
> FROM tblDataDest2 T1
> INNER JOIN tblDataDest T2
> ON T1.MembershipNo = T2.MembershipNo
> and then
> INSERt INTO tblDataDest2
> (Columnlisthere)
> SELECT (Columnlisthere)
> FROM tblDataDest T1
> WHERE NOT EXISTS
> (
> SELECT * FROM tblDataDest2 T2
> WHERE T1.MembershipNo = T2.MembershipNo
> )
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Jena
I sure would like to see the DDL of the table and some sample data to go
along with that. I have a similar business requirements and would like to se
e
if I could duplicate your results. This would give me a great start.
Thanks In Advance
kw_uh97
"Denise" wrote:
> Thanks Jens. That worked perfectly. And so simple!
>
> On 3 May 2006 03:15:25 -0700, Jens wrote:
>
>|||You can contact me along the EMailadress which can be found on my site:
http://www.sqlserver2005.de
I'm using SQL Server 2000, Windows 2000.
I'm writing a SP to check for the existence of records in a table, and to
insert or update based on this existence. In other words, I have 2 tables
- source and destination. If a record from the source table already exists
in the destination table I want to update it, if not, I want to insert the
record. At them moment I am using 'If Exists', but I don't know how to
loop through all the records in the source table and check for each one.
Because the SP finds at least one record in both tables, Exists is true and
the SP never reaches the 'Else'.
Here's what I have so far (apologies for the formatting - it's gone a bit
mad):-
CREATE PROCEDURE UpdateClient
AS
Begin
if exists(select a.MembershipNo from tblDataDest2 a, tblDataDest b
where a.MembershipNo = b.MembershipNo)
Begin
Update tblDataDest2
set NINumber = a.NINumber, Firstname = a.Firstname, Surname = a.Surname,
Title = a.Title,
Initials = a.Initials, Address1 = a.Address1, Address2 = a.Address2,
Address3 = a.Address3,
Town = a.Town, County = a.County, Postcode = a.Postcode, Location =
a.Location,
CurrentSalary = a.CurrentSalary, NRA = a.NRA, DOB = a.DOB,
StateRetirementAge = a.StateRetirementAge,
Sex = a.Sex, ServiceStartDate = a.ServiceStartDate, FullTimeIndicator =
a.FullTimeIndicator,
PlanType = a.PlanType, OldPlanBasisAtNRA5 = a.OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4 = a.OldPlanBasisAtNRA4, OldPlanBasisAtNRA3 =
a.OldPlanBasisAtNRA3,
OldPlanBasisAtNRA2 = a.OldPlanBasisAtNRA2, OldPlanBasisAtNRA1 =
a.OldPlanBasisAtNRA1,
OldPlanBasisAtNRA = a.OldPlanBasisAtNRA, OldDBBenefitAtNRA5 =
a.OldDBBenefitAtNRA5,
OldDBBenefitAtNRA4 = a.OldDBBenefitAtNRA4, OldDBBenefitAtNRA3 =
a.OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2 = a.OldDBBenefitAtNRA2, OldDBBenefitAtNRA1 =
a.OldDBBenefitAtNRA1,
OldDBBenefitAtNRA = a.OldDBBenefitAtNRA, CAREAtNRA5 = a.CAREAtNRA5,
CAREAtNRA4 = a.CAREAtNRA4, CAREAtNRA3 = a.CAREAtNRA3, CAREAtNRA2 =
a.CAREAtNRA2,
CAREAtNRA1 = a.CAREAtNRA1, CAREAtNRA = a.CAREAtNRA, DC@.11AtNRA5 =
a.DC@.11AtNRA5,
DC@.11AtNRA4 = a.DC@.11AtNRA4, DC@.11AtNRA3 = a.DC@.11AtNRA3,
DC@.11AtNRA2 = a.DC@.11AtNRA2, DC@.11AtNRA1 = a.DC@.11AtNRA1,
DC@.11AtNRA = a.DC@.11AtNRA, S2P = a.S2P, S2POld = a.S2POld, BatchID =
a.BatchID
From tblDataDest a
where tblDataDest2.MembershipNo = a.MembershipNo
End
Else
Begin
Insert into tblDataDest2 (MembershipNo, NINumber, Firstname, Surname,
Title,
Initials, Address1, Address2, Address3, Town, County, Postcode, Location,
CurrentSalary, NRA,
DOB, StateRetirementAge, Sex, ServiceStartDate, FullTimeIndicator,
PlanType, OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4, OldPlanBasisAtNRA3, OldPlanBasisAtNRA2,
OldPlanBasisAtNRA1,
OldPlanBasisAtNRA, OldDBBenefitAtNRA5, OldDBBenefitAtNRA4,
OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2, OldDBBenefitAtNRA1, OldDBBenefitAtNRA, CAREAtNRA5,
CAREAtNRA4,
CAREAtNRA3, CAREAtNRA2, CAREAtNRA1, CAREAtNRA, DC@.11AtNRA5, DC@.11AtNRA4,
DC@.11AtNRA3, DC@.11AtNRA2, DC@.11AtNRA1, DC@.11AtNRA, S2P, S2POld, BatchID)
Select MembershipNo, NINumber, Firstname, Surname, Title, Initials,
Address1, Address2,
Address3, Town, County, Postcode, Location, CurrentSalary, NRA, DOB,
StateRetirementAge, Sex,
ServiceStartDate, FullTimeIndicator, PlanType, OldPlanBasisAtNRA5,
OldPlanBasisAtNRA4,
OldPlanBasisAtNRA3, OldPlanBasisAtNRA2, OldPlanBasisAtNRA1,
OldPlanBasisAtNRA,
OldDBBenefitAtNRA5, OldDBBenefitAtNRA4, OldDBBenefitAtNRA3,
OldDBBenefitAtNRA2,
OldDBBenefitAtNRA1, OldDBBenefitAtNRA, CAREAtNRA5, CAREAtNRA4,
CAREAtNRA3,
CAREAtNRA2, CAREAtNRA1, CAREAtNRA, DC@.11AtNRA5, DC@.11AtNRA4,
DC@.11AtNRA3,
DC@.11AtNRA2, DC@.11AtNRA1, DC@.11AtNRA, S2P, S2POld, BatchID from
tblDataDest
End
End
GO
Can anyone suggest how I can tweak this in order to have it run through
each record in the source and check it against the destination?
Many thanks
DeniseWhat about updating first all rows and then add the non existing ?
UPDATE tblDataDest2
SET (YourUpdatesetlist)
FROM tblDataDest2 T1
INNER JOIN tblDataDest T2
ON T1.MembershipNo = T2.MembershipNo
and then
INSERt INTO tblDataDest2
(Columnlisthere)
SELECT (Columnlisthere)
FROM tblDataDest T1
WHERE NOT EXISTS
(
SELECT * FROM tblDataDest2 T2
WHERE T1.MembershipNo = T2.MembershipNo
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||Thanks Jens. That worked perfectly. And so simple!
On 3 May 2006 03:15:25 -0700, Jens wrote:
> What about updating first all rows and then add the non existing ?
> UPDATE tblDataDest2
> SET (YourUpdatesetlist)
> FROM tblDataDest2 T1
> INNER JOIN tblDataDest T2
> ON T1.MembershipNo = T2.MembershipNo
> and then
> INSERt INTO tblDataDest2
> (Columnlisthere)
> SELECT (Columnlisthere)
> FROM tblDataDest T1
> WHERE NOT EXISTS
> (
> SELECT * FROM tblDataDest2 T2
> WHERE T1.MembershipNo = T2.MembershipNo
> )
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Jena
I sure would like to see the DDL of the table and some sample data to go
along with that. I have a similar business requirements and would like to se
e
if I could duplicate your results. This would give me a great start.
Thanks In Advance
kw_uh97
"Denise" wrote:
> Thanks Jens. That worked perfectly. And so simple!
>
> On 3 May 2006 03:15:25 -0700, Jens wrote:
>
>|||You can contact me along the EMailadress which can be found on my site:
http://www.sqlserver2005.de
Subscribe to:
Posts (Atom)