1、建表语句:
CREATE TABLE invheader ( invid INT IDENTITY(1, 1) NOT NULL, invdate DATETIME NOT NULL, client_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL DEFAULT '0.00', tax DECIMAL(10, 2) NOT NULL DEFAULT '0.00', total DECIMAL(10, 2) NOT NULL DEFAULT '0.00', note CHAR(100) DEFAULT NULL, PRIMARY KEY (invid) );
2、插入数据:
insert后面接由多个union连接的select语句。
insert into tempdb.dbo.invheader select '2010-01-01', 2045, 103.98, 45.34, 149.32, 'This is record 1' union select '2010-01-02', 2046, 104.98, 46.34, 151.32, 'This is record 2' union select '2010-01-03', 2047, 105.98, 47.34, 153.32, 'This is record 3' union select '2010-01-04', 2048, 106.98, 48.34, 155.32, 'This is record 4' union select '2010-01-05', 2049, 107.98, 49.34, 157.32, 'This is record 5' union select '2010-01-06', 2050, 108.98, 50.34, 159.32, 'This is record 6' union select '2010-01-07', 2051, 109.98, 51.34, 161.32, 'This is record 7' union select '2010-01-08', 2052, 110.98, 52.34, 163.32, 'This is record 8' union select '2010-01-09', 2053, 111.98, 53.34, 165.32, 'This is record 9' union select '2010-01-10', 2054, 112.98, 54.34, 167.32, 'This is record 10' union select '2010-01-11', 2055, 113.98, 55.34, 169.32, 'This is record 11' union select '2010-01-12', 2056, 114.98, 56.34, 171.32, 'This is record 12' union select '2010-01-13', 2057, 115.98, 57.34, 173.32, 'This is record 13' union select '2010-01-14', 2058, 116.98, 58.34, 175.32, 'This is record 14' union select '2010-01-15', 2059, 117.98, 59.34, 177.32, 'This is record 15' union select '2010-01-16', 2060, 118.98, 60.34, 179.32, 'This is record 16' union select '2010-01-17', 2061, 119.98, 61.34, 181.32, 'This is record 17' union select '2010-01-18', 2062, 120.98, 62.34, 183.32, 'This is record 18' union select '2010-01-19', 2063, 121.98, 63.34, 185.32, 'This is record 19' union select '2010-01-20', 2064, 122.98, 64.34, 187.32, 'This is record 20' union select '2010-01-21', 2065, 123.98, 65.34, 189.32, 'This is record 21' union select '2010-01-22', 2066, 124.98, 66.34, 191.32, 'This is record 22' union select '2010-01-23', 2067, 125.98, 67.34, 193.32, 'This is record 23' union select '2010-01-24', 2068, 126.98, 68.34, 195.32, 'This is record 24' union select '2010-01-25', 2069, 127.98, 69.34, 197.32, 'This is record 25' union select '2010-01-26', 2070, 128.98, 70.34, 199.32, 'This is record 26' union select '2010-01-27', 2071, 129.98, 71.34, 201.32, 'This is record 27' union select '2010-01-28', 2072, 130.98, 72.34, 203.32, 'This is record 28' union select '2010-01-29', 2073, 131.98, 73.34, 205.32, 'This is record 29' union select '2010-01-30', 2074, 132.98, 74.34, 207.32, 'This is record 30' union select '2010-01-31', 2075, 133.98, 75.34, 209.32, 'This is record 31' union select '2010-02-01', 2076, 134.98, 76.34, 211.32, 'This is record 32' union select '2010-02-02', 2077, 135.98, 77.34, 213.32, 'This is record 33' union select '2010-02-03', 2078, 136.98, 78.34, 215.32, 'This is record 34' union select '2010-02-04', 2079, 137.98, 79.34, 217.32, 'This is record 35' union select '2010-02-05', 2080, 138.98, 80.34, 219.32, 'This is record 36' union select '2010-02-06', 2081, 139.98, 81.34, 221.32, 'This is record 37' union select '2010-02-07', 2082, 140.98, 82.34, 223.32, 'This is record 38' union select '2010-02-08', 2083, 141.98, 83.34, 225.32, 'This is record 39' union select '2010-02-09', 2084, 142.98, 84.34, 227.32, 'This is record 40' union select '2010-02-10', 2085, 143.98, 85.34, 229.32, 'This is record 41' union select '2010-02-11', 2086, 144.98, 86.34, 231.32, 'This is record 42' union select '2010-02-12', 2087, 145.98, 87.34, 233.32, 'This is record 43' union select '2010-02-13', 2088, 146.98, 88.34, 235.32, 'This is record 44' union select '2010-02-14', 2089, 147.98, 89.34, 237.32, 'This is record 45'