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
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 12:47 PM
Points: 510, Visits: 598
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
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 @ 10:00 PM
Points: 774, Visits: 5,000
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: Yesterday @ 12:10 PM
Points: 11,322, Visits: 13,115
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:00 AM
Points: 2,993, Visits: 2,590
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