Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
lcsgeek
Starting Member
38 Posts |
Posted - 2013-01-17 : 13:35:52
|
What is the best practices method for doing a bulk insert (append) into an existing table with a unique rownumber as the primary key? Example: I have the following destination tableCREATE TABLE [dbo].[dimDate]( [dateKey] [int] NOT NULL, [fullDateAK] [date] NOT NULL, [dayNumberOfWeek] [tinyint] NOT NULL, [dayNumberOfMonth] [tinyint] NOT NULL, [dayNumberOfYear] [smallint] NOT NULL, [fullDayName] [nvarchar](10) NOT NULL, [weekNumberOfYear] [tinyint] NOT NULL, [monthNumberOfYear] [tinyint] NOT NULL, [fullMonthName] [nvarchar](10) NOT NULL, [trimesterNumber] [tinyint] NOT NULL, [yearNumber] [smallint] NOT NULL, [seasonName] [nvarchar](10) NOT NULL, CONSTRAINT [PK_DimDate_dateKey] PRIMARY KEY CLUSTERED ( [dateKey] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [AK_DimDate_fullDateAK] UNIQUE NONCLUSTERED ( [dateKey] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] I want to insert the results of the query below into table above but I don't know how to handle the key column. In a perfect world I'd want this query's first column to start incrementing fromSELECT MAX(dateKey) FROM dimDateSELECT DISTINCT dontKnowWhatToDoForThisColumn AS ????? , CONVERT(Date, TestStartDate,101) AS FullDateAK , DATEPART(dw, TestStartDate) AS dayNumberOfWeek , DATEPART(d, TestStartDate) AS dayNumberOfMonth , DATEPART(dy, TestStartDate) AS dayNumberOfYear , DATENAME(w, TestStartDate) AS fullDayName , DATEPART(wk, TestStartDate) AS weekNumberOfYear , DATEPART(m, TestStartDate) AS monthNumberOfYear , DATENAME(m, TestStartDate) AS fullMonthName , CASE WHEN DATEPART(wk,TestStartDate) BETWEEN 27 AND 47 THEN 1 --first day of school + 12 weeks (trimester) back through mid year WHEN DATEPART(wk,TestStartDate) BETWEEN 11 AND 26 THEN 3 --last day of school - 12 weeks (trimester) forward through to mid year ELSE 2 END AS trimesterNumber , YEAR(TestStartDate) AS yearNumber , CASE WHEN DATEPART(wk,TestStartDate) BETWEEN 27 AND 47 THEN 'Fall' --first day of school + 12 weeks (trimester) back through mid year WHEN DATEPART(wk,TestStartDate) BETWEEN 11 AND 26 THEN 'Spring' --last day of school - 12 weeks (trimester) forward through to mid year ELSE 'Winter' END AS seasonNameFROM tblTestScoresORDER BY CONVERT(Date, TestStartDate,101) Any insight would be much appreciated. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-17 : 13:47:32
|
You can modify the code shown below. This code will start numbering at 1, you can offset it to whatever you like. If you didn't have the distinct clause in your query, you wouldn't need the subquery, you could have used the row_number function directly in your querySELECT ROW_NUMBER() OVER (ORDER BY TestStartDate) AS dontKnowWhatToDoForThisColumn, *FROM( SELECT DISTINCT CONVERT(Date, TestStartDate,101) AS FullDateAK , DATEPART(dw, TestStartDate) AS dayNumberOfWeek , DATEPART(d, TestStartDate) AS dayNumberOfMonth , DATEPART(dy, TestStartDate) AS dayNumberOfYear , DATENAME(w, TestStartDate) AS fullDayName , DATEPART(wk, TestStartDate) AS weekNumberOfYear , DATEPART(m, TestStartDate) AS monthNumberOfYear , DATENAME(m, TestStartDate) AS fullMonthName , CASE WHEN DATEPART(wk,TestStartDate) BETWEEN 27 AND 47 THEN 1 --first day of school + 12 weeks (trimester) back through mid year WHEN DATEPART(wk,TestStartDate) BETWEEN 11 AND 26 THEN 3 --last day of school - 12 weeks (trimester) forward through to mid year ELSE 2 END AS trimesterNumber , YEAR(TestStartDate) AS yearNumber , CASE WHEN DATEPART(wk,TestStartDate) BETWEEN 27 AND 47 THEN 'Fall' --first day of school + 12 weeks (trimester) back through mid year WHEN DATEPART(wk,TestStartDate) BETWEEN 11 AND 26 THEN 'Spring' --last day of school - 12 weeks (trimester) forward through to mid year ELSE 'Winter' END AS seasonName FROM tblTestScores)sORDER BY CONVERT(Date, TestStartDate,101) You don't really need to convert the teststartdate to a string in your order by clause. |
|
|
lcsgeek
Starting Member
38 Posts |
Posted - 2013-01-17 : 14:17:42
|
Worked perfectly!!! Thanks so much James. Would you mind expounding on the following? quote: This code will start numbering at 1, you can offset it to whatever you like.
I don't know where to start in setting a starting value for the ROW_NUMBER function. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-17 : 18:00:17
|
I was responding to what you said about the starting number being something other than one. So you might do something like this:DECLARE @LastDateKey INT;SELECT @LastDateKey = COALESCE(MAX(dateKey),0) FROM dimDate;SELECT ROW_NUMBER() OVER (ORDER BY TestStartDate) + @LastDateKey AS dontKnowWhatToDoForThisColumn, *FROM(............etc When you need an ever increasing number in a column, very often people make that an identity column. You may want to investigate that. |
|
|
lcsgeek
Starting Member
38 Posts |
Posted - 2013-01-18 : 13:18:44
|
Excellent!! Thanks again. One last question concerning your final thought; How would identity = yes change my INSERT INTO query? Would I simply insert a NULL into that column and let the identity property handle the actual value inserted? I have control over the table and it is a new database so I could turn up identity if that is recommended. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-18 : 13:28:44
|
Identity property is set on a column. Browse through the documentation and examples on the MSDN page here: http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx |
|
|
lcsgeek
Starting Member
38 Posts |
Posted - 2013-01-18 : 14:06:21
|
I'm familiar with setting up the identity column what I'm not sure of is how it functions when a bulk insert is performed against the table. Do I have to programmatically keep track of the column values on the bulk insert or does SQL Server automatically handle the identity values? If SQL Server handles it automatically then what to I use as an INSERT value when bulk inserting? Thank you so much for your excellent help. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-18 : 14:45:55
|
With Bulk Insert, for a column with the identity property, you have the choice of keeping the values you provide or, letting SQL Server generate the identity numbers. Even in the latter case, you should provide some placeholder values in your file. The example below perhaps would make it clearer what I am describingCREATE TABLE dbo.IdentityTest(id INT NOT NULL IDENTITY(1,1), x VARCHAR(32));-- create a test data file with 3 rows21,abcd22,xyz44,eeeBULK INSERT dbo.IdentityTest FROM 'f:\temp\Test.txt' WITH ( FIELDTERMINATOR =',' );SELECT * FROM dbo.IdentityTest-- shows this:1 abcd2 xyz3 eee TRUNCATE TABLE dbo.IdentityTestBULK INSERT dbo.IdentityTestFROM 'f:\temp\Test.txt'WITH ( FIELDTERMINATOR =',', KEEPIDENTITY );SELECT * FROM dbo.IdentityTest-- shows this21 abcd22 xyz44 eeeDROP TABLE dbo.IdentityTest; |
|
|
lcsgeek
Starting Member
38 Posts |
Posted - 2013-01-18 : 16:42:02
|
Thank you very much James, I really appreciate your taking the time to share your knowledge. This information is very useful and practical. best wishes |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-01-19 : 06:08:36
|
You are very welcome - glad to be of help! |
|
|
|
|
|
|
|