How to get the system date automatically in the columns

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to get the system date automatically in...

How to get the system date automatically in the columns

Rate Topic

Display Mode

Topic Options

Author

Message

arthi.anan

Posted Wednesday, November 07, 2012 11:56 PM

Grasshopper

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

Stewart "Arturius" Campbell

Posted Thursday, November 08, 2012 12:01 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:48 AM
Points: 3,125, Visits: 4,311

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

arthi.anan

Posted Thursday, November 08, 2012 12:07 AM

Grasshopper

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

Stewart "Arturius" Campbell

Posted Thursday, November 08, 2012 12:45 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 3:48 AM
Points: 3,125, Visits: 4,311

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

McSQL

Posted Thursday, November 08, 2012 3:54 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 146, Visits: 280

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 »

Permissions