CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Bill Software Engineer
Bill Software Engineer

Reputation: 7822

Create table on the fly with Insert Into

Is this possible? I got a big .sql file full of Insert Into statements without the database schema. Can I just create table on the fly?

Here is an example:

INSERT INTO [g_fuel_site] ([SiteID], ... ,[EMVEnabled])
  VALUES('Sep 23 2011 3:05:51:000PM', ... ,0)

EDIT: There is no tables! The script assumed I do!

Upvotes: 0

Views: 4244

Answers (2)

SumGuy
SumGuy

Reputation: 622

Aaron beat me by 20 seconds. For an example change the first insert from:

INSERT INTO [g_fuel_site] ([SiteID],[CurrentOperatingLevelID],[CurrentPriceBookID],  [NumberFuelSaleBuffers],[LinearUnitOfMeasure],[VolumeUnitOfMeasure],[PreAuthAllowed],[StackedSalesAllowed],[MaxLiveDispensers],[AllowedZeroPPUs],[MaxPPU],[MinPPU],[InitialConfigDone],[DispenserOptionModeID],[GenAuthEnabled],[PendingPriceBookID],[AllowPresetWithHandleUp],[UseFixedGradeName],[UseFixedServiceLevelName],[UseFixedGradeProductCodes],[TokenAttendantRcptCtl],[TokenAttendantNtwrkRcptCtl],[TokenAttendantPrpayRcptCtl],[RunAttendantInBufferedMode],[AllowAttendantBalanceQuery],[TokenOrStandardOperation],[TokenPrefix],[EnablePostPayLimit],[PostPayLimit],[EMVEnabled])VALUES('Sep 23 2011  3:05:51:000PM',1,1,2,'CM','L',1,1,12,0,9.9990,0.7500,1,1,1,2,1,0,0,0,0,0,0,0,0,0,'',0,100.0000,0)

to be:

SELECT 
'Sep 23 2011  3:05:51:000PM' [SiteID],
1 [CurrentOperatingLevelID],
1 [CurrentPriceBookID],
2 [NumberFuelSaleBuffers],
'CM' [LinearUnitOfMeasure],
'L' [VolumeUnitOfMeasure],
1 [PreAuthAllowed],
1 [StackedSalesAllowed],
12 [MaxLiveDispensers],
0 [AllowedZeroPPUs],
9.9990 [MaxPPU],
0.7500 [MinPPU],
1 [InitialConfigDone],
1 [DispenserOptionModeID],
1 [GenAuthEnabled],
2 [PendingPriceBookID],
1 [AllowPresetWithHandleUp],
0 [UseFixedGradeName],
0 [UseFixedServiceLevelName],
0 [UseFixedGradeProductCodes],
0 [TokenAttendantRcptCtl],
0 [TokenAttendantNtwrkRcptCtl],
0 [TokenAttendantPrpayRcptCtl],
0 [RunAttendantInBufferedMode],
0 [AllowAttendantBalanceQuery],
0 [TokenOrStandardOperation],
'' [TokenPrefix],
0 [EnablePostPayLimit],
100.0000 [PostPayLimit],
0 [EMVEnabled]
INTO g_fuel_site

After this the table will exist. It just infers column types, and will only work if the first select into contains all the columns that later inserts expect.

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

If you change the first one (and only the first one) to SELECT INTO, yes. Assuming the first INSERT has correctly deducible data types. Note that it won't magically create keys, indexes, constraints, computed columns, etc. for you.

However your example also includes a leading DELETE, which leads me to believe the table already exists. DELETE deletes all of the rows from the table, it doesn't drop the table. If the table doesn't exist, then your script should (a) check if it exists before running a delete and (b) run the first command a SELECT INTO so that it creates it. However you will probably want to define data types (I also find it hard to believe that SiteID is a DATETIME).

IF OBJECT_ID('dbo.g_fuel_site') IS NOT NULL
BEGIN
  DELETE g_fuel_site;
END
ELSE
BEGIN 
  SELECT SiteID = CONVERT(INT, 1), ... 
    INTO dbo.g_fuel_site 
    WHERE 1 = 0; -- creates table but with 0 rows
END

INSERT dbo.g_fuel_site(SiteID, ...) VALUES(...); -- first row
INSERT ...          
INSERT ...

Upvotes: 0

Related Questions