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
 Getting value from notepad
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

salmab
Starting Member

3 Posts

Posted - 01/15/2013 :  12:47:28  Show Profile  Reply with Quote
Hi Everybody,
I have table with two columns,join_date and empno.I saved the join_date value inside the notepad and saved it in C:\join_date.txt
I'm trying to write a sql statement that will fetch the value from notepad while executing the update statement

update emp_table set join_date=(file_name) where empno='890'

WHat should I put in place of file name.
or is there another way to do this.
Pls help
(The notepad has only one single date value)

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 01/15/2013 :  12:49:26  Show Profile  Reply with Quote
if it has only date value how will you determine which value to be placed for which record. it should have empno also for relating to your table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

salmab
Starting Member

3 Posts

Posted - 01/15/2013 :  12:51:18  Show Profile  Reply with Quote
It has only one date value ie '02/03/2011'.It does not have multiple date values.In other words just one row for date column.
Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 01/15/2013 :  14:19:40  Show Profile  Reply with Quote
quote:
Originally posted by salmab

It has only one date value ie '02/03/2011'.It does not have multiple date values.In other words just one row for date column.



Hi Salmab

I'm assuming the empno is populated and is numeric, but the datetime isn't or it is but is wrong?

Your txt file should have two columns in it, the empno and the date time

I.E


1,01/02/2012
2,02/02/2012
3,03/02/2012
etc



The below should then allow you to import into a temp table

REMOVE THE DROP TABLE COMMANDS BEFORE RUNNING FOR YOUR LIVE! this is for you to see it working as a test.


USE Test
GO

SET NOCOUNT ON;

IF OBJECT_ID ('CSVImp', 'U') IS NOT NULL
	DROP TABLE CSVImp;
GO

IF OBJECT_ID ('TestTab', 'U') IS NOT NULL
	DROP TABLE TestTab;
GO


CREATE TABLE CSVImp
	(
	empno int
	,join_date datetime
	)

GO

BULK
INSERT CSVimp
FROM 'H:\csvfile.txt'
WITH
	(
	FIELDTERMINATOR = ','
	,ROWTERMINATOR = '\n'
	)
GO

PRINT 'Check values in CSVImp'
SELECT 
	* 
FROM CSVImp;
GO

CREATE TABLE Testtab
	(
	empno int
	,join_date datetime
	);

GO


INSERT INTO Testtab (empno)
VALUES (1);
INSERT INTO Testtab (empno)
VALUES (2);
INSERT INTO Testtab (empno)
VALUES (3);
INSERT INTO Testtab (empno)
VALUES (4);

PRINT ''	
PRINT 'Values in test'

SELECT
	*
FROM
	Testtab
GO


UPDATE
	Testtab
SET
	Testtab.join_date = C.join_date
FROM
	Testtab A
		JOIN 
	CSVImp C ON A.empno = C.Empno

PRINT ''
PRINT 'Check values in TestTab after update'
SELECT
	*
FROM
	TestTab;
GO
	

DROP TABLE CSVImp, Testtab;
GO




Go to Top of Page

Robowski
Posting Yak Master

101 Posts

Posted - 01/15/2013 :  14:28:58  Show Profile  Reply with Quote
It might be worth checking you don't have any duplicate values in the emp_table.empno by the way (unless you want to update the date against them all)

SELECT
empno
,COUNT(empno) AS TotalCount
FROM
emp_table
GROUP BY
empno
HAVING
COUNT (empno) > 1
GO
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 01/16/2013 :  22:39:15  Show Profile  Reply with Quote
quote:
Originally posted by salmab

It has only one date value ie '02/03/2011'.It does not have multiple date values.In other words just one row for date column.


and that needs to be replicated for all records in table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2067 Posts

Posted - 01/17/2013 :  01:49:44  Show Profile  Visit jackv's Homepage  Reply with Quote
Another option to use is powershell to read the data into an array and pass through to query

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
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.3 seconds. Powered By: Snitz Forums 2000