Batch File - Read From One Table And Write To Another Database And Table
Nov 17, 2011
Any easy way for a batch file or automated process to read from one db and table and what ever entry is missing out of another database + table it writes those missing entries to.
This is a simple table in one db that is filled with usernames, I want to see if there are missing usernames in another db and table and write those entries.
db1.usr_table.usr_name = jdoenew
If jdoenew is missing in the 2nd db I will need to write entries like:
db1.usr_table.usr_name = jdoenew
db1.usr_table.password = tmppassword
db1.usr_table.active = 1
INPUT: A table contains 2 columns with data i need. column A=Filename and column B=FileContent
PROCESS: I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.
I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.
Is there any example similiar to that so i could learn how to start on that?
Hi, we're trying to read from a table and write back to the same table and are having a lot of trouble with blocking. What could we do to prevent our application from hanging due to blocking of this type?
Is there a way to get the last read/write time of a table?
I want to have a few tables, but only allow them to exist if they have been used in the last 30 days. I want to set up a "purge" job to clear out any tables that have not been used in 30 days.
I have two database files, one .mdf and one .ndf. The creator of these files has marked them readonly. I want to "attach" these files to a new database, but cannot do so because they are read-only. I get this message:
Server: Msg 3415, Level 16, State 2, Line 1 Database 'TestSprintLD2' is read-only or has read-only files and must be made writable before it can be upgraded.
What command(s) are needed to make these files read_write?
hi, I need to run a batch file in specific time of the week. can anyone show me the code to run a batch file with both ways: window nt schedualer and ms sql server task manger.... I do appreciate your help
My client will be receiving a .dbf file which needs to be uploadedinto a sql server database table (as an append) every week. They areNOT computer savvy and I would like to automate this process ratherthan go into enterprise manager and run data transformation.Is there any way to write a batch file or a set of command lines whichwill do this?Thanks.Monica
I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.
Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
I would like to store and read .doc file in a table of sql server using C#. Can some body help me on this? my id is padam76@gmail.com. Thanks & Regard,Padam Kumar Tripathi
I need to export data from a table of SQL server 2000 database, into an Excel 2000 sheet. I tried following query from the sql query analyzer
Code SnippetSELECT * INTO [Excel 4.0;Database=F:ew.xls].[sheet1] FROM tab1
It gave me following error
Code SnippetServer: Msg 2760, Level 16, State 1, Line 1 Specified owner name 'Excel 4.0;Database=F:ew.xls' either does not exist or you do not have permission to use it.
The F:ew.xls file is present and it has no additional security applied, hence I think it should be writable.
Please tell me where am I doing wrong.
I don't know if this is the correct news group or not. Let me know if I have to post to some other news group.
I want to check a table to see what rows have been updated today, then write to a text file some data from the selected rows; then I want to automate this (DTS package? TSQL stored procedure job?) to run every night at midnight. Is the DTS Wizard the best way, that's what I did, but have not confirmed that it is writing a new text file every day, and does each new version write over the old one?
Hai Everbody, for me in my project i want to read data from a csv file and insert it in a sql server databse table.The csv file may contain n number of columns,but i want only certain columns from that, and insert it in the database table.How to achieve this. Plz help me it is urgent. Thanks in advance. Thanks and regards Biju.S.G
Hello All Just wondered if someone could help me with a bit of T-SQL, i have a application in ASP.NET/VB that allows the user to update a message board by clicking a button "update" this in turn triggers my Stored Procedure for inserting this data into a table, which works great. It inserts the data into its respective fields and also takes The Title, Line 1, Line 2 and so on and creates a XML file (Using FOR XML) which is stored in the same Table under a column call XML_Data. Which again works great. My problem now is how do i output this XML_Data to an actual XML file that is on my local machine, i.e. It be created in say C:Inetpubwwwrootxmlfiles("xml file name inserted here from another column that holds xml file name").xml Any help on this would be greatfully apreciated Thanks In Advance Neil
I've a dataflow task on For Each Loop container at control flow of SSIS package. This For Each Loop container reads the CSV files from the specified location one by one, and populates a variable with current file name. Note, the tables where I would like to push the data from each CSV file are also having the same names as CSV file names.On the dataflow task, I've Flat File component as a source, this component uses the above variable to read the data of a particular file.
Now, here my question comes, how can I move the data to destination, SQL table, using the same variable name?I've tried to setup the OLE DB destination component dynamically but it executes well only for first time. It does not change the mappings as per the columns of the second CSV file. There're around 50 CSV files, each has different set off columns in it. These files needs to be migrated to SQL tables using the optimum way.
Which is the best way to setup the Dataflow task for this requirement?Also, I cannot use Bulk insert task here as we would like to keep a log of corrupted rows.
I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)
For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.
I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"
Values (CSV) 1X 1Y
Results Table(SQL) CreatedDate Message 2015-08-04 08:45:29.203 successfully inserted value 1X 2015-08-04 08:44:29.103 TEst pass 2015-08-04 08:43:29.103 successfully inserted value 1X 2015-08-04 08:42:29.203 test point 2015-08-04 08:35:29.203 successfully inserted value 1Y 2015-08-04 08:30:29.203 Test Pass 2015-08-04 08:28:29.203 successfully inserted value 1Y
If all values are inserted:
Output: All values from values table are inserted successfully Total count of values inserted: 2 If only few values are inserted, example only 1X from Values table is inserted in Message
Example: Results Table CreatedDate Message 2015-08-04 08:45:29.203 successfully inserted value 1X 2015-08-04 08:44:29.103 TEst pass 2015-08-04 08:43:29.103 successfully inserted value 1X 2015-08-04 08:42:29.203 test point
Output: All values from values are not inserted successfully in result table. Total count of values inserted: 1 Missing Values not inserted in results table are: 1Y
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.
I would like to ask if there will be a write and concurrency issue if i would create an online application with just one user connecting to the database, just like most open source php/mysql that can be downloaded
i.e Setup 1. I will grant a single user that will connect to a database and will be set to a config.inc.php file and then create a table users (userid, username, password) and this table will be used for the application authentication and access control.
Setup 2. or is it much better to grant users that will connect to the database and have the table users for access control of the application?
to further illustrate my query:
Setup 1 would be: ###################Table: Users user a@ipadd --> db1 --> userid mary --> myOnlineApp user a@ipadd --> db1 --> userid john --> myOnlineApp user a@ipadd --> db1 --> userid paul --> myOnlineApp
and Setup 2 would be: #####################Table: Users user mary@ipadd --> db1 --> userid mary --> myOnlineApp user john@ipadd --> db1 --> userid john --> myOnlineApp user paul@ipadd --> db1 --> userid paul --> myOnlineApp
will Setup 1 be enough if I will use SQL Server as database?
or Setup 2 is better user/database architecture?
This application will be online enrollment for a school with 16,000 students
very much appreciated for the feedbacks and suggestions
I'm trying to do Sharepoint DR with Log Shipping and every thing configured except one thing which is switch the WSS_Content (Standby /Read-Only) DB to be ready and Write.
I tried from
GUI or ALTER DATABASE [WSS_Content] SET READ_WRITE WITH NO_WAIT
I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:
sqlcmd -S (local)SQLExpress -i C:CreateDB.sql
This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.
I need a SP to read table A (1000 records) and updates Table B. I think I have to use Cursor, but I'm not very good at writing SP. Any help would be appreciated. Thanks.
Can anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application. I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns. In a seperate table I have a charge per day for late returns. I read this into a variable @LateCharges I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLate column*@Latecharges Any help much appreciated. Regards Clive
The following dbo.Tables of Northwind.mdf in my .SQLEXPRESS (SQL Server Management Studio Express) are missing: dbo.Categories dbo.CustomerCustomerDemo dbo.CustomerDemographics dbo.Customers dbo.Employees dbo.EmployeeTerritories dbo.Order Details dbo.Orders dbo.Products dbo.Regions dbo.Shippers dbo.Suppliers dbo.Territories.
But, I have these dbo.Tables in a different Database "xyzDatabase". How can I copy each of these dbo.Tables to the another blank dbo.Table of Northwind Database?
I right clicked on the dbo.Categories and I saw the following thing: dbo.Categories New Table... Modify Open Table Script Table as |> CREATYE To |> DROP To |> SELECT To |> INSERT To |> New Query Editor Window File.... Clipboard UPDATE To |> DELETE to |> From the above observation,I think it is possible to copy the dbo.Table from the one Database to the Northwind Database that needs to be repaired. Please help and advise me how to do this task or tell me where I can find the Microsoft document that gives the details of this X-copy thing.
Thanks in advance, Scott Chang
P. S. I am using VB 2005 Express to create a project to learn "Calling Stored Procedures with ADO.NET" (see Paul Kimmel's article in http://www.developer.com/db/article.php/3438221) that needs the dbo.Tables of Northwind Database and my Northwind Database has been screwed up for quite a while and needs a big repair.
