1

I'm trying to merge tables from different databases, ServerDB and ClientDB and save to ClientDB where the client's tables hold the master's tables records plus any records the user might add. On the other hand server tables could also be updated (new records inserted).

The database has relationships. The column in TableA I want to INSERT INTO values is a ForeignKey linking to TableB's PrimaryKey which is an auto-incremental column.

I'm saving all records from both databases in a merged dataset, and due to database design restrictions, I need to clear both tables on the client before inserting the merged tables from the dataset.

I first update TableB (the one with the PrimaryKey auto-increment column), but now the values of this column have nothing to do with the ForeignKey on TableA, so I update TableA and temporary inserting in the ForeignKey column the value of the first record of TableB's PK. Now I need to update TableA foreignKey column with the correct values from TableA PK column. Theres also a third column on each table that have the same values.

What the syntax of the sql statement should be? If I don't make much sense let me know and I'll post a better description.

Pantelis
  • 2,060
  • 3
  • 25
  • 40
  • Can you show your table structure, and your current query?? And see [Working with the INSERT statement in SQL Server](http://www.simple-talk.com/sql/learn-sql-server/working-with-the-insert-statement-in-sql-server/) for a primer on what the `INSERT` statement can do – marc_s Apr 28 '12 at 17:26
  • Hey marc, I'm in a hurry right now I don't have the time to post a table structure. I'll post it tomorrow. It's 2 tables btw. I need something like this: INSERT INTO TableA Column VALUES (SELECT id FROM TableB WHERE TableA.column = TableB.column GROUP BY id). Is this a valid statement? – Pantelis Apr 28 '12 at 17:43
  • 3
    That's not quite valid syntax (it's close), but whether that's the query that makes sense for your requirement really depends on what you mean. If the values in TableB match the values in TableA, why do you need to involve TableB? Showing the actual structure with relatable context, and some sample data to describe exactly what you're trying to do, will be quite helpful in comparison to vague word problems... – Aaron Bertrand Apr 28 '12 at 18:20

2 Answers2

2

It was a confussing question but i think you are talking about inserting values from table 1 to table 2 where table 1 value is equal to table 2 values the sql query for this operation is

INSERT INTO emp (empno,ename)
SELECT t2.deptno, 
       t2.dname 
FROM   dept t2 
       LEFT JOIN emp t1 
         ON t2.deptno = t1.deptno

in this query table 1 (emp) will insert 2 values into columns(empno and ename) from table 2 (dept) and join is on (deptno) which is present in both tables.

You can further ask if this was not helpful.

Kendall Frey
  • 43,130
  • 20
  • 110
  • 148
alternatefaraz
  • 374
  • 1
  • 5
  • 18
  • Thats very close to what I need. Turns out I need an UPDATE command, INSERT INTO will insert new rows in my table. Can you post the UPDATE version of this query? – Pantelis Apr 29 '12 at 16:40
  • Managed to make it work, after struggling for some hours with the OleDbException: Operation must use an updateable query. I'm posting the answer on a new comment. – Pantelis Apr 29 '12 at 20:28
0

Thank you all for your help. After struggling for a while with the "OledbException Operation must use an updateable query" I found out a solution in a similar topic: SQL Update woes in MS Access - Operation must use an updateable query

Thats the query that did the trick:

UPDATE DISTINCTROW PlaylistsSongs
    INNER JOIN PlaylistsNames ON PlaylistNames.PlaylistName = PlaylistsSongs.PlaylistName
SET PlaylistID = PlaylistNames.ID
Community
  • 1
  • 1
Pantelis
  • 2,060
  • 3
  • 25
  • 40