Blind SQL Server insert Statement

SkyHog

SkyHog

Touchdown! Greaser!
Joined
Feb 23, 2005
Messages
18,431
Location
Castle Rock, CO
Display Name
Display name:
Everything Offends Me
I'm trying to insert data into a table in a database in a SQL Server, but I do not know the column names (yes, I know I can get them, but there is a reason I can't grab the names).

Is there a way to do some sort of "INSERT INTO" without giving the column names?

I have the data, in an array, and it will ALWAYS be correct, so I don't need to check it.
 
To insert a row in this table using literal values, you issue the following:

INSERT INTO [dbo].[Customers] ( [ID], [FirstName], [LastName] )
VALUES (1, 'Bill', 'Gates')

Here's the syntax of the command:

INSERT INTO <table_name> | <view_name> [( <column_list> )]
VALUES ( <values_list> )

Since the column list is optional (in [] brackets), you can also do the following. Just make sure that the sequence of the values being inserted matches the sequence of the columns in the table.

INSERT INTO [dbo].[Customers] VALUES (2, 'Larry', 'Ellison')

Although the column list is optional, it is recommended that it is always provided when doing an INSERT. This is to make sure that your INSERT statement will still work and each column will receive the correct value even if new columns are added or inserted between other columns in the table. If another column is added in the table and the same INSERT command is executed without the column list, the following error will be encountered:

Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
 
Keep in mind that inserting without specifying the column names is absolutely retarded because if anyone ever adds a column or changes the table schema a little bit everything busts and no one knows why.
 
What Jesse said. This is good for a one-time load only... not for ongoing production.
 
Ahh, I didn't even try it without the column names, I thought it was required.

The main reason I'm doing it without the column names is because I will be dumping data into different tables after its been preprocessed, but without grabbing the column names. It may be dumb, but the only person that will be changing the database is me, since I am the DB admin, but believe me, the concern is noted.

Maybe I'll take the time to grab the column names. I don't really want to waste the time if I can avoid it though.
 
SkyHog said:
Ahh, I didn't even try it without the column names, I thought it was required.

The main reason I'm doing it without the column names is because I will be dumping data into different tables after its been preprocessed, but without grabbing the column names. It may be dumb, but the only person that will be changing the database is me, since I am the DB admin, but believe me, the concern is noted.

Maybe I'll take the time to grab the column names. I don't really want to waste the time if I can avoid it though.
Click to expand...


Not sure if you're using SQL2000 or 2005, but in either one you can right click on a table, select INSERT from the context menu, and then "script to window" or "script to clipboard" and you'll get pre-formatted text with the column names all set up for you. VERY easy. Takes no time at all.
 
TangoWhiskey said:
Not sure if you're using SQL2000 or 2005, but in either one you can right click on a table, select INSERT from the context menu, and then "script to window" or "script to clipboard" and you'll get pre-formatted text with the column names all set up for you. VERY easy. Takes no time at all.
Click to expand...

This assumes access to SQL Server Manager, which the user will not have, nor would I want them to be messing with.
 
SkyHog said:
This assumes access to SQL Server Manager, which the user will not have, nor would I want them to be messing with.
Click to expand...

Sorry Nick, everything I read up to this point gave me the impression you were the user. ;-)
 
SkyHog said:
Ahh, I didn't even try it without the column names, I thought it was required.

The main reason I'm doing it without the column names is because I will be dumping data into different tables after its been preprocessed, but without grabbing the column names. It may be dumb, but the only person that will be changing the database is me, since I am the DB admin, but believe me, the concern is noted.

Maybe I'll take the time to grab the column names. I don't really want to waste the time if I can avoid it though.
Click to expand...
Grabbing the column names (and data type, if req'd) is a trivial task (assuming 2005) if you want to do it:

Code: 
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'your_table_name'

And I think the "sp_help" stored proc will do approximately the same thing -- and in both 2000 and 2005.
 
SkyHog said:
Ahh, I didn't even try it without the column names, I thought it was required.

The main reason I'm doing it without the column names is because I will be dumping data into different tables after its been preprocessed, but without grabbing the column names. It may be dumb, but the only person that will be changing the database is me, since I am the DB admin, but believe me, the concern is noted.

Maybe I'll take the time to grab the column names. I don't really want to waste the time if I can avoid it though.
Click to expand...

Until you get a better job. Then the next guy walks in and says "Why in the holy hell was he doing this?" :)
 
FlyNE said:
Until you get a better job. Then the next guy walks in and says "Why in the holy hell was he doing this?" :)
Click to expand...

LOL, very true. One thing I've learned from doing this kind of work though, is regardless of how well any programmer does his job, the next guy will always say:

"Why in the holy hell was he doing this?"

I call it the "I'm a better programmer" syndrome. :D
 
SkyHog said:
LOL, very true. One thing I've learned from doing this kind of work though, is regardless of how well any programmer does his job, the next guy will always say:

"Why in the holy hell was he doing this?"

I call it the "I'm a better programmer" syndrome. :D
Click to expand...
I've said that a number of times - occasionally it turns out "I" was the "he" I was disparaging! :D
 
gkainz said:
I've said that a number of times - occasionally it turns out "I" was the "he" I was disparaging! :D
Click to expand...
Not so "occasionally" in my case... More "frequently." :D

I've been known to angrily ask people, "Who wrote this crap?!?!" only to have the answer come back "Um... You did..." more times than I'd care to admit. :redface:
 
that works, too, I think, but I was just leaning towards the Oracle/Microsoft animosities, mostly! :D
 
You must log in or register to reply here.
Back
Top