Syntax for transfering data from one table to another

Question

Syntax for transfering data from one table to another

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
December 19, 2012 at 7:37 am

#286362

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 2

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]

Lowell SSC Guru Points: 323518 More actions · Answer 3

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 4

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?

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 5

What does ColumnList mean?... actually listing the columns?

Lowell SSC Guru Points: 323518 More actions · Answer 6

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 7

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;' )

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 8

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)".

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 9

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; Theyll drag you down to their level and beat you with experience

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 10

Is this someting I create or reference from my SQL Server database I already have set up for my receiving table?