Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Syntax for transfering data from one table to another Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 7:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
What is the best way to load a .csv file's data into an existing table that has no data? Is it best to write a query? My receiving table has an indentity key field that has its seed set at zero.
Post #1398411
Posted Wednesday, December 19, 2012 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 PM
Points: 12,738, Visits: 31,040
BULK INSERT works well, BCP IN,The Import DaTa Wizard or a full blown SSIS package are your options I can think of offhand.

here's a simple example of bulk insert:
CREATE TABLE BULKACT(RAWDATA VARCHAR (8000))
BULK INSERT BULKACT FROM 'c:\Export_o.txt'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n',
FIRSTROW = 1
)



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1398415
Posted Wednesday, December 19, 2012 7:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
That seemed to work well creating a file that closely resembled the below. If I wanted to populate an existing file with an identity key with this data is that possible... the DDL of that table follows the below text:

BuyerInitial,TicketNumber,PONumber,EnteredDate,EndUserLastName,EndUserFirstName,EE Status,CostCenter,TermDate,Lic Available,TransferFrom,TransferTo,EndUserLocation,EquipType,Qty,Lic/SwDesc,Notes/Comments/SpecialInstructions,Unit Price,AllocationDate,Last RewDt,NextRewDt,LastRewPO
OCC,1178180,3073518,9/9/1930,Cahill,John,,527830,,FALSE,,,HFD,LT,1,WinzipStd v. 14,,,11/7/2008,,,
VJF,1371190,3259728,12/21/2000,Rhodes,David,,527425,,FALSE,,,RAD,,2,MS Office Pro 2010,,,,,,


CREATE TABLE [dbo].[tblTransactions](
[PO_Number] [varchar](50) NULL,
[Buyer_Initial] [varchar](50) NULL,
[Quantity] [int] NULL,
[Unit_Price] [money] NULL,
[Software_Description] [varchar](100) NULL,
[AllocationAccount] [varchar](50) NULL,
[PurchaseAccount] [varchar](50) NULL,
[HeatTicketNumber] [varchar](50) NULL,
[PurchaseCostCenter] [varchar](25) NULL,
[PO_Date] [date] NULL,
[Transaction_Date] [date] NULL,
[Transaction_Number] [int] IDENTITY(18000,1) NOT NULL,
[AllocationDate] [date] NULL,
[AllocatedYN] [varchar](10) NULL,
[EndUserFirstName] [varchar](100) NULL,
[EndUserMiddleName] [varchar](100) NULL,
[EndUserLastName] [varchar](100) NULL,
[LAN_ID] [varchar](50) NULL,
[EndUserLocation] [varchar](100) NULL,
[TermDate] [date] NULL,
[EmployeeStatus] [varchar](50) NULL,
[Notes] [varchar](255) NULL,
[LicenseAvailable] [varchar](3) NULL,
[Transaction_Type] [varchar](50) NULL,
[AllocationCostCenter] [varchar](50) NULL,
[SoftwareShortName] [varchar](10) NULL,
[PC_Name] [varchar](100) NULL,
[TransferedSoftware] [varchar](10) NULL,
[TransferToFName] [varchar](50) NULL,
[TransferToLName] [varchar](50) NULL,
[TransferToLANID] [varchar](25) NULL,
[OriginalTransactionNumber] [varchar](6) NULL,
[TransferToCostCenter] [varchar](10) NULL,
[CostCenter] [varchar](10) NULL,
[SWstatus] [varchar](25) NULL,
[SWstatusReason] [varchar](25) NULL,
[EmployeeEmail] [varchar](50) NULL,
CONSTRAINT [PK_dbo.tblTransactions] PRIMARY KEY CLUSTERED
(
[Transaction_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Post #1398425
Posted Wednesday, December 19, 2012 8:13 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 PM
Points: 12,738, Visits: 31,040
well it depends on the details Brian;
in situations where the target table does not exactly match the columns (different column order, subset of columns)
you can attack it a different way: that way the identity column can auto-generate, for example.
INSERT INTO TargetTable(ColumnList)
--64 bit:
SELECT ColumnList
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\data\;',
'SELECT * FROM Export.csv;' )



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1398437
Posted Wednesday, December 19, 2012 8:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
I'm thinking I can change the extension back to .csv and open in Excel, rename the columns to exact names of receiving table, insert missing columns, etc., then rename back to a text file... maybe this would help too?
Post #1398465
Posted Wednesday, December 19, 2012 9:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
What does ColumnList mean?... actually listing the columns?
Post #1398494
Posted Wednesday, December 19, 2012 9:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:42 PM
Points: 12,738, Visits: 31,040
yes exactly...listing the specific columns, which would of course be unique to your table and text files.
for your other question, i don't see any value to fiddling with the data as an excel and back again.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1398502
Posted Wednesday, December 19, 2012 9:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
Something like this?:

INSERT INTO tblTransactions(
PO_Number,
Buyer_Initial,
Qty,
Unit_Price,
Software_Description,
AllocationAccount,
PurchaseAccount,
HeatTicketNumber,
PurchaseCostCenter,
PO_Date,
Transaction_Date,
Transaction_Number,
AllocationDate,
AllocatedYN,
EndUserFirstName,
EndUserMiddleName,
EndUserLastName,
LAN_ID,
EndUserLocation,
TermDate,
EmployeeStatus,
Notes,
LicenseAvailable,
Transaction_Type,
AllocationCostCenter,
SoftwareShortName,
PC_Name,
TransferedSoftware,
TransferToFName,
TransferToLName,
TransferToLANID,
OriginalTransactionNumber,
TransferToCostCenter,
CostCenter,
SWstatus,
SWstatusReason,
EmployeeEmail)
SELECT PO_Number,
Buyer_Initial,
Qty,
Unit_Price,
Software_Description,
AllocationAccount,
PurchaseAccount,
HeatTicketNumber,
PurchaseCostCenter,
PO_Date,
Transaction_Date,
Transaction_Number,
AllocationDate,
AllocatedYN,
EndUserFirstName,
EndUserMiddleName,
EndUserLastName,
LAN_ID,
EndUserLocation,
TermDate,
EmployeeStatus,
Notes,
LicenseAvailable,
Transaction_Type,
AllocationCostCenter,
SoftwareShortName,
PC_Name,
TransferedSoftware,
TransferToFName,
TransferToLName,
TransferToLANID,
OriginalTransactionNumber,
TransferToCostCenter,
CostCenter,
SWstatus,
SWstatusReason,
EmployeeEmail
FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Access Text Driver (*.txt, *.csv)};DefaultDir=C:\Users\DCAMPB\Desktop\;',
'SELECT * FROM Master.csv;' )
Post #1398512
Posted Wednesday, December 19, 2012 9:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, March 13, 2014 2:33 PM
Points: 117, Visits: 315
Got error:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".
Post #1398524
Posted Wednesday, December 19, 2012 10:04 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Monday, March 31, 2014 2:22 PM
Points: 3,725, Visits: 7,067
You will need to create a linked server to reference the example code FROM OPENROWSET('NAMEHERE',

The name used in the example is: 'MSDASQL'


______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience"
Post #1398526
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse