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 within a function

CREATE FUNCTION dbo.uf_GetStateID ( @.Abbr char(2) )
RETURNS int AS
BEGIN
DECLARE @.StateID int
SET @.Abbr = UPPER(ISNULL( @.Abbr, '' ))
SET @.StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @.Abbr )
IF ( @.StateID is null ) begin
INSERT into dbo.States( strAbbr, strName ) VALUES( @.Abbr, @.Abbr )
SET @.StateID = CASE
WHEN @.@.error = 0 THEN @.@.IDENTITY
ELSE -1 END
END
RETURN ( @.StateID )
END
CREATE FUNCTION dbo.uf_GetStateID ( @.Abbr char(2) )
RETURNS int AS
BEGIN
DECLARE @.StateID int
SET @.Abbr = UPPER(ISNULL( @.Abbr, '' ))
SET @.StateID = ( SELECT MIN(lngStateID) FROM dbo.States where strAbbr = @.Abbr )
IF ( @.StateID is null ) begin
INSERT into dbo.States( strAbbr, strName ) VALUES( @.Abbr, @.Abbr )
SET @.StateID = CASE
WHEN @.@.error = 0 THEN @.@.IDENTITY
ELSE -1 END
END
RETURN ( @.StateID )
END

I m getting error at the Insert statement, it says error 443, invalid use of insert within a function,

Cann we use insert in a function, if we cann, what is the alternative to insert the values?
do help me asap.Try moving the "Insert" into a stored procedure and then "exec procedure" from your function. Other solution would be to transform your function in a stored procedure by itself|||Did you look at BOL?

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.

Control-of-Flow statements.

DECLARE statements defining data variables and cursors that are local to the function.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

INSERT, UPDATE, and DELETE statements modifying table variables local to the function.

EXECUTE statements calling an extended stored procedures.

And why are you define the same udf twice...and why isn't this a sproc?

INSERT TRUNCATION

Hi,
Thanks for all responces.
I am using insert to populate a table and I need to truncate a 50 [char]
length field to a 20 [Char] lenth field in the insert.
Any recomemdations?
Again thanks
George
You can explicitly CAST as desired. For example:
INSERT INTO MyTable (MyShorterColumn)
SELECT CAST(MyLongerColumn AS char(20))
Hope this helps.
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thanks for all responces.
> I am using insert to populate a table and I need to truncate a 50 [char]
> length field to a 20 [Char] lenth field in the insert.
> Any recomemdations?
> Again thanks
> George
>
|||I think that is the syntax I am looking for, will try it and get back to
you.
Thanks.
George
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>
|||PERFECT! Mutiple truncations in one line and it works perfect.
THANKS
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>
|||I'm glad it helped you out.
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:%23slSqvUoEHA.3252@.TK2MSFTNGP14.phx.gbl...
> PERFECT! Mutiple truncations in one line and it works perfect.
> THANKS
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
>
sql

INSERT TRUNCATION

Hi,
Thanks for all responces.
I am using insert to populate a table and I need to truncate a 50 [char]
length field to a 20 [Char] lenth field in the insert.
Any recomemdations?
Again thanks
GeorgeYou can explicitly CAST as desired. For example:
INSERT INTO MyTable (MyShorterColumn)
SELECT CAST(MyLongerColumn AS char(20))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thanks for all responces.
> I am using insert to populate a table and I need to truncate a 50 [char]
> length field to a 20 [Char] lenth field in the insert.
> Any recomemdations?
> Again thanks
> George
>|||I think that is the syntax I am looking for, will try it and get back to
you.
Thanks.
George
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Thanks for all responces.
>> I am using insert to populate a table and I need to truncate a 50 [char]
>> length field to a 20 [Char] lenth field in the insert.
>> Any recomemdations?
>> Again thanks
>> George
>|||PERFECT! Mutiple truncations in one line and it works perfect.
THANKS
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
> You can explicitly CAST as desired. For example:
> INSERT INTO MyTable (MyShorterColumn)
> SELECT CAST(MyLongerColumn AS char(20))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "george collins" <george@.nospan.com> wrote in message
> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Thanks for all responces.
>> I am using insert to populate a table and I need to truncate a 50 [char]
>> length field to a 20 [Char] lenth field in the insert.
>> Any recomemdations?
>> Again thanks
>> George
>|||I'm glad it helped you out.
--
Dan Guzman
SQL Server MVP
"george collins" <george@.nospan.com> wrote in message
news:%23slSqvUoEHA.3252@.TK2MSFTNGP14.phx.gbl...
> PERFECT! Mutiple truncations in one line and it works perfect.
> THANKS
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23GUkRfQoEHA.1160@.tk2msftngp13.phx.gbl...
>> You can explicitly CAST as desired. For example:
>> INSERT INTO MyTable (MyShorterColumn)
>> SELECT CAST(MyLongerColumn AS char(20))
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "george collins" <george@.nospan.com> wrote in message
>> news:eyQCuYQoEHA.3460@.TK2MSFTNGP10.phx.gbl...
>> Hi,
>> Thanks for all responces.
>> I am using insert to populate a table and I need to truncate a 50 [char]
>> length field to a 20 [Char] lenth field in the insert.
>> Any recomemdations?
>> Again thanks
>> George
>>
>

insert random records..HELP!

Once again - My table should consist of 100 new records for a field MobilePhone(of char type) and last 5 digits should be randomly choosed (should be like this: +381randomno1randomno2.. etc.(example: +38156465, where '+' sign makes it char type and digits after +381 are randomly choosed. :confused: Anyone knows how to solve this...PLEASE?No need for replies guys..ive figured this out ..thanx, anyway :)|||No need for replies guys..ive figured this out ..thanx, anyway :)
Would it not be a good idea to post your solution? Some may benifit from your solution who may have a similar problem. Also, let's not forget the intelligence level in this community (excluding me of course); they may have suggestions to fine tune your solution!

Just my oppinion. Every question posted should be accompanied by a solution I think!

Mike B|||[QUOTE=MikeB_2k4]Would it not be a good idea to post your solution? Some may benifit from your solution who may have a similar problem. Also, let's not forget the intelligence level in this community (excluding me of course); they may have suggestions to fine tune your solution!

Just my oppinion. Every question posted should be accompanied by a solution I think!

Im really sorry..you are so right.Ok,here is the solution:

create table #randomphonenumbers( nmbr char(10) primary key )

declare @.digits table(nr char(1))
insert @.digits(nr) select '0' union select '2' union select '4' union select '6' union select '8'
insert @.digits(nr) select nr+1 from @.digits -- implicit conversion

insert #randomphonenumbers( nmbr )
select top 100 '+' + '388' + a.nr+b.nr+c.nr+d.nr+e.nr
from @.digits a cross join @.digits b cross join @.digits c cross join @.digits d cross join @.digits e
where e.nr > 0
order by newid()

select * from #randomphonenumbers

drop table #randomphonenumbers

Bye now :)