Select statement insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select statement insert

Author

Topic

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-05-27 : 13:50:08

I have a select statemnet that I want to insert new rows if the data is not found from table 1 into table 2. How can I add onto this statement? What is the sql code neede?

Select *
From tbl_Data_OpenOrders
WHERE EXISTS (Select *
From tbl_TestData
WHERE tbl_Data_OpenOrders.oabl = tbl_TestData.oabl AND
tbl_Data_OpenOrders.ODLOTSEQ = tbl_TestData.ODLOTSEQ AND
tbl_Data_OpenOrders.OACUSTPO = tbl_TestData.OACUSTPO AND
tbl_Data_OpenOrders.OABLDATE = tbl_TestData.OABLDATE)

Lisa Jefferson

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-27 : 13:50:59

INSERT INTO Table1 (...)
YourQueryGoesHere

You probably want WHERE NOT EXISTS though.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-05-27 : 14:46:48

An error on line 6 that "from" is not correct???????????????????????????

Insert Into tbl_TestData ( OABL, ODLOTSEQ, OACUSTPO, OABLDATE, OAREGN, OASHIPDTMM, OASHIPDTDD, OASHIPDTYY, OASHIPVIA,
OAFOB, OAATTN, COBILLNUM, COCNTRY, OASFNAME, OASFAD1, OASFAD2, OASFCITY, OASFST, OASFZIP,
OASFZIP2, OASTNAME, OASTAD1, OASTAD2, OASTCITY, OASTST, OASTZIP, OADESC1, OAOPID, ODPNUM,
PUPNAME1, PUPNAME2, ODQORD, ODMEAS, ODUM, ODCSC, OCCPREM1, OCCPREM2, OCCPREM3, OCCPREM4,
OCCPREM5, OCCPREM6, OCCPREM7, OCCPREM8, OCCPREM9, OCCPREM10, OCCPREM11, OCCPREM12 )
From tbl_Data_OpenOrders
WHERE NOT EXISTS (Select *
From tbl_TestData
WHERE tbl_Data_OpenOrders.oabl = tbl_TestData.oabl AND
tbl_Data_OpenOrders.ODLOTSEQ = tbl_TestData.ODLOTSEQ AND
tbl_Data_OpenOrders.OACUSTPO = tbl_TestData.OACUSTPO AND
tbl_Data_OpenOrders.OABLDATE = tbl_TestData.OABLDATE AND
tbl_Data_OpenOrders.OAREGN = tbl_TestData.OAREGN AND
tbl_Data_OpenOrders.OASHIPDTMM = tbl_TestData.OASHIPDTMM AND
tbl_Data_OpenOrders.OASHIPDTDD = tbl_TestData.OASHIPDTDD AND
tbl_Data_OpenOrders.OASHIPDTYY = tbl_TestData.OASHIPDTYY AND
tbl_Data_OpenOrders.OASHIPVIA = tbl_TestData.OASHIPVIA AND
tbl_Data_OpenOrders.OAFOB = tbl_TestData.OAFOB AND
tbl_Data_OpenOrders.OAATTN = tbl_TestData.OAATTN AND
tbl_Data_OpenOrders.COBILLNUM = tbl_TestData.COBILLNUM AND
tbl_Data_OpenOrders.COCNTRY = tbl_TestData.COCNTRY AND
tbl_Data_OpenOrders.OASFNAME = tbl_TestData.OASFNAME AND
tbl_Data_OpenOrders.OASFAD1 = tbl_TestData.OASFAD1 AND
tbl_Data_OpenOrders.OASFAD2 = tbl_TestData.OASFAD2 AND
tbl_Data_OpenOrders.OASFCITY = tbl_TestData.OASFCITY AND
tbl_Data_OpenOrders.OASFST = tbl_TestData.OASFST AND
tbl_Data_OpenOrders.OASFZIP = tbl_TestData.OASFZIP AND
tbl_Data_OpenOrders.OASFZIP2 = tbl_TestData.OASFZIP2 AND
tbl_Data_OpenOrders.OASTNAME = tbl_TestData.OASTNAME AND
tbl_Data_OpenOrders.OASTAD1 = tbl_TestData.OASTAD1 AND
tbl_Data_OpenOrders.OASTAD2 = tbl_TestData.OASTAD2 AND
tbl_Data_OpenOrders.OASTCITY = tbl_TestData.OASTCITY AND
tbl_Data_OpenOrders.OASTST = tbl_TestData.OASTST AND
tbl_Data_OpenOrders.OASTZIP = tbl_TestData.OASTZIP AND
tbl_Data_OpenOrders.OADESC1 = tbl_TestData.OADESC1 AND
tbl_Data_OpenOrders.OAOPID = tbl_TestData.OAOPID AND
tbl_Data_OpenOrders.ODPNUM = tbl_TestData.ODPNUM AND
tbl_Data_OpenOrders.PUPNAME1 = tbl_TestData.PUPNAME1 AND
tbl_Data_OpenOrders.PUPNAME2 = tbl_TestData.PUPNAME2 AND
tbl_Data_OpenOrders.ODQORD = tbl_TestData.ODQORD AND
tbl_Data_OpenOrders.ODMEAS = tbl_TestData.ODMEAS AND
tbl_Data_OpenOrders.ODUM = tbl_TestData.ODUM AND
tbl_Data_OpenOrders.ODCSC = tbl_TestData.ODCSC AND
tbl_Data_OpenOrders.OCCPREM1 = tbl_TestData.OCCPREM1 AND
tbl_Data_OpenOrders.OCCPREM2 = tbl_TestData.OCCPREM2 AND
tbl_Data_OpenOrders.OCCPREM3 = tbl_TestData.OCCPREM3 AND
tbl_Data_OpenOrders.OCCPREM4 = tbl_TestData.OCCPREM4 AND
tbl_Data_OpenOrders.OCCPREM5 = tbl_TestData.OCCPREM5 AND
tbl_Data_OpenOrders.OCCPREM6 = tbl_TestData.OCCPREM6 AND
tbl_Data_OpenOrders.OCCPREM7 = tbl_TestData.OCCPREM7 AND
tbl_Data_OpenOrders.OCCPREM8 = tbl_TestData.OCCPREM8 AND
tbl_Data_OpenOrders.OCCPREM9 = tbl_TestData.OCCPREM9 AND
tbl_Data_OpenOrders.OCCPREM10 = tbl_TestData.OCCPREM10 AND
tbl_Data_OpenOrders.OCCPREM11 = tbl_TestData.OCCPREM11 AND
tbl_Data_OpenOrders.OCCPREM12 = tbl_TestData.OCCPREM12 )

Lisa Jefferson

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-27 : 14:57:15

You have not provided a valid query for "YourQueryGoesHere". Here are the first four lines of it:

From tbl_Data_OpenOrders
WHERE NOT EXISTS (Select *
From tbl_TestData
WHERE tbl_Data_OpenOrders.oabl = tbl_TestData.oabl AND
...

As you can see, the above is not valid T-SQL. Where is the SELECT part?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-05-27 : 15:12:06

Ok, that works. How do I add an update statement?

Lisa Jefferson

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-27 : 15:13:53

It depends what you want to do.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

l-jeff@excite.com
Starting Member

39 Posts

Posted - 2008-05-27 : 15:31:21

If the data is not there insert, if it is there update. How do I add the update statement?

Lisa Jefferson

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-27 : 15:34:13

Which columns do you want to update? What column(s) is(are) the primary key?

Here is an example update using more than one table:

UPDATE t1
SET Column2 = t2.Column2, Column3 = t2.Column3
FROM Table1 t1
INNER JOIN Table2 t2
ON t1.Column1 = t2.Column1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources