Inserting a column in an existing table

I have an existing table (see below).

----
[FormCode] [varchar] (4) NULL ,
[FiscalYear] [char] (4) NULL
----

I want to add the column below after the [FormCode] when my SPROC runs.
----
[FiscalMonth] [char] (2) NULL
----

Any ideas would be a big help?
TIF--use this to add the column

alter table MyTable
add FiscalMonth char (2)
go

--and this to drop the column

alter table MyTable
drop column FiscalMonth
go

Cheers|||Thanks for your response, however, I'm actually after adding a column in between existing columns.

So in this case, my new column FISCALMONTH will be added between FORMCODE and FISCALYEAR.

Tnx|||Why is important to have the ordinal position of your column correct?|||The quickest and easiest (at least in most cases) way to "insert" columns into a table is to put the columns wherever they fall and construct a view to order them the way you want them.

In relational algebra, columns have no order. In relational databases, the order of columns should be considered an anomoly, not an attribute.

A view on the other hand is a template for a result set, and columns do have an order in a result set.

-PatP|||I don't understand eather... Why do you need them in a specific order?|||Did you ever get an answer to this? I know that you can create a view to order your columns but it would be nice to do this in the table. No it doesn't matter from a DB perspective but it is cleaner if you are dealing with many columns.|||Why don't you go into design view of a table in Enterprise Manager, make your changes, and save the script.

I would also summarize that ALTER TABLE anything in SQL server produces ineffeciencies at the page level...

Read Nigel's great article on the subject

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html

Insert/update/delete Transaction

Hi,

I have an unbound DataGridView and I have load it with a set of records from a Data base.

I modify existing rows, delete rows and add new rows to DataGridView control. I have to send a new modified dataset back to the data base.

Please any suggestions how to solve the problem?

Thanks in advance

George

Hi George,

I think you'll have more success posting your question on the Visual Studio forums - this is the T-SQL forum which is primarily used for back-end SQL questions, rather than user interface coding problems like DataGridViews.

Hope that helps :)

Menthos
|||Thanks :)sql

Insert Uniqueidentifier after the fact

I have an existing table that i would like to add a uniquidentifier to
each record of the table. I have already create a column for the
uniqueid. What sql script could I run to actually place a value for
the newly created column for each record?
thanks for your help ahead of timeWhat sql script could I run to actually place a value for

Quote:

Originally Posted by

the newly created column for each record?

You can use NEWID() to backfill existing data:

UPDATE dbo.MyTable
SET MyColumn = NEWID()

--
Hope this helps.

Dan Guzman
SQL Server MVP

<pltaylor3@.gmail.comwrote in message
news:1157114291.688642.258140@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

>I have an existing table that i would like to add a uniquidentifier to
each record of the table. I have already create a column for the
uniqueid. What sql script could I run to actually place a value for
the newly created column for each record?
thanks for your help ahead of time
>

|||Thats perfect. Thanks for the help.
Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

What sql script could I run to actually place a value for
the newly created column for each record?

>
You can use NEWID() to backfill existing data:
>
UPDATE dbo.MyTable
SET MyColumn = NEWID()
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
<pltaylor3@.gmail.comwrote in message
news:1157114291.688642.258140@.b28g2000cwb.googlegr oups.com...

Quote:

Originally Posted by

I have an existing table that i would like to add a uniquidentifier to
each record of the table. I have already create a column for the
uniqueid. What sql script could I run to actually place a value for
the newly created column for each record?
thanks for your help ahead of time

Insert Question.

Hello. I am extending an existing proc and encountering a strange issue. I
hope someone can shed some light on this. Here's my problem :
insert into : table with decimal(14,2) columns ( where you see math )
sql statement :
TRUNCATE TABLE dbo.tbl_met_wos
INSERT INTO dbo.tbl_met_wos
SELECT
b.TopDivision as Division,
b.TopDivisionName as DivisionName,
b.Div as Department,
b.DivName as DepartmentName,
b.Dept as Class,
b.DeptName as ClassName,
a.fponhandTW,
a.fponhandMTD,
a.fponhandTQ,
a.fponhandTS,
a.fponhandTY,
c.mdonhandTW,
c.mdonhandMTD,
c.mdonhandTQ,
c.mdonhandTS, --STD
c.mdonhandTY,
d.FPSalesTW,
d.FPSalesMTD,
d.FPSalesTQ,
d.FPSalesSTD,
d.FPSalesTY,
e.MDSalesTW,
e.MDSalesMTD,
e.MDSalesTQ,
e.MDSalesSTD,
e.MDSalesTY,
--RH Added.
--cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in (0,null)
then 1 else d.FPSalesTW end) AS decimal(14,2)),
'1.25',
Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW in(0,null)
then 1 else e.MDSalesTW end) AS varchar),
(a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null) then 1
else d.FPSalesMTD end,
(c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null) then 1
else e.MDSalesMTD end,
Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ in(0,null)
then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
(c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then 1
else e.MDSalesTQ end,
(a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null) then 1
else d.FPSalesSTD end,
--(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null) then 1
else e.MDSalesSTD end,
'1.37',
(a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then 1
else d.FPSalesTY end,
(c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then 1
else e.MDSalesTY end
FROM #HIERARCHY b
LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND b.Department =
a.Department
LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND b.Department =
c.Department
LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND b.Department =
d.Department
LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND b.Department =
e.Department
ORDER BY
b.Div,
b.Dept
as you can see, this is not the hottest proc in the world, but support sucks
sometimes :) anyhow, when this insert statement runs, It will round off to
whole numbers where you see math in the fields. Where you see '1.25' and
'1.37' they insert fine as a decimal(14,2) when you select from the table. I
have tryed converting and casting the entire field like :
Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null)
then 1 else e.MDSalesTQ end AS Decimal(14,2)
but it still rounds off to a whole number. if it were trying to insert
100.65 I get 101.00 when I select from the table.
Any IDEAS? I'm really with this one.
Robert H"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:%23KHG44LrFHA.2588@.tk2msftngp13.phx.gbl...
> Hello. I am extending an existing proc and encountering a strange issue. I
> hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
>
...

> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
>
The sum or product of two decimals requires more precision than the
individual operands. As the required precision approaches 36, scale is
trimmed. So after a few operations your results quickly become converted to
decimal(36,0) unless you are diligent about converting intermediate results
to remove unneeded precision.
If you are sticking with decimal(14,2), just convert each intermediate
result to decimal(16,4), or something, to preserve your scale.
See:
http://msdn.microsoft.com/library/d...br />
8rc5.asp
David|||Robert H wrote:
> Hello. I am extending an existing proc and encountering a strange
> issue. I hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
> TRUNCATE TABLE dbo.tbl_met_wos
> INSERT INTO dbo.tbl_met_wos
> SELECT
> b.TopDivision as Division,
> b.TopDivisionName as DivisionName,
> b.Div as Department,
> b.DivName as DepartmentName,
> b.Dept as Class,
> b.DeptName as ClassName,
> a.fponhandTW,
> a.fponhandMTD,
> a.fponhandTQ,
> a.fponhandTS,
> a.fponhandTY,
> c.mdonhandTW,
> c.mdonhandMTD,
> c.mdonhandTQ,
> c.mdonhandTS, --STD
> c.mdonhandTY,
> d.FPSalesTW,
> d.FPSalesMTD,
> d.FPSalesTQ,
> d.FPSalesSTD,
> d.FPSalesTY,
> e.MDSalesTW,
> e.MDSalesMTD,
> e.MDSalesTQ,
> e.MDSalesSTD,
> e.MDSalesTY,
> --RH Added.
> --cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in
> (0,null) then 1 else d.FPSalesTW end) AS decimal(14,2)),
> '1.25',
> Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW
> in(0,null) then 1 else e.MDSalesTW end) AS varchar),
> (a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null)
> then 1 else d.FPSalesMTD end,
> (c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null)
> then 1 else e.MDSalesMTD end,
> Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
> convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ
> in(0,null) then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
> (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then
> 1 else e.MDSalesTQ end,
> (a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null)
> then 1 else d.FPSalesSTD end,
> --(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null)
> then 1 else e.MDSalesSTD end,
> '1.37',
> (a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then
> 1 else d.FPSalesTY end,
> (c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then
> 1 else e.MDSalesTY end
>
> FROM #HIERARCHY b
> LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND
> b.Department = a.Department
> LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND
> b.Department = c.Department
> LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND
> b.Department = d.Department
> LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND
> b.Department = e.Department
> ORDER BY
> b.Div,
> b.Dept
>
> as you can see, this is not the hottest proc in the world, but
> support sucks sometimes :) anyhow, when this insert statement runs,
> It will round off to whole numbers where you see math in the fields.
> Where you see '1.25' and '1.37' they insert fine as a decimal(14,2)
> when you select from the table. I have tryed converting and casting
> the entire field like :
> Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
> e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
> cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ
> in(0,null) then 1 else e.MDSalesTQ end AS Decimal(14,2)
> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
> Any IDEAS? I'm really with this one.
> Robert H
Don't use integer type values in the calculation unless you want an
integer result. Make sure all integer values are first converted/cast to
decimal (14, 2) first (you can do this inline). If it's a hard-coded
value, then use "0.00" instead of "0".
Also, the TRUNCATE TABLE operation can only be run by system
administrators, table owner, db_owner, and db_ddladmin. So you won't be
able to grant execute on the procedure to users without those rights.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Thanks Guys ! I now know a little more about sql precision! The solution
to the problem based on what you guys have said is to convert
each field to a real data type. This solved all problems. that truncate
table was allready in the proc, and was in production so Someone gave it the
proper access.
Thanks!
Robert H
"Robert H" <thestripe@.yahoo_spamno.com> wrote in message
news:%23KHG44LrFHA.2588@.tk2msftngp13.phx.gbl...
> Hello. I am extending an existing proc and encountering a strange issue. I
> hope someone can shed some light on this. Here's my problem :
> insert into : table with decimal(14,2) columns ( where you see math )
> sql statement :
> TRUNCATE TABLE dbo.tbl_met_wos
> INSERT INTO dbo.tbl_met_wos
> SELECT
> b.TopDivision as Division,
> b.TopDivisionName as DivisionName,
> b.Div as Department,
> b.DivName as DepartmentName,
> b.Dept as Class,
> b.DeptName as ClassName,
> a.fponhandTW,
> a.fponhandMTD,
> a.fponhandTQ,
> a.fponhandTS,
> a.fponhandTY,
> c.mdonhandTW,
> c.mdonhandMTD,
> c.mdonhandTQ,
> c.mdonhandTS, --STD
> c.mdonhandTY,
> d.FPSalesTW,
> d.FPSalesMTD,
> d.FPSalesTQ,
> d.FPSalesSTD,
> d.FPSalesTY,
> e.MDSalesTW,
> e.MDSalesMTD,
> e.MDSalesTQ,
> e.MDSalesSTD,
> e.MDSalesTY,
> --RH Added.
> --cast( ((a.fponhandTW + d.FPSalesTW) / case when d.FPSalesTW in
> (0,null) then 1 else d.FPSalesTW end) AS decimal(14,2)),
> '1.25',
> Cast( ((c.mdonhandTW + e.MDSalesTW) / case when e.MDSalesTW in(0,null)
> then 1 else e.MDSalesTW end) AS varchar),
> (a.fponhandMTD + d.FPSalesMTD) / case when d.FPSalesMTD in(0,null) then 1
> else d.FPSalesMTD end,
> (c.mdonhandMTD + e.MDSalesMTD) / case when e.MDSalesMTD in(0,null) then 1
> else e.MDSalesMTD end,
> Convert(varchar(20), (convert(decimal(14,2),a.fponhandTQ) +
> convert(decimal(14,2),d.FPSalesTQ)) / case when d.FPSalesTQ in(0,null)
> then 1 else convert(decimal(14,2),d.FPSalesTQ) end),
> (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null) then 1
> else e.MDSalesTQ end,
> (a.fponhandTS + d.FPSalesSTD) / case when d.FPSalesSTD in(0,null) then 1
> else d.FPSalesSTD end,
> --(c.mdonhandTS + e.MDSalesSTD) / case when e.MDSalesSTD in(0,null) then
> 1 else e.MDSalesSTD end,
> '1.37',
> (a.fponhandTY + d.FPSalesTY) / case when d.FPSalesTY in(0,null) then 1
> else d.FPSalesTY end,
> (c.mdonhandTY + e.MDSalesTY) / case when e.MDSalesTY in(0,null) then 1
> else e.MDSalesTY end
>
> FROM #HIERARCHY b
> LEFT OUTER JOIN #FPONHAND a ON b.Division = a.Division AND b.Department =
> a.Department
> LEFT OUTER JOIN #MDONHAND c ON b.Division = c.Division AND b.Department =
> c.Department
> LEFT OUTER JOIN #FULLPRICE d ON b.Division = d.Division AND b.Department
> = d.Department
> LEFT OUTER JOIN #MARKDOWN e ON b.Division = e.Division AND b.Department =
> e.Department
> ORDER BY
> b.Div,
> b.Dept
>
> as you can see, this is not the hottest proc in the world, but support
> sucks sometimes :) anyhow, when this insert statement runs, It will round
> off to whole numbers where you see math in the fields. Where you see
> '1.25' and '1.37' they insert fine as a decimal(14,2) when you select from
> the table. I have tryed converting and casting the entire field like :
> Convert(Decimal(14,2), (c.mdonhandTQ + e.MDSalesTQ) / case when
> e.MDSalesTQ in(0,null) then 1 else e.MDSalesTQ end) --AND
> cast( (c.mdonhandTQ + e.MDSalesTQ) / case when e.MDSalesTQ in(0,null)
> then 1 else e.MDSalesTQ end AS Decimal(14,2)
> but it still rounds off to a whole number. if it were trying to insert
> 100.65 I get 101.00 when I select from the table.
> Any IDEAS? I'm really with this one.
> Robert H
>
>|||Robert H wrote:
> Thanks Guys ! I now know a little more about sql precision! The
> solution to the problem based on what you guys have said is to convert
> each field to a real data type. This solved all problems. that
> truncate table was allready in the proc, and was in production so
> Someone gave it the proper access.
No. Not a REAL/FLOAT data type. Use DECIMAL/NUMERIC instead.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:eySIpGYrFHA.1028@.TK2MSFTNGP12.phx.gbl...
> Robert H wrote:
> No. Not a REAL/FLOAT data type. Use DECIMAL/NUMERIC instead.
>
Actually, using floating point in a calculation is a very reasonable thing
to to. It frees you from having to manually adjust the precision and scale
after each operation. At the end you can round off and convert back to a
decimal type, knowing that th accumulation of error in the calculation was
minimized by the floating point arithmetic.
David