4

In MICROSOFT SQL SERVER have the following table:

CREATE TABLE [T1]
(
    [ID] int IDENTITY (1, 1) NOT NULL,
    [col1] int NOT NULL,
    [col2] int NOT NULL,
    [col3] int NULL,
    [col4] datetime NOT NULL DEFAULT (getdate()),
    [col5] datetime NOT NULL DEFAULT (getdate())
)

I want to write an insert statement that select 2 columns from another table and insert all the other column as NULL or default. This is what I have tried so far (but it doesn't work):

    INSERT INTO [T1] ([col1],[col2], [COL3])
SELECT [1column],[2column],NULL
FROM [T2]

When I right click on T1 table and select open table the table has only 2 columns, even if in the columns "folder" in object explorer there are all the columns

What I want to achieve is to have in T1: in Col1 and COL2 the result of the SELECT and in COL3 NULL and in COL4 and COL5 the default value!

Improve this question
1
  • 2
    When you say the query doesn't work, it would be a lot more helpful if you would actually show us what error message the server is returning when you try to run it. Commented Apr 22, 2009 at 11:44

3 Answers 3

Reset to default
2 
INSERT INTO [T1] ([col1], [col2], [COL3])
SELECT [1column],[2column],NULL
FROM [T2]

This should work fine, just checked:

INSERT
INTO    [T1] ([col1], [col2], [col3])
SELECT  1, 2, NULL

SELECT  *
FROM    [T1]

id    col1  col2  col3  col4                     col5
---   ---   ---   ---   ---                      ---
1     1     2     NULL  2009-04-22 15:46:47.090  2009-04-22 15:46:47.090

What's the exact error you are receiving?

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

When you select to open the table and you already have a window with the table open from before, it will show you that window instead of opening another window. What you see is an old result from when the table only had two columns.

Close that window and open the table again to see the current state of the table.

Improve this answer

Comments

1

Table is wrong, query is wrong or permissions are wrong.

  1. Right click on the table name in SSMS and click refresh. Do you see 5 columns? SELECT * FROM T1 will give what you have.

  2. When you right click.. open table, click the [SQL] button on the toolbar, do you see SELECT * FROM T1?

  3. You do not have SELECT permissions on the other columns, but have INSERT (not very likely) so MetaData visibility is preventing SELECT * working.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.