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
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 736, Visits: 1,023
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
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 1,073, Visits: 6,541
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: Today @ 6:24 AM
Points: 10,421, Visits: 13,603
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: Yesterday @ 10:03 PM
Points: 3,176, Visits: 2,737
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