Insert to Subset of Columns

In a sp I am populating a temp table that may have a number of null column
values. I'd like to populate the columns that will not have null values by
using an INSERT statement, and then separately UPDATE the other columns
which may have null values.
When I execute the INSERT statement to populate *only* the MemberID,
LinkText, and TargetURL columns, I get the following error:
<< Insert Error: Column name or number of supplied values does not match
table definition >>
My Question: Must I specify some value for all columns in the INSERT
statement, or is there a way to insert into a subset of the columns and then
update the rest later?
CREATE TABLE #LINKS
(
MemberID int,
LinkText varchar(100),
TargetURL varchar(100),
ImageURL varchar(150) NULL,
ActiveImageURL varchar(150) NULL,
ExpandedImageURL varchar(150) NULL,
HoverImageURL varchar(150) NULL,
ImageHeight int NULL,
ImageWidth int NULL,
)DO you mean that ? :
INSERT INTO (Column1[,COlumn2...])
VALUES (Value1[,Value2...])
--OR
INSERT INTO (Column1[,COlumn2...])
<Query>
Refer to the BOL there are some example in there.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Smithers" <A@.B.com> schrieb im Newsbeitrag
news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...
> In a sp I am populating a temp table that may have a number of null column
> values. I'd like to populate the columns that will not have null values by
> using an INSERT statement, and then separately UPDATE the other columns
> which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID,
> LinkText, and TargetURL columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match
> table definition >>
> My Question: Must I specify some value for all columns in the INSERT
> statement, or is there a way to insert into a subset of the columns and
> then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>|||You have two options. Assume table has three columns, and you don't want to
insert in the first or
third columns:
INSERT INTO tblname(first, second, third)
VALUES(default, 23, default)
INSERT INTO tblname(second)
VALUES(23)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Smithers" <A@.B.com> wrote in message news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...[col
or=darkred]
> In a sp I am populating a temp table that may have a number of null column
values. I'd like to
> populate the columns that will not have null values by using an INSERT sta
tement, and then
> separately UPDATE the other columns which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID, LinkT
ext, and TargetURL
> columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match t
able definition >>
> My Question: Must I specify some value for all columns in the INSERT state
ment, or is there a way
> to insert into a subset of the columns and then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>[/color]|||INSERT INTO #LINKS (MemberID, LinkText, TargetURL)
VALUES (<MemberID>, <LinkText>, <TargetURL> )
works.
How do you exactly populate that table, because that error message doesn't
look familiar?
Jacco Schalkwijk
SQL Server MVP
"Smithers" <A@.B.com> wrote in message
news:%23%234o0ZjWFHA.2692@.TK2MSFTNGP15.phx.gbl...
> In a sp I am populating a temp table that may have a number of null column
> values. I'd like to populate the columns that will not have null values by
> using an INSERT statement, and then separately UPDATE the other columns
> which may have null values.
> When I execute the INSERT statement to populate *only* the MemberID,
> LinkText, and TargetURL columns, I get the following error:
> << Insert Error: Column name or number of supplied values does not match
> table definition >>
> My Question: Must I specify some value for all columns in the INSERT
> statement, or is there a way to insert into a subset of the columns and
> then update the rest later?
> CREATE TABLE #LINKS
> (
> MemberID int,
> LinkText varchar(100),
> TargetURL varchar(100),
> ImageURL varchar(150) NULL,
> ActiveImageURL varchar(150) NULL,
> ExpandedImageURL varchar(150) NULL,
> HoverImageURL varchar(150) NULL,
> ImageHeight int NULL,
> ImageWidth int NULL,
> )
>|||INSERT INTO tbl
VALUES(111,222, DEFAULT, DEFAULT, N'bla-bla', DEFAULT, ....)
Message posted via http://www.webservertalk.com