July 23, 2008 at 11:09 am
Hi All,
I have a table which has only one column "id" (int)and its become a default valued column, means it automatically insert "0" if value not inserted into it.
when I am applying insert into statements its not working....
ex....insert into table1
select
I mean to say i am unable to insert value automatically, if i am supplying a value its working properly but if not supplying its not inserting 0 automatically..but its not doing that....
clear to that, default values are not entering when i am not passing the value..then why???
give me solution now??
i am waiting today....
thanks in advance..
Milu.:)
July 23, 2008 at 12:32 pm
I assume you have other columns, correct?
You can do
create table mytable
( myid int default 0
, mychar varchar(20)
)
go
insert mytable select 1, 'A'
go
select * from mytable
go
insert mytable (mychar) values ( 'B')
go
select * from mytable
go
drop table mytable
July 23, 2008 at 12:40 pm
i am a little unsure of what you are trying to do. a default value is in place when you insert a record into a table but do not specify that column to have a value, so SQL Server will add the default value. In your case, you only have one column so you always have to supply a value in order to insert a record.
the example you proposed is not a valid sql statement, you must select something and that something is what would be entered as the value for id.
if you still want a row where the default value is entered when you do not have a value, create another column in the table of datetime for instance and then you could run the following sql:
insert into table1 (time_col)
select getdate()
hth
July 23, 2008 at 1:26 pm
Milu (7/23/2008)
Hi All,I have a table which has only one column "id" (int)and its become a default valued column, means it automatically insert "0" if value not inserted into it.
when I am applying insert into statements its not working....
ex....insert into table1
select
I mean to say i am unable to insert value automatically, if i am supplying a value its working properly but if not supplying its not inserting 0 automatically..but its not doing that....
clear to that, default values are not entering when i am not passing the value..then why???
give me solution now??
i am waiting today....
thanks in advance..
Milu.:)
I don't know exactly what problem you are having, but I am guessing that the problem you are having is because you are not specifying the columns to be inserted.
You can use:
-- uses default for column1
INSERT INTO table (column2, column3, column4) VALUES (2, 3, 4);
-- Defines default for column1
INSERT INTO table (column1, column2, column3, column4) VALUES (Default, 2, 3, 4);
-- Uses default for column1
INSERT INTO table (column2, column3, column4)
SELECT column2
,column3
,column4
FROM othertable
WHERE some_conditions;
One of the above should work for you.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 23, 2008 at 1:48 pm
I believe it goes something like this
INSERT INTO table1 DEFAULT VALUES
That is if what you say is true and you only have one column with a default value and you just want to create a bunch of them.
Why would you want to do that? With just one column, you've only got one place to use a candidate key from and putting multiple default values of the same value in it will just create duplicate records.
At the least before you do that... do this
ALTER TABLE table1 ADD id_col IDENTITY(1,1)
Now at least each record will have something that makes it unique.
July 23, 2008 at 10:43 pm
mtassin
----------------------------------------------------------------------------------------------------------------------------------------------------
At the least before you do that... do this
ALTER TABLE table1 ADD id_col IDENTITY(1,1)
Now at least each record will have something that makes it unique.
mtassin, Can you tell me what will happen, If you also add a a new column to that...?
Milu, If I am not wrong you are trying to insert default values when you don't wants to pass the values. rite??
Milu, you can achieve this without changing your table structure...
Follow this..
create table abc
(
a int default 0
)
--
select * from abc
--
insert into abc
values(default)
but here you have to pass the values as "default". hope you can easily make it... to achieve your target....
Hope this helps you...
Cheers!
Sandy.
--
July 23, 2008 at 11:11 pm
--Try this
CREATE TABLE Test(Col int DEFAULT 0)
INSERT INTO TEST DEFAULT values
SELECT Col FROM Test
Even you can use same query if Col is IDENTITY like:
CREATE TABLE Test(Col int Identity(1,1))
INSERT INTO TEST DEFAULT values
SELECT Col FROM Test
July 24, 2008 at 7:23 am
I'm still trying to figure out what you do with a single-column table that can contain a bunch of zeros, along with other values. With no other columns in the table, what do the zeros relate to?
If it was easy, everybody would be doing it!;)
July 24, 2008 at 10:44 am
Thanks to steve & all.:)
I just applied the what Sandy and Hari specified here....
Sandy thanks...so sweet of you..nice explanation....
luv's,
Milu..:)
July 30, 2008 at 3:52 am
CREATE TABLE Test(Col int DEFAULT 0)
INSERT INTO test
SELECT TOP 10 0 AS N
FROM Master.dbo.SysColumns
You can change number of records in TOP and Default value as you like and add as much records you want. Still, I dont get the purpose...
Atif Sheikh
July 30, 2008 at 4:03 am
wondering why you would need a table with a single column that contains a lot of zeros.
"Keep Trying"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply