Easy Transact SQL Question

I'm a novice with SQL Querying, and can't figure out an update command.
My SELECT statement is as follows:
SELECT * FROM table1, table2
WHERE table1.keyfield = table2.keyfield and table2.field is not null
How do I convert this to an update statement on a field in table1 while
maintaining the restriction based on the field in table2?
Thanks for the help!Cindy Mikeworth wrote:
> I'm a novice with SQL Querying, and can't figure out an update command.
> My SELECT statement is as follows:
> SELECT * FROM table1, table2
> WHERE table1.keyfield = table2.keyfield and table2.field is not null
> How do I convert this to an update statement on a field in table1 while
> maintaining the restriction based on the field in table2?
> Thanks for the help!
For example:
UPDATE table1
SET col1 = 1234
WHERE EXISTS
(SELECT *
FROM table2
WHERE table2.keycol = table1.keycol
AND table2.col IS NOT NULL) ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||First learn that rows are NOT anythign like records, nor are columns
anything like a field. it is VITAL to have the right mindset in SQL
SELECT *
FROM Table1, Table2
WHERE table1.keyfield = table2.keyfield
AND table2.field IS NOT NULL;
You don't do it at all!! One of the MANY differences between a field
and column is that a column can have constraints on it. An SQL
programmer woudl have done this in the DDL (do you know what DDL is? If
not, you are sooooo screwed).
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it. Could you program from what you posted? HOW?!
If you have a key, in ANY table is BY DEFINITION NOT NULL, so your|||Cindy Mikeworth (CindyMikeworth@.newsgroups.nospam) writes:
> I'm a novice with SQL Querying, and can't figure out an update command.
> My SELECT statement is as follows:
> SELECT * FROM table1, table2
> WHERE table1.keyfield = table2.keyfield and table2.field is not null
> How do I convert this to an update statement on a field in table1 while
> maintaining the restriction based on the field in table2?
UPDATE table1
SET field = ...
FROM table1, table2
WHERE table1.keyfield = table2.keyfield
and table2.field is not null
This uses a non-standard extension of the UPDATE statement that is
proprietary to SQL Server and Sybase. As long as one is careful that
the join produces a unique value for the row to update, this is a very
practical method, not the least because it's so easy to transform a
SELECT into an UPDATE.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||UPDATE t1
SET t1.column1 = t2.value
FROM table1 t1
INNER JOIN table2 t2
ON t1.keyfield = t2.keyfield
WHERE t2.field IS NOT NULL

Dynamically create text file as destination from sql script in SSIS

I have a select Script as follows:

SELECT c.ABC AS 'ABC'

, a.Qty AS 'Quantity_Recived'

, b.PC AS 'PC'

, b.PC AS 'PC'

, 'I' AS 'Flag'

FROM TNRInventory.dbo.tInventoryAlloc AS a

LEFT OUTER JOIN vwInventoryAllocMapping AS vwMap ON a.TNRAllocTypeID = vwMap.TNRInventoryAllocID

LEFT OUTER JOIN ABC.dbo.ZREFRESHTAB AS b ON a.DispenserID = b.Asset

LEFT OUTER JOIN ABC.dbo.TableJoinKey AS c ON a.TitleID = c.TITLE_ID

WHERE (vwMap.DataSourceID = 3) and vwMap.[DataSourceAllocName] = 'I'

group by c.SKU_NO , vwMap.[DataSourceAllocName],a.Qty , b.Profit_Center

order by c.SKU_NO,vwMap.[DataSourceAllocName]

GO

i have to send the result of aforesaid script in batch of 300 records per file (tab delimited text file)
now the file name must be dynamically created as each file will contain 300 records.

I have found some document related to same issue on this url

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1238184&SiteID=17

but still there is a catch.

Can any one guide/suggest me better way to do the aforesaid.

Thanks

Thinking while typing, this could be done with a few steps.

1 - Data Flow - Load a staging table with the results of the SQL. Add to it a row number counter so that every row is numbered uniquely.

2 - Control Flow - Run an Execute SQL Task to select max(rownumber) from that staging table

3 - Control Flow - Use a script task to take the output of the Execute SQL task and populate another variable with the number of iterations needed to populate files with up to 300 rows. (max(rownumber) / 300 - if no remainder, use that value, if remainder add one to the integer, etc...)

4 - Control Flow - Use a for loop to iterate the output variable from step 3 above.

5 - Control Flow - Build a variable set to an expression to use a base filename and the variable from step 3 above.

6 - Control Flow For Loop - Loop through the variable from step 3 above and add a data flow. Inside this data flow, use an OLE DB (or whatever) to connect to the staging table from step 1, filtering by (variable_step3 * 300) you can select just the records for this iteration. Hook that up to a flat file destination, which uses expressions to set the file name equal to the variable from step 5 above.

Something like that.|||

You might also check Jamie's blog post on this topic.

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

Essentially the same technique that Phil recommended, but he's got some sample code already

|||

jwelch wrote:

You might also check Jamie's blog post on this topic.

http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx

Essentially the same technique that Phil recommended, but he's got some sample code already

D'oh! Should've just looked there first!|||

What i did is i create a view for aforesaid script and then used that script in the following VB.net script

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Data.SqlClient

Imports System.Text

Imports System.IO

Public Class ScriptMain

Public Sub Main()

Dim ConnectionString As String = "Server=ServerName;Database=DatabaseName;uid=Login;pwd=Password;"

Dim querystring As String = "SELECT * FROM vwInventory_I"

Dim con As New SqlConnection(ConnectionString)

Dim adapter As New SqlDataAdapter()

Dim ds As New DataSet

Dim dt As New DataTable

adapter.SelectCommand = New SqlCommand(querystring, con)

adapter.Fill(ds)

dt = ds.Tables(0)

CreateFile(dt)

End Sub

Private Sub CreateFile( ByVal table As DataTable)

Dim intRowCount As Integer = table.Rows.Count

Dim Column1Value As String

Dim Column2Value As String

Dim Column3Value As String

Dim Column4Value As String

Dim Column5Value As String

Dim sb As New StringBuilder

Dim seperator As String = vbTab

Dim nFileNameCount As Integer

nFileNameCount = 0

'For i As Integer = 0 To table.Columns.Count - 1

Dim i As Integer = 0

Dim j As Integer = 0

For j = 0 To intRowCount - 1

If nFileNameCount = 100 Then

PrintFile(sb, j)

'clears the stringbuilder

sb.Remove(0, sb.ToString.Length - 1)

nFileNameCount = 0

End If

Column1Value = table.Rows(j)(i).ToString

Column2Value = table.Rows(j)(i + 1).ToString

Column3Value = table.Rows(j)(i + 2).ToString

Column4Value = table.Rows(j)(i + 3).ToString

Column5Value = table.Rows(j)(i + 4).ToString

sb.Append(Column1Value & seperator & Column2Value & seperator & Column3Value & seperator & _

Column4Value & seperator & Column5Value & seperator & vbCrLf)

nFileNameCount = nFileNameCount + 1

Next

PrintFile(sb, j)

'Next

End Sub

Private Sub PrintFile( ByVal sb As StringBuilder, ByVal recordcount As Integer)

' Create an instance of StreamWriter to write text to a file.

Dim strDate As String

strDate = String.Format("{0:yyyy}" , DateTime.Now)

strDate = strDate & String.Format( "{0:MM}", DateTime.Now)

strDate = strDate & String.Format( "{0d}", DateTime.Now)

Using sw As StreamWriter = New StreamWriter("C:\SSIS\I_" & strDate & "_" & recordcount & ".txt")

sw.WriteLine(sb.ToString)

sw.Close()

End Using

'this will create a text file in bin directory

End Sub

End Class

This is how you generate tab delimited files to test check Use Script Tas from SSIS

|||Uh, okay. Good. So why even use SSIS then? Write your own program as you have done, compile it, and execute the resulting binary file. Leave the bloat of SSIS out of it.|||

I am going to put this piece in middle of my design.

It was the one of the key to finish my jik-so-puzzle.

Other one is to delete the files from Unix Aix Server with FTP connection.

And here too I have to go all the way round, as there FTP connection works fine on Microsoft server but not on UNIX server.

It does not allow you to delete the files on UNIX server.

That is the reason I am using the code in SSIS.