export results of SQL Query the import elswhere

Faolchu · 01-03-2013 12:54PM #1

Guys I have no clue about SQL so i'm in a bind. basically we have a database (and no dba) that database has a table of user account codes. I need to export some of those records. the reason being we have a backup of the database but for some strange reason its not possible to run a backup of the live database and restore that backup to our off site server and the user table on the backup server is a tad out of date.

So i can run something like this:

select * from USER_TABLE where USER_CODE in ('user1', 'user2')

ok so far i have the required results on screen, i can even have that output the results to a .txt file.

heres my problem how would i go about getting the results of that txt file into the database that's off site? Obviously i copy the file off site thats a given but how do i import the content?

on my offsite server do i do something like this?

insert into USER_TABLE from c:\tmp\users.txt

looking at the txt file that is created if i run the SELECT FROM above the file has headers and a load of dashes, also there's nothing like a comma separating each value, would this cause problems for the import because the table itself also includes date and time entries that are part of the same field. so for example there will be a field called PWD_DATETIME and the entry will be something like 03/01/2013 12:13:05

I'm thinking that the SELECT FROM would need to output the results to a .csv file that way each field is clearly separated so that when i import the data again from a csv the comma will distinguish each value for each field. the problem is how do i go about doing that

seamus · 01-03-2013 12:56PM

Roughly speaking, yes this is doable, and there are a number of ways of doing it, though it depends on what kind of database it is - SQL Server, MySQL, Oracle, etc?

Also you need to know if the table has an automatically-assigned primary key and whether this key is referenced in other tables.

Faolchu · 01-03-2013 02:07PM

Seamus we're using SQL 2005.

in management studio if i browse to the table in question and look at Keys its shows two, one PK and one FK.

when i look at the properties of the PK the name is USER_CODE which is the name of one of the columns in the table. so i would assume that column in the table is used as the PK. it would appear that the PK is not made up or combined with anything else ie a FK from some other table.

the FK relates to a user group which would exist on the backup database anyway so that shouldn't cause an issue.

in a nut shell I'm exporting a list of user accounts from our live DB into a copy of that very same DB just in another physical location. the problem is though the tables and the database name etc are the same the server name is different. normally as far as i know this wouldn't be an issue but the DB is the back end to an application and that has tattooed the server name all over the db so i cant just backup teh db and take the tape off site and do a restore of the db i need to work with the actual data itself. its only 30 accounts which is no problem. but that's 30 today next year it could be 3000.

assuming that that user_code value is the PK, what code would i use to both export and import the data?

Faolchu · 01-03-2013 03:55PM

sorted..

if i run the inital query and have the results sent to grid I can clock on the grid and select save as CSV. its then just a case of ensuring teh exported date is in the right format for the table on the backup server. so in some instances cells would be marked as NULL, i just had to delete the word NULL and that record would be imported. same with dates, i just had to ensure they were in the correct format

uch · 07-03-2013 03:11PM

I know it's a bit late but the easiest way would be to export the table to an Access DB through ODBC, then just import the table into the other DB.
Also for future reference if you are using SSMS to manage, there is a Import/Export Wizard that works a treat.

export results of SQL Query the import elswhere

Comments