| Author |
 Topic  |
|
|
salmab
Starting Member
3 Posts |
 Posted - 01/15/2013 : 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
India
52326 Posts |
Posted - 01/15/2013 : 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 - 01/15/2013 : 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 - 01/15/2013 : 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 - 01/15/2013 : 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
India
52326 Posts |
Posted - 01/16/2013 : 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
Flowing Fount of Yak Knowledge
United Kingdom
2179 Posts |
Posted - 01/17/2013 : 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 |
|
|
| |
Topic |
|
Getting value from notepad