Write SQL that writes SQL |
|||
|
Ever need to take data from one database and put into another? There a lots of tools to help with that, but
here's one failsafe way that will work with any system: Use the source database to write a series of INSERT statements that can later be run on the destination database. The query below SELECT 'INSERT INTO MyTable(Field1,Field2,Field3) VALUES(' + Field1 + ',''' + Field2 + ''',''' + Field3 + ''')' will produce something like INSERT INTO MyTable(Field1,Field2,Field3) VALUES(NumericValue1,'StringValue1a','StringValue1b') INSERT INTO MyTable(Field1,Field2,Field3) VALUES(NumericValue2,'StringValue2a','StringValue2b') INSERT INTO MyTable(Field1,Field2,Field3) VALUES(NumericValue3,'StringValue3a','StringValue3b') Note that '' in the original query, becomes a single ' in the output query. This technique is one you shouldn't need to resort to all the time - particularly if the source and destination database are on the same network - but it is a handy technique to keep up your sleeve because it doesn't rely on any level of compatibility between the source and destination databases.
|