Insert Records From Foxpro Tables To SQL Server Tables
Apr 22, 2004
Hi,
Currently, I'm using the following steps to migrate millions of records from Foxpro tables to SQL Server tables:
1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables in a dummy database. All the SQL tables have the same columns as the Foxpro tables.
2. Manipulate the data in the SQL tables of the dummy database and save the manipulated data into the SQL tables of the real database where the tables may have different structure from the corresponding Foxpro tables.
I only know the following ways to import Foxpro data into SQL Server:
#1. Transfer Foxpro records to .dat files and then bcp to SQL Server tables
#2. Transfer Foxpro records to .dat files and then Bulk Insert to SQL Server tables
#3. DTS Foxpro records directly to SQL Server tables
I'm thinking whether the following choices will be better than the current way:
1st choice: Change step 1 to use #2 instead of #1
2nd choice: Change step 1 to use #3 instead of #1
3rd choice: Use #3 plus manipulating in DTS to replace step 1 and step 2
Thank you for any suggestion.
View 2 Replies
ADVERTISEMENT
Mar 16, 2008
Is it possible to use SSIS to synchronize the data between a Foxpro .dbf and a compatible SQL Server table on a near realtime basis?
I have succesfully created an SSIS package that will insert data into the SQL Server Table but this is only useful for migrating data. What I need is a way to insure that the data in the SQL Server table matches that in the .dbf on a near realtime basis.
Or is there a way to link from SQL Server to the .dbf (similar to an Oracle DBLink).
Thanks for any and all assistance.....
Dave
View 9 Replies
View Related
Aug 29, 2006
I have searched in length and cant seem to find a specific answer.I have a tmptable to hold user "shoppingcart" ( internal supplies)What i want is to take when the user clicks order to take that table pull the records with that user and populate the order and order details tablesThe order table has a PK of orderID and the orderdetails has a FK of orderIDI know how to insert to the main table, but dont know how to populate the details at the same timeI have this.Insert into supplyordersselect requestor from tmpordercart where requestor = &name so how do i also take from the tmpordercart the itemno and quanity and put them into the orderdetails so that it links back to order table?
View 1 Replies
View Related
Apr 2, 2004
I need to update two tables. I have created a view and am using the code in the attached file to insert into the two tables.
The page loads without errors, but I get this message that the view is not updatable because the modification affects multiple base tables.
I thought this was the purpose of views?
Does anyone have any suggestions? I am using Dreamweaver MX and SQL Server.
Thanks!
N
View 11 Replies
View Related
Sep 24, 2007
Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.
How can I do this?
Thanks
View 6 Replies
View Related
Mar 17, 2014
Using SSE 2012 64-bit.I need to insert records from multiple Access Tables into 1 Table in SSE and ensure no duplicates are inserted.This is executing, but is very slow, is there a faster way?
Code:
INSERT INTO dbTarget.dbo.tblTarget
(All fields)
SELECT
(All Fields)
FROM dbSource.dbo.tblSource
WHERE RecordID NOT IN (SELECT RecordID FROM dbTarget.dbo.tblTarget)
View 6 Replies
View Related
Aug 30, 2015
This is a bit lengthy, but lets say we have three tables
a) tblSaleStatementCustomer
b) tblCreditors
c) tblReceiptDue
which shows records like below
Table 1 - tblSaleStatementCustomer
ID CustomerName VoucherType Outbound Inbound CustomerType
----------------------------------------------------------------------------------------------
1 ABC Sales 10000 0 Dealer
2 MNC Sales 9000 0 Dealer
3 MNC Sales 4000 0 Dealer
Table 2 - tblCreditors
ID Name OpeningBalance
----------------------------------------------------------------------------------------------
1 ABC 20000
2 MNC 15000
3 XBM 18000
4 XYZ 12000
View 2 Replies
View Related
Aug 30, 2015
Lets say we are executing this query below to retrieve each customer and the amount associated to a table
"INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE - Inbound END) AS AMOUNT from
tblSaleStatementCustomer WHERE CustomerType = 'Dealer' GROUP BY CustomerName"
Which display the data like below
DEALER AMOUNT
------------------------------------------------
ABC 2000
XYZ 1000
However I have one more table TABLE2 which contains two columns
DEALER OPENING
-------------------------------------------------------
ABC 5000
XYZ 7000
I want to modify my query in such a way that AMOUNT column should also add
OPENING from TABLE2 So that I must get the result like below
DEALER AMOUNT
------------------------------------------------
ABC 7000
XYZ 8000
View 10 Replies
View Related
Jun 28, 2007
Windows XP Pro - Sql Server 2005 Management Studio (current) - current visual foxpro oledb driver
I have an origin data folder with many foxpro dbf files
When I go through the "wizard" to import data from the origin folder using the "free table" option, I can locate the folder just fine. The form shows me a list of the files in the folder - but I cannot scroll through the list.
When I select the folder and continue, I test the connection and get a message that it is fine.
Then I go on to actually import tables. The list that is presented of tables that I can import does not include all the dbf files in the folder. There are 207 files in the origin folder, but only a fraction (about 120) of those show up in the list as candidates to be imported. I cannot see a pattern to the ones that are excluded from the list.
Does anyone know what could be causing this strange behavior?
Thanks.
View 3 Replies
View Related
Aug 7, 2014
How can I add the option to import data from Visual FoxPro tables into SQL 2012 database?
View 0 Replies
View Related
Oct 17, 2007
Hi,
I am trying load data from multiple Foxpro tables which are under a folder. I can have multiple folders with 17 foxpro tables. I was able to do it in DTS using ActiveX script. Here is the ACtiveX script.
'**********************************************************************
' Visual Basic ActiveX Script
'************************************************************************
Option Explicit
Dim conObj,DSNGosfbill,comObj,objRs,HostServer
Dim sFolder,sFileFolder, Details,subFolderoccur,sFileFolderDBF,sFileFolderFPT,CheckFile,dFiles,Fil
Dim fso, folderObj,subFolderList,dFolderObj
Dim objPackage,oStep,objPackage_1,oStep_1,ConnObj_001,ConnObj_004,ConnObj_031,ConnObj_032,ConnObj_033
Dim ConnObj_Hclaimb, ConnObj_HProv, ConnObj_Hids, ConnObj_HCodes, ConnObj_HSpan, ConnObj_002, ConnObj_HCHGB
Set conObj = CreateObject("ADODB.Connection")
HostServer =DTSGlobalVariables("gvServer").Value
'DSNGosfbill ="DRIVER={SQL Server};SERVER=" & HostServer & ";DATABASE=GOSFBILL"
DSNGosfbill ="DRIVER={SQL Server};SERVER=" & HostServer & ";UID=syntelhum;PWD=syntel123;DATABASE=GOSFBILL"
conObj.open DSNGosfbill
sFolder =DTSGlobalVariables("gvSSIUnzipPath").Value
CheckFile =DTSGlobalVariables("gvSSIBatchPath").Value
set comObj=CreateObject ("adodb.command")
set comObj.ActiveConnection =conObj
Function Main()
Dim Dir_Name,DirFlag
Dir_Name = ""
DirFlag = "N"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(checkFile) Then
Else
Details = "***** Success.Lst file is missing in Batch folder. BATCH job may not be successfull or there are no folders in UNZIP directory to process. Check the batch run.*****"
Call Write_Log
Main = DTSTaskExecResult_Failure
Exit Function
End If
Set folderObj = fso.GetFolder(sFolder)
Set subFolderList = folderObj.SubFolders
For Each subFolderOccur in subFolderList
DirFlag = "Y"
Dir_Name = subFolderOccur.Name
Call Process_Dir(1,subFolderOccur.Name)
Next
If DirFlag = "N" Then
Details = "***** No directories to process in SSI UNZIP folder*****"
Call Write_Log
End If
If DirFlag = "Y" Then
Call Process_Dir(2,Dir_Name)
If objRs.Eof Then
Details = "***** No directories to process in SSI UNZIP folder*****"
Call Write_Log
End If
While not objRs.EOF
set sFileFolder = fso.GetFolder(sFolder & objRs("zip_file_name"))
Details = "***** Start-Time " & sFileFolder & " " & Date & " " & Time & "*****"
Call Write_Log
Call Update_Process_Flag("L",objRs("zip_file_name"))
'*******Execute the package for each directory****************'
'********* Call the Package**************'
Set objPackage = CreateObject("DTS.Package")
Set objPackage_1 = CreateObject("DTS.Package")
'objPackage.LoadFromSQLServer HostServer, , ,256, , , , "GOSFBILL_SSI_STAGING_LOAD"
objPackage.LoadFromSQLServer HostServer,"syntelhum","syntel123", , , , , "GOSFBILL_SSI_STAGING_LOAD"
'objPackage_1.LoadFromSQLServer HostServer, , ,256, , , , "GOSFBILL_SSI_Update_FileSource"
objPackage_1.LoadFromSQLServer HostServer,"syntelhum","syntel123", , , , , "GOSFBILL_SSI_Update_FileSource"
Set ConnObj_001 = objPackage.Connections("SSIPATH001")
ConnObj_001.DataSource = sFileFolder
Set ConnObj_002 = objPackage.Connections("SSIPATH002")
ConnObj_002.DataSource = sFileFolder
Set ConnObj_004 = objPackage.Connections("SSIPATH004")
ConnObj_004.DataSource = sFileFolder
Set ConnObj_031 = objPackage.Connections("SSIPATH031")
ConnObj_031.DataSource = sFileFolder
Set ConnObj_032 = objPackage.Connections("SSIPATH032")
ConnObj_032.DataSource = sFileFolder
Set ConnObj_033 = objPackage.Connections("SSIPATH033")
ConnObj_033.DataSource = sFileFolder
Set ConnObj_Hclaimb = objPackage.Connections("SSIPATHCLAIMB")
ConnObj_Hclaimb.DataSource = sFileFolder
Set ConnObj_HProv = objPackage.Connections("SSIPATHPROV")
ConnObj_HProv.DataSource = sFileFolder
Set ConnObj_Hids = objPackage.Connections("SSIPATHHIDS")
ConnObj_Hids.DataSource = sFileFolder
Set ConnObj_HCodes = objPackage.Connections("SSIPATHCODES")
ConnObj_HCodes.DataSource = sFileFolder
Set ConnObj_HSpan = objPackage.Connections("SSIPATHSPAN")
ConnObj_HSpan.DataSource = sFileFolder
Set ConnObj_HCHGB = objPackage.Connections("SSIPATHCHGB")
ConnObj_HCHGB.DataSource = sFileFolder
objPackage.Execute
For Each oStep In objPackage.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
Details = "***** GOSFBILL_SSI_Staging_Load failed. " & Date & " " & Time & "*****"
Call Write_Log
Main = DTSTaskExecResult_Failure
Exit Function
End If
Next
Call Update_Process_Flag("X",objRs("zip_file_name"))
objPackage_1.GlobalVariables("gFileSource").Value = objRs("zip_file_name")
objPackage_1.Execute
For Each oStep_1 In objPackage_1.Steps
If oStep_1.ExecutionResult = DTSStepExecResult_Failure Then
Details = "***** GOSFBILL_SSI_Update_FileSource failed. " & Date & " " & Time & "*****"
Call Write_Log
Main = DTSTaskExecResult_Failure
Exit Function
End If
Next
'********************************************'
Details = "***** End-Time " & sFileFolder & " " & Date & " " & Time & "*****"
Call Write_Log
objPackage.Uninitialize
objPackage_1.Uninitialize
Set objPackage = Nothing
Set objPackage_1 = Nothing
sFileFolder = ""
sFileFolderDBF = ""
sFileFolderFPT = ""
objRs.MoveNext
Wend
objRs.Close
End If
Call Close_Conn
Main = DTSTaskExecResult_Success
End Function
Sub Process_Dir (Para_cntl,Dir_Name)
comObj.CommandText ="dbo.Usp_Process_Dir"
comObj.commandtype = 4
comobj.parameters.Refresh
comobj.parameters("@Para_Cntl")= para_cntl
comobj.parameters("@Dir_Nm")= Dir_Name
comobj.parameters("@File_Type")= "SSI"
If (Para_Cntl = 1)Then
comObj.Execute()
Else If Para_Cntl = 2 Then
Set objRs = comObj.Execute()
End If
End If
End Sub
Sub Update_Process_Flag(P_Flag,Dir_Name)
comObj.CommandText ="dbo.Usp_Process_Flag"
comObj.commandtype = 4
comObj.parameters.Refresh
comObj.parameters("@Process_Flag")= P_Flag
comobj.parameters("@Dir_Nm")= Dir_Name
comObj.Execute()
End Sub
Sub Write_Log
comObj.CommandText ="dbo.usp_etl_write_log"
comObj.commandtype = 4
comobj.parameters.Refresh
comobj.parameters("@Text")= Details
Comobj.parameters("@NDC_SSI_IND")= "SSI"
Comobj.parameters("@Process_Stage")= "Staging"
comObj.Execute()
End Sub
Sub Close_Conn
Set comObj = Nothing
Set objRs = Nothing
conObj.Close
Set conObj = Nothing
Set fso = Nothing
Set folderObj = Nothing
Set subFolderList = Nothing
End Sub
When I migrated this code to SSIS, its not working. How can I achive this functionality in SSIS. Any one pls help me.
Thanks in advance
Gijo
View 1 Replies
View Related
Jul 30, 2007
Does anyone have a good query that would return records from two tables that are found in one, but not it the other table? In my situation I have 2 tables that are duplicate tables and I need to find an additional 3000 records that were added to one of the tables. I also have a composite key so the query would have col1, col2 and col3 as the composite key. So far I have tried concatenating the 3 columns and giving the result an alias and then trying to show the ones that were not in both tables, but have been struggling. Thanks..
View 4 Replies
View Related
Jan 24, 2007
I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...
View 1 Replies
View Related
Jul 13, 2007
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.
I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):
Table 1: Classes --Columns: ClassID, ClassName
Table 2: Roster--Columns: ClassID, StudentID, Student Name
Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName
Table 4: Scores--StudentID, AssignmentID, Score
What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.
What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.
The stored procedure I created was
DELETE FROM Classes WHERE ClassID=@classid
I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:
The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'.
The statement has been terminated.
What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?
I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.
Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.
Further, is there something else I need to do besides assigning primary keys and foreign keys?
View 6 Replies
View Related
Mar 3, 2015
I'm using MS SQL Server 2008 and I'm trying to figure out if it is possible to identify what tables / columns contain specific records.
In the example below information generated for the end user, so the column headers (Customer ID, Customer, Address, Phone, Email, Account Balance, Currency) are not necessarily the field names from the relevant tables, they are simply more identifiable headers for the user.
Customer ID CustomerAddress Phone Email Account Balance Currency
js0001 John Smith123 Nowhere Street555-123-456 jsmith@nowhere.com-100 USD
jd2345 Jane Doe 61a Down the road087-963258 jdoe@downthe road.com-2108 GBP
mx9999 Mr X Whoknowsville 147-852369 mrx@whoknows.com0 EUR
In reality the column headers may be called eg (CustID, CustName, CustAdr, CustPh, CustMail, CustACBal, Currency).
As I am not the generator of this report, I would like to know whether or not it is possible to identify the field names and / or what tables they exist in, if I were to used the report info to search for it. For example, could I perhaps find out the field name and table for "jd2345" or for "mrx@whoknows.com", because the Customer ID or Email may not be what the actual fields are called.
I'm not a DB admin and I don't have rights to do a stored procedure on the server. I'm guessing what I want is not so simple to do, but is it possible to do via a query?
View 2 Replies
View Related
Jul 1, 2015
i have this query in a proc
declare @bu_id INT,
@CurCaptureDate DATETIME,
@user_id INT,
@col_name VARCHAR(100),
@sort_order VARCHAR(4),
@CityPair_ID INT=NULL,
[code]....
where @reasons and @departure_code can be multiple.
View 2 Replies
View Related
Jan 25, 2007
I did use query plans to find out more. ( Please see the thread BELOW)I have a question on this, if someone can help me with that it will begreat.In my SQL query that selects data from table, I have a where clausewhich states :where PermitID like @WorkTypeorder by WorkStart DESC@WorkType is a input parameter to the Stored proc and its value is'01%'When I use the above where clause, all the Sorts in the ESTIMATED QueryExecution plan show me a COST of 28%.However if I change the query manually to say:where PermitID like '01%'order by WorkStart DESCThe COST of the Sort (in ESTIMATED Query Execution plan) reduces to 2%and at the beginning of the PLAN, there is a Bookmark Lookup whichincludes the above where clause.Whereas with the FIRST example , the BookMark Lookup in the beginningdoesn't show that where condition.Can anyone help me better understand this anomaly?TIA=====================================I am using derived tables to Page data on the SQL Server side.I used this link as my mentor for doing paging on the SQLServerhttp://msdn2.microsoft.com/en-us/library/ms979197.aspxI wanted to use USER PAGING, thus I used the following code:CREATE PROCEDURE UserPaging(@currentPage int = 1, @pageSize int =1000)ASDECLARE @Out int, @rowsToRetrieve int, @SQLSTRING nvarchar(1000)SET @rowsToRetrieve = (@pageSize * @currentPage)SET NOCOUNT ONSET @SQLSTRING = N'selectCustomerID,CompanyName,ContactName,ContactTitle from( SELECT TOP '+ CAST(@pageSize as varchar(10)) +'CustomerId,CompanyName,ContactName,ContactTitle from( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM( SELECT TOP ' + CAST(@rowsToRetrieve as varchar(10)) +'CustomerID,CompanyName,ContactName,ContactTitle FROM Customers as T1ORDER BY contactname) AS T2 ORDER BY contactname DESC ) AS T3)As T4 ORDER BY contactname ASC'EXEC(@SQLSTRING)RETURNGOWhen I use this. Assume that the Total records returned by the SQLquery is 1198.Thus when I am on Page1 the above Stored Proc (SP) willreturn the first 1000 records.This works absolutely fine.Now I am on Page2, now I need to retrieve only the remaining 198records.But if I use the above SP, it will return the last 1000records.So to tweak this I used the following logic to set the@pagesize variable:Declare @PageCount intselect @PageCount = @TotalRows/@PageSizeif @currentPage @PageCount SET @PageSize = @TotalRows%@PageSizeSince I am on Page2 the above logic will set the PageSize to 198 andnot 1000.But when I use this logic, it takes forever for the SP toreturn the 198 records in a resultset.However if the TotalRows were = 1800, and thus the PageSize=800 orgreater, this SP returns the resultset quickly enough.Thus to get over this problem I had to use the other logic i.e. usingApplication Paging (i.e. first storing the entire result set into aTemp table, then retrieving only the required records for the PAGE)Can anyone suggest what is wrong with my user paging logic?????TIA...
View 8 Replies
View Related
Sep 11, 2007
Hi,I am trying to update a sql server 2000 database breaking up 1 huge confused table into several smaller tables to allow for more flexibilty.I want to use the "insert into" statement to create 2 new tables from the one old table. Old Table - structureCommercialProperties Cols: ID(identity) - PropName - PropAddress - Pic1 - Pic2 - Pic3New tablesPropertyCols: ID(doesn't have to be same as original id) - PropName - PropAddress PropertyImagesCols: ID - PropID(from new Property table ) - PicFile ----------------------------I know this won't work but it is to show what I want to do. I want to cycle through all the CommercialProperty rows and add the values to the new tables.I just need it to run in Query Analyzer declare @pic1, @pic2, @pic3 as varchar(255) declare @newID as integer declare @PropName, @PropAddress as varchar(255) select @PropName=PropName, @PropAddress =PropAddress , @pic1=Pic1, @Pic2=Pic2, @Pic3=Pic3 from CommercialPropertiesinsert into Property (PropName, PropAddress) values (@PropName, @PropAddress)SELECT @NewID AS [SCOPE_IDENTITY]how do I use the new Identity and to add the existing images details into the PropertyImages tableinsert into PropertyImages (PropID, PicFile) values (@NewID, @Pic1(from the original query)insert into PropertyImages (PropID, PicFile) values (@NewID, @Pic2)insert into PropertyImages (PropID, PicFile) values (@NewID, @Pic3) go to next CommercialProperty Thanks for your helpP
View 2 Replies
View Related
Jan 30, 2008
how to insert images into tables in sql server ?
is it possible to insert images in to SQL Server 2000. ?
View 9 Replies
View Related
Oct 7, 2015
I am using the following select statement to get the row count from SQL linked server table.
SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')
MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?
View 1 Replies
View Related
Jan 24, 2008
I'm new to my company, although not new to SQL 2005 and I found something interesting. I don't have an ERD yet, and so I was asking a co-worker what table some data was in, they told me a table that is NOT in SQL Server 2005's list of tables, views or synonyms.
I thought that was strange, and so I searched over and over again and still I couldn't find it. Then I did a select statement the table that Access thinks exists and SQL Server does not show and to my shock, the select statement pulled in data!
So how did this happen? How can I find the object in SSMS folder listing of tables/views or whatever and what am I overlooking?
Thanks,
Keith
View 4 Replies
View Related
Sep 27, 2012
Is there a way to open EDI file in SQL Server and insert the data into tables?
View 9 Replies
View Related
Jan 2, 2015
Ok I think I will need to use a temp table for this and there is no code to share as of yet. Here is the intent.
I need to insert data into two tables (a header and detail table) the Header Table will give me lets say an order number and this order number needs to be placed on the corresponding detail lines in the detail table.
Now if I were inserting a single invoice with one or more detail lines EASY, just set @@Identity to a variable and do a second insert statement.
What is happening is I will be importing a ton of Invoice headers and inserting those into the header table. The details are already in the database across various tables and and I will do that insert based on a select with some joins. As stated I need to get the invoice number from IDENTITY of the header table for each DETAIL insert.
I am assuming the only way to do this is with a loop... Insert one header, get identity; Insert the detail table and include the IDENTITY variable, and repeat.
View 9 Replies
View Related
Mar 2, 2006
Hello (sorry my bad english, im brazilian)
I was using Visual Studio 2003 and SQL Server CE 2.0 for C# mobile applications. The .sdf database were created in the emulator or in the mobile device itself using Query Analizer.
The application developed need some initial data to run, and this data is obtained executing one service that reads a postgree database, and insert the data in the SQL CE database of the mobile device. But, given the size of the database (maybe 10.000 rows), it tooks too much time (sometimes 6 hours).
Now we are migrating to Visual Studio 2005 and SQL Server 2005 Mobile Edition.
I want to know if its possible to create the .sdf database and load the data into this database on the desktop. Maybe through the execution of a .sql script, or through a service executed on the desktop.
After this, its just upload de .sdf file to the mobile device.
Thanks
Robson
View 8 Replies
View Related
Dec 9, 2007
Hi all,
I have a large Excel file with one large table which contains data, i've built a SQL Server DataBase and i want to fill it with the data from the excel file.
How can it be done?
Thanks, Michael.
View 1 Replies
View Related
Jan 3, 2005
I am trying to take an entire MS SQL database and put it in an sql file. I have succesfully copied the tables into an sql file by highlighting the tables in enterprise manager and choosing 'generate sql script'.
That gives me the structure, but now I would like the data (in insert statements). I have looked in enterprise manager's export wizard and sql analyzer to no avail. There seem to be a lot of options for exporting data except this one! Please point me in the right direction.
At the end of the day, I would like to be able to put everything in a text file. Then, should I have problems, I can just copy my text into query analyzer and have a brand new database.
Thank you in advance.
View 4 Replies
View Related
Feb 7, 2008
I want to compare two tables. if the data is existed to the other table i will insert it to other table if not exist it will load also to diffrent table.
Example: if my table was Tbl_StudentID and Tbl_FacultyID i want to insert in tbl_Grade if the data in Tbl_StudentID is exist or present in Tbl_FacultyID . And if the Tbl_StudentId is not exist in Tbl_FacultyID the data will move to Tbl_SupervisorID.
Please Help..
View 3 Replies
View Related
Apr 9, 2007
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
View 5 Replies
View Related
Jul 2, 2004
Is it possible to determine the data size of records in a table. basically I would like to run a select query with a condition and the result will be xKB. I know that this is possible on a database level, but can it be done on a record level (or number of records).
Cheers
H
View 4 Replies
View Related
Sep 11, 2000
I am trying to update a field within one table with the values from another table. With the criteria that another field in each table are equal. What is the correct way to do this. My syntax is all wrong.
thanks
Jason
View 1 Replies
View Related
Feb 26, 2001
I report SQL Server table information (table names, field names, field datatypes, etc.) to my users using an ACCESS front end. Most of this information exists in the system tables. But I can't find a record count per table in the system tables. What's the fastest way to get a record count for all tables?
View 1 Replies
View Related
Jan 27, 2008
Hi!
I have 2 tables (both have the same structure):
ID -> bigint (identity, not for replication, primary key)
Url -> nvarchar(1000)
MainUrl -> nvarchar(1000)
Tbl1 cantains about 0,5 mln records, and tbl2 - 1 mln.
What I need, is to copy records from tbl2 to tbl1. But records in tbl1 are unique, and it can't change. (Unique must be only "Url"; (and ID, but it's automatic)). How can I do this in fast way? Now I'm using SELECT for each record in tbl2 to see if it exist in tbl1. But it's a bit slow... Is there any faster method? (One thing: I'm beginner in databeses, so I'm wrote VB application to transfer records. How can I do it using only Microsoft Sql server?)
--------------
I'm forgot to write, I'm using MsSql 2005.
View 7 Replies
View Related
Nov 15, 2006
I have two tables that have a common column (ID). Now, what i am trying to do is find what is not in one table that is in the other.
For instance:
Table A
ID NAME
1 Tom
2 George
3 Richard
Table B
ID NAME
1 Tom
3 Richard
4 Kevin
With this information, I am trying to write a query that would tell me that Kevin is the only record that doesn't exist in both tables ... like an outlier.
I have tried using something like the following:
SELECT distinct id, name
FROM Table 1 INNER JOIN Table 2
ON Table 1.id <> Table 2.id
Any help would be great. Thanks!
-L
View 3 Replies
View Related
