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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 appending from flat file into table with PK

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 table


CREATE 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 from
SELECT MAX(dateKey) FROM dimDate


SELECT 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 seasonName
FROM
tblTestScores
ORDER 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 query
SELECT 
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
)s
ORDER BY CONVERT(Date, TestStartDate,101)
You don't really need to convert the teststartdate to a string in your order by clause.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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 describing
CREATE TABLE dbo.IdentityTest(id INT NOT NULL IDENTITY(1,1), x VARCHAR(32));

-- create a test data file with 3 rows
21,abcd
22,xyz
44,eee

BULK INSERT dbo.IdentityTest
FROM 'f:\temp\Test.txt'
WITH
(
FIELDTERMINATOR =','
);
SELECT * FROM dbo.IdentityTest
-- shows this:
1 abcd
2 xyz
3 eee

TRUNCATE TABLE dbo.IdentityTest
BULK INSERT dbo.IdentityTest
FROM 'f:\temp\Test.txt'
WITH
(
FIELDTERMINATOR =',',
KEEPIDENTITY
);
SELECT * FROM dbo.IdentityTest
-- shows this
21 abcd
22 xyz
44 eee

DROP TABLE dbo.IdentityTest;
Go to Top of Page

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
Go to Top of Page

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!
Go to Top of Page
   

- Advertisement -