Selecting from table and adding ur own values

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

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Selecting from table and adding ur own values

Author

Topic

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-19 : 07:02:19

Hey all

This is probably the wrong category to post my question in. Apologies if it is.

I have got a Data Flow Task that has an OLE DB Source and a Flat file destination.

Is there a way to do the following:

SELECT id,'31809','C:\LC\Import\DataImages' & id & '.jpg', '1' FROM table1

This obviously doesn't work but I want to insert the id from the table, followed by the value '31809', followed by 'C:\LC\Import\DataImages\id.jpg', followed by '1'

So, the id in 'C:\LC\Import\DataImages\id.jpg' is the same as the id from the table.

I want the file to look like:

1, 31809, C:\LC\Import\DataImages\1.jpg, 1
2, 31809, C:\LC\Import\DataImages\2.jpg, 1
5, 31809, C:\LC\Import\DataImages\5.jpg, 1
etc...

I would really appreciate it if someone could point me to the right direction.

I am trying to work on it..will put up a solution if I find one myself.

Many thanks,

Rupa

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-19 : 07:13:09

You can do this by using a derived column task in between to get the value of path column and add it to data flow.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-19 : 07:23:59

[code]SELECT
id, '31809',
'C:\LC\Import\DataImages' + cast(id as varchar(5)) + '.jpg', '1'
FROM table1[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-19 : 07:31:00

You're a genius Harsh..Many, many thanks. That works!

Thanks for your reply too Visakh.

Much appreciated.

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-19 : 10:26:54

Do you know a way of deleting the data in the table before inserting it.

For example:
DELETE * FROM tbl_table1 where id=id
SELECT
id, '31809',
'C:\LC\Import\DataImages' + cast(id as varchar(5)) + '.jpg', '1'
FROM table1

So basically I want to delete the row:

1, 31809, C:\LC\Import\DataImages\1.jpg, 1

before inserting:

1, 31809, C:\LC\Import\DataImages\1.jpg, 1

So basically, I have the photo for person no 1 but would like to insert a new photo!!

I tried delete and then select in the DB Source Editor but I don't think it'll let you run 2 separate queries?

Any ideas?

Many thanks,
Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-19 : 10:35:57

I know it's an update but it won't be update each time. There will be new records as well.

Thanks,

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-19 : 10:54:46

You need to wrap those two statements inside a stored proc.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-19 : 11:17:57

Thx Harsh!

I was thinking of using a trigger on table1..once tbl_temp has records in it, it does the deletion.

Any ideas on this??

Thanks again!

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-19 : 11:30:30

Not quite sure what is the use of trigger here? Can you elaborate a bit?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-19 : 11:40:21

I have two tables:

table1 - that holds images and has following fields:
id, length, picture, type

tbl_temp - holds data of whose picture is stored in table 1. Fields:
sNo, forname, surname etc..

tbl_temp is emptied after comparisons are done. Data is transferred into the main table. Say, X

As soon as data is dumped into tbl_temp, I want a trigger to delete all the pictures where tbl_temp.sNo=table1.id

Hope this makes more sense? How would I create a trigger that would delete all the rows in table1 where tbl_temp.sNo=table1.id

So, table1 already has:

17941, 31809, binary data, 1
17943, 31809, binary data, 1

tbl_temp now has:

17941, x, sample....
18692, y, example...

I want to delete:

17941, 31809, binary data, 1
FROM table1
cos tbl_temp.sNo=table1.id

Any ideas?

Thanks,

Rupa

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-19 : 11:59:57

I've used a stored procedure to do the job. Thank you very much Harsh!

Much appreciated!

Rupa

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-19 : 12:53:24

quote:
Originally posted by Rupa

I have two tables:

table1 - that holds images and has following fields:
id, length, picture, type

tbl_temp - holds data of whose picture is stored in table 1. Fields:
sNo, forname, surname etc..

tbl_temp is emptied after comparisons are done. Data is transferred into the main table. Say, X

As soon as data is dumped into tbl_temp, I want a trigger to delete all the pictures where tbl_temp.sNo=table1.id

Hope this makes more sense? How would I create a trigger that would delete all the rows in table1 where tbl_temp.sNo=table1.id

So, table1 already has:

17941, 31809, binary data, 1
17943, 31809, binary data, 1

tbl_temp now has:

17941, x, sample....
18692, y, example...

I want to delete:

17941, 31809, binary data, 1
FROM table1
cos tbl_temp.sNo=table1.id

Any ideas?

Thanks,

Rupa

Yes you can create INSERT trigger on tbl_temp for that.

Create trigger temp_trig1 on tbl_temp
for insert
as
delete t1
from table1 t1 join inserted i
on i.sNo=t1.id
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Rupa
Posting Yak Master

123 Posts

Posted - 2008-05-20 : 09:32:18

Thank you :-)

Rupa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources