How to insert date in empty table?-CodePudding

I have table with empty columns with a column StartDate in which I need to insert a date in the format of 2001-01-01 00:00:00.000. I should insert 77 such dates, each one in new row. How should I do this?

CodePudding user response：

Is it the same date you want to insert 77 times? In that case, use something like:

INSERT INTO dbo.YourTable (StartDate)
VALUES ('20010101');
GO 77;

Trouble is: you also have other columns that are NOT NULL - so this won't work - you'll need to provide values for those as well (unless you have default constraints on those columns that adds some default value for them):

INSERT INTO dbo.YourTable (StartDate, ProductName, SupplierId, CategoryName)
VALUES ('20010101', 'Dummy', -1, 'Dummy');
GO 77;

If you need to store only the date part (no time), then I'd strongly suggest using the DATE datatype for your column StartDate (instead of DateTime).

CodePudding user response：

As an alternative, since you know how many of each value you need, you could do something like this:

DECLARE @src table (date date, num int);
INSERT @src(date, num)
  VALUES('20010101', 10), ('20010102', 5);--, etc.;


DECLARE @max int = (SELECT MAX(num) FROM @src);

;WITH x AS
(
  SELECT n = 1 UNION ALL
  SELECT n   1 FROM x WHERE n < @max
)
--INSERT dbo.somewhere(StartDate) 
SELECT s.date FROM x
  CROSS JOIN @src AS s
  WHERE x.n <= s.num
  OPTION (MAXRECURSION 0);

Or more manually with:

INSERT dbo.somewhere(StartDate) VALUES

-- 10 of these:
('20010101'), ('20010101'), ('20010101'), ('20010101'), ('20010101'), 
('20010101'), ('20010101'), ('20010101'), ('20010101'), ('20010101'), 

-- 5 of these: 
('20010102'), ('20010102'), ('20010102'), ('20010102'), ('20010102'); -- etc.