August 21, 2014 at 12:34 pm
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?
August 21, 2014 at 12:50 pm
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
August 21, 2014 at 12:59 pm
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
Consultant - Straight Path Solutions
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
August 21, 2014 at 10:25 pm
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;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply