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

How to get the system date automatically in the columns Expand / Collapse
Author
Message
Posted Wednesday, November 7, 2012 11:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 6:23 AM
Points: 20, Visits: 55
Hi experts,

I have a table called branch with columns
branch_code
branch_name
branch_desc
modified_by
modified_date

Here i have data for the 1st three columns and the rest 2 fields 'modified_by' and 'modified_date' has to get values automatically from the system...
I did a bulk insert to get the data into those 3 columns but it is expecting 'modified_by' and 'modified_date' fields.
How do i give it separately? the values in those last 2 fields should be given as 'system' and GETDATE()

Please help me with ur scripts

Post #1382282
Posted Thursday, November 8, 2012 12:01 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 4,158, Visits: 5,556
Have you tried binding default constraints to the columns, e.g.
ALTER TABLE [dbo].[branch] ADD  CONSTRAINT [df_mod_by]  DEFAULT (suser_sname()) FOR [modified_by]
GO

ALTER TABLE [dbo].[branch] ADD CONSTRAINT [df_mod_date] DEFAULT (sysdatetime()) FOR [modified_date]
GO



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1382284
Posted Thursday, November 8, 2012 12:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 6:23 AM
Points: 20, Visits: 55
Yes I tried...but that is not working. I'm getting the same error

GMBAR,BARBIL,ODISHA REGION
GMBBS,BHUBANESWAR,ODISHA REGION
GMBEL,BELLARY,SOUTH REGION
GMBHU,BHUJ,GUJARAT
GMKND,KANDLA(SEZ),GUJARAT (SPECIAL ECONOMIC ZONE)
GMHYD,HYDERABAD,SOUTH REGION
GMKOD,KODURU,SOUTH REGION
GMKOL,KOLKATA,EAST REGION
GMMGG,MANDIGOBINDGARAH,NORTH REGION

This is my branch data which is a .csv file which i have to load it using bulkinsert.
The last 2 fields should be given as 'system' and GETDATE()
Post #1382286
Posted Thursday, November 8, 2012 12:45 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 10:51 AM
Points: 4,158, Visits: 5,556
Have you considered using INSERT ... SELECT * FROM OPENROWSET(BULK instead of BULK INSERT?
you can specify the columns to be inserted to with this command.

e.g.
INSERT testAll.[dbo].[branch](branch_code, branch_name, branch_desc)
SELECT * FROM OPENROWSET(BULK 'C:\myBranches.dat', SINGLE_BLOB) a



____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1382297
Posted Thursday, November 8, 2012 3:54 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 2:51 AM
Points: 154, Visits: 297
Have you tried adding default values on the fields within the database?
You sometimes have to set the fields to "Allow NULLs" if the data isn't contained within the set you're trying to import.
But just set a string value of "system" against the user field and the getdate() variable against the date field, as defaults and they should populate automatically with the imported dataset.
Post #1382380
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse