Field Default Value

I'm copying MyDbTable to TempdbTable. There are 12 fields and I'm ignoring 11 of them when copying the one table to another with DTSWizard.

Once the TempdbTable is there, I'm adding 2 fields along with the one that is there already.

1) field1 int, Nullable, Default value ((3))

2) field2 tinyint, Nullable, Default value ((1))

The reason why I'm creating these two fields and giving them a value so they will match up when I import TempdbTable to MyDb2ndTable.

The problem I'm having is... field1 and field2 have a Null value intead of the value I'm giving them.

I'm new at this, so maybe the cause of the problem is obvious, but if anyone can help me I'd really appreciate it.

Thanks,

Bill

When you add the new columns, you MUST make them NOT NULL if you wish to set a DEFAULT value and assign that default value to the existing rows.

ALTER TABLE #MyTable
ADD MyNewColumn varchar(30) NOT NULL DEFAULT 'N/A'

|||

Thanks for responding Arnie.

I Ran This...

ALTER TABLE #xlaANLsubscribers
ADD listid int NOT NULL DEFAULT '3'
ADD isconfirmed tinyint NOT NULL DEFAULT '1'

I Got this Error...

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'isconfirmed'.

When I added one field only I got something like...

The table doesn't exist or you don't have permission.

Thanks,

Bill

|||

ALTER TABLE #xlaANLsubscribers
ADD listid int NOT NULL DEFAULT '3',
isconfirmed tinyint NOT NULL DEFAULT '1'

(without the additional ADD)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens,

This it the error I'm getting when running that, and the table is definitely there.

Msg 4902, Level 16, State 1, Line 1

Cannot find the object "#xlaANLsubscribers" because it does not exist or you do not have permissions.

Thanks,

Bill

|||Was the #Temp table created by the same user in the same session/connection?|||

Thanks Arnie,

I imported the records into the table of the database with DTSWizard authenicated in Windows. I then copied this table to Temp with the same authenication, but I've been running your script and doing the editing of the Temp table in SQL authenication. So I tried running your script and editing in Windows authenication and the same error happened. How that's not to confusing.

Thanks,

Bill

|||Unless you specify the ## as a prefix instead of a single #, the table is only available to the session where it was created in. Even other connected session might have no access to the table.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||I'm getting the same error even when I specify ## as the prefix of the table.|||

Bill,

Does this code execute without error.

Code Snippet

SET NOCOUNT ON

CREATE TABLE #MyTable
( RowID int IDENTITY,
Name varchar(20)
)

INSERT INTO #MyTable VALUES ( 'Bill' )

SELECT *
FROM #MyTable

ALTER TABLE #MyTable
ADD Address varchar(30) NOT NULL DEFAULT 'N/A'

SELECT *
FROM #MyTable

DROP TABLE #MyTable

|||

Arnie,

No errors, it worked perfectly.

|||Then I assume that your executing user is another one than the one which was uses to execute Arnies script, right ? Try to create explicitly dbo.#Something and select also with the full qualified name.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

I can only suggest walking back throught the rest of your code (all code that touches the #xlaANLsubscribers table), and verify that there isn't a DELETE TABLE stuck in there somewhere...

If you are creating the #Temp table, and populating it in the same session, it 'should' be there. So I'm baffled at the moment and will have to give this some thought...

|||

Jens,

I thought that schema/owner was ignored in TempDb...

|||I'm using DTSWizard to copy the table from the Main Database to Temp... am I wrong in doing it that way?