Prev: CREATE AGGREGATE failed because type 'Concatenate' does not conform toUDAGG specification due to method 'Accumulate'.
Next: CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Accumulate'.
From: Toby on 13 Nov 2009 11:23
Here is a DDL that I'm using for trying to figure out the issue


CREATE TABLE [dbo].[Currency](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Currency] [varchar](3) NOT NULL,
CONSTRAINT [PK_Currency] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Currency] ADD CONSTRAINT [DF_Currency_Currency] DEFAULT
('USD') FOR [Currency]
GO


"Simon Whale" wrote:

> remove the where clause so it looks like this
>
> SELECT *
> INTO newtablename
> FROM oldtablename
>
> "Toby" <Toby(a)discussions.microsoft.com> wrote in message
> news:943C032D-7434-4BB9-B3A3-709F10D29633(a)microsoft.com...
> >I know that using something like this is the quickest way to create a
> >table:
> > SELECT * INTO newtablename FROM oldtablename WHERE 1>2
> >
> > However I need for the new table to have all the default values that have
> > been declared in the old table and the select statement above does not do
> > that. How can I keep the default values without having to run an alter
> > statement that adds them back in.
> >
> > Thanks in advance
>
>
> .
>
From: Toby on 13 Nov 2009 11:23
That didn't work



"Simon Whale" wrote:

> remove the where clause so it looks like this
>
> SELECT *
> INTO newtablename
> FROM oldtablename
>
> "Toby" <Toby(a)discussions.microsoft.com> wrote in message
> news:943C032D-7434-4BB9-B3A3-709F10D29633(a)microsoft.com...
> >I know that using something like this is the quickest way to create a
> >table:
> > SELECT * INTO newtablename FROM oldtablename WHERE 1>2
> >
> > However I need for the new table to have all the default values that have
> > been declared in the old table and the select statement above does not do
> > that. How can I keep the default values without having to run an alter
> > statement that adds them back in.
> >
> > Thanks in advance
>
>
> .
>
From: Scott Morris on 13 Nov 2009 12:14
"Toby" <Toby(a)discussions.microsoft.com> wrote in message
news:7034883C-05AE-4E2C-9788-7F49B6093439(a)microsoft.com...
> Here is a DDL that I'm using for trying to figure out the issue
>
>
> CREATE TABLE [dbo].[Currency](
> [ID] [int] IDENTITY(1,1) NOT NULL,
> [Currency] [varchar](3) NOT NULL,
> CONSTRAINT [PK_Currency] PRIMARY KEY CLUSTERED
> (
> [ID] ASC
> )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
> OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> ) ON [PRIMARY]

Have you considered:
http://www.iso.org/iso/support/currency_codes_list-1.htm

If you need to know currency, you usually need to know country. And why in
the world you need a default for the only "real" column of this table - and
a column that is the true unique identifier - escapes me.


From: Toby on 13 Nov 2009 14:23
I was afraid of needing the alter statement but I guess I have no choice..

Thanks to everyone for there help!!

"Bob Barrows" wrote:

> Toby wrote:
> > I know that using something like this is the quickest way to create a
> > table: SELECT * INTO newtablename FROM oldtablename WHERE 1>2
> >
> > However I need for the new table to have all the default values that
> > have been declared in the old table and the select statement above
> > does not do that. How can I keep the default values without having
> > to run an alter statement that adds them back in.
> >
> I'm not really sure that is the quicest method ... maybe for the
> developer, but probably not for the database engine.
>
> The reason this might be the "quickest" is because there is no attempt
> made to determine and duplicate the constraints, indexes and defaults in
> the source table.
> You need to use ALTER TABLE to add constraints and indexes to the new
> table. There is no getting around that.
>
> The good news is that you can use SSMS to generate the script needed to
> create a table and all its constraints. So do that, change the table and
> associated object names to avoid duplication, and you're good to go. If
> you need to figure out how to generate this script programmatically,
> turn on SQL Profiler while SSMS generates the script to see what you
> have to do in your program code.
>
> --
> HTH,
> Bob Barrows
>
>
> .
>
 | 
Pages: 1
Prev: CREATE AGGREGATE failed because type 'Concatenate' does not conform toUDAGG specification due to method 'Accumulate'.
Next: CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Accumulate'.