Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Export/Import query Expand / Collapse
Author
Message
Posted Tuesday, August 6, 2013 12:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 30, 2014 9:03 AM
Points: 68, Visits: 307
Hello all,
I have two databases, db1 and db2.
I am pulling data from db1 with view which I have to insert in db2 table:
- if record in db2 with same field1 and field2 exists - update;
- if record in db2 doesn't exist, I need to insert it.
Thanks,
Brano
Post #1481506
Posted Tuesday, August 6, 2013 1:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
branovuk (8/6/2013)
Hello all,
I have two databases, db1 and db2.
I am pulling data from db1 with view which I have to insert in db2 table:
- if record in db2 with same field1 and field2 exists - update;
- if record in db2 doesn't exist, I need to insert it.
Thanks,
Brano


MERGE

http://technet.microsoft.com/en-us/library/bb510625.aspx


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1481508
Posted Tuesday, August 6, 2013 1:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 30, 2014 9:03 AM
Points: 68, Visits: 307
One more issue with this specific query, which gave me headache is:
- db1 has record like this:
record_id, column1, column2, column3, column4.
- db2 has record like this:
record_id, column_id (based on column value - db2 has table with column_id, column value), and value column.

So, I am inserting next:

From db1: record_id, column1 in new db2 record (or update existing) with values record_id, column_id, value.

Example (for new record):
INSERT (from db1) (record_id, column1) with values (1, Drawings)
INTO
db2 (record_id, column_ID) = 1, 123 (where 123 is column_id for Drawings).

After first, INSERT is taking second value from db1 and inserting it ...

Thanks,
Brano
Post #1481512
Posted Tuesday, August 6, 2013 1:15 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
branovuk (8/6/2013)
One more issue with this specific query, which gave me headache is:
- db1 has record like this:
record_id, column1, column2, column3, column4.
- db2 has record like this:
record_id, column_id (based on column value - db2 has table with column_id, column value), and value column.

So, I am inserting next:

From db1: record_id, column1 in new db2 record (or update existing) with values record_id, column_id, value.

Example (for new record):
INSERT (from db1) (record_id, column1) with values (1, Drawings)
INTO
db2 (record_id, column_ID) = 1, 123 (where 123 is column_id for Drawings).

After first, INSERT is taking second value from db1 and inserting it ...

Thanks,
Brano


You are going to have to paint a far clearer picture here if you want detailed assistance. You will need to post a few things:

1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1481514
Posted Tuesday, August 6, 2013 1:27 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 30, 2014 9:03 AM
Points: 68, Visits: 307
Thank you Sean,
I will make it clearer.
I don't have actual DLL, I have data/query, will try to be more clear in next post/reply on this issue.
Post #1481520
Posted Tuesday, August 6, 2013 2:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 30, 2014 9:03 AM
Points: 68, Visits: 307
First, db1 has:

SELECT ObjectID, Media, artist, place, gender from db1.view1

Example:

1, paint, Arthur, Europe, male;
2, paint, John, US, male;
3, wood, Ann, Europe, female;


Second, db2 has:

SELECT CustomData.Field_ID, CustomData.Record_ID, FieldDef.Name,
CustomData.StringValue
FROM CustomData LEFT OUTER JOIN
FieldDef ON FieldDef.Field_ID = CustomData.Field_ID


Example for CustomData table (Field_ID, Record_ID, StringValue):

10, 18031, 1
10141, 18031, Arthur


Example for FieldDef table (Field_ID, Name):

10, ObjectID
10140, Media
10141, artist
10142, place
10222, gender


What I need to check: If Object_ID = 1 (or any other Object_ID which is in db1.view) exists in CustomData.
If yes, then INSERT/UPDATE CustomData table to have:

10, 18031, 1
10140, 18031, paint
10141, 18031, Arthur
10142, 18031, Europe
10222, 18031, male

I hope I explained better this time, it looks quite difficult for me, even to explain.
Post #1481544
Posted Tuesday, August 6, 2013 2:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
This does look difficult to explain with words. Using ddl this probably would be a lot simpler. Ultimately you need some help putting together a query which means that somebody on the other end needs to have something to work with.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1481564
Posted Tuesday, August 6, 2013 9:37 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:01 PM
Points: 8, Visits: 42
I am having trouble working out exactly what you are asking but see below it may help you?

UPDATE
--table to update
Values1
SET
--column you want the value inserted into and from
Values1._Value = Values2._Value
-- normal join
FROM
Values1
INNER JOIN
Values2
ON
Values1._ID = Values2._ID
-- use where as your if statement
where Values1._Value = Values2._Value_2
Post #1481636
Posted Wednesday, August 7, 2013 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:32 PM
Points: 13,302, Visits: 12,168
bawinkley (8/6/2013)
I am having trouble working out exactly what you are asking but see below it may help you?

UPDATE
--table to update
Values1
SET
--column you want the value inserted into and from
Values1._Value = Values2._Value
-- normal join
FROM
Values1
INNER JOIN
Values2
ON
Values1._ID = Values2._ID
-- use where as your if statement
where Values1._Value = Values2._Value_2


What I am asking for is ddl and sample data. That means create table statements and insert statements. I can't possibly know if any code I might toss together will work when I have nothing to work with. Please take a few minutes and read the article in my signature about best practices when posting questions.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1481839
Posted Wednesday, August 7, 2013 8:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, May 30, 2014 9:03 AM
Points: 68, Visits: 307
I think I explained very detailed what I need in one of my posts.
Please check and let me know if anything missing in my second explanation:

First, db1 has:

SELECT ObjectID, Media, artist, place, gender from db1.view1

Example:

1, paint, Arthur, Europe, male;
2, paint, John, US, male;
3, wood, Ann, Europe, female;


Second, db2 has:

SELECT CustomData.Field_ID, CustomData.Record_ID, FieldDef.Name, CustomData.StringValue
FROM CustomData LEFT JOIN
FieldDef ON FieldDef.Field_ID = CustomData.Field_ID


Example for CustomData table (Field_ID, Record_ID, StringValue):

10, 18031, 1
10141, 18031, Arthur


Example for FieldDef table (Field_ID, Name):

10, ObjectID
10140, Media
10141, artist
10142, place
10222, gender


What I need to check: If Object_ID = 1 (or any other Object_ID which is in db1.view) exists in CustomData.
If yes, then INSERT/UPDATE CustomData table to have:

10, 18031, 1
10140, 18031, paint
10141, 18031, Arthur
10142, 18031, Europe
10222, 18031, male
Post #1481915
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse