Comments by TenmanS14

TenmanS14 13-May-16 5:27am View

by the look of that you just need to be creating 3 tables to import into,

applicant which will hold applicantid and other stuff if you have it
school which will hold schoolid and name and other stuff

and a 3rd table to join them which will hold applicantid and schoolid.

to import from that CSV I'd import it into a temp table and then run through that with a counter to add records to the other tables.

TenmanS14 25-Jan-16 6:41am View

maybe look at SQLite...

https://www.sqlite.org/about.html

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.

TenmanS14 18-Jan-16 11:29am View

at a guess you want to create a stored procedure...

https://msdn.microsoft.com/en-GB/library/ms345415.aspx

TenmanS14 18-Jan-16 6:41am View

LOL, apologies for that, had been in the pub for lunch before I wrote it... on the right lines though I think, I suspect you could achieve what you want with a self join..

TenmanS14 15-Jan-16 11:48am View

is there a value which will be common between them and unique for each final record, rather than them just being 2 different weeks of data? i.e. could you only have one memberno per week or one of each unit per week?

INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price, week2Usage, week2Price)
select InvD1.MEMBERNO, InvD1.ITEMCODE, InvD1.DESCRIPTION, InvD1.QTYSHIPPED, InvD1.PRICE, InvD2.qtyshipped, InvD2.price
FROM INVOICEDETAIL as InvD1
join INVOICEDETAIL as InvD2 on InvD1.unit = InvD2.unit
WHERE InvD1.UNIT=@Unit
AND (INVOICEDATE BETWEEN @BegDate AND @Week1End)

TenmanS14 15-Jan-16 10:34am View

the result of a select statement isn't NULL its just an empty result set, if thats supposed to be part of a SELECT statement, have a look at using CASE.

https://msdn.microsoft.com/en-gb/library/ms181765.aspx

select blah1, blah2, CASE (Select count(ItemCode)
From PlatypusUnitMapping
where Unit='BIG RED ONE'
and MemberNo='42'
and MemberItemCode= '314IMPie'
and ItemCode in (Select ItemCode from UnitProducts where Unit='BIG RED ONE'))
WHEN 0 THEN 'X'
ELSE itemcode
END,

TenmanS14 11-Jan-16 9:36am View

doh... didn't read question properly

TenmanS14 11-Jan-16 4:20am View

could use mySQL, or PostgreSQL... mySQL works well in windows if you can't host the DB on an alternate OS...

TenmanS14 25-Jun-15 9:21am View

oops... mySQL, assuming your on a 'nix machine you do it with a cron job

TenmanS14 25-Jun-15 9:11am View

yup, and if your using express edition then you can do it with scheduled tasks, batch files and the sqlcmd command, prob. run it at 00:01 everyday....

TenmanS14 24-Jun-15 10:39am View

just use rsync...

TenmanS14 24-Jun-15 4:11am View

hope you don't reuse that uname/password elsewhere....

TenmanS14 23-Jun-15 9:34am View

not the cause of a BCP error I don't think, but you can't create a .xls file from bcp, you can create a .csv which excel will open though.

TenmanS14 23-Jun-15 9:16am View

gonna guess your gonna be wanting to google cron... thats a vague guess though...

Comments by TenmanS14 (Top 14 by date)