Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>sql

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John B
Hi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||One of the ways:
http://support.microsoft.com/default...b;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>
|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the prima
ry
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/defaul...kb;en-us;139444
Regards.
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...[vbcol=seagreen]
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:

> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAM
E,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =
d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:

> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID
).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
>
>

Delete Duplicate

SQL 2K
I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
SUPVR, MOD_DATE
I want to retain just one row for unique (ID, DPTID) combination, doesn't
matter with values of rest of the columns.
Thanks In Advance
John BHi
Maybe if mod_date is unique for each id,dptid then
DELETE FROM DptAct
WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
John
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||What does the DDL (i.e. Create Table statement) look like? What is the primary
key on the table?
Thomas
"MS User" <sqlman@.sql.com> wrote in message
news:%23l$$uYhYFHA.3220@.TK2MSFTNGP14.phx.gbl...
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||One of the ways:
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444
Regards.
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||In my case , mod_date is not unique.
Let me be more clear, actually I copied table 'DptAct' from different
databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
Here are the steps I did
1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
2> Copied DptAct into 'DptAct_All' from all databases
3> Now I need to get data for all the unique (ID, DPTID)
Hope I am clear
Thanks for your time
John B
"John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> Hi
> Maybe if mod_date is unique for each id,dptid then
> DELETE FROM DptAct
> WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> John
> "MS User" wrote:
>> SQL 2K
>> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
>> NAME,
>> SUPVR, MOD_DATE
>> I want to retain just one row for unique (ID, DPTID) combination, doesn't
>> matter with values of rest of the columns.
>> Thanks In Advance
>> John B
>>
>>
>>|||John,
Hopefully you now understand WHY it's such a stupid idea to have an "ID" and
a "DEPTID" column. If DEPTID uniquley identifies the row, then MAKE IT THE
PRIMARY KEY!
Also, in SQL Server, we do not have tight naming limitations -- it is OK to
use vowels and words > 8 chars. People wh come around after words will
actually be able to understand things then!
--
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"MS User" wrote:
> SQL 2K
> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID, NAME,
> SUPVR, MOD_DATE
> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> matter with values of rest of the columns.
> Thanks In Advance
> John B
>
>
>|||Hi
If you have a primary key on the original table then you could insert only
the rows that do not exist from your other databases. If your databases are
on the same server you can use three part names or if you create a linked
server you can use four part naming to access the second table.
Using three part names for another database (Otherdb) on the same server:
INSERT INTO DptAct ( id, deptid, col1, col2 )
SELECT id, deptid, col1, col2 FROM Otherdb..DptAct O
WHERE NOT EXISTS ( SELECT * FROM DptAct D WHERE O.od = D.Id and O.DEPTID =d.DeptId )
col1 and col2 are example columns and should be replaced by the actual
column names.
John
"MS User" wrote:
> In my case , mod_date is not unique.
> Let me be more clear, actually I copied table 'DptAct' from different
> databases and trying to make a master 'DptAct' with Primary key (ID, DPTID).
> Here are the steps I did
> 1> Created a table DptAct_All with all columns and WITHOUT PrimaryKey
> 2> Copied DptAct into 'DptAct_All' from all databases
> 3> Now I need to get data for all the unique (ID, DPTID)
> Hope I am clear
> Thanks for your time
> John B
>
>
> "John Bell" <jbellnewsposts@.h0tmail.com> wrote in message
> news:053E1D63-B76C-4E16-A0C7-66EFC55E95C7@.microsoft.com...
> > Hi
> >
> > Maybe if mod_date is unique for each id,dptid then
> >
> > DELETE FROM DptAct
> > WHERE EXISTS ( SELECT * FROM DptAct d where d.id = DptAct.id and
> > DptAct.DPTID = d.DPTID and DptAct.Mod_Date > d.Mod_date)
> >
> > John
> >
> > "MS User" wrote:
> >
> >> SQL 2K
> >>
> >> I got a table 'DptAct' having 3 million rows with columns - ID, DPTID,
> >> NAME,
> >> SUPVR, MOD_DATE
> >>
> >> I want to retain just one row for unique (ID, DPTID) combination, doesn't
> >> matter with values of rest of the columns.
> >>
> >> Thanks In Advance
> >>
> >> John B
> >>
> >>
> >>
> >>
> >>
> >>
>
>

Delete data from GridView and ObjectDataSource

The function that is supposed to delete a row, is not working. The function is called, and the windows is refreshed, but the row is not deleted.
Can anyone see anything wrong with this code:

public static void DeleteBlog(int original_BlogID)
{
string insertCommand = "DELETE FROM Blog WHERE BlogID = @.BlogID";
SqlConnection myConnection = new SqlConnection(Blog.ConnectionString);
SqlCommand command = new SqlCommand(insertCommand, myConnection);

command.Parameters.Add(new SqlParameter("@.BlogID", original_BlogID));

myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
}You must be using ASP.NET 1.1. The Parameters.Add contructor has 2 overloads with 2 parameters:

Add(parameterName, object)
Add(parameterName, SqlDbType)

Since your original_BlogID is numeric, ADO.NET is assuming you are using the 2nd overload (parameterName, SqlDbType), so your value is not assigned to the parameter. Try it this way instead:

command.Parameters.Add(new SqlParameter("@.BlogID", SqlDbType.Int)).Value = original_BlogID;

Or, as a shortcut:

command.Parameters.Add("@.BlogID", SqlDbType.Int).Value = original_BlogID;

The overload with the object parameter has been deprecated in ADO.NET 2.0 because of this issue, and has been replaced with the method AddWithValue.|||Still not working, and I cannot see what's wrong either :(
The page is refreshed, but no rows are deleted .

public static void DeleteBlogg(int original_blogID) { //System.Diagnostics.Debug.WriteLine("original_blogID og BlogID" + original_blogID + BlogID); string insertCommand = "DELETE FROM Blog WHERE BlogID = @.BlogID"; SqlConnection myConnection = new SqlConnection(Blog.ConnectionString); SqlCommand command = new SqlCommand(insertCommand, myConnection); command.Parameters.Add(new SqlParameter("@.BlogID", SqlDbType.Int)).Value = original_blogID; myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); }|||and this is the aspx page:
Still it is not working, the page is refreshed, but no rows are deleted

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="DeleteBlogg"
SelectMethod="ListMyBlogs" TypeName="Blog">
<DeleteParameters>
<asp:Parameter Name="original_blogID" Type="Int32" />
</DeleteParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="Message" HeaderText="Message" SortExpression="Message" />
<asp:BoundField DataField="MessageCreated" HeaderText="MessageCreated" ReadOnly="True"
SortExpression="MessageCreated" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="BlogID" HeaderText="BlogID" SortExpression="BlogID" />
<asp:BoundField DataField="MessageUpdated" HeaderText="MessageUpdated" ReadOnly="True"
SortExpression="MessageUpdated" />
</Columns>
</asp:GridView>

delete contraint

HI all,
Hope I have the correct terminology.
I have related tables, with cascading deletes off. In otherwords, you cannot
delete a parent row if there are related child row.
Now when this happens a correct error is produced.
Is there any way to have a single return statement stipulation which child
table was involved in the cascading delete( or rarther the parent delete
failing)
Reasoning behind this is that because I have many child tables related with
a single parent, it would be a lot easier to know which table cause the
delete to fail, go to that table and determin if row in that particular
table can be deleted.
Currently I have to go to every related child table, and figure out if it
caused the delete to fail, and if ti did, then take the needed action.
I'm really looking for principals here, or for someone to steer me in the
right direction, perhaps some reading material etc.
Thanks
RobertAre you saying that you want to know which referencing table is it that proh
ibits the DELETE
operation? Check out the error message from the DELETE operation. If you hav
e several referencing
tables that has a conflict, you will only get on of them, though:
CREATE TABLE rd(c1 int primary key)
INSERT INTO rd (c1) VALUES(1)
GO
CREATE TABLE rs1(c1 int REFERENCES rd(c1))
CREATE TABLE rs2(c1 int REFERENCES rd(c1))
INSERT INTO rs2 (c1) VALUES(1)
CREATE TABLE rs3(c1 int REFERENCES rd(c1))
INSERT INTO rs3 (c1) VALUES(1)
GO
DELETE FROM rd WHERE c1 = 1
Server: Msg 547, Level 16, State 1, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__rs2__c1__
4D0CD9BB". The conflict
occurred in database "tempdb", table "dbo.rs2", column 'c1'.
The statement has been terminated.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Robert Bravery" <me@.u.com> wrote in message news:egQxAm8MGHA.2828@.TK2MSFTNGP12.phx.gbl...[
color=darkred]
> HI all,
> Hope I have the correct terminology.
> I have related tables, with cascading deletes off. In otherwords, you cann
ot
> delete a parent row if there are related child row.
> Now when this happens a correct error is produced.
> Is there any way to have a single return statement stipulation which child
> table was involved in the cascading delete( or rarther the parent delete
> failing)
> Reasoning behind this is that because I have many child tables related wit
h
> a single parent, it would be a lot easier to know which table cause the
> delete to fail, go to that table and determin if row in that particular
> table can be deleted.
> Currently I have to go to every related child table, and figure out if it
> caused the delete to fail, and if ti did, then take the needed action.
> I'm really looking for principals here, or for someone to steer me in the
> right direction, perhaps some reading material etc.
> Thanks
> Robert
>[/color]

Delete a row and all other rows thats linked to it

Hi
i want to delete a row in my database but the problem is, i cant delete it as other table rows is linked to it. I have to delete all the rows thats linked to the row i wanna delete first.

Is there a easier way to delete the row and all the rows thats linked to it? i wanna code it to do it.

an suggestions?That's what cascading deletes are for, but that's in the database design. Do searches on Declaritive Referential Integrity or DRI.sql

Delay affecting key violation?!

Hello!
I'm having a little problem with SQL Server (this doesn't happen in Access):
Using ADO:
I begin a transaction
I insert a row into table X, but the primary key already exists so(an error
should be raised immediately)
I insert a related row into a related table X.
I do some more things
Hey! Error?! Key violation?! Where? At table X (my first insert)...
uhhhhhhhhh!!!
Is there any way to make SQL Server send an error immediately if the insert
violate the primary key?
I just don't get this :(
Thanks for all your help.
Jorge Carvalho
rdc02271@.yahoo.comset xact_abort on
"Jorge C" wrote:

> Hello!
> I'm having a little problem with SQL Server (this doesn't happen in Access
):
> Using ADO:
> I begin a transaction
> I insert a row into table X, but the primary key already exists so(an erro
r
> should be raised immediately)
> I insert a related row into a related table X.
> I do some more things
> Hey! Error?! Key violation?! Where? At table X (my first insert)...
> uhhhhhhhhh!!!
> Is there any way to make SQL Server send an error immediately if the inser
t
> violate the primary key?
> I just don't get this :(
> Thanks for all your help.
> Jorge Carvalho
> rdc02271@.yahoo.com
>
>|||Thanks but little problem:
If I set transaction mode to explicit the entire transaction will be rolled
back, neglecting my commit or rollback statement.
So what's the point of having explicit transaction mode when you are unable
to: control the error the minute it happens + make necessary changes and try
again without rolling back the transaction?
Thanks.
Jorge Carvalho
rdc02271@.yahoo.com
"Ray D" <RayD@.discussions.microsoft.com> escreveu na mensagem
news:F78827F1-F1BC-4ADF-B25A-F517529B9C12@.microsoft.com...
> set xact_abort on
> "Jorge C" wrote:
>
>|||You are correct that an error should be raised as soon as you insert a
row that violates a constraint. However, what you haven't told us is
the exact "key violation" error message you are getting? Was it the
foreign key or primary key constraint? It seems like the second insert
caused the error so maybe the message is "INSERT statement conflicted
with COLUMN FOREIGN KEY constraint", which might explain why you see it
as an error "at table X".
Also, check if there are any triggers on these tables. Maybe a trigger
is at fault.
David Portas
SQL Server MVP
--|||Hi!
The primary key at the first table is being violated.
Our scheme is like this: (if you have a better system please share :) )
1)The user fills out an invoice (header+details+some other info)
2)Then we try to save the invoice using the latest invoice number+1;but
since we have more one application doing the same with the same database
(multi-user) the insert for the first table may fail (invoice header). So we
need to change the invoice number and try again.
The primary key violation message refers to this first operation.
Thanks.
Jorge Carvalho
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> escreveu na mensagem
news:1112358100.042970.307850@.z14g2000cwz.googlegroups.com...
> You are correct that an error should be raised as soon as you insert a
> row that violates a constraint. However, what you haven't told us is
> the exact "key violation" error message you are getting? Was it the
> foreign key or primary key constraint? It seems like the second insert
> caused the error so maybe the message is "INSERT statement conflicted
> with COLUMN FOREIGN KEY constraint", which might explain why you see it
> as an error "at table X".
> Also, check if there are any triggers on these tables. Maybe a trigger
> is at fault.
> --
> David Portas
> SQL Server MVP
> --
>|||That isn't an efficient strategy for generating an incrementing key. Also,
as a matter of design principle it's not a good idea to build a system that
generates errors as part of its correct behaviour.
Max invoice number + 1:
INSERT INTO Invoices (invoice_num,... /* other columns */)
SELECT COALESCE(MAX(invoice_num),0)+1, ... /* other data */
FROM Invoices
If it isn't a problem to have gaps in the sequence of invoice numbers then
you might also consider using the IDENTITY property to generate the
sequence.
Don't forget to declare your table's natural key. Maybe (customer,
invoice_date) for example.
David Portas
SQL Server MVP
--

Defining custom column groups on a matrix report in report builder

I have table called Buildings which has market and sq. ft information.
I want to create a matrix report that has the market as the row group and
sq. ft ranges as the column group and shows the # of buildings in each
market that fall in the sq. ft ranges.
Sq. ft ranges are 0-5000, 5001-10000 etc...
My questions is a) is this something that can be done using report builder
b) how do i define the sq. ft ranges
c) how do I find the count of buildings whose sq. footage falls within the
range.
ThanksOn Nov 14, 1:18 pm, "shikarishambu" <shikarishamb...@.hotmail.com>
wrote:
> I have table called Buildings which has market and sq. ft information.
> I want to create a matrix report that has the market as the row group and
> sq. ft ranges as the column group and shows the # of buildings in each
> market that fall in the sq. ft ranges.
> Sq. ft ranges are 0-5000, 5001-10000 etc...
> My questions is a) is this something that can be done using report builder
> b) how do i define the sq. ft ranges
> c) how do I find the count of buildings whose sq. footage falls within the
> range.
> Thanks
A) Yes
B) In your Datasets window, right-click on the name of the dataset and
Add a new field. Call it "Sq Foot Range" and make it a calculated
field. Use an expression like
= Fields!SqFoot.Value - Fields!SqFoot.Value Mod 5000
Since you are shifting your range to include the evenly divided number
in the lower group, you really want
= CStr( ( (X-1) - (X-1) Mod 5000 ) + 1 ) & " to " & CStr( ( (X-1) -
(X-1) Mod 5000 ) + 5000 )
C) Create a Matrix with a Market in the Row Group, Sq Foot Range in
the Column Group, and Count( Fields!SqFoot.Value ) in the Details.
Hope that helps.
-- Scott

define row delimiter to fixed width files

hi all,

is there a way to define a row delimiter to fixed width files? such as this one:

1122333

4455666

7788999

in this file i have 3 columns that are fixed (col1.width = 2, col2.width = 2, col3.width = 3) but have {CR}-{LF} as the row delimiter. when i try to create a flat file connection to these kind of files- he always reads the CR-LF as part of the file text, and there is no place where i can define the row delimiter if i have.

thanks for your help!!!

Create and extra 2 byte field at the end for the import of the CR/LF and just ignore the field in your transforms.

Or use the "Ragged Right" option for the file.|||Have no idea - the work around it to add additional column of size 2 to capture CRLF.|||

What we have here is a terminology issue. The down-side of which is that fixed width files have no row-delimiters, that is not valid fixed width format. The up-side is that ragged right is exactly what you want. It has confused me a couple of times too.

As an aside you could use Fixed Width if you really wanted. You would increase your specified row length by 2, such that it includes the CR_LF if it does not already. This should be easy to see in the Columns UI. Then add an extra two character column such that it just has the CR+LF characters in. You can then ignore this column downstream, just don't use it in any transforms, especially your destination(s).

From Books Online - Format:

Delimited

Columns are separated by delimiters, specified on the Columns page.

Fixed width

Columns have a fixed width.

Ragged right

Ragged right files are files in which every column has a fixed width, except for the last column. It is delimited by the row delimiter.

Default Values for a Column

I have table where I want one column (EquivNo) to default to the primary key
column (PartID) when the row is inserted. This value may be updated later,
but at the point where the row is created, I want the values to be the same.
The PartID column is an Identity column so I don't know the value in
advance.
As far as I understand it, you can't have a calculated value as the default
value for a column; can somebody confirm this?
So I have thought of two alternatives:
1) Create a trigger that takes the identity value for PartID and updates the
EquivNo column
[How do I know what the inserted PartID is within the trigger?]
2) The stored procedure inserts the row on the first pass, and uses
Scope_Identy() to update the EquivNo in an update statement.
I was just wondering about the relative merits of these two solutions. And
are there any better alternatives?
Thanks in advance
ChrisCJM
Second one seems to be good for you.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.phx.gbl...
>I have table where I want one column (EquivNo) to default to the primary
>key column (PartID) when the row is inserted. This value may be updated
>later, but at the point where the row is created, I want the values to be
>the same. The PartID column is an Identity column so I don't know the value
>in advance.
> As far as I understand it, you can't have a calculated value as the
> default value for a column; can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates
> the EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses
> Scope_Identy() to update the EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
> are there any better alternatives?
> Thanks in advance
> Chris
>|||An advantage of using 1 is that the modifications are performed in the same
transaction. The trigger
code is very straight forward:
USE tempdb
drop table t
GO
create table t(c1 int identity primary key, c2 int NULL)
GO
CREATE TRIGGER tr ON T FOR INSERT
AS
UPDATE t SET c2 = c1
WHERE EXISTS
(
SELECT *
FROM inserted AS i
WHERE i.c1 = t.c1
)
GO
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
insert into t(c2) VALUES(NULL)
SELECT * FROM t
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.ph
x.gbl...
>I have table where I want one column (EquivNo) to default to the primary ke
y column (PartID) when
>the row is inserted. This value may be updated later, but at the point wher
e the row is created, I
>want the values to be the same. The PartID column is an Identity column so
I don't know the value
>in advance.
> As far as I understand it, you can't have a calculated value as the defaul
t value for a column;
> can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates t
he EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses Scope_
Identy() to update the
> EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
are there any better
> alternatives?
> Thanks in advance
> Chris
>|||CJM wrote:
> I have table where I want one column (EquivNo) to default to the primary k
ey
> column (PartID) when the row is inserted. This value may be updated later,
> but at the point where the row is created, I want the values to be the sam
e.
> The PartID column is an Identity column so I don't know the value in
> advance.
> As far as I understand it, you can't have a calculated value as the defaul
t
> value for a column; can somebody confirm this?
> So I have thought of two alternatives:
> 1) Create a trigger that takes the identity value for PartID and updates t
he
> EquivNo column
> [How do I know what the inserted PartID is within the trigger?]
> 2) The stored procedure inserts the row on the first pass, and uses
> Scope_Identy() to update the EquivNo in an update statement.
> I was just wondering about the relative merits of these two solutions. And
> are there any better alternatives?
> Thanks in advance
> Chris
Is EquivNo a self-referencing foreign key? (please post DDL, then we
won't have to guess). I'm of the opinion that IDENTITY isn't a good
choice to use as a foreign key in the same table. Your problem is just
one of one of the reasons why.
If EquivNo is not a key then I'm not sure why you'd want it to be the
same as the PartID. It's a bad idea to expose IDENTITY columns to
users. From the business perspective it shouldn't matter what value is
assigned to PartID and therefore it shouldn't matter whether it's the
same as EquivNo. I think that your real problem is that you need a more
convenient alternative method to generate an incrementing key for
EquivNo. See the following article for suggestions:
http://www.sqlmag.com/Articles/Arti...8165/48165.html
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
--|||Tibor
> An advantage of using 1 is that the modifications are performed in the
> same transaction. The trigger code is very straight forward:
Just keep thinking if the user inserts a new row (one transaction) , the
trigger is fired and open another transaction, am I right?
If the trigger fails that an identity peroperty is already in the table , so
actually in my opinion that using
a stored procedure will be more useful in terms of performans as well as
more secure.
CREATE PROC myproc
AS
DECLARE @.idnt INT
BEGIN TRAN
INSERT INTO T1 VALUES (....)
SELECT @.idnt =SCOPE_IDENTITY()
INSERT INTO T2 SELECT @.idnt
--Error handler here
COMMIT
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23C2r4bWWGHA.5096@.TK2MSFTNGP03.phx.gbl...
> An advantage of using 1 is that the modifications are performed in the
> same transaction. The trigger code is very straight forward:
> USE tempdb
> drop table t
> GO
> create table t(c1 int identity primary key, c2 int NULL)
> GO
> CREATE TRIGGER tr ON T FOR INSERT
> AS
> UPDATE t SET c2 = c1
> WHERE EXISTS
> (
> SELECT *
> FROM inserted AS i
> WHERE i.c1 = t.c1
> )
> GO
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> insert into t(c2) VALUES(NULL)
> SELECT * FROM t
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "CJM" <cjmnews04@.newsgroup.nospam> wrote in message
> news:%23UkDUPWWGHA.3448@.TK2MSFTNGP03.phx.gbl...
>|||> Just keep thinking if the user inserts a new row (one transaction) , the
> trigger is fired and open another transaction, am I right?
No, the code in a trigger is in the same transaction as the statement that f
ired the trigger.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:OxFvq8WWGHA.3800@.TK2MSFTNGP03.phx.gbl
..
> Tibor
> Just keep thinking if the user inserts a new row (one transaction) , the
> trigger is fired and open another transaction, am I right?
> If the trigger fails that an identity peroperty is already in the table ,
so
> actually in my opinion that using
> a stored procedure will be more useful in terms of performans as well as
> more secure.
>
> CREATE PROC myproc
> AS
> DECLARE @.idnt INT
> BEGIN TRAN
> INSERT INTO T1 VALUES (....)
> SELECT @.idnt =SCOPE_IDENTITY()
> INSERT INTO T2 SELECT @.idnt
> --Error handler here
> COMMIT
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%23C2r4bWWGHA.5096@.TK2MSFTNGP03.phx.gbl...
>|||Surely the transaction issue is a red herring? That is, we can explicitly
define what is and isn't included in the transaction (Begin/Commit/Rollback)
anyway.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23E1lfUWWGHA.3800@.TK2MSFTNGP03.phx.gbl...
> CJM
> Second one seems to be good for you.
>
I'm erring this way, if only because it's a simple and uncomplicated
solution.|||> Surely the transaction issue is a red herring? That is, we can explicitly define what is
and isn't
> included in the transaction (Begin/Commit/Rollback) anyway.
Yes, of course we can. My point (perhaps not explicit enough) was that the t
ransaction handling will
be transparent for those who does INSERTs into the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:%23lbLUbXWGHA.924@.TK2MSFTNGP03.phx
.gbl...
> Surely the transaction issue is a red herring? That is, we can explicitly
define what is and isn't
> included in the transaction (Begin/Commit/Rollback) anyway.
>|||Did you look at the trigger code I posted? What part of that code did you fi
nd complicated? I'm not
trying to defend my proposal, I just want to make sure you see the simplicit
y of the trigger
alternative.
Oh, and I fully agree with David's point regarding IDENTITY should be comple
mented with a natural
key and identity not be exposed to users, btw.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message news:u$0KGgXWGHA.1348@.TK2MSFTNGP05.phx.
gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23E1lfUWWGHA.3800@.T
K2MSFTNGP03.phx.gbl...
> I'm erring this way, if only because it's a simple and uncomplicated solut
ion.
>

Default Value

I set up a database using Studio Express. I defined one row to be a date
field with a default value of 9999-12-31. When I go back and look at it it
looks like "(((9999)-(12))-(31))".
When I insert a row into this table and I have not specified a value for
this date field, it show a date of "1927-04-06 00:00:00.000". This does not
resemble anything which was suppose to have been defaulted. What am I doing
wrong?It works goos for me...
Try the below code:-
create table xxxx(i int, j datetime default '9999-12-31')
go
insert into xxxx(i) values(100)
go
select * from xxxx
Thanks
Hari
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:68C415E4-058B-49B6-9A08-AFD78266E306@.microsoft.com...
>I set up a database using Studio Express. I defined one row to be a date
> field with a default value of 9999-12-31. When I go back and look at it
> it
> looks like "(((9999)-(12))-(31))".
> When I insert a row into this table and I have not specified a value for
> this date field, it show a date of "1927-04-06 00:00:00.000". This does
> not
> resemble anything which was suppose to have been defaulted. What am I
> doing
> wrong?|||The result of the following script:
SELECT CAST(9999-12-31 AS datetime)
is 1927-04-06 00:00:00.000 because SQL server takes the result of the
arithmetic expression (9956) and converts it to datetime. The result of:
SELECT CAST('9999-12-31' AS datetime)
is the expected '9999-12-31 00:00:00.000' because SQL parses the string and
properly converts it to datetime. However, many date format strings are
ambiguous and dependent on your dateformat settings. I suggest you specify
dates in 'yyyymmdd' format so that the value is properly interpreted
regardless of your dateformat setting. For example:
ALTER TABLE dbo.Table1
ADD CONSTRAINT DF_Table1_MyDatetime
DEFAULT '99991231' FOR MyDatetime
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:68C415E4-058B-49B6-9A08-AFD78266E306@.microsoft.com...
>I set up a database using Studio Express. I defined one row to be a date
> field with a default value of 9999-12-31. When I go back and look at it
> it
> looks like "(((9999)-(12))-(31))".
> When I insert a row into this table and I have not specified a value for
> this date field, it show a date of "1927-04-06 00:00:00.000". This does
> not
> resemble anything which was suppose to have been defaulted. What am I
> doing
> wrong?

default value

i want a defauld vaule in my query to come for each row(50 row)

say i have address table with column name,street1, state,pin but no column for city--

How do i return a query for each name(50 row) which has a default value of city as 'Miami' for all 50 rows
the city name is not there in databse at all

any help will be appreciatedSELECT street1, state,pin,'Miami' as city
FROM yourTable|||thanks how stupid of Meeeeeeeeeeee op|||futher to that can i put city value as Miami for say record 1,5,10,15,40
and the rest as 'Davie' ??|||Are 1,5,10,15,40 field values in some kinf of primary key?

If so try something like this...

SELECT (CASE WHEN fieldname in (1,5,10,15,40) THEN 'Miami' ELSE 'Davie' END) as City
FROM yourTable

This is not really scalable. What happens when you have 50K or 500K records you want marked as Miami. You might just want to add a city column.|||and what if you want to include miramar, plantation, coral gables, and dania? :D
best to create a city column and populate accordingly
you can add a column after the table is created

Books Online{Adding and Deleting Columns}

then you could add a default constraint to the column to specify miami
Books Online{Default Constraints}

Sample

create database helpme
go
use helpme
go
create table t1
(
c1 int
,c2 int
)
go
alter table t1 add city varchar(20) null
go
alter table t1 add Constraint dCity Default('Miami') for city
go
insert t1(c1,c2) values (1,2)
go
select * from t1
go
--drop table t1|||GOOd but some of it was greeek to me since i came over from oracle

Can you give example from say pubs here is the query
and i want to change the city value from this query to say 'miami,davie, ftlauderdale

how do i put in a table and update the table pull out the output and drop the table ?

And can i do it in the same database?

select e.au_ord,a.city,a.state, a.au_lname,t. price, t.ytd_sales,t.pub_id
from authors a, titles t, titleauthor e
where a.au_id = e.au_id
AND t.title_id = e.title_id

thanks|||GOOd but some of it was greeek to me since i came over from oracle

Can you give example from say pubs here is the query
and i want to change the city value from this query to say 'miami,davie, ftlauderdale

how do i put in a table and update the table pull out the output and drop the table ?

And can i do it in the same database?

select e.au_ord,a.city,a.state, a.au_lname,t. price
from authors a, titles t, titleauthor e
where a.au_id = e.au_id
AND t.title_id = e.title_id

thanks HElp those whom you can ?|||how come no body could do this ?|||Do what?

-PatP|||if you could help me understand what it is that you want, i could help you better.
help me to help you
Help Me.
To help You...|||Multi table query --> put in dummy table ( so create and insert)

Update --> dummy table with some condition
(Say change city name from a to anew
b to bnew etc)
I have a complex calculation to do

Return the updated output from this dummy table

drop the dummy table

So here is the query from pubs database

select e.au_ord,a.city,a.state, a.au_lname,t. price
from authors a, titles t, titleauthor e
where a.au_id = e.au_id
AND t.title_id = e.title_id

thanks|||I'm going WAY out on a limb here, because I still am not sure I understand your question, but does this help:CREATE TABLE city_renames (
city VARCHAR(20) NOT NULL
CONSTRAINT XPKcity_renames
PRIMARY KEY (city)
, newname VARCHAR(20) NOT NULL
)

INSERT INTO city_renames (city, newname) VALUES ('Oakland', 'Otherside')
INSERT INTO city_renames (city, newname) VALUES ('Palo Alto', 'Andy''s Place')
INSERT INTO city_renames (city, newname) VALUES ('Corvallis', 'HP-41c')

SELECT
e.au_ord
, Coalesce(r.newname, a.city)
, a.state
, a.au_lname
, t. price
FROM authors AS a
JOIN titleauthor AS e
ON (e.au_id = a.au_id)
JOIN titles AS t
ON (t.title_id = e.title_id)
LEFT JOIN city_renames AS r
ON (r.city = a.city)

DROP TABLE city_renames-PatP|||how come no body could do this ?

Dude, have YOU even TRIED looking in Books Online to figure out how SELECT statements, UPDATEs, and say Temporary tables/table variable work?

Taunting won't get you far around here. Your problem is probably easy to solve, but you have yet to explain it clearly.|||Sorry i didnot mean't to taunt just asking
I dont know how Temporary tables/table variable work

i am trying this example since everyone has pubs with them mine is more complecated,for i have to write many conditionaly statements inbetween

So let me try again

first start with a query that i gave ie (mine is more complex)

select e.au_ord,a.city,a.state, a.au_lname,t. price
from authors a, titles t, titleauthor e
where a.au_id = e.au_id
AND t.title_id = e.title_id

Store this whole result of above query into a dummy table <-- this is what i
dont know ?

Do changes to the dummy table --(here i have a complex change with an if
then else statement city
name is just an example )

do a select from this dummy table only !!!!!

return the value from dummy table and drop the dummy table|||I like my previous idea better, but how about:SELECT e.au_ord,a.city,a.state, a.au_lname,t. price
INTO #foo
FROM authors a, titles t, titleauthor e
WHERE a.au_id = e.au_id
AND t.title_id = e.title_id-PatP|||SELECT e.au_ord,a.city,a.state, a.au_lname,t. price
INTO #foo
FROM authors a, titles t, titleauthor e
WHERE a.au_id = e.au_id
AND t.title_id = e.title_id

Does this also create a dummytable FOO that can be updated, inserted and select statement ?

Any other way would also be helpfulll|||yes but the table is known as a Temporary table and is queried explicitly as #foo
as in select * from #foo

a temporary table exists only as long as your session.

you should consider using microsoft books online which is in the sql server start menu.

look up temporary tables , variables, insert, update, and delete.|||What you are calling a "dummy" table can be implemented in TSQL as eigther a temporary table or a table variable.

Briefly, a temporary table is created in a stored procedure using the same syntax as for a permanent table, except that the name it preceded with a # character. A temporary table exists for the duration of the stored procedure or, if running a script in Query Analyzer, until dropped or the session ends. A temporary table can be indexed and joined like any other table.

A table variable is a variable that holds a dataset. Like other variables, a table variable is declared and the name is preceded with a @. symbol. A table variable exists until it goes out of scope. Table variables are generally faster than temporary tables, but they cannot be indexed to the extent of temporary tables. They can be queried and joined like any other table, but you will need to alias them.

Please look up Temporary Tables and Table Variables in Books Online for more information.|||Thanks you'll have being great Help

Wish if you'll were girls or i was ; would have Kissed you'all

But a Special Thanks|||?
I have no response to that.|||?
I have no response to that.That's ok... Its better that way.

-PatP|||After my query is put in table say #foo
I have to add 2 columns say year and month

One column (small int) in #foo has date in the form 104,204...1204
I want to update this column depending on system date

if > than system date then "F" for forcast
and if < system date then "B" for budget

so # foo has among other column

Bill column2 column3 column4
104 entry1 entry2 entry3
106 value1 value2 value 3

First row has Jan04 (past) and second row has Jan06(future)

replace that Jan04 with "B"
and Jan06 with "F"

also i want to add column5(year) and column6 (month)
and insert Month and year with relevant value|||I'd suggest you post the code you've got so far, and I'll see if I can mangle it into something useful (if somebody else doesn't beat me to it).

-PatP|||Select p.Billcycle,'Corp1' as RecordSourceCode, '5407' as MarketID, 'Miami' as MarketName,
sum(p.grossrev) BudgetGross,
(sum(p.grossrev)-sum(p.agyrev))BNetDollars,(sum(p.grossreve)- sum(p.agyrev)-sum(p.reprev)) BNetNetDollars
into #mytable
from conproj p, contract n
where p.conid=n.conid
and billcycle IN (105,205,305,405,505,605,705,805,905,1005,1105,120 5)
and n.status not in ('H')
group by p.billcycle

select * from #mytable

How do i add 2 columns ( say year and Month) to #mytable ???

One column Billcycle (small int) in #mytable has date in the form 104,204...1204
I want to update this column depending on system date

if > than system date then "F" for forcast
and if < system date then "B" for budget

so # mytable has among other column

Bill column2 column3 column4
104 entry1 entry2 entry3
106 value1 value2 value 3

First row has Jan04 (past) and second row has Jan06(future)

replace that Jan04 with "B"
and Jan06 with "F"

the result should look like this

Bill Newcolumn1 Newcolumn2 column2 column3 column4
B Jan 2004 entry1 entry2 entry3
F Jan 2006 value1 value2 value 3|||What's the data type for p.Billcycle? If character, exactly how is the value formatted? Please show examples for Jan-Dec of some year.

-PatP|||p.Billcycle smallint
sum(p.grossrev) Money
p.agyrev Money
p.reprev Money

How do i add 2 columns ( say Myyear and MYMonth) to #mytable ???
of type Varchar to store Month and year

One column Billcycle (small int) in #mytable has date in the form 104,204...1204
I want to update this column depending on system date

if > than system date then "F" for forcast
and if < system date then "B" for budget

so # mytable has among other column

Billcycle(smallint--size 2) col2 ( money size 8) col3(money size8), col4

104 entry1 entry2 entry3
204 entry1 entry2 entry3
304 entry1 entry2 entry3......
1204 entry1 entry2 entry3 ...... (each month may have one row)
106 value1 value2 value 3
206 value
306
106 value1 value2 value 3

First row has Jan04 (past) and second row has Jan06(future)

replace that Jan04 with "B"
and Jan06 with "F"

the result should look like this

Bill Newcolumn1 Newcolumn2 column2 column3 column4
B Jan 2004 entry1 entry2 entry3
F Jan 2006 value1 value2 value 3|||Here is the excel sheet(see zippped attachment) which first has #Foo table

i want in the form of new table if possible
or second choice if thats not possible|||can i do this

Select p.Billcycle,'Corp1' as RecordSourceCode, '5407' as MarketID, 'Miami' as MarketName,
sum(p.grossrev) BudgetGross,
(sum(p.grossrev)-sum(p.agyrev))BNetDollars,(sum(p.grossreve)- sum(p.agyrev)-sum(p.reprev)) BNetNetDollars
into #mytable
from conproj p, contract n
where p.conid=n.conid
and billcycle IN (105,205,305,405,505,605,705,805,905,1005,1105,120 5)
and n.status not in ('H')
group by p.billcycle

exec( create table newtable as select * from #mytable)

go

alter table newtable
add column newcolumn1 varchar(20);

go

alter table new table
add column newcolumn2 varchar(20)

go

insert in to new column

something

will this work ?
I had included the change in zip file that i want how do i proceed ?|||How about:SELECT
p.Billcycle, 'Corp1' as RecordSourceCode, '5407' as MarketID
, 'Miami' as MarketName, sum(p.grossrev) BudgetGross
, (sum(p.grossrev) - sum(p.agyrev)) AS BNetDollars
, (sum(p.grossreve) - sum(p.agyrev) - sum(p.reprev)) AS BNetNetDollars
, b.BillCycle % 100 AS MyYear
, b.BillCycle / 100 AS MyMonth
, CASE WHEN GetDate() < Convert(DATETIME, Str(CASE WHEN b.BillCycle % 100 < 50
THEN 2000 ELSE 1900 END + b.BillCycle % 100, 4) + '-'
+ Str(b.BillCycle / 100, 2) + '-01') THEN 'F' ELSE 'B' END AS thingy
INTO #mytable
FROM conproj AS p
JOIN contract AS n
ON (p.conid = n.conid)
WHERE billcycle IN (105, 205, 305, 405, 505
, 605, 705, 805, 905, 1005, 1105, 1205)
AND n.status not in ('H')
GROUP BY p.billcycle-PatP

Default Value

I set up a database using Studio Express. I defined one row to be a date
field with a default value of 9999-12-31. When I go back and look at it it
looks like "(((9999)-(12))-(31))".
When I insert a row into this table and I have not specified a value for
this date field, it show a date of "1927-04-06 00:00:00.000". This does not
resemble anything which was suppose to have been defaulted. What am I doing
wrong?It works goos for me...
Try the below code:-
create table xxxx(i int, j datetime default '9999-12-31')
go
insert into xxxx(i) values(100)
go
select * from xxxx
Thanks
Hari
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:68C415E4-058B-49B6-9A08-AFD78266E306@.microsoft.com...
>I set up a database using Studio Express. I defined one row to be a date
> field with a default value of 9999-12-31. When I go back and look at it
> it
> looks like "(((9999)-(12))-(31))".
> When I insert a row into this table and I have not specified a value for
> this date field, it show a date of "1927-04-06 00:00:00.000". This does
> not
> resemble anything which was suppose to have been defaulted. What am I
> doing
> wrong?|||The result of the following script:
SELECT CAST(9999-12-31 AS datetime)
is 1927-04-06 00:00:00.000 because SQL server takes the result of the
arithmetic expression (9956) and converts it to datetime. The result of:
SELECT CAST('9999-12-31' AS datetime)
is the expected '9999-12-31 00:00:00.000' because SQL parses the string and
properly converts it to datetime. However, many date format strings are
ambiguous and dependent on your dateformat settings. I suggest you specify
dates in 'yyyymmdd' format so that the value is properly interpreted
regardless of your dateformat setting. For example:
ALTER TABLE dbo.Table1
ADD CONSTRAINT DF_Table1_MyDatetime
DEFAULT '99991231' FOR MyDatetime
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim Heavey" <JimHeavey@.discussions.microsoft.com> wrote in message
news:68C415E4-058B-49B6-9A08-AFD78266E306@.microsoft.com...
>I set up a database using Studio Express. I defined one row to be a date
> field with a default value of 9999-12-31. When I go back and look at it
> it
> looks like "(((9999)-(12))-(31))".
> When I insert a row into this table and I have not specified a value for
> this date field, it show a date of "1927-04-06 00:00:00.000". This does
> not
> resemble anything which was suppose to have been defaulted. What am I
> doing
> wrong?