Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Adding columns to existing table Expand / Collapse
Author
Message
Posted Thursday, August 21, 2014 12:34 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, November 20, 2014 3:24 PM
Points: 640, Visits: 868
Hi All,

I have a table. I want to add 2 date columns. One when we are inserting any record it will show and another whenever the record updated to record that.

I want to insert dummy data for the previous dates. How to insert those dummy dates in batch wise?
Post #1605963
Posted Thursday, August 21, 2014 12:50 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:56 PM
Points: 887, Visits: 5,678
You would have to deal with the non-dummy dates first. (Or if you can do it with a join?)

Then you could just use something like

UPDATE <tablename>
SET someDate = '01-01-1900'
WHERE someDate IS NULL
Post #1605967
Posted Thursday, August 21, 2014 12:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 1:42 PM
Points: 10,340, Visits: 13,341
If you create the new columns as NOT NULL with a DEFAULT constraint then the columns will be populated with whatever you put in for the DEFAULT. Like this:

USE tempdb;
GO

CREATE TABLE test (c INT NOT NULL);

GO
INSERT INTO dbo.test
(c)
VALUES
(0 -- c - int
);

GO

ALTER TABLE dbo.test
ADD create_date SMALLDATETIME NOT NULL DEFAULT '1900-01-01';

GO

SELECT
*
FROM
dbo.test AS T;

GO

DROP TABLE dbo.test;





Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1605971
Posted Thursday, August 21, 2014 10:25 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:40 PM
Points: 3,062, Visits: 2,673
If you add "WITH VALUES" to your ALTER TABLE ADD statement, it will put the default value in regardless of the NULL/NOT NULL setting...
USE tempdb;
GO

CREATE TABLE test (c INT NOT NULL);

GO
INSERT INTO dbo.test
(c)
VALUES
(0 -- c - int
);

GO

ALTER TABLE dbo.test
ADD create_date SMALLDATETIME NULL DEFAULT '1900-01-01' WITH VALUES;

GO

SELECT
*
FROM
dbo.test AS T;

GO

DROP TABLE dbo.test;



Post #1606100
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse