Using A SQL JOIN In A SQL UPDATE Statement (Thanks John Eric!)

Posted September 4, 2007 at 10:31 AM

Tags: SQL

I love learning new, cool stuff about SQL. It doesn't happen all that often (most of my SQL is fairly simple), but every now and then someone shows me something that just rocks my world, whether it be the power of Indexing or just something as simple as using UNION ALL instead of UNION. Last week, John Eric dropped a bomb shell on me, demonstrating how to update a table in conjunction with a SQL JOIN statement.

I have known for a long time that you could update a SQL View in Microsoft SQL Server (back when I used to use Views), so it makes sense that you could update a JOIN, but it never occurred to me to try this. Not only did it not occur to me, but the syntax used to do this is very strange to me (although now that I have stared at it for a long time, it's starting to make more sense).

Anyway, enough talk, let's take a look at this in action. Since I don't have any tables ready to play with, I have created three in-memory SQL tables: boy, girl, and relationship. The boy table lists boys, the girl table lists girls, and the relationship table lists out romantic relationships between the two (what can I say, I am a romantic fool at heart). Then, what I am going to do is UPDATE the boy table based on certain relationship criteria - in this case, anyone who has dated Winona Ryder is clearly a stud and should be flagged as such.

 Launch code in new window » Download code as text file »

Notice how the SQL UPDATE statement is JOINing the @boy, @girl, and @relationship table using INNER JOINs and limiting it to boys who have dated Winona Ryder. The update is made to the result of that JOIN and then we are selecting all the rows from that updated @boy table (to see that it works). Running the above code, we get the following CFDump output:

Notice that the Ben record was updated to reflect the Studly property. This is pretty cool stuff. Frankly, I haven't even ever used a FROM clause in my UPDATE statement (unless part of a sub-query). This is going to give me some cool stuff to explore.

Thanks John Eric!

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

Reader Comments

Ben,

As general rule you can write all your UPDATE, DELETE query the same way you can write SELECT Query. They have same structure of syntax. You could also DELETE using JOIN as well. Try following query you will love it.

DELETE tableName
FROM tableName tn
INNER JOIN JoinedTable jn ON jn.col = tn.col
WHERE jn = someval

I see where you are going. Similar thing happened to me.

I was biggest fan of ColdFusion and can not stop thinking about it. One day, I started to learn SQL and here it goes. I could not come back to ColdFusion from SQL.

I guess, Once you go SQL...

Pinal

@Pinal Dave,

Cool stuff. I didn't know you could use this in DELETE as well. Awesome stuff. One of the things I have always wanted to do is create a table alias in the UPDATE statement, but it has always failed due to Syntax error. That was before I knew you could do a FROM clause in the UPDATE statement. This should make things much easier, especially when dealing with an UPDATE that uses a sub-query.

Good stuff!

Pinal --> For me it's been using them both together. I've seen some people write a lot of CF code to do something they could do in just a few SQL commands passed off via CFQuery.

@Pinal,

Works like a charm!

http://www.bennadel.com/index.cfm?dax=blog:939.view

The syntax is a bit strange to me, but I just need to get used to it. Thanks for the hot tip.

I am with Allen on this one. I don't think one is necessarily better than the other - they do different things. They work together to create harmony. The trick is to know when to leverage the powers of each tool.

Ben,

I am sure my blog readers will find it useful.
http://blog.sqlauthority.com/2007/09/05/sqlauthority-news-interesting-read-using-a-sql-join-in-a-sql-updatedelete-statement-ben-nadel/

Regards,
Pinal

How odd that I would stumble across this. Recently I started using UPDATE...SET...FROM to persist an array of objects in one SQL statement.

Essentially, I pass an array of components to a persistence object, and construct an in-memory table within the UPDATE statement, looping through the array of objects to be persisted and adding a UNION ALL after every loop iteration. Except the last one, of course.

Works well, and is much, much faster than using one SQL statement per loop iteration. Although, I'll admit upfront that my measurement process consists of running the app and saying "Well, that seemed a lot faster". :)

@Matt,

If you think about applications from a "user experience" standpoint, it's the "that runs faster" moment much more important than any numeric reading? As long as you perceive it to be faster, that's all that counts.

Ben and Pinal

Hi guys!

I was wondering if this technique could be modified to set is_stud to the value of one of the other tables in the join (say, g.id) instead of a static 1.

This would be very useful for me in a situation I've been finding myself in frequently.

I have a table of countries (country_id, country_name etc.) and a new table (people) with a country_name field that matches up with countries.country_name

I would like to set people.country_id (which for all records is currently set to zero since the data is new) equal to countries.country_id where the country names in each table match.

Is this achievable with just SQL and not CF as I have been doing?

I'm just not sure of values can be returned from the join conditions being met.

Thanks and keep up the good help :)

Steve

@Steve,

Yes, you can update one table using the value in another table in the manor. Create the JOIN that you think is appropriate and then set one column to the other.

Thanks for your help

I like any piece of code that contains the line INSERT INTO Girl ;-)

http://www.rajib-bahar.com/rajib/BlogEngine.Web/post/2009/03/26/useful-blog-entries-on-Joins-using-join-in-update-or-delete-statement.aspx

I put a linkback to this entry because some of my colleagues/students may find it useful. All the best.

Effin ay, mang! You should teach a class! As far as blogs go, this one didn't suck. And I learned something. kudos.

@James,

Thanks my man. Glad you don't think my blog sucks :)

Excellent help!!

I am a fan of creating an array via XML, parsing it into a table, then using the above set-based insert/update/delete method.

By using XML and the above method, you can drop the execution time of multiple inserts in one transaction drastically. For example, if you had to loop over an insert statement; try changing it to creating XML with the same loop, then having SQL parse the XML into a temporary table and doing your insert via a select statement on the temporary table.

It is also possible to create a "CRUD" like stored procedure by adding a variable in the nodes to flag whether it is an insert, update or delete.

If anyone is interested in the XML CRUD stored procedure, let me know and I will see if I can dig it out of one of my past projects.

@eXcalibur.lk,

I am sorry, I don't follow what you are saying; but, I am intrigued. What XML are you talking about? And how are you turning that XML into an INSERT / UPDATE statement?

I am on holidays in another state at the moment, so I can not get my previous work; however here is an approach that is a little bit less verbose than my example: http://pratchev.blogspot.com/2008/11/import-xml-file-to-sql-table.html

When I get back, I will post my example. I did a proof of concept at my last job by looping over a cfquery with a sql insert and compared that to looping over an xml document, then doing the insert via sql parsing and table to table inserts. The results even surprised me.

@Andrew,

Oh wow, that's pretty cool. I've never seen that before. Do you know what databases this is valid for?

@Ben,

The functionality is for MS SQL (I think form 2005 on), I have not been able to find anything similar for other DBMSs; mind you have not been looking to hard, as currently all my projects have been with MS SQL.

@Andrew,

Gotcha, makes sense - MS SQL has some pretty powerful stuff.

Hi Ben, thanks for posting this, hope you don't mind I've put a link from my (rarely used) blog to your page and thought you'd like to know that I just used this technique to update 132 million records in an hour!

@Matt,

132 million records!!! Dangy! Glad the technique is effective.

hi, does this syntax also suit for Insert Query?

@J.D.

Sure can, Ben shows how in the blog:

INSERT INTO @girl
(
id,
name
)(
SELECT 1, 'Maria Bello' UNION ALL
SELECT 2, 'Christina Cox' UNION ALL
SELECT 3, 'Winona Ryder'
);

If you need the values to come from a table, then substitute the static information for column names and add a from clause; for example:

INSERT INTO @girlInRelationship
(
id,
name
)(
SELECT girl.id, girl.name
FROM girl
INNER JOIN relationship
ON girl.id = relationship.girl_id
);

** I am assuming Ben's code is for MySQL, which I don't normally code in, as such the syntax for my example may be off.

thanks Andrew Bauer :D

may I ask do you know does SQL can do "split" like C# can do?

in my column save data like this

Table A

SHOPNO GOODS
101`102 BANANA

Table B

SHOPNO SHOPNAME
101 SUN
101 MOON

is it possible to split A.SHOPNO then compare with B.SHOPNO to get B.SHOPNAME?

I hope can get result like this

SHOPNO SHOPNAME GOODS
101'102 SUN`MOON BANANA

does SQL could do split like other code languae can do?

thanks alot

@JD,

I am glad that I could help with my previous reply.

With your question; it is possible to do with stored procedures and functions, but I would not suggest it. It is not a good idea to hold multiple key values in the same column.

Having multiple keys in a column leads to data integrity issues (like will banana exist in your database, if the links to shops 101 & 102 are removed?). I would suggest changing it into 3 tables:

Table A (Goods)
GoodsID, GoodsName
1, Banana
2, Apple

Table B (Shops)
ShopNo, ShopName
101, Sun
102, Moon

Table C (ShopGoods or Inventory) "Join Table"
ID, GoodsID, ShopNo
1, 1, 101
2, 1, 102
3, 2, 102

This is the basic setup for a many-to-many relationship between two entities (tables). By adding the join table you can then manage the relationships with simple Insert/Update/Delete statements. Also the result you are after is achieved with an inner join (however over multiple rows):

SELECT B.ShopNo, B.ShopName, A.GoodsName
FROM Goods AS A
INNER JOIN ShopGoods AS C
ON A.GoodsID = C.GoodsID
INNER JOIN Shops AS B
ON B.ShopNo = C.ShopNo

Results:
ShopNo, ShopName, GoodsName
101, Sun, Banana
102, Moon, Banana
102, Moon, Apple

@Ben,

Sorry for the long comment. I realize that I could of answered the question directly, and I don't want to hi-jack your blog comments with essay responses of my own; however I have seen too many people fall into the trap of getting a table's rows to describe more than one entity, and am hoping to help with an alternative approach.

hi, Andrew Bauer

again thanks for your reply !!

I been google for find the answer

people just siad like you just told

not suggest to save mutiple value in same column..

but the schema and exist data just save like this format.. ( I can't change schema... which old Data exist many data row already)

Table A

SHOPNO GOODS
101`102 BANANA

if I really need to find a way to retrieve the SHOPNAME..is the best way by other code language not SQL?

thank you very much!!

@JD,

I can understand the pain of not being able to change the schema and I know that a well structured schema saves you from a lot of headaches.

Personally, I would flat-out refuse to work with this type of database schema, opting to volunteer rewriting the structure and creating ETL (Extract Transfer Load) scripts to move the data to the new schema. In my opinion, a couple of days for writing a schema/ETL scripts and changing code that my already rely on the current structure, is much better to creating a hack that will cause you troubles down the line.

If you absolutely must continue with the current structure; then the quickest and easiest approach would be to query Table A in ColdFusion, loop over each record, then loop over each value in the ShopNo list of IDs (ColdFusion has some useful list functions), query Table B with the list values, then write the desired values to a custom query result set. This approach is very verbose and will make it difficult to maintain, not too mention it will be very slow as more records are added.

I hope for your sanity that you manage to convince whoever to let you change the schema.

hi thank you Andrew Bauer

I just tried to convince them to let me change the schema , but ... in vain , for some reason they just want this kind of schema to save data..

maybe I will try the way ( use other code languae ) to get the result I need

thanks for your patient and suggestion !!

thanks alot

I love the style and look of your website blog. What blogging application are you using? I see the design was inspired by Tim Ferriss. Could you please point me in the right direction.

PS. Thanks for the comments in SQL Joins it helped solve my problem.

Thank you in advance. Mike (Smile)

@Michael,

My blog app is home-built. The design was also hand crafted using Fireworks.

Great blog! Saved me an hour + of typing... I had updated a table in my development world and wanted to use some of the field data to update the live table counterpart.

sorry for asking a sql question (I am sorry for if it's ok not to ask in here)

I spend 1 week to think out how to finger out this soluction

SELECT SHOPNO,HWSNO,BDATE,HPYNO FROM ECRPYHS
RESULT:
SHOPNO HWSNO BDATE HPYNO
A 01 2004/06/15 01
A 01 2004/06/15 01
A 01 2004/06/15 03
A 01 2004/06/20 03
A 01 2004/06/20 03
A 01 2004/06/20 01

SELECT SHOPNO,HWSNO,BDATE,HSALE_ID FROM ECRHDHS
RESULT:
SHOPNO HWSNO BDATE HSALE_ID HASTOTAL
A 01 2004/06/15 ALLEN 10
A 01 2004/06/15 ALLEN 5
A 01 2004/06/15 NAOMI 10
A 01 2004/06/20 NAOMI 10
A 01 2004/06/20 NAOMI 5
A 01 2004/06/20 ALLEN 10

SELECT PYNO,PYID FROM PAYINFO

RESULT:
PYNO PYID
01 TWD
02 CNY
03 USD
04 JPY
05 HKD
06 KRW
07 GBP
08 AUD
09 CRD

WOULD LIKE TO HAVE THIS RESULT THEN NEED TO BINDING WITH GRIDVIEW

HSALE_ID TWD CNY USD JPY HKD KRW GBP AUD CRD
ALLEN 25 0 0 0 0 0 0 0 0
NAOMI 0 0 25 0 0 0 0 0 0

THE QUESTIONS I WOULD LIKE TO ASK IS
1 ASSUME I DON'T KNOW HOW MANY KINDS OF PAYINFO ( PAYINFO.PYNO) HOW TO SETTING BoundField FOR GRIDVIEW?

2 HOW TO JOIN PAYINFO THEN COUNT EACH KIND OF PAYMENT TYPE THEN JOIN TO MAIN TABLE ?

IT'S NOT PROBLEM FOR ME TO JOIN ECRPYHS WITH ECRHDHS
THEN I HAVE PROBLEM TO JOIN WITH PAYINFO ...

PLEASE HELP.. OR GIVE ME A EXAMPLE TO KNOW HOW TO DO
THANK YOU VERY MUCH!!

Hi J.D,

If you can join ECRPYHS and ECRHDHS, it should be easy to join PAYINFO as well. It looks like PAYINFO.PYNO is related to ECRPYHS.HPYNO

Once you have the joined table, you can use the crosstab stored procedure below to create the cross tabbed table.

You can then tell gridview to automatically generate columns with the tag AutoGenerateColumns="True". You can then configure the columns via sub routines if necessary.

I do this quite often and find it invaluable.

Common syntax for sp_crosstab is;
EXEC [dbo].[sp_Crosstab]
@DBFetch = 'table or view to be cross tabbed - can be a temp table',
@DBField = 'Field to become column headings - PYID in your case',
@PCField = 'Field to be totaled - HASTOTAL in your case',
@PCBuild = 'COUNT, SUM, MIN, MAX or AVG'

I'm not sure where I get this procedure from, so I'm not sure who to give credit to. It is certainly very useful though!

Cheers
Dave

P.S - Sorry for the long post.

--------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_Crosstab]
@DBFetch varchar(4000), --specifies the set of data to summarize
@DBWhere varchar(2000) = NULL, --is optional, specifies a WHERE clause to filter the data specified by the @DBFetch parameter
@DBPivot varchar(4000) = NULL, --is optional, specifies the column names to appear in the result set
@DBField varchar(100), --specifies the column containing the data that becomes result set column names
@PCField varchar(100), --specifies the column to be aggregated.
@PCBuild varchar( 20), --specifies the aggregation to be performed (COUNT, SUM, MIN, MAX or AVG)
@PCAdmin varchar( 20) = NULL, --is optional, specifies a result set column name to represent null values
@DBAdmin int = 0, --is optional, specifies whether any additional columns should be added to the result set for totals
@DBTable varchar(100) = NULL, --is optional, specifies a table name for the result set
@DBWrite varchar(160) = NULL, --is optional, specifies a database name for the @DBTable parameter
@DBUltra bit = 0 --is optional, specifies whether the result set table (if specified) should be dropped (if it already exists) before saving the result set. A value of zero (0) means the table is dropped and created again. A value of one (1) means the result set is to be appended to an already existing table
AS

SET NOCOUNT ON

DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int

SET @Status = 0

DECLARE @TPre varchar(10)

DECLARE @TDo3 tinyint
DECLARE @TDo4 tinyint

SET @TPre = 'tbl'

SET @TDo3 = LEN(@TPre)
SET @TDo4 = LEN(@TPre) + 1

DECLARE @DBAE varchar(40)

DECLARE @Task varchar(8000)

DECLARE @Bank varchar(4000)

DECLARE @Cash varchar(2000)

DECLARE @Rich varchar(2000)

DECLARE @DBAI varchar(4000)
DECLARE @DBAO varchar(8000)
DECLARE @DBAU varchar(2000)

DECLARE @Name varchar(100)
DECLARE @Same varchar(100)

DECLARE @Home varchar(160)

DECLARE @Some varchar(20)

DECLARE @Work int

DECLARE @Wink int

SET @DBAE = '##Crosstab' + RIGHT(CONVERT(varchar(10),@@SPID+100000),5)

SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE

--PRINT @Task

EXECUTE (@Task)

CREATE TABLE #DBAT (Work int IDENTITY(1,1), Name varchar(100))

SET @Bank = @TPre + @DBFetch

IF NOT EXISTS (SELECT * FROM sysobjects WHERE RTRIM(type) = 'U' AND name = @Bank)

BEGIN

SET @Bank = CASE WHEN LEFT(@DBFetch,6) = 'SELECT' THEN '(' + @DBFetch + ')' ELSE @DBFetch END
SET @Bank = REPLACE(@Bank, CHAR(94),CHAR(39))
SET @Bank = REPLACE(@Bank,CHAR(45)+CHAR(45),CHAR(32))
SET @Bank = REPLACE(@Bank,CHAR(47)+CHAR(42),CHAR(32))

END

IF @DBWhere IS NOT NULL

BEGIN

SET @Cash = REPLACE(@DBWhere,'WHERE' ,CHAR(32))
SET @Cash = REPLACE(@Cash, CHAR(94),CHAR(39))
SET @Cash = REPLACE(@Cash,CHAR(45)+CHAR(45),CHAR(32))
SET @Cash = REPLACE(@Cash,CHAR(47)+CHAR(42),CHAR(32))

END

SET @DBField = REPLACE(@DBField,CHAR(32),CHAR(95))

SET @PCField = REPLACE(@PCField,CHAR(32),CHAR(95))

SET @PCBuild = REPLACE(@PCBuild,CHAR(32),CHAR(95))

SET @PCAdmin = REPLACE(@PCAdmin,CHAR(32),CHAR(95))

SET @DBTable = REPLACE(@DBTable,CHAR(32),CHAR(95))

SET @DBWrite = REPLACE(@DBWrite,CHAR(32),CHAR(95))

SET @DBWhere = CASE WHEN @DBWhere IS NULL THEN '' ELSE ' WHERE (' + @Cash + ') AND 0 = 0' END

SET @Some = ISNULL(@PCAdmin,'NA')

SET @Task = 'SELECT * INTO ' + @DBAE + ' FROM ' + @Bank + ' AS T WHERE 0 = 1'

--PRINT @Task

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

IF @DBPivot IS NOT NULL

BEGIN

IF LEFT(@DBPivot,6) <> 'SELECT'

BEGIN

SET @Wink = 1

SET @Work = CHARINDEX('|',(@DBPivot)+'|')

WHILE @Work > 0

BEGIN

SET @Name = SUBSTRING(@DBPivot,@Wink,@Work-@Wink)

INSERT #DBAT (Name) VALUES (@Name)

SET @Wink = @Work + 1

SET @Work = CHARINDEX('|',(@DBPivot)+'|',@Wink)

END

END

ELSE

BEGIN

SET @Task = 'INSERT #DBAT (Name) ' + @DBPivot

SET @Task = REPLACE(@Task, CHAR(94),CHAR(39))
SET @Task = REPLACE(@Task,CHAR(45)+CHAR(45),CHAR(32))
SET @Task = REPLACE(@Task,CHAR(47)+CHAR(42),CHAR(32))

--PRINT @Task

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

END

ELSE

BEGIN

SET @Task = ' INSERT #DBAT (Name)'
+ ' SELECT CONVERT(varchar(100),' + @DBField + ')'
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY CONVERT(varchar(100),' + @DBField + ')'
+ ' ORDER BY 1'

--PRINT @Task

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

UPDATE #DBAT SET Name = @Some WHERE Name IS NULL

SET @DBAI = ''

SET @DBAO = ''

SET @Rich = ''

DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT C.name
FROM tempdb.dbo.sysobjects AS O
JOIN tempdb.dbo.syscolumns AS C
ON C.id = O.id
AND C.name != @DBField
AND C.name != @PCField
AND O.name = @DBAE
ORDER BY C.colid

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

OPEN Fields

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

FETCH NEXT FROM Fields INTO @Same

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

WHILE @@FETCH_STATUS = 0 AND @Status = 0

BEGIN

SET @DBAI = @DBAI + ', ' + @Same

FETCH NEXT FROM Fields INTO @Same

SET @Retain = @@ERROR IF @Status = 0 SET @Status = @Retain

END

CLOSE Fields DEALLOCATE Fields

SET @DBAI = SUBSTRING(@DBAI,3,4000)

DECLARE Fields CURSOR FAST_FORWARD FOR
SELECT Name
FROM #DBAT
ORDER BY Work

OPEN Fields

FETCH NEXT FROM Fields INTO @Same

WHILE @@FETCH_STATUS = 0 AND @Status = 0

BEGIN

IF LEN(@DBAO) < 7900 - LEN(@DBField) - LEN(@PCField) - LEN(@Same) - LEN(@Same)

BEGIN

SET @DBAO = @DBAO + ', ' + @PCBuild + '(CASE WHEN ISNULL(CONVERT(varchar(100),' + @DBField + '),'
+ CHAR(39) + @Some + CHAR(39) + ') = '
+ CHAR(39) + @Same + CHAR(39) + ' THEN '
+ @PCField + ' ELSE NULL END) AS '
+ CHAR(91) + @Same + CHAR(93)

END
ELSE
BEGIN

SET @Status = 50000

END

FETCH NEXT FROM Fields INTO @Same

END

CLOSE Fields DEALLOCATE Fields

IF @DBAdmin IN (1,3) SET @Rich = @Rich + ', ' + @PCBuild + '(' + @PCField + ') AS All_' + @PCBuild

IF @DBAdmin IN (2,3) SET @Rich = @Rich + ', COUNT(' + @PCField + ') AS All_COUNT'

IF @DBAdmin IN (2,3) SET @Rich = @Rich + ', MIN(' + @PCField + ') AS All_MIN'

IF @DBAdmin IN (2,3) SET @Rich = @Rich + ', MAX(' + @PCField + ') AS All_MAX'

SET ANSI_WARNINGS OFF

SET @Home = ''

SET @Name = ''

IF @DBTable IS NOT NULL

BEGIN

SET @Name = @DBTable

IF LEFT(@Name,2) = '##'

BEGIN

IF @DBUltra = 0

BEGIN

SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @Name + CHAR(39) + ') DROP TABLE ' + @Name

--PRINT @Task

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

END

ELSE

BEGIN

IF @DBWrite IS NOT NULL SET @Home = @DBWrite + '.dbo.'

IF @DBUltra = 0

BEGIN

SET @Task = 'IF EXISTS (SELECT * FROM ' + @Home + 'sysobjects WHERE name = ' + CHAR(39) + @Name + CHAR(39) + ') DROP TABLE ' + @Home + @Name

--PRINT @Task

IF @Status = 0 EXECUTE (@Task) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

END

END

IF @DBTable IS NOT NULL

BEGIN

IF @DBUltra = 0

BEGIN

IF @Status = 0 EXECUTE ( ' SELECT ' + @DBAI + @DBAO + @Rich
+ ' INTO ' + @Home + @Name
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY ' + @DBAI
+ ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

ELSE

BEGIN

IF @Status = 0 EXECUTE ( ' INSERT ' + @Home + @Name
+ ' SELECT ' + @DBAI + @DBAO + @Rich
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY ' + @DBAI
+ ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

END

ELSE

BEGIN

IF @Status = 0 EXECUTE ( ' SELECT ' + @DBAI + @DBAO + @Rich
+ ' FROM ' + @Bank + ' AS T ' + @DBWhere
+ ' GROUP BY ' + @DBAI
+ ' ORDER BY ' + @DBAI ) SET @Return = @@ERROR

IF @Status = 0 SET @Status = @Return

END

SET ANSI_WARNINGS ON

SET @Task = 'IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = ' + CHAR(39) + @DBAE + CHAR(39) + ') DROP TABLE ' + @DBAE

--PRINT @Task

EXECUTE (@Task)

DROP TABLE #DBAT

SET NOCOUNT OFF

RETURN (@Status)

--------------------------------

Post Comment  |  Ask Ben

Post A Comment

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.