SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 appending from flat file into table with PK
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lcsgeek
Starting Member

USA
38 Posts

Posted - 01/17/2013 :  13:35:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3715 Posts

Posted - 01/17/2013 :  13:47:32  Show Profile  Reply with Quote
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

USA
38 Posts

Posted - 01/17/2013 :  14:17:42  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3715 Posts

Posted - 01/17/2013 :  18:00:17  Show Profile  Reply with Quote
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

USA
38 Posts

Posted - 01/18/2013 :  13:18:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3715 Posts

Posted - 01/18/2013 :  13:28:44  Show Profile  Reply with Quote
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

USA
38 Posts

Posted - 01/18/2013 :  14:06:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3715 Posts

Posted - 01/18/2013 :  14:45:55  Show Profile  Reply with Quote
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

USA
38 Posts

Posted - 01/18/2013 :  16:42:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3715 Posts

Posted - 01/19/2013 :  06:08:36  Show Profile  Reply with Quote
You are very welcome - glad to be of help!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000