June 1, 2009 at 8:51 am
Hello everyone,
I recently have to bulk copy data from our old server to new server. Two tables differ either in data type or size. I opted to use character type format and did export. Now I have a confusion how to import and convert data type according to new table data type.
For example if the old and new table are structured as follows
TABLE A : OLD
columnA Varchar (40),
ColumnB int
TABLE B : NEW
columnA varchar(20)
columnB bit
since character type export is used everydata exported will now be represented as character. Is there any possibility to convert data type during import process?
ta
June 1, 2009 at 11:45 am
Could you please post your BCP command line call (without authentication)?
I just tried:
CREATE TABLE TestBcpOut (ColA VARCHAR(40), ColB INT)
CREATE TABLE TestBcpIn (ColA VARCHAR(20), ColB BIT)
INSERT INTO TestBcpOut
SELECT 'blah', 1
UNION ALL SELECT 'bluff', 0
BCP OUT:
bcp Sandbox.dbo.TestBcpOut OUT test.bcp -S . -T -c
BCP IN:
bcp Sandbox.dbo.TestBcpIn IN test.bcp -S . -T -c
Worked fine. If you get any errors please post the complete error message(s).
Greets
Flo
June 1, 2009 at 3:10 pm
I have used delimeter ',' and also had some text containing ',' in one of the data field which was causing the problem. But I still wondering how to populate the values on the multiple table one by one as identity genereated on one table has to be populated on the other. any help is appreciated.
ta
June 3, 2009 at 8:06 am
To get previous tables Identity, u can use SELECT @@IDENTITY
so if u have
INSERT INTO TableA(ColA,ColB)
VALUES(@ColA,@ColB)
where ColA is not ur Identity column as Identity key gets self generated.
SELECT @TabBColA = @@identity
INSERT INTO TableB(TabColA, TabCloB)
VALUES(@TabBcolA,@TabcloB)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply