Getting value from notepad - SQL Server Forums

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

Getting value from notepad

Author

Topic

salmab
Starting Member

3 Posts

Posted - 2013-01-15 : 12:47:28

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

52326 Posts

Posted - 2013-01-15 : 12:49:26

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/

salmab
Starting Member

3 Posts

Posted - 2013-01-15 : 12:51:18

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.

Robowski
Posting Yak Master

101 Posts

Posted - 2013-01-15 : 14:19:40

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

Robowski
Posting Yak Master

101 Posts

Posted - 2013-01-15 : 14:28:58

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-16 : 22:39:15

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/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-01-17 : 01:49:44

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources